June 1, 2010 at 9:58 am
I have a batch job (.BAT) that calls a DTS. This DTS has an SQL Task. This SQL Task executes a stored procedure. This stored procedure fails because of a collision between the user and the DTS (both try to update the same record).
Here's what's strange:
My batch job receives the error message from SQL Server, which it logs into a separate LOG file. So I see the error message that SQL Server returns to my batch job. But when I go to the server and open the SQL Server Log node, there is no entry for that error at the time the error occurred.
Shouldn't I see an error in the Log File Viewer at the time the error happened?
The client is running a French version of SQL Server but the error message is something along the lines of:
"The tansaction (process ID 259) was blocked because the resources were locked by another user and was chosen as the victim. Re-execute the transaction."
June 2, 2010 at 7:01 pm
You have to enable logging of that message. You can query sys.messages to see all the available messages. The english version of the message you are looking for is found with this query:
select * from sys.messages where language_id = 1033 and text like '%deadlock victim%'
The french language_id I believe is 1036....I don't have all languages installed on my system, so I can't test...
English version query returns message id 1205. To enable logging of this message, run:
sp_altermessage 1205, 'WITH_LOG', 'true';
By default, only messages with a severity level between 19 and 25 are logged. This one is a severity level 13.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply