Link to home
Start Free TrialLog in
Avatar of royalcyber
royalcyber

asked on

transac sql breakpoint

hello,


I am new to transact sql

how do i put debug statements in transact sql and where it will be printed

how do i put breakpoint on a  transac sql  statement ; should i do this in query analyzer ?


thankyou!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Avatar of DireOrbAnt
DireOrbAnt

Stick PRINT or SELECT here and there in your code to debug it

PRINT 'Before Insert'
INSERT.......
PRINT 'After Insert'
DELETE....
PRINT 'After Delete'

Run that code in QA.
I've never used it and unsure if it still applies to 2005, but there is a T-SQL debugger:
http://www.dbazine.com/sql/sql-articles/larsen9
There isn't anything like the normal break-points you have in VS.NET... however, DireOrbAnt way is one good way of debugging your code.. it's equivalent to adding a msgbox in your code and poping it up after each step...

the outcome will show in query analyzer and if you run it as a job in the job history, but if run through a stored procedure in an application you won't get anything....

In a stored roc, you can do:
PRINT 'Before Insert'
INSERT.......
PRINT 'After Insert'
DELETE....
PRINT 'After Delete'

However I don't recall if you get the printout sequential. Maybe if you use to show result as text in QA?
Avatar of royalcyber

ASKER

where do i get the "T-SQL debugger" from for sql server 2000
ASKER CERTIFIED SOLUTION
Avatar of DireOrbAnt
DireOrbAnt

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
FYI - it is not as user friendly as VS debugging tools. I rarely bother with it unless I am really struggling - better to stick with PRINT. Also MS recommend you don't run the debugger on a prod server as it really takes up server resources.
Avatar of Aneesh
royalcyber,
> how do i put breakpoint on a  transac sql  statement ; should i do this  in query analyzer ?
There is a debugger fascility available in sql server 2000, but the thing is you can debug only stored procedures. Also you need to configure the sql service account as the user with which you are  logon to the Windows (Start->run -> Services.msc

then find the sql server servise 'MSSQLserver', rightclick-> properties , move to logon tab, there you can find the option local system account and some other account, you need to browse and  select the user with which you are logging . Once this is done, restart the service . )
Now you can go to the Query analyyzer, Press F8 to get the object browser, right click on the desired sp -> put input parameters if any,  that's it