September 20, 2011 at 10:40 am
SQL 2008 SP2...
We've got a reporting environment set up that pulls reporting information from various tables and inserts the results into a "queuing" table. A SQL Agent job then runs to pick up the records in the queue and processes them individually utilizing bcp, rs.exe, etc.
There are multiple Agent jobs running which pick up each report, and it's critical that each report only get picked up once (hence UPDLOCK, READPAST): Consider the code snippet:
...
...
WHILE (SELECT COUNT(1) FROM .dbo.AppReportQueue WITH(NOLOCK)) >= 1
BEGIN
DELETE FROM @Reports
BEGIN TRANSACTION AppReport
INSERT INTO @Reports
SELECT TOP 1 col1, col2, col3...
FROM dbo.AppReportQueue WITH(UPDLOCK, READPAST)
SELECT @err1 = @@ERROR
...
...
IF (@Err1 = 0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
...
...
END
And the SQL Agent job(s, as there are many of these running) execute:
WHILE (SELECT COUNT(1) FROM dbo.AppReportQueue WITH(READUNCOMMITTED) WHERE Running = 0) >= 1
BEGIN
EXEC dbo.utl_ApplicationReOccurring 1
END
FYI, everyting works great when being executed from the server it's all running on. However, the entire reason for this post is why can't I get this to run from a different server? I've set up the new server in the same manner, where the Agent job runs:
IF (SELECT dbo.fx_FetchJobStatus(dbo.fx_FetchSQLAgentJobID(@job, 1), 1)) = 3
BEGIN
WHILE (SELECT COUNT(1) FROM dbo.AppReportQueue WITH(NOLOCK) WHERE Running = 0) >= 0 AND @Cnt <= 3
BEGIN
BEGIN TRY
EXEC dbo.utl_ApplicationReOccurring 7
END TRY
BEGIN CATCH
PRINT 'ERROR :: ' + CAST(ERROR_NUMBER() as varchar) + ' Msg: ' + ERROR_MESSAGE()
END CATCH
SET @Cnt = @Cnt + 1
END
END
I get this error every time:
Message
Executed as user: MYCOMPANY\User. OLE DB provider "SQLNCLI10" for linked server "MYSERVER" returned message "Cannot start more transactions on this session.". [SQLSTATE 01000] (Message 7412) ERROR [MYSERVER]:: 7395 Msg: Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "MYSERVER". A nested transaction was required because the XACT_ABORT option was set to OFF. [SQLSTATE 01000] (Message 0) Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. [SQLSTATE 42000] (Error 3998). The step failed.
Seems like multiple issues going on here but not 100% sure where to start with resolving it - does anything jump out to anyone right off the bat?
If I SET XACT_ABORT to ON, it will all run successfully but the remote server does not respect the UPDLOCK/READPAST and therefore different SQL Agent jobs pick up the same report to process more than once (which is not allowed). Is there some sort of issue with locking a row from two different servers?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 20, 2011 at 10:59 am
1. Are both servers running the exact same version of SQL Server? i.e. 10.50.2500
2. We had issues running stored procedures that had heterogeneous queries and found that you must create the procedure with warnings and ansi nulls set to on; http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1d501b57-fc58-4fbe-9bec-6c38ad158a62/
Report back with any findings 🙂
Thanks,
Jared
Jared
CE - Microsoft
September 20, 2011 at 11:13 am
Yes, they are all set up exactly the same (only differences are hardware).
I checked out the artilcle and will see if that helps, appreciate the input...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 20, 2011 at 3:30 pm
I still receive the same error. From my research it would appear that by design MSSQL doesn't allow nested transactions across servers. Not 100% certain that's what I was even doing...I mean, on one server 6 different Agent jobs access the table (which create a seperate transaction each time, simultaneously), one the remote server it should do the same thing? Not sure where the 'nested' comes in to play :unsure:
http://support.microsoft.com/kb/316872
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 20, 2011 at 3:35 pm
Well... A nested transaction is where the transaction inside another needs to occur first. It must be that the one inside cannot be accessing a linked server. Hmm... Sorry, I can't help too much here except to see if you can somehow "un-nest" any transactions.
Jared
Jared
CE - Microsoft
September 20, 2011 at 3:43 pm
Any way to shorten the transactions?
Maybe use a local temp table to save the next 4-5 ids to process.
Then in the main table have 1 flag assigned. Do the begin / end tran there.
Then another short tran to delete the ids done in the queue.
That should wipe out the "main" transaction.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply