SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

rayban81
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)
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
Flowing Fount of Yak Knowledge

USA
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
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).
Go to Top of Page

rayban81
Starting Member

2 Posts

Posted - 02/05/2007 :  03:35:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000