Problem With ALTER TABLE Syntax

Jul 1, 2005

I finally found a way to "deploy" my local SqlServerExpress (SSE) database to the remote Sql2K server... In VisualWebDeveloper (VWD) I can create the table definition and then save the creation sql script and use that in SSE Express Manager while connected to my remote DB. I am describing this because I'm so surprised no one has had problems with this as thousands of developers, some very unexperienced (as me maybe!), are trying the same situation now that some are offering 2.0 hosting... Well I thought this was a great idea until the Sql2K server is returning error messages on the script VWD created. So please could you help since I'm not that good at complex sql scripting. It seems the error comes from the ALTER TABLE syntax:BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONCREATE TABLE dbo.Table2 ( prID int NOT NULL IDENTITY (1, 1), DateInserted datetime NOT NULL, Title nvarchar(100) NOT NULL, Description nvarchar(MAX) NULL, CategoryID smallint NOT NULL, DateLastUpdated datetime NULL, Price int NOT NULL, SpecialPrice int NULL, ImgSuffix nvarchar(5) NULL, ImgCustomWidth smallint NULL )  ON [PRIMARY]  TEXTIMAGE_ON [PRIMARY]GOALTER TABLE dbo.Table2 ADD CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED ( prID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GOCOMMIT

View 2 Replies


ADVERTISEMENT

Alter Table Syntax

Aug 1, 2001

I keep getting a syntax error when I try to execute the following statement from Query Analyzer:

Alter Table Table1
Alter Column Field1 Int

I am getting the following error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'column'.

I am converting Field1 from a varchar to int, is this not allowed?

TIA,

Chris

View 2 Replies View Related

Syntax Error ALTER TABLE

Jul 25, 2006

HiHaving a problem with a ms sql 2000 server. The script below wascreated i SQL manager 2005 lite and gives a syntax error near '('ALTER TABLE [dbo].[Community_ActivityLog]ADD CONSTRAINT [PK_Community_Errors]PRIMARY KEY CLUSTERED ([activity_ID])WITH (PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)ON [PRIMARY]GOAny ideas of what might be wrong?/Michael

View 1 Replies View Related

Syntax To Add 2 Constraints Using Alter Table?

Jul 20, 2005

Just started learning SQL recently.But one thing i'm still not clear on is about altering relationships betweentables after they've been created.Instead of creating a foreign key when the table is first created - i createthe table and then run a query to set the foreign key and relationship(one-to-one, one-to-many etc)Anyways, long story short is i want to create a one-to-one relationship witha table but am having problems with adding more than one constraint at atime when altering a table.Understand yet? Easiest thing to do is show you:I have 2 tables: Branch_Table and Employee_TableI want to create a one-to-one relationship between emp_id on theBranch_Table and manager_id on the Employee_Table.The SQL i've written which doesn't seem to work is:ALTER TABLE Branch_TableAdd Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)Add Constraint Branch_Table_UQ1 Unique (manager_id)References Employee_Table (emp_id));Am having trouble with that second Add constraint (UQ1 unique). I know it'ssomething to do with the Add syntax above.So basically, my question is can i create a one-to-one relationship withjust the one SQL Query? And how would i do it?Many thanks in advance.

View 6 Replies View Related

Syntax Error With "alter Table" Statement

Mar 27, 2001

Folks!

What is wrong with my syntax with the following command?:

alter table EmployeeInfo alter column OriginDate smalldatetime not null default getdate()

I'm getting:

Incorrect syntax near the keyword 'default'

Currently, in my table OriginDate is nullable with no default.
Thanks in advance for your help!

APF

View 2 Replies View Related

Alter Table Syntax Error - Anyone Sees The Error?

Nov 24, 2004

Hello,

the following alter table statement:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED

is answered with:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.

which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.

Note that:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL

DOES get executed, and

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY

produces the same error.

Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.

So - anyone an idea?

View 4 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

INCORRECT SYNTAX NEAR STRING FOR ALTER SQL

Oct 20, 2007

HELP
I am trying to create a new column for every file in a folderbut i keep getting an sql exception  - incorrect syntax near ' whatever the value of the file name is'it works if i just type in the value directlymy code look like this
 fsofolder = CreateObject("Scripting.FileSystemObject")
        folder = fsofolder.GetFolder("the path to the Files")
        files = folder.Files
        For Each objfile In files
            sname = objfile.Name
                  
            cmd3.CommandText = "ALTER TABLE NEW ADD " & "' " & sname & " ' " & " nvarchar(MAX)"

            DatabaseConnection.Open()

            Try

                cmd3.Connection = DatabaseConnection
                cmd3.ExecuteNonQuery()
            Catch ex As SqlException
                MsgBox(ex.Message)
            End Try

            DatabaseConnection.Close()

View 10 Replies View Related

ALTER LOGIN Syntax Problem

Oct 30, 2007



I typed the following in sqlcmd:

1> ALTER LOGIN [BUILTINAdministrators] WITH DEFAULT_DATABASE=master
2> GO


And I got this error:
Line 1: Incorrect syntax near 'LOGIN'.

Can you help me find the problem? Thanks

View 6 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related

Alter Table Alter Column...

Oct 8, 2007

I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?



my code:
Alter table customers alter column age

View 7 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

Alter Column Results In Incorrect Syntax Near 'column'

Oct 29, 2001

I ran this query against the pubs database and it runs successfully

ALTER TABLE publishers ALTER COLUMN state CHAR(25)

I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)

and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.

My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.

View 1 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

View 4 Replies View Related

How To Alter The Table With Delete/update Cascade Without Recreating The Table

Jul 26, 2004

I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.

Thanks


************************************************** ******
Contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

View 3 Replies View Related

How To Alter Column Length Of The Master Table Along With The Slave Table?

Aug 13, 2006

In SQL Server 2005,here are two tables, created by the following SQL Statements:

CREATE TABLE student(
ID CHAR(6) PRIMARY KEY,
NAME VARCHAR(10),
AGE INT
);

CREATE TABLE score(
ID CHAR(6) PRIMARY KEY,
SCORE INT,
FOREIGN KEY(ID) REFERENCES student(ID)
);

For the length of Column ID is not enough, So I want to alter its length.The alter statement is:

ALTER TABLE student ALTER COLUMN ID CHAR(20)

For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.

But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?

Thanks!

View 2 Replies View Related

SQL Server 2008 :: Alter Table Triggers Recreate Table?

May 26, 2015

which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?

View 2 Replies View Related

How To Modify Colum (alter Table) Of Table That Is Replicated?

Jul 20, 2005

Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern

View 1 Replies View Related

ALTER TABLE SWITCH PARTITION On A Replicated Table

Apr 10, 2007

A customer wants to implement table partitioning on a replicated table.



They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.



So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).



What are his architectural or practical solutions to using table partitioning and replication?











thx

View 5 Replies View Related

Alter A Table All In C#

Feb 15, 2006

Is there a way to add a column to an existing table and do it all in C#If my query string is as follows how do I execute the query?ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')ThanksMoonWa

View 2 Replies View Related

ALTER TABLE

Aug 13, 2002

I think I have blown a gasket..... a elementary simple sp that will not work but I am unable to find an explanation as to why..... please help.


CREATE PROCEDUREbrsp_table_alteration
@var_1 varchar(45)
,@var_2varchar(45)
,@var_3varchar(30)
AS

ALTER TABLE @var_1
ADD@var_2@var_3NULL


Thanks.

View 3 Replies View Related

Alter Table ......

Sep 30, 2003

I have a table tblABC which exists in 800 databases. I want to run a cursor to turn off the pk field ID indentity permannetly and then to "insert tblABC select * from tblXYZ".

I have tried the "set indentity_insert tblABC on" statement and failed on insert tblABC select * from tblXYZ.

Is there any alter table related t-sql could permannetly turn off the identity?

thanks
David

View 7 Replies View Related

Alter Table

Jun 7, 2001

Is there anyway to Insert a new column between two existing columns without creating a new table?

Thanks

View 3 Replies View Related

Alter Table

Oct 17, 2001

Hi,
is it possible to override the system when adjusting
columns. i.e. a system override setting.

For instance if I've got a column that is indexed and I
want to adjust it from varchar(50) to varchar(51) it lets
me. But if I then try and adjust the column to varchar
(49) I get the error below:

Server: Msg 5074, Level 16, State 8, Line 1
The index 'LastName' is dependent on column 'lastname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN lastname failed because one or
more objects access this column.

I asked this question on another Forum and was told I had to drop the index first. Just thought I'd ask for a second opinion.

Thanks for the help.

Steve

View 1 Replies View Related

Alter Table

Jan 30, 2004

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!

View 2 Replies View Related

Can I Alter A Table Specified By @name?

Jun 5, 2008

hello,
i am trying to add a column to every table in a database, if it (doesn't already exist), with the following code:

declare @name varchar(50)
declare c cursor for select name from sys.tables
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
if( (select count(*) from sys.columns
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @name )
and name='ins_id') = 0 )
begin
alter table @name add ins_id int
end

fetch next from c into @name
end
close c
deallocate c

and when i run the script i get the error message
Incorrect syntax near '@name'.
reffering to the alter table statement
So, my question is, how can i actually perform the alter table in this case?
thank you

View 3 Replies View Related

Alter Table

