02-07-2013 07:11 AM
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
02-07-2013 07:30 AM
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.
02-07-2013 08:04 AM
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
02-07-2013 08:22 AM
Can you give me an example of the update statement? Are you perhaps crossing databases like this:
set col1 = (some value from a sas dataset);
02-07-2013 08:27 AM
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?
02-07-2013 08:35 AM
So both of these work for me:
libname LCRPT SQLSVR Datasrc=.....;
/*create sqlsrv table*/
/*create local sas dataset*/
testcol='data from NewData';
update lcrpt.test set testcol='SomeNewValue';
update lcrpt.test set testcol=(select testcol from work.newdata);
Can you provide the update statement?
02-07-2013 08:57 AM
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
02-07-2013 08:59 AM
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.
02-07-2013 09:15 AM
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?
02-07-2013 09:38 AM
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.
02-07-2013 09:50 AM
02-07-2013 09:43 AM
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.
02-07-2013 09:53 AM
libname myodbc odbc noprompt="dsn=ntauth;trusted connetcion=yes" reread_exposure=yes;
drop table myodbc.vxdtemp;
/* give everyone 10% pay increase in the SAS table */
data sasfile(drop= sal);
newsal=sal + (.10 * sal);
/*update sqlserver table w/ new salary and newhiredate*/
modify myodbc.vxdemp (dbkey=empno cntllev=rec) key=dbkey;
if _iorc_=0 then do;