December 7, 2007 at 11:05 am
Hello everyone,
I have been using the express agent ( http://www.valesoftware.com/products-express-agent.php ) to set up scheduled data transfers between 5 seperate licensee
servers and one corporate server, and i'm running into the following problem
with my stored procedure, which i'm hoping someone could shed some light on.
Each server is running an instance of SQLEXPRESS 2005
The problem is i'm receiving the following error message from the scheduler, for the following code...
SCHEDULER ERROR MESSAGE
2007-12-06 13:17:33: Job New Job started.
2007-12-06 13:17:33: Job New Job: Job Step 1 (New Step) started.
2007-12-06 13:17:35: Job New Job: Job Step 1 (New Step) executing.
2007-12-06 13:17:41: Job New Job: Job Step 1 (New Step) execution failed
(Error: 3910, Severity: 16, Descr: Transaction context in use by another
session.).
2007-12-06 13:17:44: Job New Job execution failed.
THE CODE
Begin Transaction
INSERT INTO
[61.134.209.39\SQLEXPRESS].CorporateMBM.dbo.tbl_LicenseeSalesData(PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype)
Select PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype
FROM
[204.108.31.120\SQLEXPRESS].MBM.dbo.tbl_credit_purchase
IF(@@ERROR<>0)
rollback
else
commit
----------------------------------------------------------------------------
------------------------
Without the following rollback command in the code...
IF(@@ERROR<>0)
rollback
else
commit
The proc runs without error! But with the rollback command in the proc, it
trips the scheduler error message as indicated above.
Any insight, or help with what could be causing the scheduler to throw the
"Error: 3910, Severity: 16, Descr: Transaction context in use by another
session" error would be greatly appreciated.
Thanks in advance for your help!
Sincerely,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
December 7, 2007 at 11:12 am
I think this is a distributed transaction problem. Is there any reason you have it wrapped in a transaction there? Any error should throw a rollback anyway.
Does it complete? Could there be another open transaction from testing? (dbcc opentran)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply