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 a table

Hi,
How do I copy a table in my SQL Server 2000?
Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the records
inside too.
Any help will be appreciated.
JasonCREATE TABLE MyTable2 (...) ;
INSERT INTO MyTable2 (...)
SELECT ...
FROM MyTable ;
Or:
SELECT ...
INTO MyTable2
FROM MyTable ;
In this second case, the constraints won't be copied so you'll still have to
create them yourself.
This is development/admin of course. It isn't normally good practice to
create tables at runtime.
--
David Portas
SQL Server MVP
--
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I copy a table in my SQL Server 2000?
> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> records inside too.
> Any help will be appreciated.
>
> Jason
>|||Hi Jason
David gave you the best technique to copy data from one table to another.
The alternative way of doing this is using DTS.
DTS implements the same technique that David used
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David Portas" wrote:
> CREATE TABLE MyTable2 (...) ;
> INSERT INTO MyTable2 (...)
> SELECT ...
> FROM MyTable ;
> Or:
> SELECT ...
> INTO MyTable2
> FROM MyTable ;
> In this second case, the constraints won't be copied so you'll still have to
> create them yourself.
> This is development/admin of course. It isn't normally good practice to
> create tables at runtime.
> --
> David Portas
> SQL Server MVP
> --
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > How do I copy a table in my SQL Server 2000?
> > Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> > records inside too.
> > Any help will be appreciated.
> >
> >
> > Jason
> >
>
>|||Thanks David!
The MicroSoft has the "Copy" and "Paste" in many occasions, why don't they
have the "Copy" "Paste" in the SQL Server? Will this be too stupid or I am
too lazy?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¼¶¼g©ó¶l¥ó·s»D:2r6dnX2bm-yyuMreRVnyhA@.giganews.com...
> CREATE TABLE MyTable2 (...) ;
> INSERT INTO MyTable2 (...)
> SELECT ...
> FROM MyTable ;
> Or:
> SELECT ...
> INTO MyTable2
> FROM MyTable ;
> In this second case, the constraints won't be copied so you'll still have
> to create them yourself.
> This is development/admin of course. It isn't normally good practice to
> create tables at runtime.
> --
> David Portas
> SQL Server MVP
> --
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> How do I copy a table in my SQL Server 2000?
>> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
>> records inside too.
>> Any help will be appreciated.
>>
>> Jason
>|||Hi,
There are several reason that microsoft has not provided the facility
of copy and paste because each table has a unique and containe unique
record in terms of name and indexes constraints and many more.
Hope this much is sufficient for u to understand.
from
Doller
Jason Huang wrote:
> Thanks David!
> The MicroSoft has the "Copy" and "Paste" in many occasions, why don't they
> have the "Copy" "Paste" in the SQL Server? Will this be too stupid or I =am
> too lazy?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> =BC=B6=BCg=A9=F3==B6l=A5=F3=B7s=BBD:2r6dnX2bm-yyuMreRVnyhA@.giganews.com...
> > CREATE TABLE MyTable2 (...) ;
> >
> > INSERT INTO MyTable2 (...)
> > SELECT ...
> > FROM MyTable ;
> >
> > Or:
> >
> > SELECT ...
> > INTO MyTable2
> > FROM MyTable ;
> >
> > In this second case, the constraints won't be copied so you'll still ha=ve
> > to create them yourself.
> >
> > This is development/admin of course. It isn't normally good practice to
> > create tables at runtime.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> > "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> > news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> >> Hi,
> >>
> >> How do I copy a table in my SQL Server 2000?
> >> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> >> records inside too.
> >> Any help will be appreciated.
> >>
> >>
> >> Jason
> >>
> >
> >

Converting String -> UniqueIdentifier

I get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:

The data was truncated while converting from one data type to another. [ Name of function(if known) = ]

The column I am inserting into has a datatype of uniqueIdentifier.

This is the query I am running:

INSERT INTO [Table] ([guid], [value1], [value2])

VALUES (CONVERT(UNIQUEIDENTIFIER, 'F067FE20-EC76-44C8-9859-FEF222FBC96D'), 'Test, 'Test')

What am I doing wrong?

Matt

This works fine:

Code Snippet

INSERT INTO [Guidtest] ([Field1], [Field2])

VALUES (1, 'F067FE20-EC76-44C8-9859-FEF222FBC96D')

So no need to CONVERT!|||

Thks~

Converting String -> UniqueIdentifier

I get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:

The data was truncated while converting from one data type to another. [ Name of function(if known) = ]

The column I am inserting into has a datatype of uniqueIdentifier.

This is the query I am running:

INSERT INTO [Table] ([guid], [value1], [value2])

VALUES (CONVERT(UNIQUEIDENTIFIER, 'F067FE20-EC76-44C8-9859-FEF222FBC96D'), 'Test, 'Test')

What am I doing wrong?

Matt

This works fine:

Code Snippet

INSERT INTO [Guidtest] ([Field1], [Field2])

VALUES (1, 'F067FE20-EC76-44C8-9859-FEF222FBC96D')

So no need to CONVERT!|||

Thks~