Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

sql server 2005 debugging

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
dotnet0824
Asked:
dotnet0824
  • 4
  • 3
1 Solution
 
dublingillsCommented:
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
 
dotnet0824Author Commented:
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
 
dublingillsCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
dotnet0824Author Commented:
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
 
dotnet0824Author Commented:
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
 
dublingillsCommented:
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
 
dublingillsCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now