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'.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.