SQL stmt. for debug? 2

[jhonty]

Hi guys,

How anyone can set/code the debug level, so that he can see the actual sql stmt. where error has occured? One thing I've think that I should take stmt. in one variable and if return is error print that variable. Is there any other way Sybase provides or any body can suggest.

Thanks and regards.

 

[JMCraig]

Sorry, but it's unclear what you need. Are your running a long script or a stored procedure or what? If you provide a bit more detail, we may be able to help you. I'm not aware of any setting that'll "set the debug level" in the manner you're hoping to find, but if you can let us know what it is you're doing, we might be able to help you.

 

[jhonty]

Hi JMCraig,

Thanks for your response and efforts. Its like user can configure their own debug level, higer level would return (in log)only SP name, intermediate level will tell little more (user defined error msg. and desc.) and lowest level will tell the actual sql stmt. where error occured, this is what users expectation, I remeber in SYBASE APT there were global variable which was returning the sql stmt. but not in TSQL. I understand that this will increase the length of the code. Do u've any idea abt. this, how to manage such things efficiently?

regards.

 

[JMCraig]

There may be some kind of global Trace Flag setting you can use, but that type of capability isn't something I'm familiar with. One thing you can do is enable auditing and then you can enable and disable this (but the results don't go into the log, but into the sysaudits DB and the error and SQL isn't correlated, I don't believe). In any case, you might take a look at the auditing portion of the Sys Admin Guide and focus on the part that mentions capturing of the actual queries and see if that might get you close to what you want.

I've found it very helpful to build in a way to capture SQL right in the application I'm coding. This way, my application can grab the SQL that's generated by my application and also record the error messages I get back. This is so handy that I understand your interest in having a way to do this.

You might also try the Troubleshooting and Error Guide and see if there's anything in there.

Good luck; sorry not to be of any direct help.

J M Craig
nsjmcraig@netscape.net

 

[RayPeck]

To locate an error, I always put the following after each insert, update or delete:

if @@error != 0
begin
rollback tran
select @msg = "xxx"
raiserror 25xxx @msg
return
end

If you are calling a stored procedure:

exec @status = stored_proc_p

if @status != 0 or @@error != 0
begin
same as above
end

This will catch every single error that occurs, even when nested stored procedures or triggers are involved and properly handle them.

Then you can simply use different @msg or different raiserror numbers to identify which procedure, or which exact SQL statement gave the error.

The above should be standard coding or you will run into problems anyway, and it has the additional benefit of allowing you with @msg or raiserror number to more quickly find which SQL statement caused the error.

Ray