Solved: Debug SQL 2000 stored procedures in VS 2005 Pro.

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

neverrealm

Debug SQL 2000 stored procedures in VS 2005 Pro.

I am looking for help in stepping through a SQL 2000 stored procedure in Visual Studio 2005 Pro.

What I can already do is attach and step into the stored procedure. However, this isn't really what I need. What I really need to be able to do is set a breakpoint in the stored procedure that will be hit whenever a DTS package that calls this stored procedure runs. I set a breakpoint, but the DTS package doesn't run the SP in any kind of debug context, so the breakpoint never hits.

Are there any solutions to this issue? I've tried googling for a few hours, but to no avail at this point. If I can't get a breakpoint to hit from the DTS package, is there anyway to step into a DTS package. I really need to debug the SP within that framework.

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

DBoyes

The stored procedure will be running inside the DTS package from the perspective of one of your connection objects - what I would normally do is emulate the same connection in Query Analyzer, then copy the code from the SP, and if necessary, run it step by step from within Query Analyzer.

The only scenario when I can see this not working quite right is if your DTS package is in some way passing parameters to the SP, which might vary, or the SP is conditionally being called in a different manner depending on the results of a previous step within the DTS package.

Let me know if any of the above helps - I have not heard of a way of setting breakpoints - not in 2000, at least!
Regards,
David.

neverrealm

ASKER

The problem is that the SP is about the 10th step in the DTS package, and I need all of the other steps to complete first. Duplicating them in some other manner would be time-consuming.

I'm not using query analyzer for debugging, I'm using Visual Studio 2005 Proffessional Edition, which is where the breakpoints are being set. After connecting to the SQL Server and attaching to the sqlserver process I can debug SP's, but so far I've only figured out how to debug them while just stepping into them. I really need to debug it at the moment that it's actually being run in the DTS package.

ASKER CERTIFIED SOLUTION

nashwin80

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

ommer

You may try to split your DTS into two parts. Make all the steps leading toward the sp you want to debug into one DTS, then you can call that DTS in QA using shell cmd, or in your app (Vb6, dotnet) which will have a breakpoint, then step through the rest of the steps.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.