Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.com
Hi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>
|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>