Alter Table Problem

When I try to run the following command on my table to change the data types:
ALTER TABLE CATConstr_2869026820 MODIFY( CategoryName  nvarchar(MAX)     not null, ApiTalk VARBINARY(MAX) not null, Account VARBINARY(MAX) not null);

I get the following exception:
 Incorrect syntax near '('

I don't get it since the table exists as well as each column.

I am using C#/.net.
here is the C# code:
SqlCommand setlen = new SqlCommand("ALTER TABLE " + tablename + " MODIFY( CategoryName  NVARCHAR(MAX)     not null, ApiTalk VARBINARY(MAX) not null, Account VARBINARY(MAX) not null);", connection);
setlen.ExecuteNonQuery();
                               
LVL 1
kayhustleAsked:
Who is Participating?
 
LowfatspreadCommented:
does it work if you break it down into 3 separate alter table statement and modify a single column each time?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ALTER TABLE CATConstr_2869026820 ALTER COLUMN  CategoryName  nvarchar(MAX)     not null, ApiTalk VARBINARY(MAX) not null, Account VARBINARY(MAX) not null
0
 
ptjcbCommented:
Do not use MODIFY and drop the paranthesis:

something like this should work:


ALTER TABLE CATConstr_2869026820 ALTER COLUMN CategoryName  nvarchar(MAX)     NOT null, ApiTalk VARBINARY(MAX) NOT  null, Account VARBINARY(MAX)  NOT null;

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kayhustleAuthor Commented:
Ok, so this is my new statement:
ALTER TABLECATConstr_2869026820 ALTER COLUMN CategoryName NVARCHAR(MAX) NOT null, ApiTalk VARBINARY(MAX) NOT null, Account VARBINARY(MAX) NOT null;

And now I get the following:
Incorrect syntax near ','.
0
 
kayhustleAuthor Commented:
ALTER TABLE CATConstr_2869026820 ALTER COLUMN CategoryName NVARCHAR(MAX) NOT null, ApiTalk VARBINARY(MAX) NOT null, Account VARBINARY(MAX) NOT null;

thats the actual statement
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think you need 3 separate alter statements

ALTER TABLE CATConstr_2869026820 ALTER COLUMN CategoryName NVARCHAR(MAX) NOT null
ALTER TABLE CATConstr_2869026820 ALTER COLUMN ApiTalk VARBINARY(MAX) NOT null
ALTER TABLE CATConstr_2869026820 ALTER COLUMN Account VARBINARY(MAX) NOT null
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I didn;t c ur post 'LowFatSpead'
0
 
anyoneisSoftware DeveloperCommented:
Use SQLProfiler to see what is actually getting sent to SQL Server when you generate a query from code. It can often save you a lot of time. I typically run under a template with the event classes:

Security Audit
 AuditLogin
 AuditLogout
Sessions
 ExistingConnection
Stored Procedures
 RPC:Completed
 SP:StmtCompleted
TSQL
 SQL:BatchCompleted

Also, I filter out a few things:
ApplicationName
 Not Like
  SQL Profiler
  SQL Agent%
  Report%

But if you are still seeing too much data, add filters like DatabaseName, UserName, etc.

David


0
 
ptjcbCommented:
Yes, I tried it as one statement and it failed, but it worked as three separate statements.

ALTER TABLE CATConstr_2869026820 ALTER COLUMN CategoryName NVARCHAR(MAX) NOT null;
ALTER TABLE CATConstr_2869026820 ALTER COLUMN ApiTalk VARBINARY(MAX) NOT null;
ALTER TABLE CATConstr_2869026820 ALTER COLUMN Account VARBINARY(MAX) NOT null;
0
 
kayhustleAuthor Commented:
Yes it worked for me too, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.