Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris
|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!
|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.WithDependencies = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.DestinationDatabase = "Clarina_N";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.TransferData();

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

And same problem with defaults even when
xfr.Option.Default = true;

|||This has nothing to do with SSIS. You should try posting to the SMO forum.

Thanks,
Mattsqlsql

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy data from one table to another table with change in identity column values

Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

If I understand your question, you want to insert the data from table
test into an already existing table.
>
INSERT existingtable
SELECT a, b
FROM test

I guess it should be:

INSERT existingtable
SELECT 10 + a, b
FROM test

since Salish wanted to change the values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxOn Mon, 20 Aug 2007 21:54:50 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>since Salish wanted to change the values.

Thanks for catching that, Erland.

Roy

copy a record?

Hi,
I need to copy a record in a table that has an identity column. Plus,
I want to copy this record without using a column list in the SQL
statement.
So I searched the net, came up with an elegant solution:
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
however, when tried that I received:
Error 8101 An explicit value for the identity column in table can only
be specified when a column list is used and IDENTITY_INSERT is ON
So searched again and had the following answers:
"...Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
" and someone added:
"...
You need a column list for your INSERT statement:
INSERT t2 (
[id], [first], [org], [rest], [aux] ) SELECT
[ie], [first], [org], [rest], [aux] FROM t1
..."
BUT - I have to ask:
what if I want to insert without having to use a column list? if
i use a column list, then every time a column is added, modified or
deleted I need to change this store procedure too, which results in
doubling maintenance costs.
any ideas? how can I copy a record without using a named column
list?
Thanks very much for ANY idea,
Lior<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
Generate the SQL (including the column list) yourself at runtime
Liz
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?|||FWIW, anyone who submits SQL, or SPL that has INSERT/UPDATE statements that
do not have column lists, gets their code back, I won't even allow it on QA.
The reason is maintenance. If I add a column to a table, the code will
break, and then I have to dig it out of the system to do a hot-fix.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||WHY do you not want to do a column list' I can think if several reasons
why you SHOULD do one.
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||Thanks to all answers. the reason I DONT want a coloumn list is to
avoid having the code breaks when I add/modify/delete a column. I want
to minimize the explicit names of columns and to have something like
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
See? Here I have only one column name. therefore, less maintainence.
does it make sense? thanks for any ideas
thanks,
Lior
Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> WHY do you not want to do a column list' I can think if several reasons
> why you SHOULD do one.
w
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <liormessin...@.gmail.com> wrote in message
> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > Hi,
> > I need tocopy a recordin a table that has an identity column. Plus,
> > I want to copy this record without using a column list in the SQL
> > statement.
> > So I searched the net, came up with an elegant solution:
> > INSERT INTO <table>
> > SELECT *
> > FROM table WHERE (table.ID = @.objID)
> > however, when tried that I received:
> > Error 8101 An explicit value for the identity column in table can only
> > be specified when a column list is used and IDENTITY_INSERT is ON
> > So searched again and had the following answers:
> > "...Before your SQL Statement:
> > SET IDENTITY_INSERT <tablename> ON
> > " and someone added:
> > "...
> > You need a column list for your INSERT statement:
> > INSERT t2 (
> > [id], [first], [org], [rest], [aux] ) SELECT
> > [ie], [first], [org], [rest], [aux] FROM t1
> > ..."
> > BUT - I have to ask:
> > what if I want to insert without having to use a column list? if
> > i use a column list, then every time a column is added, modified or
> > deleted I need to change this store procedure too, which results in
> > doubling maintenance costs.
> > any ideas? how can Icopy a recordwithout using a named column
> > list?
> > Thanks very much for ANY idea,
> > Lior|||Actually, very interesting - here is a great thread I found about the
subject. My problem is exactly as Chris's
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/e59a1ef42bafeff9/23480e2a7fc6dc7a?lnk=gst&q=copy+a+record&rnum=2#23480e2a7fc6dc7a|||The real root problem here seems to be your developement process!! If you
aren't doing an impact analysis when you alter a table and making necessary
modifications to your code then that needs to be rectified, IMHO. BTW, you
can STILL make things work by simply using NULLable columns and/or defining
defaults for your tables, right?
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1189040443.208198.139120@.o80g2000hse.googlegroups.com...
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
>
>
>
>
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> WHY do you not want to do a column list' I can think if several reasons
>> why you SHOULD do one.
> w
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <liormessin...@.gmail.com> wrote in message
>> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
>> > Hi,
>> > I need tocopy a recordin a table that has an identity column. Plus,
>> > I want to copy this record without using a column list in the SQL
>> > statement.
>> > So I searched the net, came up with an elegant solution:
>> > INSERT INTO <table>
>> > SELECT *
>> > FROM table WHERE (table.ID = @.objID)
>> > however, when tried that I received:
>> > Error 8101 An explicit value for the identity column in table can only
>> > be specified when a column list is used and IDENTITY_INSERT is ON
>> > So searched again and had the following answers:
>> > "...Before your SQL Statement:
>> > SET IDENTITY_INSERT <tablename> ON
>> > " and someone added:
>> > "...
>> > You need a column list for your INSERT statement:
>> > INSERT t2 (
>> > [id], [first], [org], [rest], [aux] ) SELECT
>> > [ie], [first], [org], [rest], [aux] FROM t1
>> > ..."
>> > BUT - I have to ask:
>> > what if I want to insert without having to use a column list? if
>> > i use a column list, then every time a column is added, modified or
>> > deleted I need to change this store procedure too, which results in
>> > doubling maintenance costs.
>> > any ideas? how can Icopy a recordwithout using a named column
>> > list?
>> > Thanks very much for ANY idea,
>> > Lior
>|||On 4 Sep, 23:19, liormessin...@.gmail.com wrote:
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
Please do NOT multi-post. You have several replies in the
microsoft.public.sqlserver.programming newsgroup.
Posting independently to multiple groups wastes everyones time and
makes it harder for people to help you.
--
David Portas|||On Sep 6, 7:00 am, liormessin...@.gmail.com wrote:
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > WHY do you not want to do a column list' I can think if several reasons
> > why you SHOULD do one.
> w
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <liormessin...@.gmail.com> wrote in message
> >news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > > Hi,
> > > I need tocopy a recordin a table that has an identity column. Plus,
> > > I want to copy this record without using a column list in the SQL
> > > statement.
> > > So I searched the net, came up with an elegant solution:
> > > INSERT INTO <table>
> > > SELECT *
> > > FROM table WHERE (table.ID = @.objID)
> > > however, when tried that I received:
> > > Error 8101 An explicit value for the identity column in table can only
> > > be specified when a column list is used and IDENTITY_INSERT is ON
> > > So searched again and had the following answers:
> > > "...Before your SQL Statement:
> > > SET IDENTITY_INSERT <tablename> ON
> > > " and someone added:
> > > "...
> > > You need a column list for your INSERT statement:
> > > INSERT t2 (
> > > [id], [first], [org], [rest], [aux] ) SELECT
> > > [ie], [first], [org], [rest], [aux] FROM t1
> > > ..."
> > > BUT - I have to ask:
> > > what if I want to insert without having to use a column list? if
> > > i use a column list, then every time a column is added, modified or
> > > deleted I need to change this store procedure too, which results in
> > > doubling maintenance costs.
> > > any ideas? how can Icopy a recordwithout using a named column
> > > list?
> > > Thanks very much for ANY idea,
> > > Lior- Hide quoted text -
> - Show quoted text -
When you do not explicitly mention the column it can cause very hard
to find bugs where wrong columns get populated! Tust me on this
because it happened to me. Also, if you add/delete/modify a column,
your code will break when you do not have a column list.

copy a db, identity problem

hi
i need to copy a db to another db every single table to another similar to
the first one but with some difference. When i try to do that i need to
remove some constraint and all the identity on the new db. i'd like do that
with a script, i tried:
CREATE TABLE (
id int IDENTITY (1,1) NOT NULL
)
ALTER TABLE prova ALTER COLUMN id int null
GO
it doesnt work.
how can i remove identity from a column of my table'
tx
carloYou can't directly remove the IDENTITY property. You would have to
create a new column or new version of the table.
However, you can use the SET IDENTITY_INSERT option to allow you to
populate the column without removing the IDENTITY property. See Books
Online for details.
David Portas
SQL Server MVP
--|||>> how can i remove identity from a column of my table'
You cannot do this directly. The alternative is to create another table,
copy the data & rename it. The EM interface can get this done with a few
mouse clicks and it may perform reasonably for small-medium sized tables.
Anith|||You don't need to. And, actually, you shouldn't. Look up SET IDENTITY_INSERT
in Books Online. And then use it.
It helps you achieve exactly what you need (judging from your post).
ML