Problem with linked server - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with linked server

Author

Topic

rayban81
Starting Member

2 Posts

Posted - 2007-02-02 : 03:29:59

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)
BEGIN
EXEC sp_droplinkedsrvlogin
@rmtsrvname = 'AccessExport',
@locallogin = NULL
EXEC sp_dropserver
@server = 'AccessExport'
END

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,...)
SELECT
SlabNo, ChargeNo,.....
FROM PDO
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.
@RETURN_VALUE = N/A

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

Clemens

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-02 : 12:37:20

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
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx

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

rayban81
Starting Member

2 Posts

Posted - 2007-02-05 : 03:35:02

Hi!

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. :(

Rgds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources