Solved

How to view executed T-SQL statement from Visual Studio 2008 R2

Posted on 2014-11-24
8
172 Views
Last Modified: 2014-11-30
What i need:
How can i view the executed T-SQL statement from Visual Studio 2008 R2 at run time from within the Visual Studio Designer???

Problem Statement:
For unknown reasons the report i am designing is not returning any rows for the dataset. I am using a number of parameters and filters with my report. I want to see what the actual SQL statement is being passed from VS to MSSQL so that i can more quickly debug the issue.

I know that T-SQL i am using for the dataset works in SQL if i manually plug in the parameter values. But i have been unable to fathom why it is not working in VS.

Thank you in advance for your help
0
Comment
Question by:nussedog
  • 5
  • 2
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40463003
>How can i view the executed T-SQL statement from Visual Studio 2008 R2 at run time from within the Visual Studio Designer???
afaik you can't, other than click on your data set > Query > Query Designer.

>I want to see what the actual SQL statement is being passed from VS to MSSQL so that i can more quickly debug the issue.
Does this mean you have T-SQL in your SSRS report and are using it as a data source?

If yes, copy-paste it into this question.

Also, if you're having a lot of problems here a better way to do this is by creating a Stored Procedure and have that as your report's data set, with parameters passed from your SSRS report.

benefits
SP's can be compiled and optimized on the server, T-SQL in an SSRS report cannot.
Impact Analysis, aka 'What would break if we renamed this table/column?, is a whole lot easier with all of the code in the server as opposed to in the server plus hundreds of SSRS reports.
Inside the Stored Procedure you can always 'log' the SP call plus all parameters passed into a 'log' table for further analysis
0
 

Author Comment

by:nussedog
ID: 40463041
thanks Jim

Yes, embedded TSQL in Dataset. See SQL below.
The reason I am not using SPs is because it has proven very difficult to get the permissions I need to create SPs from MSSQL DBA. that is another battle for another day.

This is a very simple query that should work, but I am obviously doing something wrong.

There are four parameters as well
@prmStartDate, @prmEndDate, @prmDriverID, @prmTerminalID
These have been added as filters to dataset. So final TSQL should look like the attached with something like
"Where date is between @prmStartdate and prmEnddate and @prmDriverID is IN (xxxx) and @prmTerminalID is IN (xxxx)

The filters work for the dates but as soon as I add any of the IN filters I do not get any results. But as I said, this works fine in MSSQL if I do the query manually.

Here is the Query:

/* rdn
 Charges summary query for Driver Revenue Report
 11/17/2014
 11/21/2014 Removed invoiceheader table. Causing join issues and not needed
 */
 
SELECT
         --CASE
    --     WHEN Max(ih.ivh_hdrnumber) = 0 THEN 'N/A'
    --     ELSE Max(ih.ivh_hdrnumber)
    --   END                     AS [Invoice Number],
       Max(oh.mov_number)      AS [Move Number],
       --Sum(ih.ivh_totalcharge) AS TotalCharge,
       Max(oh.ord_hdrnumber)   AS OrderNo,
       MAX(oh.ord_status)      AS OrderStatus,
       Max(oh.ord_refnum)      AS Vin,
       Max(id.cht_itemcode)    AS [Charge Code],
       Max(ct.cht_description) AS [Charge Code Desc],
       Max(lh.lgh_startdate)   AS [Start Date],
       CASE
         WHEN LEFT(id.cht_itemcode, 2) = 'LH' THEN Sum(id.ivd_charge)
         ELSE 0
       END                     AS FreightChg,
       CASE
         WHEN LEFT(id.cht_itemcode, 4) = 'MILE' THEN Sum(id.ivd_charge)
         ELSE 0
       END                     AS MileageChg,
       CASE
         WHEN LEFT(id.cht_itemcode, 3) = 'INS' THEN Sum(id.ivd_charge)
         ELSE 0
       END                     AS InpsectionChg,
       CASE
         WHEN LEFT(id.cht_itemcode, 4) = 'FUEL' THEN Sum(id.ivd_charge)
         ELSE 0
       END                     AS FuelChg,
       Max(oh.ord_driver1)     AS Driver,
       MAX(oh.ord_revtype4)    AS Terminal,
       Max(mpp.mpp_firstname)  AS FirstName,
       Max(mpp.mpp_lastname)   AS LastName,
       Max(mpp.mpp_type1)      AS DriverType,
       (SELECT COUNT(*) FROM orderheader WHERE mov_number = MAX(lh.mov_number)) as [NumberOfVins]
FROM   orderheader AS oh
       LEFT OUTER JOIN invoicedetail AS id
                    ON oh.ord_hdrnumber  = id.ord_hdrnumber
       --LEFT OUTER JOIN invoiceheader AS ih
       --             ON ih.ivh_hdrnumber  = id.ivh_hdrnumber
       LEFT OUTER JOIN chargetype AS ct
                    ON id.cht_itemcode = ct.cht_itemcode
       LEFT OUTER JOIN legheader AS lh
                    ON oh.mov_number = lh.mov_number
       LEFT OUTER JOIN manpowerprofile AS mpp
                    ON oh.ord_driver1 = mpp.mpp_id
WHERE oh.ord_status IN ('CMP','STD')            
GROUP  BY
         oh.ord_revtype4, -- Driver
       id.cht_itemcode
ORDER  BY
       oh.ord_revtype4
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40464039
Run a SQL Profiler and capture the SQL statements.
0
 

Author Comment

by:nussedog
ID: 40464539
Vitor, I actually tried this but couldn't find the SQL statements I wanted. Any ideas what filters to apply to find TSQL statements executed by Visual Studio when testing reports?  I filtered down to my login but no luck.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40464546
If you are working in a DEV/UAT environment there's no need to filter by your login since I guess you can control who's connected.
Filter only by SQL:BatchCompleted and SQL:StmtCompleted

But if you still can't capture it then there's a good chance that the application is not sending any query to SQL Server.
0
 

Author Comment

by:nussedog
ID: 40464595
That makes sense. Since I was never able capture it
0
 

Accepted Solution

by:
nussedog earned 0 total points
ID: 40465038
Okay, I fixed this.

The problem was with the multi value parameter automatically inserting an index value of zero when defining parameters and filters.  
Parameters!ParameterName.value(0)

By removing the index value filtering for multi value parameter is now working.
Parameters!ParameterName.value
0
 

Author Closing Comment

by:nussedog
ID: 40472340
Newbie thing I am guessing.  Probably to obvious for the rest of the VS world
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Viewers will learn how the fundamental information of how to create a table.

757 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

18 Experts available now in Live!

Get 1:1 Help Now