>> Did you try closing the first ODBCDirect workspace and reopening a new
>> one?
> Maybe this not, but a lot of other things. Instead of using an ODBCdirect
> workspace a used a pass through query, so there is no ODBCdirect
> workspace. Or a opened a new (jet) workspace for the bulk query only after
> setting the IDENTITY_INSERT. Nothing helped. I then thought about two
> alternative technologies, namely (1) constructing a script that imports
> all the data (encouraged by a former answer in this newsgroup), and (2)
> using SQL-DMO. I guessed to have (in my situation) a little bit less work
> with SQL-DMO, so I tried this and it worked. Of course (like it would also
> be with a script) the transfer is then done record by record instead with
> one single query for a whole table. But at least it works now.
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uNiKQGvoHHA.4400[ at ]TK2MSFTNGP03.phx.gbl...
>> Did you try closing the first ODBCDirect workspace and reopening a new
>> one?
>>
>> A second possibility would be to use an intermediate table on SQL-Server
>> without an identity column and copy first to this table before copying
>> from it to the real table. The second step can be done entirely on the
>> SQL-Server, so it should be very fast.
>>
>> A final possibility is that Access is opening more than a single
>> connection. I don't remember how exactly the ODBCDirect is working but
>> maybe you will have some hints if you take a look with the SQL-Server
>> profiler. It could also be a problem with the Connections Pool, hard to
>> say. With many providers, you can deactivate the connections pool, so
>> maybe there is something there to look at.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Lorenz Ingold" <l.ingold[ at ]winvs.ch> wrote in message
>> news:OTM7HRpoHHA.3952[ at ]TK2MSFTNGP03.phx.gbl...
>>>I have Access 2003 and SQL-Server 2000 SP3a. I want to copy the data of
>>>several tables from an Access DB to an SQL DB. In an mdb-program I link
>>>all the tables, so that I have normal linked tables (the source) and
>>>odbc-linked tables (the target). The tables have an identity, so in a
>>>special ODBC-direct workspace I send the TSQL-statement "SET
>>>IDENTITY_INSERT mytable ON" (which seems to be successful, I checked
>>>that), then I execute an INSERT INTO-query on the linked tables for
>>>transferring all the data, then I send "SET IDENTITY_INSERT mytable OFF".
>>>Then comes the next table, and so on.
>>> For the first table everything is OK. The second table however failed
>>> when trying the INSERT INTO-query, giving the error "Cannot insert
>>> explicit value for identity column in table 'mytable2' when
>>> IDENTITY_INSERT is set to OFF". If I change the order of the first two
>>> tables, then again the one that executes first is successful and the one
>>> that comes after, failed.
>>> Now I found an article of Microsoft
>>> (
http://support.microsoft.com/kb/878501/en-us) that addresses this
>>> problem (not saying, however, that one table might work and the second
>>> not). Microsoft says that the resolution is to install SP4 of the
>>> SQL-server 2000. I did that, but it didn't help at all; there is exactly
>>> the same behaviour as before. What is wrong here, and what should be
>>> done?
>>>
>>
>>
>
>