Solved

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

Posted on 2014-11-24
8
177 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 47

Expert Comment

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

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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