BookmarkSubscribeRSS Feed
Jamie
Calcite | Level 5

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

14 REPLIES 14
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

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

Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

,

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
Calcite | Level 5

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

Thanks to you both for your help, much appreciated.

Haikuo
Onyx | Level 15

,

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
SAS Employee

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 14 replies
  • 3166 views
  • 0 likes
  • 4 in conversation