BookmarkSubscribeRSS Feed
mbsuther
Calcite | Level 5

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.

11 REPLIES 11
Reeza
Super User

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
Calcite | Level 5

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
Super User

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

Options validvarname=any(
mbsuther
Calcite | Level 5

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

Reeza
Super User

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
Super User

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
Calcite | Level 5

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
Super User
libname x odbc '...' preserve_tab_names=yes preserve_col_names=yes ;
mbsuther
Calcite | Level 5

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
Super User
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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1503 views
  • 0 likes
  • 3 in conversation