| Author |
 Topic  |
|
|
rayban81
Starting Member
2 Posts |
 Posted - 02/02/2007 : 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
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 02/02/2007 : 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 - 02/05/2007 : 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 |
|
|
| |
Topic |
|
|
|
Problem with linked server
