debug/detect error on SP. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

debug/detect error on SP.

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-05 : 16:53:11

I'm running a asp.net website with SQL2005.
I have a SP which i run from the asp.net website ,something like 20 times a day.
Every now and then i find out that the SP didn't work .

This is the SP:

DECLARE @Username int
SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
	UPDATE Users
	SET u_paid_member = @IsPaid
	WHERE
		@Username = u_username
		u_paid_member = @IsPaid
	WHERE
		@Username = u_username

Im looking for a way to know when and why the SP doesn't work (deadlock or something)

thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-05 : 16:56:26

What do you mean by >>> the SP didn't work
Do you see any errors?
Before you do an UPDATE you might want to check if there is a value in @UserName.
Also your WHERE clause is repeated.


DECLARE @Username int
SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
	UPDATE Users
	SET u_paid_member = @IsPaid
	WHERE
		@Username = u_username
		u_paid_member = @IsPaid
	WHERE
		@Username = u_username

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-05 : 16:56:37

you probably made a typo but your syntax is wrong.

use try ... catch in your stored procedure and call raiserror in the catch to see what the error was.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-06 : 06:58:41

Hi , Thanks for the replies .
Yes it was a typo , the correct SP is :

DECLARE @Username int
SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
	UPDATE Users
	SET
		u_paid_member = @IsPaid
	WHERE
		@Username = u_username

quote:

What do you mean by >>> the SP didn't work
Do you see any errors?

No , i dont see any errors , the SP is used on a producation website and SP runs every day several times without a problem.
BUT , once every few days , when the SP is run , trying to set u_paid_member = 1 , the SP fails and u_paid_member doesn't update to the "1" value.

I dont really know if it fails , i just know that the result is that the u_paid_member isn't updated to "1".

the question is : how can i find if it fails and find out why ?

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-06 : 07:00:47

quote:
use try ... catch in your stored procedure and call raiserror in the catch to see what the error was.

spirit1 , i never used try and catch in sql , maybe you can give me an example of what you mean?
Can i use this on producation website ?
Where to would i get the error log?

thanks:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 07:29:07

looks like this:

begin try
... your code
end try
begin catch
... your error handling code
end catch

the whole topic is well explained in BOL = Books Online = SQL Server help

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-07 : 08:25:23

Hello,

I have added the try and catch code to the sql SP , and im posting it for everyone else:


	DECLARE @Username int

BEGIN TRY 

	SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
	UPDATE Users
	SET
		u_paid_member = @IsPaid
	WHERE
		@Username = u_username

END TRY
BEGIN CATCH
	INSERT INTO tbErrors 
	Values (
		ERROR_NUMBER(),
		ERROR_SEVERITY(),
		ERROR_STATE(),
		ERROR_PROCEDURE(),
		ERROR_LINE(),
		ERROR_MESSAGE()
	)

END CATCH;

I added a table called tbErrors to store the error log when something happens.
Nothing came up till now , im still waiting to "catch" something.

Maybe there is another way todo a bigger trace of errors , is it possible to track all changes made to a certain column of a certain table , and to log those changes + (success/fail comments) ?

thanks!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-09 : 12:52:50

anyone?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-09 : 12:58:04

Are you still getting errors at the front end? Also check if there is a value in @username.


Set @username = null
SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
IF @username IS NOT NULL
	BEGIN
		UPDATE Users
		SET
			u_paid_member = @IsPaid
		WHERE
			@Username = u_username
	END

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-12 : 08:08:43

hi , thank for the post.

the bug is still there , the try and catch didn't "catch" anything.
i'll try checking if the @username is empty.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-12 : 08:14:04

I'll ask the same question again , maybe i wasn't clear .

i think the best way to find the BUG is to track the whole table.
Meaning , create some sort of command/mechanism/code that will log into a file every command that was performed on the table.
i.e the log file should look like this:

12/08/07 22:35:00 UPDATE Users SET u_paid_member = 1 WHERE @Username = 3544 - OK
11/08/07 23:32:00 UPDATE Users SET u_paid_member = 0 WHERE @Username = 3944 - OK
11/08/07 23:32:00 SELECT * from Users WHERE @Username = 3244 - OK
10/08/07 23:32:00 SELECT * from Users WHERE @Username = 3244 - FAILED

the log should track all changes made to the table.
this way i can find when and why the SP failed.

IS THAT POSSIBLE?

thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-12 : 09:25:08

[code]UPDATE u
SET u.u_paid_member = @IsPaid
FROM Users AS u
INNER JOIN Subscriptions AS s ON s.u_username = u.u_username
WHERE s.s_id = @SubscriptionID[/code]

E 12°55'05.25"
N 56°04'39.16"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-08-12 : 20:35:24

Put the try catch statement in your asp.net.
I've noticed that if you call an SP with .NET as soon as there is an error it's detected by ADO.NET and the rest of the SP does not run.

You can also setup a trace and filter for the table name while saving results to table. You can use a tool like lumnigent to examine the transaction log and filter for the table or statements in question. You can also use triggers to keep track of data changes to tables.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-13 : 09:28:17

[code]DECLARE @Username int

BEGIN TRY

SET @Username = (SELECT u_username FROM Subscriptions WHERE @SubscriptionID = s_id)
UPDATE Users
SET
u_paid_member = @IsPaid
WHERE
@Username = u_username

INSERT INTO tbErrors2
Values (@SubscriptionID ,@Username,@IsPaid)

END TRY
BEGIN CATCH
INSERT INTO tbErrors
Values (
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
)

END CATCH;[/code]

I have some progress , using the above code i was able to track a user (for the example , lets say its username=1000) on which the above command has been executed.

i have a log in tbErrors2 , which shows that username=1000 was set to u_paid_member=true , but when looking at the ispaid variable after this was executed , the "u_paid_member" was STILL FALSE.

1. Any idea why this happend?
2. Try and Catch - would this "catch" a deadlock situtation that can cause the "u_paid_member" not to update?

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-15 : 07:35:03

anyone?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-15 : 08:21:54

The problem may not be with the stored procedure.

In ADO.net:
1. if you are using manual transactions, make sure you use commit/rollback correctly.
2. If you are using automatic transactions, you need to call either the SetComplete or SetAbort methods.

pootle_flump

1064 Posts

Posted - 2007-08-15 : 08:33:00

Try checking @@ROWCOUNT after the update. My bet is there is no record with username 1000 in the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources