SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Alter table in SQL database

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Alter table in SQL database

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.

 


Accepted Solutions
Solution
‎10-07-2015 09:51 AM
Respected Advisor
Posts: 4,173

Re: Alter table in SQL database

[ Edited ]
Posted in reply to ram_adhikari_jacks_sdstate_edu

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.

 

View solution in original post


All Replies
Super User
Posts: 19,869

Re: Alter table in SQL database

Posted in reply to ram_adhikari_jacks_sdstate_edu
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.
Occasional Contributor
Posts: 18

Re: Alter table in SQL database

I am in MS SQL

Occasional Contributor
Posts: 18

Re: Alter table in SQL database

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

Super User
Posts: 19,869

Re: Alter table in SQL database

Posted in reply to ram_adhikari_jacks_sdstate_edu
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;
Super User
Posts: 5,438

Re: Alter table in SQL database

Posted in reply to ram_adhikari_jacks_sdstate_edu

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.

Data never sleeps
Solution
‎10-07-2015 09:51 AM
Respected Advisor
Posts: 4,173

Re: Alter table in SQL database

[ Edited ]
Posted in reply to ram_adhikari_jacks_sdstate_edu

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.

 

Occasional Contributor
Posts: 18

Re: Alter table in SQL database

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

 

 

 

Respected Advisor
Posts: 4,173

Re: Alter table in SQL database

Posted in reply to ram_adhikari_jacks_sdstate_edu

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

 

Super User
Posts: 5,438

Re: Alter table in SQL database

Posted in reply to ram_adhikari_jacks_sdstate_edu

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

 

Data never sleeps
Occasional Contributor
Posts: 18

Re: Alter table in SQL database

Thank you so much Patrick

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 828 views
  • 1 like
  • 4 in conversation