OpenQuery using OLE DB provider 'SQLOLEDB'

OpenQuery using OLE DB provider 'SQLOLEDB'

Post by Jessica M » Sun, 02 Feb 2003 03:38:33



Hi,

I'm trying to populate data in one SQL Server db (TARGET)
using data from another SQL Server (SOURCE).  In the
TARGET server I set up SOURCE server as a linked server, I
have DTC service running.

Here is the code I'm trying to execute (it worked once,
then does work the rest of the time, I'm sure there is no
syntax error):

-- specify database name in TARGET db
Use Guardian

-- declare and set value for variables








-- begin populating reference tables
BEGIN TRANSACTION
print 'working with ApplicationClass table'
-- insert new reference data from BOI1160
INSERT INTO dbo.ApplicationClass
(ApplicationClassID, ApplicationClassName,
ApplicationClassDescription, ActiveFlag)
SELECT ApplicationClassID, ApplicationClassName,

FROM OPENQUERY(BOI1160, 'select * from
Guardian.dbo.ApplicationClass')
-- commit transaction
COMMIT

Here is the error I kept getting:
Server: Msg 7391, Level 16, State 1, Line 33
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].

I tried shutting down the DTC service, TARGET server,
deleting then adding the linked server, etc, with no
luck.  This worked once yesterday, then refused to work
afterwards.

Please help....

 
 
 

OpenQuery using OLE DB provider 'SQLOLEDB'

Post by Jessica M » Wed, 05 Feb 2003 09:32:06


I'm trying to populate data in one SQL Server db (TARGET)
using data from another SQL Server (SOURCE).  In the
TARGET server I set up SOURCE server as a linked server, I
have DTC service running.

Here is the code I'm trying to execute (it worked once,
then does work the rest of the time, I'm sure there is no
syntax error):

-- specify database name in TARGET db
Use Guardian

-- declare and set value for variables








-- begin populating reference tables
BEGIN TRANSACTION
print 'working with ApplicationClass table'
-- insert new reference data from BOI1160
INSERT INTO dbo.ApplicationClass
(ApplicationClassID, ApplicationClassName,
ApplicationClassDescription, ActiveFlag)
SELECT ApplicationClassID, ApplicationClassName,

FROM OPENQUERY(BOI1160, 'select * from
Guardian.dbo.ApplicationClass')
-- commit transaction
COMMIT

Here is the error I kept getting:
Server: Msg 7391, Level 16, State 1, Line 33
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].

I tried shutting down the DTC service, TARGET server,
deleting then adding the linked server, etc, with no
luck.  This worked once yesterday, then refused to work
afterwards.

Please help....

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

 
 
 

1. Using Microsoft's OLE-DB Provider against a large Oracle database takes longtime

Hi All

I am using DTS to extract data from a Oracle database which as about
17,000 tables and about 12,000 indexes. I am using the MS OLE-DB
provider for Oracle when I double click on the transform it takes more
than 10-15 minutes for the screen asking me for the query. If I access
a Oracle database that as  50 or so tables the MS OLEDB provider comes
out fast. Does this need a MDAC patch ?

Thanks
Samantha

2. Library system in PICK

3. SQLOLEDB subset using OLE DB Provider template

4. native MS SQL driver very slow ???

5. Can't connect to Oracle using MSDAORA OLE DB provider

6. The Data Model Resource Book

7. Error 7399: OLE DB provider 'MSDASQL'

8. OLE DB provider 'STREAM' error

9. Microsoft's OLE DB Simple Provider 1.5 Library isn't cutting it :(

10. OLE DB provider 'SQLOLEDB' does not contain table 'mytable'

11. Can't retrieve temp table rows using SQLOLEDB provider