I am finding problems in trying to copy a databse table from one SQL
2000 server to another using ADO.net. However the source table has an
identity column and I need to preserve the values in the destination
table. Obviously every time I run the below code the destination table
does not have the correct identity column.

For Each objDataRow In dsSource.Tables(0).Rows

With dsDest.Tables(0)

objDBRow = .NewRow()
For Each c In .Columns
objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
Next
.Rows.Add(objDBRow)
End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
objAdapter.Update(dsDest, tablename)

Next

What is the best approach in acheiving this? Bear in mind that the two
SQL servers cannot talk to each other so that I cannot use
sp_addlinkedserver and use a stored procedure.

Am I going about this incorrectly?

Thanks Rippo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,

If you want to insert an explicit value into an identity column, you can
issue SET IDENTITY INSERT ON statement on the connection before performing
the inserts. Take a look in books online for the exacty syntax and
restriction.

Hope this helps

--
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor
"Rippo" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I am finding problems in trying to copy a databse table from one SQL
> 2000 server to another using ADO.net. However the source table has an
> identity column and I need to preserve the values in the destination
> table. Obviously every time I run the below code the destination table
> does not have the correct identity column.
>
> For Each objDataRow In dsSource.Tables(0).Rows
>
> With dsDest.Tables(0)
>
> objDBRow = .NewRow()
> For Each c In .Columns
> objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
> Next
> .Rows.Add(objDBRow)
> End With
>
> ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
> objAdapter.Update(dsDest, tablename)
>
> Next
>
> What is the best approach in acheiving this? Bear in mind that the two
> SQL servers cannot talk to each other so that I cannot use
> sp_addlinkedserver and use a stored procedure.
>
> Am I going about this incorrectly?
>
> Thanks Rippo
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Chris

Ok here is what I have:-

objAdapter = New SqlDataAdapter("SET IDENTITY_INSERT tblScreens ON;
SELECT * FROM tblScreens WHERE 1 = 2 ", cnMainLocal)

objAdapter.Fill(objDSDBTable, tablename)

For Each objDataRow In dsSource.Tables(0).Rows
With dsDest.Tables(0)

objDBRow = .NewRow()
For Each c In .Columns
objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
Next
.Rows.Add(objDBRow)
End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
objAdapter.Update(dsDest, tablename)

Next

However I get the following error message when i perform the
objAdapter.Update(objDSDBTable, tablename):-

Explicit value must be specified for identity column in table
'tblScreens' when IDENTITY_INSERT is set to ON

This means that the columns need to be Explicitly defined but the update
does not appear to send the column names.

Can you provide further information?
Many thanks
Rippo




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You can't use SELECT *, you must specify each column name.

You also need to SET IDENTITY_INSERT tblScreens OFF when you are done as it
can only be set on one table at a time, and will fail (I believe) if you try
to turn it on a table for which it is already on.

You probably won't be able to use the CommandBuilder either, because it
won't use the identity column for the insert. Set the InsertCommand for the
dataadapter in code.

Try it from Query Analyzer before you try to code it.

"Rippo" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Chris
>
> Ok here is what I have:-
>
> objAdapter = New SqlDataAdapter("SET IDENTITY_INSERT tblScreens ON;
> SELECT * FROM tblScreens WHERE 1 = 2 ", cnMainLocal)
>
> objAdapter.Fill(objDSDBTable, tablename)
>
> For Each objDataRow In dsSource.Tables(0).Rows
> With dsDest.Tables(0)
>
> objDBRow = .NewRow()
> For Each c In .Columns
> objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
> Next
> .Rows.Add(objDBRow)
> End With
>
> ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
> objAdapter.Update(dsDest, tablename)
>
> Next
>
> However I get the following error message when i perform the
> objAdapter.Update(objDSDBTable, tablename):-
>
> Explicit value must be specified for identity column in table
> 'tblScreens' when IDENTITY_INSERT is set to ON
>
> This means that the columns need to be Explicitly defined but the update
> does not appear to send the column names.
>
> Can you provide further information?
> Many thanks
> Rippo
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Thanks for that.

However I will be using this code to copy over a lot of tables and did not
want to hard code the field names as i have this outer loop

For Each objDataRow1 In dsDataForXML.Tables(0).Rows
tablename = objDataRow1.Item("tablename")

... other code already poste below....
next

Is there a another way to achieve this

Thanks

"Jim Hughes" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You can't use SELECT *, you must specify each column name.
>
> You also need to SET IDENTITY_INSERT tblScreens OFF when you are done as
> it can only be set on one table at a time, and will fail (I believe) if
> you try to turn it on a table for which it is already on.
>
> You probably won't be able to use the CommandBuilder either, because it
> won't use the identity column for the insert. Set the InsertCommand for
> the dataadapter in code.
>
> Try it from Query Analyzer before you try to code it.
>
> "Rippo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Chris
>>
>> Ok here is what I have:-
>>
>> objAdapter = New SqlDataAdapter("SET IDENTITY_INSERT tblScreens ON;
>> SELECT * FROM tblScreens WHERE 1 = 2 ", cnMainLocal)
>>
>> objAdapter.Fill(objDSDBTable, tablename)
>>
>> For Each objDataRow In dsSource.Tables(0).Rows
>> With dsDest.Tables(0)
>>
>> objDBRow = .NewRow()
>> For Each c In .Columns
>> objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
>> Next
>> .Rows.Add(objDBRow)
>> End With
>>
>> ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
>> objAdapter.Update(dsDest, tablename)
>>
>> Next
>>
>> However I get the following error message when i perform the
>> objAdapter.Update(objDSDBTable, tablename):-
>>
>> Explicit value must be specified for identity column in table
>> 'tblScreens' when IDENTITY_INSERT is set to ON
>>
>> This means that the columns need to be Explicitly defined but the update
>> does not appear to send the column names.
>>
>> Can you provide further information?
>> Many thanks
>> Rippo
>>
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!
>
>

This may seem impertinent, but is there a reason you are using ADO to
do this? It sounds like DTS or bcp would be better suited to the task.