Re: Identity_Insert

Herb wrote:

> I'm trying to set Identity_Insert for a SQL Server table using VB code via
> an
> ADO connection but it doesn't seem to be working.
>
> According to the SQL Server 2000 books online you can have only one table
> in
> a database set with this value, at any one time. I've proved this using
> query
> analyzer and setting ON for table1 and then my attempt to set ON for
> table2
> fails telling me that table is already set.
>
> However, If I leave the setting on for table1 and then execute the
> following
> code behind an MSAccess form:
>
> Const TargetConnectionString = "Provider=SQLOLEDB.1;Initial
> Catalog=BookkeeperSource;Data Source=PTLPSRV02;Integrated Security=SSPI;
> Persist Security Info=False;"
>
> Dim cmd As New ADODB.Command
> cmd.CommandText = "SET IDENTITY_INSERT BookkeeperSource.dbo.table1 OFF"
> cmd.ActiveConnection = TargetConnectionString
> cmd.Execute
> cmd.ActiveConnection.Close
>
> I do not get an error out of this code but when I attempt to set
> identity_insert on for table2 in query analyzer, I get the message saying
> that identity_insert is already on for table1.
>
> Am I wrong to think that the code executed behind the form should have
> removed that setting for table1?
>
> Thanks in advance for any help you can shed on this.

Hi,

I've done almost exactly the same thing with SQL Server databases with no
problems. It's as if your code did not run. Can you somehow verify that the
code ran, perhaps displaying a message at the end?

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net


.

Relevant Pages