Creating tables in EG via SQL Server ODBC Connection

[Jamie]

Hi folks,

We have a library on SQL Server 2008 with read/write/update etc permissions.

The library has been set up in SMC and we can create tables on it without issues via EG.

The issue is when we try to update/sort etc. We get the following error:

ERROR: The ODBC table test has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing

       object. This table will not be replaced. This engine does not support the REPLACE option.

I have tried searching the web with not much luck. Is this to do with the ODBC connection? Do we need to perhaps use passthrough code and create the table with native SQL Server code rather than SAS/Data step?

Thanks in advance

Jamie

[DBailey]

Are you perhaps running a proc sort with no output dataset?  I don't think that works on remote databases.  I am confused by your last statement.  Does the table already exist and you are trying to replace it?  If so, then you'll need to explicitly drop the table prior to running the create statement.

[Jamie]

Hi,

Even with a proc sql update table statement I get this error. Your right in that a workaround is to drop and recreate the table, which does work. I was just wondering why we cannot update the table directly. Im sure as you said it will be something to do with the ODBC/remote library connection but wanted an explicit reason or solution.

Thanks for your input

[DBailey]

Can you give me an example of the update statement?  Are you perhaps crossing databases like this:

proc sql;

update sqlsvr.tab1

set col1 = (some value from a sas dataset);

quit;

[Jamie]

thanks again

yeah that's right in my example we were updating a sqlserver table with a sas table.

the sql server table was however created from sas code originally - is the cross database update the issue?

[DBailey]

So both of these work for me:

libname LCRPT SQLSVR Datasrc=.....;

/*create sqlsrv table*/

data lcrpt.test;

testcol='TestVal';

run;

/*create local sas dataset*/

data work.NewData;

testcol='data from NewData';

run;

proc sql;

update lcrpt.test set testcol='SomeNewValue';

update lcrpt.test set testcol=(select testcol from work.newdata);

quit;

Can you provide the update statement?

[Jamie]

so your code worked for me aswell :-0

It failed the second time, because as the error says it cannot REPLACE the table test due to the ODBC engine not supporting it. So thats probably answered my question in that the issue was not with updating a table but recreating a table which already exists

Thanks very much for your time and input

[Haikuo]

Comparing to SQLSVR engine, generic ODBC is crippled in many ways, that is why people pay a premium for SQLSVR engine. So there maybe a chance that ODBC is the culprit. Why not using pass-through, so you don't have to worry about possible "lost in translation"? Not only that, if you don't have SAS/SPDS, the odds is that pass-through will deliver much better performance as well.

Haikuo

[DBailey]

I don't think you can use pass-through to update based on a local datasets.

[Haikuo]

Not directly. If the "replace" , not the 'create' , is the problem, you can always convert/copy a local SAS table into SQL server table, and then do the pass-through. Am I missing something here?

[DBailey]

certainly an option.  As with most cases, the actual performance curve would depend on how many rows you upload vs. how many rows are being updated.  Would require testing.  In the OP's case, though, I don't think the issue was performance.

[Haikuo]

,

I agree, I, too, have a feeling that "performance" may not be OP's concern. We do lots of merges, joins on millions of obs daily, the approach I suggested is widely used. It started from an incidence that 3-hour running by Proc SQL dropping to 20-minutes using pass-thru.

Haikuo

[Jamie]

unfortunately a proc setinit does not show a licence for SQLSVR only ODBC 😞

Thanks to you both for your help, much appreciated.

[Haikuo]

,

If you are using pass-through, you don't need SQLSVR engine, as hinted by its name"pass-through", it literally passes though SQL code to your SQL server side and executed by your SQL server. Two things considered:

1. Like Dbailey pointed out, first you need to create a SQL server table based on your SAS table using ODBC engine (the libname part of his code should help).

2. The code wrapped in the pass-through wrapper has to be SQL server code, NOT SAS proc sql code, they are largely the same, but there will be some differences, so you will have to dig into the docs to figure it out.

if you have never used pass-through, search it, and in 10 minutes you will be on your way.

Haikuo

[VarshaD]

libname myodbc odbc noprompt="dsn=ntauth;trusted connetcion=yes" reread_exposure=yes;
proc sql;
drop table myodbc.vxdtemp;
quit;
data myodbc.vxdemp;
set myodbc.emp;
run;
/* give everyone 10% pay increase in the SAS table */
data sasfile(drop= sal);
set myodbc.vxdemp;
newsal=sal + (.10 * sal);
newhiredate=Today();
run;
/*update  sqlserver table w/ new salary and newhiredate*/

data myodbc.vxdemp;
set sasfile;
modify myodbc.vxdemp (dbkey=empno cntllev=rec) key=dbkey;

if _iorc_=0 then do;
  sal=newsal;
hiredate=newhiredate;
  replace;
end;
else
_error_=0;
run;