Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Daily SQL Articles by email:
Sign up
Back
SQLServerCentral
Register
Home
»
Reporting Services
»
Reporting Services 2005 Development
»
SSRS 2008 will not return correct dataset...
SSRS 2008 will not return correct dataset from Stored Procedure
Post reply
Like
353
Add to Briefcase
SSRS 2008 will not return correct dataset from Stored Procedure
View
Options
Author
Message
samalex
samalex
Posted 9 years ago
#776865
Hall of Fame
Group: General Forum Members
Points: 3557
Visits: 1101
Hi,
I have a stored procedure that generates different output datasets based on the parameter, but SSRS 2008 appears to be populating the column list from the first Select regardless of logic within the stored procedure and regardless of which parameter I feed it.
It needs to just execute the SP and run with what comes back, not evaluate the code behind the SP and try to guess what I want it to do, which it's doing incorrectly. I've tried setting-up the dataset using Text hard coding the parameter and also as Stored Procedure but same results either way. And I was able to verify SSRS is returning the first Select because i changed the order of the Selects and it changed my columns in the report.
Something else is if I open Query Designer it shows the correct dataset from the stored procedure, but clicking OK then Fresh Fields still shows the wrong dataset.
I'd really rather not split out the logic because it just doesn't make since to do so. I've done this before in other versions of SSRS, so does anyone know of a work around for this bug?
Thanks...
Sam Alex
39
Quote
samalex
samalex
Posted 9 years ago
#776885
Hall of Fame
Group: General Forum Members
Points: 3557
Visits: 1101
Hi Everyone,
I found a 'solution' that works, but it's crummy I have to do this. I ended up bundling my SQL statements in to a variable and executing those variables at the end to make the report work. I guess if it doesn't find a valid Select it just executes it and runs with what comes back, which is what it should be doing from the start.
Still, any suggestions on how to avoid using Dynamic SQL to make this work? It's better then splitting the logic into multiple stored procedures, but still not what I hoped to do.
Thanks --
Sam Alex
47
Quote
Jack Corbett
Jack Corbett
Posted 9 years ago
#777479
SSC Guru
Group: General Forum Members
Points: 124702
Visits: 15067
SSRS stores the names of the columns as part of the report definition (rdl file), that's the only way it can know what to display where in the report itself. So if you save the report with the columns FirstName, LastName, and BirthDate, those are the only columns the report itself knows about. SO if you change the parameter and the stored procedure now returns CompanyName, ContactName, PhoneNumber the report can't know about those columns because they are not stored in the report definition (rdl file). If I were doing this I would probably write the stored procedure so that it always returns ALL the columns. In my example I would always return FirstName, LastName, BirthDate, CompanyName, ContactName, PhoneNumber, but the unused columns would be NULL.
Jack Corbett
Consultant
Straight Path Solutions
Dont let the good be the enemy of the best. --
Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. --
Rod at work
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
39
Quote
samalex
samalex
Posted 9 years ago
#777609
Hall of Fame
Group: General Forum Members
Points: 3557
Visits: 1101
Jack Corbett: SSRS stores the names of the columns as part of the report definition (rdl file), that's the only way it can know what to display where in the report itself. So if you save the report with the columns FirstName, LastName, and BirthDate, those are the only columns the report itself knows about. SO if you change the parameter and the stored procedure now returns CompanyName, ContactName, PhoneNumber the report can't know about those columns because they are not stored in the report definition (rdl file). If I were doing this I would probably write the stored procedure so that it always returns ALL the columns. In my example I would always return FirstName, LastName, BirthDate, CompanyName, ContactName, PhoneNumber, but the unused columns would be NULL.
Hi Jack,
Returning all rows is just as muddy as using dynamic SQL which is the best way I've found to make this work. This procedure will be used for processes outside of reporting, so I need the output to be clean.
My main gripe is that SSRS should see the output of the procedure, including parameters, and run with that instead of trying to get the column names from the procedure itself since the desired output wouldn't be known until it executes with parameters.
Thanks for the reply and take care --
Sam Alex
43
Quote
Jack Corbett
Jack Corbett
Posted 9 years ago
#777614
SSC Guru
Group: General Forum Members
Points: 124702
Visits: 15067
How would you suggest MS implement "dynamic" creation of columns within a dataset? At some point the engine has to know that FirstName goes in column1 if it is returned, but CompanyName goes in column1 if that is returned.
Jack Corbett
Consultant
Straight Path Solutions
Dont let the good be the enemy of the best. --
Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. --
Rod at work
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
41
Quote
thetodaisies
thetodaisies
Posted 9 years ago
#778154
Right there with Babe
Group: General Forum Members
Points: 793
Visits: 166
Please correct if i am wrong. both select statements returns same number of fields and the names of the fields are also the same but the no of records would be different from both selected statemets. if this is the case i really think it is some issue with the parameter value being checked. try returning the condition output from the SP. because SSRS doesnt do anything in SP execution logic.
38
Quote
satyajitrmohanty
satyajitrmohanty
Posted 9 years ago
#783412
SSC Journeyman
Group: General Forum Members
Points: 85
Visits: 204
Hi!!
hers the solution which seems to be working for me
Scenario- I had the stored procedure with IF ELSE in it which returned multiple result set depending on Input parameter.
i had returning four colunms for each result set for (daily,monthly,weekly,quaterly,yearly)
eg: DAILY....ACTIVE...CLOSED... SUSPENDED colunms for daily parameter grouped by daily
Problem-the dataset returnrd only for the first IF condition in SSRS designer eg-DAILY(excluded the the other but the results were showed along with BLANK values for 1 st colunm)
SOlution- create query in Desiner SSRS which returns table variable
the table variable will contain the results by excecuting main stored proc.
eg-
DECLARE @DATE TABLE (
PERIOD NVARCHAR(20),
ACTIVE int,
CLOSED int,
SUSPENDED int
)
if(@Frequency='DAILY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
else if(@Frequency='MONTHLY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
else if(@Frequency='WEEKLY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
this one works for me perfectly fine
Thanks & regards
Satyajit
35
Quote
samuel.bayeta
samuel.bayeta
Posted 2 years ago
#1780746
Valued Member
Group: General Forum Members
Points: 71
Visits: 7
Hi Every one, Though this sounds a late response to a 6 years old question, I happen to feel it is beneficial to respond a best answer as others might have referred this website for their day to day need. in short the answer to this is fake SSRS by creating your stored procedure with a hint SET FMTONLY OFF. This is a dramatic trick.
35
Quote
DB4food
DB4food
Posted 2 years ago
#1799169
Valued Member
Group: General Forum Members
Points: 73
Visits: 18
Using SET FMTONLY OFF does not work.
Some suggest using Exec under text in the dataset properties.
But doing this way won't allow me to pass parameters.
So if anyone has a solution for either of this, I would be really appreciated:
1) Have the stored procedure return the fields
2) Using the workaround of Exec, how do I pass parameter?
I have google everywhere.
This suggestion below does not work:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d8c68166-fca2-4da4-8af9-107363d96c57/ssrs-not-passing-fieldsparameters-to-report-when-stored-procedure-keeps-parameters-inside-if?forum=sqlreportingservices
samuel.bayeta (4/25/2016)
Hi Every one, Though this sounds a late response to a 6 years old question, I happen to feel it is beneficial to respond a best answer as others might have referred this website for their day to day need. in short the answer to this is fake SSRS by creating your stored procedure with a hint SET FMTONLY OFF. This is a dramatic trick.
36
Quote
Go
Post reply
Like
353
Add to Briefcase
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2018 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.