BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6

Hello Everyone,

I am trying to use pass through SQL to append new data to an existing SQL Server table.  I get this error pointing to the libname reference devsrv: "Expecting a : .".  I use the same libname reference when I create tables on the same server and and it has always worked.  I suspect the error is caused by something else in my code.  Does anyone have any suggestions?  Any input would be greatly appreciated.  Thank you!

Here is my code:

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

     connect to odbc(dsn="SERV1");

          select *

          into devsrv.cust_table1

          from cust_data;

     disconnect from odbc;

QUIT;

RUN;

12 REPLIES 12
DBailey
Lapis Lazuli | Level 10

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

         create table devsrv.cust_table1 as

          select *

          from cust_data;

QUIT;

GlenGold
Calcite | Level 5

I'm not sure exactly what you are trying to accomplish, but I've used the into: to create a macro variable in PROC SQL.

something like this:

PROC SQL;

select max(order_date) into: most_recent_shipment

from cust_data

where ship_ind = 1;

quit;

PROC SQL;

create table late_ship as

select order_id

from order_table

where ship_ind = 0

and order_date < &most_recent_shipment;

quit;

ballardw
Super User

SAS SQL instruction into is attempting to place the data into a SAS macro variable which should be indicated by the : .

DBailey
Lapis Lazuli | Level 10

reread your question...try this:

PROC SQL;

     connect to odbc(dsn="SERV1");

     execute (

          select *

          into cust_table1

          from cust_data

     ) by odbc;

     disconnect from odbc;

QUIT;

RUN;

KevinC_
Fluorite | Level 6

Thank you, DBailey!

I tried again using your code (exactly as you typed) and I got this error:

CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'cust_data'.

The cust_data  dataset exits.  Not sure why it doesn't like it.  Any suggestions?  Thanks again!

LinusH
Tourmaline | Level 20

First, this is becoming a bit SQL Server specific question.

But to read your question again, by doing select into in SQL Server. it means that you are creating a table based on the structure in the select statement.

So in your case, your query fails because the table already exists (or does it exist within SQL Server?).

But you said you wish to insert data into your table, then you need to use the insert into construct instead/as well.

If your table cust_data resides in SAS, you can't use explicit pass-thru.

Data never sleeps
KevinC_
Fluorite | Level 6

Thank you, LinusH.

I changed my SAS code to this:

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

     connect to odbc(dsn="SERV1");

     execute (

          Insert into devsrv.cust_table1 (cust_id, loan_count, category)

          select cust_id, loan_count, category

          from cust_data

     ) by odbc;

     disconnect from odbc;

QUIT;

RUN;

This is the error I got:

CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'devsrv.cust_table1'.

Any suggestions from anyone?  Thanks.:)


Tom
Super User Tom
Super User

Now that you are sending the SQL code directly to ODBC yourself you need to use syntax that ODBC can interpret.  It knows nothing of the SAS libref DEVSRV that you created so you need to use the native syntax for referencing the table you want. In your LIBNAME statement you linked the SAS libref of DEVSRV to the DBO schema.

You probably need to change devsrv.cust_table1 to dbo.cust_table1.

Patrick
Opal | Level 21

You haven't told us yet if your table "cust_data" is a database table or a SAS WORK table. Assuming it's a SAS work table code as below should do (not tested):

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

libname devsrv ODBC DSN="SERV1" schema=dbo DBCOMMIT=0;

proc append base=devsrv.cust_table1 data=cust_data force /* nowarn */ ;

run;

To take full advantage of "DBCOMMIT=0" you might also need to set values other than the defaults for:

"INSERT_SQL" and "INSERTBUFF"

DBailey
Lapis Lazuli | Level 10

The odbc connections include a default library which if you didn't specify becomes MASTER.  On the SQLServer, there is a database that you would include and the syntax is

     databasename.schema.tablename.  So...

PROC SQL;

     connect to odbc(dsn="SERV1");

     execute (

          select *

          into databasename.schema.cust_table1

          from databasename.schema.cust_data

     ) by odbc;

     disconnect from odbc;

QUIT;

If this doesn't work, then you probably will need to get with the sqlserver dba to help understand the correct databasename and schema.

CTorres
Quartz | Level 8

My contribution:

To use the SQL Pass-Through facility you don't need the libname but instead you need to do a select * from connection.

Try this:

PROC SQL;

     connect to odbc(dsn="SERV1");

     select * from connection to odbc (

          **** Here the sql code you want the SQL server to execute ****

         );

     disconnect from odbc;

QUIT;

RUN;

Regards,

Lenvdb
Quartz | Level 8

Did you ever get a solution with this issue?

I am now having a similar issue and found by inserting this line I can do it:

USE <databasename>;

PROC SQL;

     connect to odbc as sasx(dsn="SERV1");

Execute(USE &db; /*==specify your database===*/

Insert into cust_table1 (cust_id, loan_count, category)

          select cust_id, loan_count, category

          from cust_data

)by sasx;

     disconnect from sasx;

QUIT;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 16694 views
  • 0 likes
  • 9 in conversation