topic Re: SQL Server tables in SAS Programming

SQL Server tables

mbsuther — Wed, 20 Apr 2016 12:32:42 GMT

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.

Re: SQL Server tables

Reeza — Wed, 20 Apr 2016 12:38:08 GMT

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.

Re: SQL Server tables

mbsuther — Wed, 20 Apr 2016 15:32:10 GMT

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.

Re: SQL Server tables

Reeza — Wed, 20 Apr 2016 16:01:29 GMT

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

Options validvarname=any(

Re: SQL Server tables

mbsuther — Wed, 20 Apr 2016 21:16:45 GMT

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

Re: SQL Server tables

Reeza — Wed, 20 Apr 2016 21:38:17 GMT

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.

Re: SQL Server tables

Reeza — Wed, 20 Apr 2016 21:38:18 GMT

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.

Re: SQL Server tables

Ksharp — Thu, 21 Apr 2016 03:08:12 GMT

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

Re: SQL Server tables

mbsuther — Thu, 21 Apr 2016 12:59:46 GMT

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.

Re: SQL Server tables

mbsuther — Thu, 21 Apr 2016 13:52:21 GMT

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

Re: SQL Server tables

Ksharp — Fri, 22 Apr 2016 01:07:13 GMT

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 ;

Re: SQL Server tables

mbsuther — Tue, 26 Apr 2016 11:34:58 GMT

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.