Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Problem with linked server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/02/2007 :  03:29:59  Show Profile  Reply with Quote
Hi there!

I hope i'm not boring people here with my question, but i'm searching for hours now and even didn't get close to solve the problem.

I'll start with a post of my stored procedure that's causing the problem:

IF EXISTS (SELECT srvname FROM master.dbo.sysservers WHERE srvname = N'AccessExport' AND isremote = 1)
EXEC sp_droplinkedsrvlogin
@rmtsrvname = 'AccessExport',
@locallogin = NULL
EXEC sp_dropserver
@server = 'AccessExport'

EXEC sp_addlinkedserver
@server = 'AccessExport',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\A247\Archive\4_2007-01-31 15_35_12.mdb'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AccessExport',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = NULL

INSERT INTO AccessExport...PDO
(SlabNo, ChargeNo,...)
SlabNo, ChargeNo,.....
WHERE SlabNo = 3

After that, i get this error:

Server: Msg 8524, Level 16, State 1, Procedure p_export_2_mdb, Line 40
[Microsoft][ODBC SQL Server Driver][SQL Server]The current transaction could not be exported to the remote provider. It has been rolled back.

Line 40 is the one where the insert starts! The addition of the linked server itself works fine as far as i can see.

The funny thing is, this only happens when i start the whole procedure (i only postet the upper part of the code). If i take the posted code snippet and let it run alone i get this one:

Server: Msg 208, Level 16, State 1, Line 23
Invalid object name 'PDO'.

What i'm doing wrong? Is it maybe just a permission problem?

Thanks in advance an regards


Flowing Fount of Yak Knowledge

1837 Posts

Posted - 02/02/2007 :  12:37:20  Show Profile  Reply with Quote
Is the code in the stored proc starting a transaction (BEGIN TRAN[SACTION]) before the code you posted, or is the stored proc being called from another proc that starts a transaction? If so, that may be the problem. Either don't do this in a transaction or if you really want it in a transaction, you'll need to check into your MSDTC setup because this will require a distributed transaction. This should help

When you run it alone, you need to make sure you USE the right database (where the stored proc is located).
Go to Top of Page

Starting Member

2 Posts

Posted - 02/05/2007 :  03:35:02  Show Profile  Reply with Quote

Thx for your reply! In fact there is a transaction started in the stored proc, somewhere below the part i posted. But even if i remove the BEGIN TRANSACTION statement i get the same error.

The DTC is also installed and running! (i had that fault before, and got some other error message)

In the meantime i managed to get the code snippet to run (you were right...wrong db selected in the query analyzer, shame on me), but the whole procedure won't run anyway. :(

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000