SQL code that works fine in Management Studio but not in Visual Studio
SQL code runs fine in Management Studio but when used in a SSRS report, the Group By and Having statements in the query blinks (it displays and 1 second later is gone and then displays and is gone). This is using visual studio 19 community. If I try the report in Visual Studio 13 it doesn't blink but it also doesn't pull up any data. The report itself runs fine in both versions, but I need to make some changes to the report so need to access the query.
Note I just migrated from SQL 2008 R2 - I don't know if this has anything to do with it?
Fund-code-that-doesn-t-work.txt
Note I just migrated from SQL 2008 R2 - I don't know if this has anything to do with it?
Fund-code-that-doesn-t-work.txt
ASKER
I believe I understand what you are saying... I have confirmed two things, but if I'm going in the wrong direction please let me know. There is a specific line that is added to the rsreportserver config file if you use sql native client. I do not have that line added, so am not using sql native client for SSRS reports. I believe the only place I'm using it is when I run the ETL packages to update our data warehouse. I also confirmed for SQL 2014 that I am using the correct version of native client - SQLNCLI11
Do you have any other ideas??
Do you have any other ideas??
When run in ssms, how long does the query run to return answers?
Can you convert this query to an storedprocedure to which you will pass parameters?
Try within your VS code to generate the simplest similar formatted query
Just to confirm VS can query
Your ssrs uses a VS code to get data?
Can you convert this query to an storedprocedure to which you will pass parameters?
Try within your VS code to generate the simplest similar formatted query
Just to confirm VS can query
Your ssrs uses a VS code to get data?
ASKER
In SSMS it ran in 6 seconds.
I do know that in VS if I remove the blinking code, it runs fine in the SSRS query... I have other reports that also have a group by with a having statement and those also work fine. I'm using the SSRS plugin to VS and then creating shared data sets along with a query to get the data.
I know how to create a stored procedure, but have never used parameters with a stored procedure so would need to learn how to do that first.
I do know that in VS if I remove the blinking code, it runs fine in the SSRS query... I have other reports that also have a group by with a having statement and those also work fine. I'm using the SSRS plugin to VS and then creating shared data sets along with a query to get the data.
I know how to create a stored procedure, but have never used parameters with a stored procedure so would need to learn how to do that first.
You may have, but my use of parameters might be not the terminology .....
does anything change in the query, or it auto adjusts a range of data?
the group by ..
the only thing to check is whether the size of the query is what causes the issue.
https://social.msdn.microsoft.com/Forums/en-US/cfb656ca-c71f-497c-ad4b-0ec6c2dfa6b5/size-limit-for-dataset-query-in-report-designer
Something is causing an issue. possible not the data, but the query itself are you assigning the query into a variable before execution??
does anything change in the query, or it auto adjusts a range of data?
the group by ..
the only thing to check is whether the size of the query is what causes the issue.
https://social.msdn.microsoft.com/Forums/en-US/cfb656ca-c71f-497c-ad4b-0ec6c2dfa6b5/size-limit-for-dataset-query-in-report-designer
Something is causing an issue. possible not the data, but the query itself are you assigning the query into a variable before execution??
ASKER
The number of lines on the query are pretty small - maybe 40 lines. I have a lot more complex queries that I use. And no I'm not assigning the query into a variable - just a straight query for the report data.
If you do not adjust the query, or can incorporate a date range that will auto adjust.
try creating an SP that will generate the same results and see whether executing the SP will be better.
Suspect the length of the query might be what causes your issue
the full query is more than 6000 bytes. that might be the issue.
do you capture errors to see why it does not return data, if you query within VS do you have data coming back?.
try creating an SP that will generate the same results and see whether executing the SP will be better.
Suspect the length of the query might be what causes your issue
the full query is more than 6000 bytes. that might be the issue.
do you capture errors to see why it does not return data, if you query within VS do you have data coming back?.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
SQL server usually can communicate one level down and one level up from the SQL client library.