Solved

sql server 2005  debugging

Posted on 2009-05-18
7
212 Views
Last Modified: 2012-05-07
Hi,
I want to debug a lengthy stored procedure in SQL Server 2005 which uses lot of  Table variables and temporary tables and produces output finally.

Its been difficult to analzye what each query's data would be like.
Is there a way I could write it a file or something to see what data each query would produce. .. I am aware to use print statements for variables etc, but for Data being retrieved by all these temporary tables etc how could we know what data is being retrieved.
0
Comment
Question by:dotnet0824
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

by:dublingills
ID: 24415777
Hi,

If you're running your proc in Management Studio you can put select statements into the procedure to display the results of each query and table variable as the procedure runs, i.e.
select * from @TABLE, select * from #TABLE etc.

If you have access to Visual Studio you can debug a stored procedure line by line, see this link for help on doing that:
http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005

HTH
0
 

Author Comment

by:dotnet0824
ID: 24416368
Hi,
Thanks for it. As I know thats possible like quickwatch etc. But how could i just type in the SQL stored proc window  Select * from #temp (Which doesnot exist earlier) and check its result like .. would the vs.net 2005 allow u to embed statements while debugging. It doesnot allow u that right.!! correct me if i am wrong.
0
 
LVL 4

Expert Comment

by:dublingills
ID: 24416672
No you're not wrong on that score, and in fact the output window doesn't display inline select statement results until the procedure exits.

The only option I can suggest to you is the one that I use myself which is to create what I call a debug copy of the stored proc and put select statements inline to display the contents, you then call the debug procedure from management studio which does display inline selects as it goes.  Something like this (if you can understand my gibberish!):
create procedure spTest as

insert table1 (field1, field2) select field1, field2 from table2;

insert table1 (field1, field2) select field1, field2 from table3;

go
 

create procedure spTestDebug as

insert table1 (field1, field2) select field1, field2 from table2;
 

--debug bit

select * from table1;
 

insert table1 (field1, field2) select field1, field2 from table3;

go

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:dotnet0824
ID: 24416794
thanks a lot.  OR another way is to comment out everthing until u want the result to be printed. check it and uncomment rest and go by that way till end to understand.
0
 

Author Comment

by:dotnet0824
ID: 24416829
Another way I was thinking is
Is there a way we can write the results of queries to Textfiles or whatever like in SQLServer we can execute a query to a grid or table etc right..!!

Just wondering if we could put such kind of write statements in the stored proc so that it would be easier for us to view the output in textfiles or whatever. If you have done could u please send those statements
0
 
LVL 4

Accepted Solution

by:
dublingills earned 250 total points
ID: 24416891
Sure.  If you're not opposed to third party tools you may want to take a look at this:

http://www.sqlsolutions.com/products/sql-ultimate-debugger/


0
 
LVL 4

Expert Comment

by:dublingills
ID: 24416907
If xp_cmdshell is enabled try this in your stored proc:
EXEC xp_cmdshell 'bcp "SELECT * FROM [tableName]" queryout "C:\textfile.txt" -T -c -t,' 

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now