SQL Server tables

[mbsuther]

How do I read from SQL Server tables with a period embedded in its name? (e.g SQL Server table name of Analytics.New.Phase1) I get an error 22-322 when trying to read it.

[Reeza]

Try a name literal

'name.test.work'n

However if your database is Analytics, schema is New and table is Phase1 then you do that in how you set up your SQL connection. 

[mbsuther]

Did not work.

Here is a code snippet

libname LumenDB OLEDB init_string="................" schema=dbo;

%global LUMENTABLE;

data _null_;

lumentable='ActivityLog.Entry'n;

call SYMPUT("LUMENTABLE",lumentable;

run;

.

.

.

proc sql;

  create table Lumen1 as

    select EntryID, UserID, ServerID

         from &LumenDB..&LUMENTABLE;

run;

quit;

also tried:

data temp;

  set &LumenDB..'ActivityLog.Entry'n;

run;

quit;

Neither of these worked. Got the ActivityLog.Entry is not a valid SAS name.

[Reeza]

Turn on the following option and try posting a proc datasets result for the library.

Options validvarname=any(

[mbsuther]

still did not work. Posting the code snippet and errors.

[Reeza]

You didn't use the name literal. This is a guess. 

"Table.name"n

The other method that will work - for sure - is a SQL Pass Thru query. 

[Reeza]

You didn't use the name literal. This is a guess. 

"Table.name"n

The other method that will work - for sure - is a SQL Pass Thru query. 

[mbsuther]

I did try 'ActivityLog.Entry'n. It let me assign the value to a variable with no problem. However, I still got the error "table name not recognized" when I used the variable in either a SET or a "Select..." statement.

[Ksharp]

libname x odbc '...' preserve_tab_names=yes preserve_col_names=yes ;

[mbsuther]

I am using OLEDB instead of ODBC. However, i tried these option in the init_string, in provider_string, etc. They gave no errors, but I still get the error "ActivityEntry.Entry is an invalid table name......."

[Ksharp]

Maybe you should check documentation relating to OLEDB, and find some options like preserve_tab_names=yes preserve_col_names=yes Or you could try this system option : option validmemname=extend ;

[mbsuther]

Actually found the issue was not related to how I tried to access the table. The instances actually has two database owners. i put in the correct owner in the "schema'" of the libname and all worked. Thanks for all the suggestions.