topic Re: Alter table in SQL database in SAS Data Management

Alter table in SQL database

ram_adhikari_jacks_sdstate_edu — Mon, 05 Oct 2015 15:44:29 GMT

Hi everyone,

I am trying to update the table named "test" in the in the SQL database. I have tried the libname option as;

libname PIlibt odbc noprompt="uid=myuid;

pwd=mypassword;

dsn=mydsn;"

stringdates=yes;

PROC SQL;

ALTER TABLE PILIBT.TEST

ADD MYVAR NUM

DROP AGE;

QUIT;

This statemnt gave me an error message :

ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC engine.

ERROR: View PILIBT.TEST cannot be altered.

Next I am trying to use pass through facility option using the the code below and my code is not correct.

PROC SQL;

CONNECT TO ODBC AS MYCON

(DSN=mydsn USER=myuid PASSWORD=mypassword);

ALTER TABLE TEST

ADD NEWVAR NUM

DROP AGE;

DISCONNECT FROM MYCON;

QUIT;

could you help me fixing this codes to update my table.

Thanks for your support.

Re: Alter table in SQL database

Reeza — Mon, 05 Oct 2015 15:57:42 GMT

When you use Pass Through your code needs to comply with the SQL requirements of your DB. What type of DB are you on, Oracle SQL, MS SQL, etc? The SQL would need to match that system.

Re: Alter table in SQL database

ram_adhikari_jacks_sdstate_edu — Mon, 05 Oct 2015 16:28:55 GMT

I am in MS SQL

Re: Alter table in SQL database

ram_adhikari_jacks_sdstate_edu — Mon, 05 Oct 2015 16:35:36 GMT

Hi Reeza,

I am updating in the MS SQL. It does not let me add or drop the columns in the the existing tables in the database.

Thanks,

Ram

Re: Alter table in SQL database

Reeza — Mon, 05 Oct 2015 17:47:57 GMT

Are you using something like the following. Please post your full code that doesn't work. Also, verify you have permissions to add/drop columns.

Alter table my_schema.my_table ADD column_b VARCHAR(20) NULL;

Re: Alter table in SQL database

LinusH — Mon, 05 Oct 2015 20:01:33 GMT

What is the use case for altering a SQL Server table from SAS?

SAS programs is normally not a DBA tool, unless you try to build it into some kind of application.

The easiest should be to use SQL Server Management Studio.

Re: Alter table in SQL database

Patrick — Mon, 05 Oct 2015 22:46:16 GMT

I believe the following SAS note applies also for SQL Server http://support.sas.com/kb/37/015.html

Your pass-through SQL code needs to be in an EXECUTE() block as shown in the SAS Note.

The pass-through SQL code must be valid SQL syntax in the flavor of the database.

Re: Alter table in SQL database

ram_adhikari_jacks_sdstate_edu — Tue, 06 Oct 2015 20:10:31 GMT

Hi Patrick,

Thank you for the exec options. The code that you provided works when creating additional columns in the table, but it does not work when i try to drop the columns from the existing table.

When Submitted the code below error was generated

PROC SQL;

CONNECT TO ODBC

(DSN=mydsn USER=myuid PASSWORD=mypassword);

EXEC (ALTER TABLE DBO.TEST

drop Age) BY ODBC;

DISCONNECT FROM ODBC;

QUIT;

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]'Age' is not a

constraint. : [Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop constraint.

See previous errors.

Could you help me fix this.

Thanks

Re: Alter table in SQL database

Patrick — Tue, 06 Oct 2015 21:12:56 GMT

Looks like your SQL syntax is incorrect for dropping a column. The error you get 'Age' is not a constraint indicates that this would be valid syntax for dropping a constraint.

I believe the valid syntax would be:

ALTER TABLE DBO.TEST

drop column Age

Re: Alter table in SQL database

LinusH — Tue, 06 Oct 2015 23:44:16 GMT

We are ready to help, and share knowledge. But this is not a free of charge ordering service. So please phrase differently from now on ("Could you help me fix this").

Second, this is not SAS syntax, it's SQL Server, and something you clearly could have googled yourself.

Now that@Patrick had the kindness to answer your inquiry, make sure to mark it as a solution.

Linus

Re: Alter table in SQL database

ram_adhikari_jacks_sdstate_edu — Wed, 07 Oct 2015 13:53:20 GMT

Thank you so much Patrick