BookmarkSubscribeRSS Feed
aj34321
Quartz | Level 8

Hi All,

Im trying to insert records into SQL Server table through SQL Passthrough from SAS Dataset. Below is the code which Im using and error I get. Looks like i cannot use the SAS Dataset reference in a SQL Passthrough. Please advice how can i fix this.

Note:

On SQL Server, i dont have option to create a temporary table.

I can use a normal sql insert, but wondering how can i use it this way.

 

 

connect to sqlsvr (&sqlconn.);
execute
(
insert into sqlschema.sqltable
(
col1,
col2,
col3
)
select
col1,
col2,
col3
from mylib.sas_dataset;
) by sqlsvr;
disconnect from sqlsvr;

 

ERROR: CLI execute error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'mylib.sas_dataset'.

2 REPLIES 2
Reeza
Super User

SQL Pass Through is a direct connection to your server, and this means you lose access to your SAS data sets. 

 

One possible workaround is to create a macro that generates the records you need to insert, but if you can use a standard insert that's easier to program.

aj34321
Quartz | Level 8

Thanks Reeza.

 

One more query - I'm trying to use BULKLOAD option and getting below error :

ERROR: BCP=/BULKLOAD= option not supported by this datasource.
ERROR: Error in the LIBNAME statement.

 

odbc.ini has EnableBulkLoad=1

Note: I'm using SAS from AIX environment.

 

Request our advise on this.

 

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 3144 views
  • 0 likes
  • 2 in conversation