SQL Server tables

Reply
Occasional Contributor
Posts: 13

SQL Server tables

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.

Grand Advisor
Posts: 17,308

Re: SQL Server tables

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. 

 

Occasional Contributor
Posts: 13

Re: SQL Server tables

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.

Grand Advisor
Posts: 17,308

Re: SQL Server tables

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

Options validvarname=any(
Occasional Contributor
Posts: 13

Re: SQL Server tables

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

Grand Advisor
Posts: 17,308

Re: SQL Server tables

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. 

Grand Advisor
Posts: 17,308

Re: SQL Server tables

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. 

Occasional Contributor
Posts: 13

Re: SQL Server tables

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.

Grand Advisor
Posts: 9,567

Re: SQL Server tables

libname x odbc '...' preserve_tab_names=yes preserve_col_names=yes ;
Occasional Contributor
Posts: 13

Re: SQL Server tables

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......."

Grand Advisor
Posts: 9,567

Re: SQL Server tables

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 ;
Occasional Contributor
Posts: 13

Re: SQL Server tables

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.

Ask a Question
Discussion stats
  • 11 replies
  • 734 views
  • 0 likes
  • 3 in conversation