Link to home
Start Free TrialLog in
Avatar of nussedog
nussedog

asked on

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

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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
Avatar of nussedog
nussedog

ASKER

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
Run a SQL Profiler and capture the SQL statements.
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.
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.
That makes sense. Since I was never able capture it
ASKER CERTIFIED SOLUTION
Avatar of nussedog
nussedog

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Newbie thing I am guessing.  Probably to obvious for the rest of the VS world