Viewing actual SQL code sent to database via DataSources 4

[toekneel]

I'm working in Visual Studio 2005 with VB.NET. In the 2005 environment, you can work with GridViews and other helpful tools that do the work for you so that you don't have to build much code.

That would be fine and good, but when problems arise, troubleshooting is difficult. Quite often I feel that if I could only see the actual SQL query that is sent to the database, I could easily resolve my problems. I have found out how to view the basic SQL code, (SQLDataSource.SelectCommand.ToString) but that doesn't fill in the values passed with the parameter which is used, so it doesn't help. I've seen code for capturing parameter values, but that doesn't add all of the formatting that would be in the final SQL code.

Can anyone help me by telling me how to capture the actual SQL code (with my parameter information imbedded in it) as it appears when it is presented to the database?

Thanks!

 

[RiverGuy]

I would start with using the SQL Server Profiler.

 

[toekneel]

I can't say that I've ever heard of that. Is that embedded in Visual Studio? Will I find information there explaining how to use that?

Thanks!

 

[gmmastros]

http://www.developer.com/db/article.php/3482216

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[macleod1021]

Open up SQL Management Studio and connect to the server. Then click on Tools >> SQL Server Profiler. this will open a new window. You can do a generic trace by clicking the "New" Trace button and then click run.

You may want to alter the filters a bit to get just what you want.

Also...run this on a workstation...NOT the server. It can be very resource intensive and you don't want to degrade your server's performancefor this.

im in ur stakz, overflowin ur heapz!

 

[macleod1021]

Nice link George. I put it in my favorites so that I can pass it on to others who ask in the future.

* 2 U

im in ur stakz, overflowin ur heapz!

 

[toekneel]

Thanks for the help. It still doesn't present it in the way that I'd like to see it- this is essentially joining what I had been doing before into one string- but it is easier to see and work with than what I had been attempting in VB.NET. I'll play with this and see if I can resolve my issues from this point.

 

[macleod1021]

Another trick is to put it into a table...that way you can run queries against it.

im in ur stakz, overflowin ur heapz!

 

[toekneel]

It still doesn't put it exactly like I want to see it, which is how I would have built the query myself in code. I would have an end result that would look like

Select * from TABLE where STATE = 'New Mexico'

rather than

Select * from TABLE where STATE = @Parameter

This allows me to verify the exact way that the parameter is pulling in, which appears to be the problem when working with these SQL Data Sources or Object Data Sources in VS2005. I'm trying to set up code so that more than one discrete item can be pulled into the GridView. I don't see any easy way of doing this in the wizards and such in VS, so I'm attempting to code it.

 

[macleod1021]

So what you're looking for is how's actually written in code?

If that's the case, you'd have to write an appthat would read through your code and find your select statements.

im in ur stakz, overflowin ur heapz!

 

[toekneel]

I guess that puts me back to square one, doesn't it!

Any idea how to do this in Visual Studio 2005? I'm using SQLDataSources; attempting to read what the select statement is and hoping to be able perhaps to modify the SelectCommand for the SQLDataSource at runtime in order to pull this off.

 

[macleod1021]

Unless there's something I'm not aware of, the only way to do this is to actually parse the .vb code files.

If you create a new app, you can point it at a directory and tell it to parse all files...and if it comes across a file/line that has the word Select, Insert, or Delete to log it (and any other infomation you want). You could also have it output each line to a text file until you get to the execute command.

im in ur stakz, overflowin ur heapz!