January 16, 2015 at 12:00 am
Comments posted to this topic are about the item Debugging SQL Server
January 16, 2015 at 12:21 am
As yet I haven't found a descent debugging tool for either SQL Server or ORACLE and I have been developing in both systems for some times.
For SQL debugging I use print to display variable values.
For SSIS I use script tasks to display variables.
In SQL Server, there is a debugging tool similar to .NET where one can put a break point and step through the code. However I don't find it as powerful as that in .NET and as a developer I use the tried and tested method, print, that I have relied on for so many years.
Look forward to suggestions from other readers.
January 16, 2015 at 1:25 am
In SQL I use the Print statement if I need to check one value and if I need more, for example to see what it will insert or what it has deleted, I will insert into a temp table and check from there. I also insert a standard comment line above each one so I can search through a long procedure and remove or comment out the debug code when happy with it all.
January 16, 2015 at 2:21 am
With SQL Server I'm definitely a 'print'-er. Obviously if writing SQLs in an IDE for .NET then can do quite a bit of debugging in there too.
For stored procedure debugging I find the RAISEERROR WITH NOWAIT statement useful too
January 16, 2015 at 3:33 am
This is a very good topic and look forward to learning other methods to debug SQL SPs.
I am a "Print a comment" debugger and recently using Raise Error to do similar messages.
For SSIS I use the Script Task with a message box.
January 16, 2015 at 4:01 am
I'm still a PRINT and temp table guy I'm afraid :blush: feels quite stone age but you know where you stand.
Debugging applications as a developer I tend to use the profiler on the dev system a lot to be sure of exactly what query is being passed, and go from there with the aforementioned techniques.
January 16, 2015 at 4:03 am
PRINT and temp tables +1
January 16, 2015 at 4:37 am
But. if log to temp table and rollback occurs, then you lost the log. And during rollbacks the most important logs.
January 16, 2015 at 5:31 am
A combination of PRINT, logging to a table and even raiserror depending on what I'm investigating and where the whole project is in the process.
Then there's always examining the execution plans. Grant did an excellent presentation on this at SQL Saturday in DC this past year and some of his tips have been very handy in unraveling exactly what your SQL is trying to do.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
January 16, 2015 at 6:01 am
evgeny.marmalstein (1/16/2015)
But. if log to temp table and rollback occurs, then you lost the log. And during rollbacks the most important logs.
Log to a table variable and then, after any rollbacks could have occured copy to temp table if necessary for querying, or just select from...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2015 at 6:02 am
call.copse (1/16/2015)
I'm still a PRINT and temp table guy I'm afraid :blush: feels quite stone age but you know where you stand.
Stone age, but it works. I'm there too. Print, raiserror (where necessary), temp tables/table variables, stats IO/Time and execution plans
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2015 at 6:29 am
I'm using a general purpose logging table where any part of the application (backend or frontend) can log to. I'm curious to know what your table(s) look like. Naturally, the columns that are filled by the code that registers an event varies. It depends...
create table myapp_eventlog
(
eventid int identity(1,1),
process_name varchar(50),
computer_name varchar(50),
network_user varchar(50),
app_user varchar(50),
time_started datetime,
time_ended datetime,
elapsed_minutes decimal(10,2),
event_info1 varchar(1000),
event_info2 varchar(1000),
event_info3 varchar(1000)
)
January 16, 2015 at 7:23 am
I will write values to a debug_table to debug processing within a trigger. ex. create debug_table(code varchar(20), desc varchar(254))
For stored procs, for simple debugging I use a print.
For more complex debugging of stored procs, I will convert the proc into a script and run it in sections. Plus I use select statements throughout to follow the logic.
Example:
select '1', @param1, @param2, @param3
select '2', * from #temp_table
select '3', col1, col2, col3 from table1 t1, table2 t2 where ...
January 16, 2015 at 7:43 am
I was excited at first to read what others used (to learn new trick) but I feel we mainly all rely on the same ways to debug.
When I had the need to use the SQL debugger, it's mainly because the underlying code "was the issue" (not set based but coded if it was a .Net application, line by line). In that case it helped a bit.
Otherwise, almost all of the time it's print, temp / var tables, raiserror or sometimes profiler when too many SP / statements are involved.
In short whatever I feel will get me to the problematic point the fastest possible.
Printing stuff to debug feel it is the same strategy I used when I was learning to debug batch file under old DOS 5 & 6.xx way back in the past... (But SQL has a fancier GUI instead of the 80x25 DOS txt editor Yeah !)
January 16, 2015 at 8:13 am
Temp tables
Output with the Merge statement along with rollback
Depends on the situation.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply