I have a database table and it has a column called ttt_id which has an identity seed. Now the Id value is 2845746 and if I insert the new record the value would be 2845747. But I want to change that to 2865740 instead of 2845747. How can do that?
Thanks.Check out DBCC CHECKIDENT in BOL. It should give you everything you need.|||-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT <TableName> ON
GO
INSERT INTO <TableName> (<FieldName>) VALUES(2845747)
GO
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 30, 2012
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
sql
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
sql
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the Datatype of a column in SQL 7
Hi Everyone,
I am trying to change a column for a table by using Transact-SQL 7
The table name is ActionLog and the column name is ActiveSys
Before change:
ActiveSys datatype char(12)
After change:
ActiveSys datatype varchar(30)
I use this command:
alter table ActionLog alter column ActiveSys varchar(30) not null
but the system keeps giving me this error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'column'.
I follow the syntax provide by the MS: (and I search on Google and found none of the standard SQL syntax works.)
Syntax
ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD
{ [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}
Can anyone help me? or Point out what is wrong with my code?
Thanks in advance.
dont use the keyword column, just use
alter table ActionLog alter ActiveSys varchar(30) not null
EXEC sp_dbcmptlevel '<Database name>', 70
alter table ActionLog alter ActiveSys varchar(30) not null
EXEC sp_dbcmptlevel '<Database name>', 65
Cheers.
How to change the data type of an IDENTITY column
Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql
How to change the data type of an IDENTITY column
Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Wednesday, March 28, 2012
How to change the column measure into Row Measure in Reporting services
Hi,
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
Thanks
best regards,
Tanipar
This is easily supported (no need to quote every other tool under the sun to prove your piont).
You just have to drag the field from the dataset window to the right area and drop when you see a horizontal bar
How to change sys_syslanguage SQL Server 2005 for edit column dateformat?
How to change sys_syslanguage SQL Server 2005 for edit column dateformat. Not posible edit this columnhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=955940&SiteID=1sql
Monday, March 26, 2012
How to change report line color or text color based on report cont
In Crystal Reports, I can change text color based on the report content. For
example, if the column total_amount < 0, I can change the text to red color.
Does anybody know how to do the same thing in Reporting Service?
Thanks a lot.Use a conditional If (IIF)... There is an example on www.msbicentral.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BF" <BF@.discussions.microsoft.com> wrote in message
news:97C568FC-3744-40A5-8AE3-8F0C5E673C41@.microsoft.com...
> In Crystal Reports, I can change text color based on the report content.
> For
> example, if the column total_amount < 0, I can change the text to red
> color.
> Does anybody know how to do the same thing in Reporting Service?
> Thanks a lot.sql
example, if the column total_amount < 0, I can change the text to red color.
Does anybody know how to do the same thing in Reporting Service?
Thanks a lot.Use a conditional If (IIF)... There is an example on www.msbicentral.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BF" <BF@.discussions.microsoft.com> wrote in message
news:97C568FC-3744-40A5-8AE3-8F0C5E673C41@.microsoft.com...
> In Crystal Reports, I can change text color based on the report content.
> For
> example, if the column total_amount < 0, I can change the text to red
> color.
> Does anybody know how to do the same thing in Reporting Service?
> Thanks a lot.sql
Friday, March 23, 2012
How to change MM/DD/YYYY format to DD/MM/YYYY format by using SQL query.
I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.
How To Change Just Year from a column(DueDate)
Hi
I am getting problem in changing just year from a column name duedate
in order to do m getting all months and day are also changing
help me out
Hey try this...
update tablename set duedate=dateadd(year,2,getdate())
I am getting problem in changing just year from a column name duedate
in order to do m getting all months and day are also changing
help me out
Quote:
Originally Posted by lokaish
Hi
I am getting problem in changing just year from a column name duedate
in order to do m getting all months and day are also changing
help me out
Hey try this...
update tablename set duedate=dateadd(year,2,getdate())
How to change identity to an existing column?
Hi
can anybody tell , Is it possible to alter existing column to add IDENTITY?
Thanks in advance
Yes
It is possible to add Identity Column to a table already have some data. BUT there is no such a command to alter a column to be an IDENTITY Column. You have to go through the following steps.
1. Create a same table with the IDENTITY COLUMN. but with diffrent Name
2. Move all data to the New Table, with IDENTITY INSERT ON option
3. Drop All constraints from the OLD table
4. Drop the OLD table
5. Rename the new table as the OLD table
6. Attach all constraints to the new table ...
Hope that it will help
|||Thank u akbar
How to change identity through script
Hi All,
I have a Column with identity(1,1) .
Using script how to set that to identity(2,2)
Thanks in advance.
SupriyaAssume your original table with identity (1,1) is called test. You can go
through the script below to change the seed and increment.
CREATE TABLE dbo.Tmp_test
(
id int NOT NULL IDENTITY (2, 2),
string varchar(90) NULL
)
SET IDENTITY_INSERT dbo.Tmp_test ON
IF EXISTS(SELECT * FROM dbo.test)
INSERT INTO dbo.Tmp_test (id, string)
SELECT id, string FROM dbo.test WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo.Tmp_test OFF
DROP TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||Well, I thought I remembered that you could do this, so I tried to whomp a
little something up. Couldn't get it to work. But then I focus on 2000 docs,
while using 2005. So finally, I looked at the 2005 docs: this is an exact
copy/paste from 2005 BOL: ALTER TABLE:
--
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
--
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'.
--
If the supported syntax doesn't match BOL, we got a problem somewhere. So,
if this is true, then you're stuck creating a new table and doing the
insert.
Sorry,
Jay
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||On Thu, 13 Sep 2007 06:48:38 -0700, Jay wrote:
>Well, I thought I remembered that you could do this, so I tried to whomp a
>little something up. Couldn't get it to work. But then I focus on 2000 docs,
>while using 2005. So finally, I looked at the 2005 docs: this is an exact
>copy/paste from 2005 BOL: ALTER TABLE:
>--
>CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
>CompanyName NvarChar (50))
>INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
>ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
Hi Jay,
If you go to that page in Books Online again, you'll see at the top of
the screen that this applies to SQL Server 2005 Compact Edition. This is
a different product, that is for some mysterious reason documented in
the same version of BOL (and I've lost track of the number of times I
myself felll victim to this misunderstanding - especially since the CE
version of BOL tends to come up before the "regular SQL Server" docs).
Changing identity is only supported on CE, and not on "normal" editions
of SQL Server.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
google for DBCC CHECKIDENT RESEED
isnt this the easiest way?|||does work on 2005.
use tempdb
if object_id('t') is not null drop table t
CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
(10))
insert into t(col2) values('one')
DBCC CHECKIDENT (t, RESEED, 200)
insert into t(col2) values('two')
select * from t
"densial" <densial@.gmail.com> wrote in message
news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
> google for DBCC CHECKIDENT RESEED
> isnt this the easiest way?
>|||I get it now.
This isn't resetting the initial value, or the increment, it is setting the
highest value used so the next value comes in above it.
Not what the OP asked for, usefull, but it doesn't change the increment from
1, to 2.
"Jay" <spam@.nospam.org> wrote in message
news:eGmGiZG%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> does work on 2005.
> use tempdb
> if object_id('t') is not null drop table t
> CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
> (10))
> insert into t(col2) values('one')
> DBCC CHECKIDENT (t, RESEED, 200)
> insert into t(col2) values('two')
> select * from t
> "densial" <densial@.gmail.com> wrote in message
> news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
>> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
>> google for DBCC CHECKIDENT RESEED
>> isnt this the easiest way?
>sql
I have a Column with identity(1,1) .
Using script how to set that to identity(2,2)
Thanks in advance.
SupriyaAssume your original table with identity (1,1) is called test. You can go
through the script below to change the seed and increment.
CREATE TABLE dbo.Tmp_test
(
id int NOT NULL IDENTITY (2, 2),
string varchar(90) NULL
)
SET IDENTITY_INSERT dbo.Tmp_test ON
IF EXISTS(SELECT * FROM dbo.test)
INSERT INTO dbo.Tmp_test (id, string)
SELECT id, string FROM dbo.test WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo.Tmp_test OFF
DROP TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||Well, I thought I remembered that you could do this, so I tried to whomp a
little something up. Couldn't get it to work. But then I focus on 2000 docs,
while using 2005. So finally, I looked at the 2005 docs: this is an exact
copy/paste from 2005 BOL: ALTER TABLE:
--
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
--
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'.
--
If the supported syntax doesn't match BOL, we got a problem somewhere. So,
if this is true, then you're stuck creating a new table and doing the
insert.
Sorry,
Jay
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||On Thu, 13 Sep 2007 06:48:38 -0700, Jay wrote:
>Well, I thought I remembered that you could do this, so I tried to whomp a
>little something up. Couldn't get it to work. But then I focus on 2000 docs,
>while using 2005. So finally, I looked at the 2005 docs: this is an exact
>copy/paste from 2005 BOL: ALTER TABLE:
>--
>CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
>CompanyName NvarChar (50))
>INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
>ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
Hi Jay,
If you go to that page in Books Online again, you'll see at the top of
the screen that this applies to SQL Server 2005 Compact Edition. This is
a different product, that is for some mysterious reason documented in
the same version of BOL (and I've lost track of the number of times I
myself felll victim to this misunderstanding - especially since the CE
version of BOL tends to come up before the "regular SQL Server" docs).
Changing identity is only supported on CE, and not on "normal" editions
of SQL Server.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
google for DBCC CHECKIDENT RESEED
isnt this the easiest way?|||does work on 2005.
use tempdb
if object_id('t') is not null drop table t
CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
(10))
insert into t(col2) values('one')
DBCC CHECKIDENT (t, RESEED, 200)
insert into t(col2) values('two')
select * from t
"densial" <densial@.gmail.com> wrote in message
news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
> google for DBCC CHECKIDENT RESEED
> isnt this the easiest way?
>|||I get it now.
This isn't resetting the initial value, or the increment, it is setting the
highest value used so the next value comes in above it.
Not what the OP asked for, usefull, but it doesn't change the increment from
1, to 2.
"Jay" <spam@.nospam.org> wrote in message
news:eGmGiZG%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> does work on 2005.
> use tempdb
> if object_id('t') is not null drop table t
> CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
> (10))
> insert into t(col2) values('one')
> DBCC CHECKIDENT (t, RESEED, 200)
> insert into t(col2) values('two')
> select * from t
> "densial" <densial@.gmail.com> wrote in message
> news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
>> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
>> google for DBCC CHECKIDENT RESEED
>> isnt this the easiest way?
>sql
How to change encoding for XML Column
I am updating an xml column in SQLServer 2005, but it always gives me
an error
"XML parsing: line 1, character 38, unable to switch the encoding"
my csharp is like the following
cmd.Parameters.Add("@.xml", SqlDbType.Xml);
cmd.Parameters["@.xml"].Value =xmldata;
My xml have the following line
"<?xml version="1.0" encoding="UTF-8"?>..."
If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.
Please advice.
Thanks in advance.
JohnWell I do search a bit and perhaps you want to remove that line before you
insert it in to the column.
chanmm
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>|||Try using SqlDbType.VarChar (even if your parameter in the proc is really
XML). This worked for me. Reason is likely that you're using a System.String
(Unicode) and the XML data type is internally UTF-16, so telling it UTF-8 is
switching encodings, after its "decided" you wanted UTF-16. There is
automatic string conversion if the encoding matches the SQL varchar data
type.
Bear in mind, you'll always get the data out as UTF-16.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>
an error
"XML parsing: line 1, character 38, unable to switch the encoding"
my csharp is like the following
cmd.Parameters.Add("@.xml", SqlDbType.Xml);
cmd.Parameters["@.xml"].Value =xmldata;
My xml have the following line
"<?xml version="1.0" encoding="UTF-8"?>..."
If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.
Please advice.
Thanks in advance.
JohnWell I do search a bit and perhaps you want to remove that line before you
insert it in to the column.
chanmm
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>|||Try using SqlDbType.VarChar (even if your parameter in the proc is really
XML). This worked for me. Reason is likely that you're using a System.String
(Unicode) and the XML data type is internally UTF-16, so telling it UTF-8 is
switching encodings, after its "decided" you wanted UTF-16. There is
automatic string conversion if the encoding matches the SQL varchar data
type.
Bear in mind, you'll always get the data out as UTF-16.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>
Wednesday, March 21, 2012
How to change data type of column from int to bigint in replication
Hi,
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql
How to change column order of a table
Hi,
I need to modify column order of some of my sql server 2k tables
because i want to keep related columns together. How can i do this?
Thanks
Nikhilcreate a view with the columns in the order you want
rudy|||Why?
If you really need to do it.
create an new table as you want the columns to be.
Copy the data into it.
rename the old table to a different name.
rename the new table to the old name.
Fix all the Foreign keys to point to the correct table.
and pray it works!!!
I would suggest getting an book on SQL design because there is normaly no good reason to change the column order of the table.
Tim S|||It's very simple use Enterprise Manager -> Tables _> Design Table -> and use drag and drop to move one column in the position you want (it' necessary to select the row of the column in order for drag and drop to function)
Beware, this only can be done in SQL Server 2k not in any previous versions of SQL Server.
Anyway to move a column has a meaning only for a better visualisation of the table design, because the order of columns, or the phisical order of rows in the table has no importance (it's a base rule of relational database).
But, if you decide to move one column before another, you can do it nothing is going to change within your application.
In fact, there is one problem in SQL 2k. If you are changing the position of a column and for that table you have a trriger that use the function COLUMNS_UPDATED(n) (where n is the no of a row in the table) and after your switching positions of columns the positin of the column implied in COLUMN_UPDATED function changes you should modify your trriger in order to work corectly.
IONUT
I need to modify column order of some of my sql server 2k tables
because i want to keep related columns together. How can i do this?
Thanks
Nikhilcreate a view with the columns in the order you want
rudy|||Why?
If you really need to do it.
create an new table as you want the columns to be.
Copy the data into it.
rename the old table to a different name.
rename the new table to the old name.
Fix all the Foreign keys to point to the correct table.
and pray it works!!!
I would suggest getting an book on SQL design because there is normaly no good reason to change the column order of the table.
Tim S|||It's very simple use Enterprise Manager -> Tables _> Design Table -> and use drag and drop to move one column in the position you want (it' necessary to select the row of the column in order for drag and drop to function)
Beware, this only can be done in SQL Server 2k not in any previous versions of SQL Server.
Anyway to move a column has a meaning only for a better visualisation of the table design, because the order of columns, or the phisical order of rows in the table has no importance (it's a base rule of relational database).
But, if you decide to move one column before another, you can do it nothing is going to change within your application.
In fact, there is one problem in SQL 2k. If you are changing the position of a column and for that table you have a trriger that use the function COLUMNS_UPDATED(n) (where n is the no of a row in the table) and after your switching positions of columns the positin of the column implied in COLUMN_UPDATED function changes you should modify your trriger in order to work corectly.
IONUT
how to change column names in the grid view programatically?
I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3...Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption".
fldfieldno Fldcaption
---- -----
11 9/07/2007
12 9/14/2007
13 9/21/2007
So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptions
where fldfieldno = 11
That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.
My current query looks like this:
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,
So can I do something like the following?
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
Hi tomhirt,
So can I do something like the following? SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
You cannot do that . Column alias can only be constant string.
Note what you need to modify is only gridview column header texts. So,you can create another query clause againstlkpdatecaptionsand assign the resultset to be your gridview column header texts.
Hope my suggestion helps
|||ok, I think I understand the concept, but I could use some examples or pointers.
|||It's very easy to use the approach i suggested above.
What you all need to do is to: build a connection to yourlkpdatecaptionsdatabase and fetch the data (use the smae parameters as you did when you get the first table). As to modify the datagridview column headers, see msdn documnet:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.datacontrolfield.aspx
|||I know it was a long weekend and I'm brain dead this morning, but I don't see any type of example where a data connection is looped to assign values to column header text.
|||
sqlcommand cmd=new sqlcommand("Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where XXXXXX")// this will retrun a collection of weeks.using(sqlconnection con=new sqlconnection("your con-string")){ cmd.connection=con; con.Open(); sqldatareader rder; rder=cmd.ExecuteReader();int tmp=0;while(rder.Read()) { yourdatagrid.columns[tmp].HeaderText=rder[tmp].ToString(); tmp++; } } Hope my suggsestion helps|||Perfect!!! That's what I was missing. Thanks!!!!
Subscribe to:
Posts (Atom)