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
nussedogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
nussedogAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Run a SQL Profiler and capture the SQL statements.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

nussedogAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
nussedogAuthor Commented:
That makes sense. Since I was never able capture it
0
nussedogAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nussedogAuthor Commented:
Newbie thing I am guessing.  Probably to obvious for the rest of the VS world
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Dev Tools

From novice to tech pro — start learning today.