topic Re: SAS SQL Passthrough - Insert records from SAS dataset in SAS Data Management

SAS SQL Passthrough - Insert records from SAS dataset

aj34321 — Mon, 28 Nov 2016 01:04:15 GMT

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

Re: SAS SQL Passthrough - Insert records from SAS dataset

Reeza — Mon, 28 Nov 2016 01:21:49 GMT

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.

Re: SAS SQL Passthrough - Insert records from SAS dataset

aj34321 — Mon, 28 Nov 2016 01:49:13 GMT

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.