An error occurred while the batch was being executed when update the column name and set it to auto increment

Question

I have created a table into the database. However, I forget to set my "invoiceID" column to auto-increment, and I wish to edit the column name to "inv_ID"

CREATE TABLE [dbo].[Order] (
    EXEC sp_rename '[inv_Id]', '[invoiceID]', 'COLUMN', INT  NOT NULL,;       
    [inv_date] DATE NOT NULL,
    [cust_Id]  INT  NOT NULL,
    PRIMARY KEY CLUSTERED ([inv_Id] ASC)

);

Error Message:

An error occurred while the batch was being executed.

I am using Microsoft Visual Studio, local database named: Data.mdf — user4562396, Mar 19 '15 at 08:14

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

It looks like you have two questions here:

1) How to change the column-name using sp_rename:

You can do that using:

EXEC sp_rename '<oldname>', '<newname>', 'COLUMN';

2) How to add the auto-increment. That's actually answered in this SO question but it's a bit of a pain if you want to do it without dataloss.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 19 '15 at 08:38

Ruben Steins

2,782
4
27
48

For (1), I get the the error "SQL80001:Incorrect Syntax near "EXEC", I have edited my code as above; correct me if I am wrong....thanks =D – user4562396 Mar 19 '15 at 10:01
Did you read the MSDN-documentation on sp_rename? This is a call to a stored procedure. Don't add it to the CREATE statement. You're not adding a new table, are you? You're changing an existing one. – Ruben Steins Mar 19 '15 at 13:01
I am using local database, my data source is "(LocalDB)\v11.0;AttachDbFilename="C:\Users\xchoo\Documents\Visual Studio 2012\Projects\Data.mdf", then I put this data source as the server name while connecting to an instance of Database Engine, then select window authentication. (follow the sp_rename source) – user4562396 Mar 19 '15 at 16:07
But I get the error saying "Cannot connect to (LocalDB)\v11.0;AttachDbFilename="C:\Users\xchoo\Documents\Visual Studio 2012\Projects\Data.mdf". A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid. ) (Microsoft SQL Server, Error: -1983577829) " – user4562396 Mar 19 '15 at 16:08

An error occurred while the batch was being executed when update the column name and set it to auto increment

1 Answers1