Debugging TSQL (1 Viewer)

Dear Access Expert.

I am using Access to connect to a SQL Server 2005 Expres Backend. I am using Management Studio 2008 Express.

Eventhough I am able to set break points int Management Studios 2008 the execution never halts.

I believe this is a limitation of SQL Server 2005. Is there any workaround?

I am new to SQL Server and without the ability to Step through my Stored Procedure Scripts it makes learning and debuggin that much more difficult.

Thank you for your response / suggestions.

What are you trying to debug? You have to understand that Management Studio is not SQL Server. It is an external program that provides an interface. So, if you are running something in Access you won't be able to have stored procs have breakpoints and stop. You would need to debug completely in Management Studio for that. How are you running the SP from Access? Are you using ADO? If so, you can configure it to send back error messages from SQL Server. It isn't automatically going to send them to you unless you ask though.

I believe for 2005 you could only debug with a Visual Studio installment. You could set breakpoints but it has to be started by Visual Studio to actually debug.

With 2008 (2008 R2?) that is no longer a requirement. To start debugging, you would press the Play button instead of "Execute button. The Play button is to right to Execute button.

However, I'm inclined to think that it's not terribly useful debugger. It can only tell you what values are set for variables but wouldn't let you view any intermediate table. For that reason, I tend to not bother with T-SQL debugging and just write queries that are easy to validate.

Also, you have to appreciate that when you're working with T-SQL, you really should not approach it the same way as you approach VBA. T-SQL is in fact very poor procedural language - you want to think of it in terms of sets, not step by step procedures. When you write a stored procedure that work with data as a set, you will see much more benefit and vice versa - if you truly need an iterative processing (a good example of this is looping a recordset and peeking into a previous adjacent row for values to do some kind of computation*), then VBA + { DAO | ADO } is probably going to do a better job.



* SQL Server does have Windows functions which make iterative processing possible to some degree so some row-wise comparisons can be done just dandy inside T-SQL, but not all, of course.

Thanks for your responses Bob and Banana.

I believe for 2005 you could only debug with a Visual Studio installment. You could set breakpoints but it has to be started by Visual Studio to actually debug.

Click to expand...

Yes I read this too Banana. I was hoping there was another easier way than Visual Studio since I do not have have experience with Visual Studio. In my 2002 ADP book they mention a tool called Microsoft Query Analyzer but I am assuming such a tool does not exist for SQL Server 2005?

For that reason, I tend to not bother with T-SQL debugging and just write queries that are easy to validate.

Click to expand...

Interesting. What do you mean by queries that are easy to validate? Simple queries where you are confident about the result?

if you truly need an iterative processing (a good example of this is looping a recordset and peeking into a previous adjacent row for values to do some kind of computation*), then VBA + { DAO | ADO } is probably going to do a better job.

Click to expand...

I actually created my algorithm in VBA and only used Stored Procedures to produce the recordsets but my manager asked me to put all the procedural logic in TSQL. He wants me to push as much code onto the server as possible.

Banana why do you say VBA would do a better job? According to my manager it is more effecient to keep as much on the server as possible.

My VBA algorithm leverages Thrown errors. Specifically Index Violation error and Referential Integrity Error. Is there a way to catch Errors in TSQL like in VBA?

Thank you for your help.