SqlServer 2005: Importing Data: Idenitity fields getting made into non-identities

Hi;

We just migrated to SqlServer 2005. When I import tables from from
SqlServer database into another the identity fields get switched off
from being identities.

How can I prevent that from happening?

Thanks in advance for any information

Steve





------------------------------------------------------------ -------
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Re: SqlServer 2005: Importing Data: Idenitity fields getting made into non-identities

Steve (tinker123@gmail.com) writes:
> We just migrated to SqlServer 2005. When I import tables from from
> SqlServer database into another the identity fields get switched off
> from being identities.
>
> How can I prevent that from happening?

Seems like the way to go is to click Edit SQL and add the IDENTITY
property manually.

> Microsoft SQL Server Management Studio
> 9.00.1399.00

This is the RTM version. I recommend that you install SP2, which
has several enhancements to the tools. If you plan to use maintenance
plans, make sure to get hold a GDR that fixes two serious bugs in
SP2 with maintenance plans.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SqlServer 2005: Importing Data: Idenitity fields getting made into non-identities

On May 16, 6:42 pm, Erland Sommarskog wrote:
> Steve (tinker...@gmail.com) writes:
> > We just migrated to SqlServer 2005. When I import tables from from
> > SqlServer database into another the identity fields get switched off
> > from being identities.
>
> > How can I prevent that from happening?
>
> Seems like the way to go is to click Edit SQL and add the IDENTITY
> property manually.

Our databases have several hundred tables, is there a more practical
solution ( I will talk to my admin and boss about getting service pack
2 )

Re: SqlServer 2005: Importing Data: Idenitity fields getting made into non-identities

Steve (tinker123@gmail.com) writes:
> On May 16, 6:42 pm, Erland Sommarskog wrote:
>> Steve (tinker...@gmail.com) writes:
>> > We just migrated to SqlServer 2005. When I import tables from from
>> > SqlServer database into another the identity fields get switched off
>> > from being identities.
>>
>> > How can I prevent that from happening?
>>
>> Seems like the way to go is to click Edit SQL and add the IDENTITY
>> property manually.
>
> Our databases have several hundred tables, is there a more practical
> solution ( I will talk to my admin and boss about getting service pack
> 2 )

I hope not all of them are using the IDENTITY property, because that would
indicate a poor database design.

What exactly is the reason you are doing this huge transfer operation?
If you want to copy the entire database, using BACKUP/RESTORE is both
easier and more reliable.

The wizard anyway seems very unpractical. The missing IDENTITY property
is only the start of the problems. It does not copy keys and indexes,
and most certainly you don't want that.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx