Keep getting Deadlock using patindex

[Glowworm27]

Hello all,

I am having a re-occuring problem.

I am trying to retrieve some customer information, last, first, middle names, address, city, state zip. etc.

from our customer database, I have it inner joined on the invoice table so I can check if the customer spent more than 50 bucks.

The problem I am having is that I am removing records if the firstname is null, or blank, and using the patindex , I am removeing them if the firstname has any numbers, or non-alpha characters in the first position. (i am also doing the same checks on the lastname field as well.

The error I keep getting is "Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

IF I comment out the 2 patindex WHERE statements the SQL executes no problem. but I still have a few records with non-alpha characters in the first positions of the lastname or firstname.

Any idea on how to prevent the deadlock, or is there a better way to filter out records where the fields lastname or firstname have non-alpha characters in them?

here is the sql I am trying to execute

SELECT DISTINCT 
                      Invoices.CustomerID, Customers.FirstName, Customers.LastName, Customers.Add1, Customers.Add2, Customers.City, Customers.State, 
                      Customers.Zip
FROM         Invoices INNER JOIN
                      Customers ON Invoices.CustomerID = Customers.ID INNER JOIN
                      Payments ON Invoices.ID = Payments.InvID
WHERE     (Invoices.Storeno = 0857) 
AND (Invoices.Trandate BETWEEN '01/01/2002' AND '03/31/2004') 
And patindex('%[!-9]%',Customers.firstname) <> 1
And patindex('%[!-9]%',Customers.lastname) <> 1
AND (ISNULL(Customers.FirstName, '') <> '') 
AND (LEN(Customers.FirstName) > 1) 
AND (ISNULL(Customers.LastName, '') <> '') 
AND (LEN(Customers.LastName) > 2) 
AND (ISNULL(Customers.Add1, '') <> '') 
AND (LEN(Customers.Add1) > 7) 
AND (ISNULL(Customers.Add1, '') <> '') 
AND (ISNULL(Customers.City, '') <> '') 
AND (ISNULL(Customers.State, '') <> '') 
AND (ISNULL(Customers.Zip, '') <> '') 
AND (Payments.PayAmt > 49.99)

Thanks in advance



I can't program for the Unknown, I have A keyboard not a Ouija Board. (See an online ouija board at

http://www.museumoftalkingboards.com/WebOuija.html)

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[vongrunt]

Hm... message says "lock resources". Can you post detailed error message info from SQL Server log?

About replacement for patindex(): try (ASCII(fieldname) NOT BETWEEN 48 and 57)

 

[NoCoolHandle]

I did have this exact problem about a year ago, I can't remember exactly what the solution was, but I have the feeling that it might be you are passing a null in from one of the records......

I do remember getting very frustrated. Good luck if it doesnt work post back and I will see if I can find any traces of the problem and it's resolution..

(the weird thing was patindex would do this but charindex was fine.....)

Rob

 

[Glowworm27]

There are no entries in the SQL Server Log for my error message. I was running the query from SQL analylizer.

Thanks for the idea for the ascii replacement


I can't program for the Unknown, I have A keyboard not a Ouija Board. (See an online ouija board at

http://www.museumoftalkingboards.com/WebOuija.html)

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[vongrunt]

Sorry I was too quick on submit button. Deadlock info won't apper in log unless trace flag 1204 is turned on. You can do it with DBCC TRACEON( 1204 ) or through server startup parameters (-T1204). And my ASCII() example eliminates only numerics (0-9) at first character.

 

[Glowworm27]

vongrunt,

Thanks, I think I can add the asscii code for the other chars [.-!] etc.

and I realy can't mess with the SQL server in that way, the server is in production and can't be fiddled with.

So I think I will just implment your idea
Thank You

I can't program for the Unknown, I have A keyboard not a Ouija Board. (See an online ouija board at

http://www.museumoftalkingboards.com/WebOuija.html)

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips