Alter table in SQL database

[ram_adhikari_jacks_sdstate_edu]

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.

[Reeza]

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.

[ram_adhikari_jacks_sdstate_edu]

I am in MS SQL

[ram_adhikari_jacks_sdstate_edu]

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

[Reeza]

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;

[LinusH]

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.

[Patrick · Accepted solution]

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.

[ram_adhikari_jacks_sdstate_edu]

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

[Patrick]

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

[LinusH]

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.

Br

Linus

[ram_adhikari_jacks_sdstate_edu]

Thank you so much Patrick