Prev: Create data set if condition
Next: SAS DSD Option on a delimited file
From: Richard A. DeVenezia on 24 Sep 2009 09:37
On Sep 24, 7:24 am, jim.1s...(a)YAHOO.COM (Jim Groeneveld) wrote:
> Hi friends,
>
> My problems consists of wanting to modify the contents of an MS SQL table on
> a server from SAS, more specifically emptying it (removing rows and columns)
> and refilling it. Deleting the rows succeeds, but the columns doesn't. My
> code is (basically):
>
> LIBNAME SQLserv1 ODBC DSN="SQL test server" USER=XXXXX PWD=YYYYY;
> %LET DataBase = SQLserv1./*FlowDevelopment.SasSync.*/BlindingCodes;
> %LET dBaseVar = <list of variables, separated by a comma>
>
> PROC SQL;
> * A. Make server table empty, no column, no rows;
> * 1b. delete all rows;
>   DELETE FROM &DataBase ; * Successful! ;
> * 2b. delete columns;
>   ALTER TABLE &DataBase DROP &dBaseVar;
>   .........
> QUIT;
>
> I don't have creation rights on the server, but I have access and modifying
> rights for the concerning single table. That is why I have to rebuild the
> table from scratch instead of just CREATing one from a local dataset.
>
> The error that I get from step 2b is:
> ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC engine.
> ERROR: View SQLSERV1.BlindingCodes cannot be altered.
>
> Now, what does the first ERROR mean? I am not using UPDATE, but ALTER. Isn't
> this possible with the ODBC driver? Or don't I still have the correct access
> rights?
> How come the second ERROR? My db-admin says SQLSERV1.BlindingCodes isn't a
> view, but a real table.
>
> Regards - Jim.

Jim:

You could try via SQL pass-through.

From the helps

Proc SQL;
CONNECT TO dbms-name <AS alias> <(<database-connection-arguments>
<connect-statement-arguments> )>;
EXECUTE (dbms-specific-sql-statement) BY alias;


Also, have you checked if you can alter the table via any other ODBC
interfacing or SQL server client ?

--
Richard A. DeVenezia
http://www.devenezia.com
From: Jim Groeneveld on 24 Sep 2009 10:48
Hi Richaard,

To start with your last Q: no I didn't. Something to consider.

Regarding you suggestion: am I wrong thinking that filling a remote SQL
table from a local SAS dataset is almost impossible via the EXEC statement?
I mean the EXEC statement needs a remote SQL command, running entirely in
the remote environment. It can't make use of local variable information,
while that is what I would want: copy a local dataset to an existing remote
table, without CREATing the table.

I hoped someone could give a reason and solution for the ERROR.

But you gave me thoughts to consider; I'll try to DELETE / ALTER+DROP+ADD
via SQL pass-through and EXEC and to INSERT the local file using the ODBC
engine; tomorrow.......

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld


On Thu, 24 Sep 2009 06:37:43 -0700, Richard A. DeVenezia
<rdevenezia(a)GMAIL.COM> wrote:

>On Sep 24, 7:24 am, jim.1s...(a)YAHOO.COM (Jim Groeneveld) wrote:
>> Hi friends,
>>
>> My problems consists of wanting to modify the contents of an MS SQL table on
>> a server from SAS, more specifically emptying it (removing rows and columns)
>> and refilling it. Deleting the rows succeeds, but the columns doesn't. My
>> code is (basically):
>>
>> LIBNAME SQLserv1 ODBC DSN="SQL test server" USER=XXXXX PWD=YYYYY;
>> %LET DataBase = SQLserv1./*FlowDevelopment.SasSync.*/BlindingCodes;
>> %LET dBaseVar = <list of variables, separated by a comma>
>>
>> PROC SQL;
>> * A. Make server table empty, no column, no rows;
>> * 1b. delete all rows;
>> DELETE FROM &DataBase ; * Successful! ;
>> * 2b. delete columns;
>> ALTER TABLE &DataBase DROP &dBaseVar;
>> .........
>> QUIT;
>>
>> I don't have creation rights on the server, but I have access and modifying
>> rights for the concerning single table. That is why I have to rebuild the
>> table from scratch instead of just CREATing one from a local dataset.
>>
>> The error that I get from step 2b is:
>> ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC
engine.
>> ERROR: View SQLSERV1.BlindingCodes cannot be altered.
>>
>> Now, what does the first ERROR mean? I am not using UPDATE, but ALTER. Isn't
>> this possible with the ODBC driver? Or don't I still have the correct access
>> rights?
>> How come the second ERROR? My db-admin says SQLSERV1.BlindingCodes isn't a
>> view, but a real table.
>>
>> Regards - Jim.
>
>Jim:
>
> You could try via SQL pass-through.
>
>From the helps
>
>Proc SQL;
> CONNECT TO dbms-name <AS alias> <(<database-connection-arguments>
><connect-statement-arguments> )>;
> EXECUTE (dbms-specific-sql-statement) BY alias;
>
>
>Also, have you checked if you can alter the table via any other ODBC
>interfacing or SQL server client ?
>
>--
>Richard A. DeVenezia
>http://www.devenezia.com
From: Charles Harbour on 24 Sep 2009 12:26
You might try running an ODBC trace, to see exactly what the code is that's
being submitted. I asked around the office what the ODBC driver really
does, and the answer was 'it depends'. Sound familiar? :-)

The depends in this case refers to how the odbc driver actually executes the
sql in the native db code--and based on the error you received, it looks
like it's doing it through a view (implying you can't alter the structure).
Whether that's based on your permissions or just the way the driver works
is a mystery--but if you run that trace, that should tell you where to start
looking.

And--pass-through is your friend.

HTH,
CH
 | 
Pages: 1
Prev: Create data set if condition
Next: SAS DSD Option on a delimited file