Mar 2, 2007

Pramod writes "I want to change the primary key field of my database to Identity seed. the table is with data. data should be secured
this is the structure
'=================================
CREATE TABLE [tblStock] (
[StockId] [numeric](18, 0) NOT NULL ,
[ItemID] [numeric](18, 0) NULL ,
[Batch] [varchar] (50) NULL ,
[Qty] [float] NULL ,
[Prate] [float] NULL ,
[SRate] [float] NULL ,
[Mrp] [float] NULL ,
[PTR] [float] NULL ,
[batchid] [numeric](18, 0) NULL ,
[BatchCode] [varchar] (50) NULL ,
CONSTRAINT [PK_tblStock] PRIMARY KEY NONCLUSTERED
(
[StockId]
) ON [PRIMARY] ,
CONSTRAINT [IX_tblStock] UNIQUE NONCLUSTERED
(
[ItemID],
[Batch],
[Prate]
) ON [PRIMARY]
) ON [PRIMARY]
===================================
How do i use alter table with identity seed
ALTER TABLE tblStock alter column StockID Numeric IDENTITY (1, 1)

how the p[rimary key affects

Please help me"

View 1 Replies View Related

Alter Table Name

Sep 13, 2007

How to alter a table name by using Query Analyser

i tried like this..

ALTER TABLE of_info_grp
RENAME TO stp_info_grp_map

It is giving an error like this
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TO'.

Some one help on this

View 2 Replies View Related

T-sql Alter Table Udf Help!

Jul 23, 2005

Hi,I'm trying to write a function check if a column exists on a table, andcreates it if it doesn't. The line that the query analyzer is citingis noted. It seems unhappy taking variables in the ALTER TABLEcommand. I can think of a hack around this, but I'm hoping there's abetter way? Muchas gracias in advance :)-DJCode followsCREATE FUNCTION fieldCreator(@TableName varchar(20),@FieldName varchar(20))RETURNS BITASBEGINif (EXISTS (SELECT count (objname)FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',@TableName, 'column', @FieldName)GROUP BY objname))BEGINALTER TABLE @TableName ADD @FieldName int DEFAULT 4 --ERRORS HERE!!!EXEC sp_addextendedproperty 'caption', 'Created by script foranalysis', 'user', dbo, 'table', @TableName, 'column', @FieldNamereturn 1ENDreturn 0ENDGO

View 8 Replies View Related

Alter A SQL Table

Aug 18, 2006

I used the MS Access 2000 Upsize wizard to create a Client/serverapplication. After the wizard ran, the original table names had becomethe linked tables and then there were duplicates of the the tablesnamed xxx_local.I realized later that I needed to add some fields to a table andcouldn't do that in Access (couldn't edit the linked tables). So I usedthe Query Analyzer and created a script to add the fields to my table.It looked fine in Query Analyzer, but back in Access the fields wereadded to xxx_local and not xxx, the linked table. My forms and reportsare built on xxx, not xxx_local.How do I do this correctly?Jane

View 2 Replies View Related

Alter Table- ID

Jan 28, 2008

I am trying to insert some new rows in the table tbl_3, but the problem is that I don't know how to go along with the ID column. I want to insert new rows so that it gets appended at the end and new ID is automatically generated.
Looks like this had to be done while creating the table. The original table has the id assigned as 'Identity', but I have imported this table to my database and now it does not show that property in the ID column.

The Insert code that I am using is as follows and the tbl_3 has same no. of columns except it also has id.
Can I alter table without touching the previous IDs? The ids start at 28118 and ends at 28300.





Code Snippet
INSERT INTO [dbo].[tbl_3]
(3_name
,3_sort
,3_active
,3_display
,3_date)

select _MANUFACTURERNAME , 1, 1, 'True',
GETDATE()
from dbo._MANUFACTURERS where upper(_MANUFACTURERNAME) not in (
select Upper(3_name) from dbo.tbl_3 join
dbo._MANUFACTURERS on Upper(3_name) = Upper(_MANUFACTURERNAME))






And the error it is returning to me is

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column '3_id', table '.dbo.tbl_3'; column does not allow nulls. INSERT fails.

The statement has been terminated.

View 2 Replies View Related

Alter Table

Mar 18, 2008

hi,

i have table having just two columns. One of columns is defines as nchar(5).

Want to alter table like
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [Something] [nchar] (2)

COLLATE Latin1_General_CI_AS NOT NULL

GO
?

This script runs without problem till i have no data in table. When i add one row, this script fails with

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

The statement has been terminated.


When i change the table in design mode, it says that data will be truncated and do the change (with truncating).

So is there any possibility (a switch or whatever) to update this table by script with letting engine truncate data?


Thanks for any response
Lubos

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved