Sql Server 0x80004005 deadlock error using ADO

Posted on 2009-12-16
Last Modified: 2012-06-27
I am using ADO and I get a deadlock error.  When I query the errors collection I get back 0x80004005 and the typical deadlock error message.  I know that other messages can come back from the database with the same error number.  Is there any way to distinguish the difference other than searching through the message.  We handle our code execution different if it is a deadlock.  I cant search the message because other languages are used.

I am using this provider
Provider=SQLOLEDB.1;Data Source=devmacine;Initial Catalog=AAD;Persist Security Info=False

Question by:PaulSlechta
    LVL 9

    Expert Comment

    try out following with ur values.

    '* set your variables here
    serverIP = ""
    serverPORT = "1433" '* default port is 1433/TCP
    serverDB = "Northwind"
    serverLOGIN = "login"
    serverPASSWORD = "pass"

    '* open connection
    Dim con
    Set con = Server.CreateObject("ADODB.Connection")
    con.ConnectionString = "Provider=SQLOLEDB.1;" & _
    "Persist Security Info=False;" & _
    "Network Library=DBMSSOCN;" & _
    "Data Source=" & serverIP & "," & serverPORT & ";" & _
    "Initial Catalog=" & serverDB & ";" & _
    "User ID=" & serverLOGIN & ";" & _
    "Password=" & serverPASSWORD & ";" & _
    "Connect Timeout=5;Pooling=False"

    Author Comment

    No difference.

    Author Comment

    This message is what I need.  1205 is the number I need to get.  All I get is the Message and 0x80004005

    Msg 1205, Level 13, State 51, Line 1
    Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Accepted Solution

    I found the solution to this issue. In the errors collection the is a property SQLState.  The value is a code returned from SQL Server itself.  The value for a deadlock returned by SQLState is 40001.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now