• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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();
                               
0
kayhustle
Asked:
kayhustle
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
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:
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
 
anyoneisCommented:
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now