duplicate identity

Hi there,
we are using merge replication on sql2000.We are using
partial replicas.
We have different identity value set for each of our
remote subscirbers. all tables are fine they all have the
key value as what they should have except one table.
this particular table on all our servers is using one
identity value which belongs to one of our remote
location. as a result when plants are replicating we get
conflicts on this table as the violation of primary key
value.
as this table is only keeping track of the user security
so its not causing us any problems right now.
but i am curious as why the identity value gets changed
only for this one particular table.
Does that table have the identity column set with the "not for replication"
option? If not, then the identity column will get reseeded and cause this
problem.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

DTSWizard and identity columns

I have used the dtswizard which comes with sql server 2005 to create a copy of my db. The problem is, that the dtswizard doesn't set identity columns and primary keys on the db-copy. This causes a lot of errors since there are no primary keys and identity columns. How do I copy these settings when using DTSwizard?

thanks,
Thomas

I don't believe you can copy the constraints and keys with the DTSWizard.

However, the Copy Database function copies the keys and constraints.

Using SSMS, in the Object Explorer pane, on the Database you wish to copy, right-click, and select [Tasks...], then [Copy Database...]

|||I cannot find the Copy Database function. Maybe it is because my DB is on a shared hosting solution. I am not sure, I have the rights to do it?

Is there another way in which I can do a complete copy of the DB?

|||

If I had known that first...

For shared hosting situations, the SQL Server team has created a special utilitiy (Database Publishing Wizard) to create a script of the database so that you can copy the script up to the host and execute the script on the remote server, making a duplicate of the database. The Wizard also allows updating specific tables, or data within the tables.

Download your copy here:

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thanks alot. That was exactly what I needed

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?