Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

sql

Data Set returns but Report doesn't display

I'm returning a data set from a stored procedure. When I go to preview the
report I'm getting an error: An error has occured during report processing.
Invalid attempt to read when no data is present. I made a minor change
(added a parameter) to the stored procedure that wouldn't impact the data set
being returned and the data set returns. The report parameter is set up
correctly in the report but is not displayed anywhere on the report. The
build succeeded. Does this error mean I need to drag & drop my fields back
onto the report layout again?I was able to fix this issue. Re-ran the stored procedure and then re-added
all of the fields. For some reason it didn't want to work when I tried it
before.
"patty" wrote:
> I'm returning a data set from a stored procedure. When I go to preview the
> report I'm getting an error: An error has occured during report processing.
> Invalid attempt to read when no data is present. I made a minor change
> (added a parameter) to the stored procedure that wouldn't impact the data set
> being returned and the data set returns. The report parameter is set up
> correctly in the report but is not displayed anywhere on the report. The
> build succeeded. Does this error mean I need to drag & drop my fields back
> onto the report layout again?

Data Retrieval Question

I have a report that queries a table in the database (using a stored procedure) and displays various things about a customer. The report is basically displaying the same information that one of the screens in our web app displays. The problem is that one of the fields that I need to display on the report is not stored in the database table it is calculated. The calculation is performed by doing some database calls and various other stuff. A method exists for this calculation in our data layer that I would like to call from the report. My question is what is the best way to go about doing this call? Can/Should I do this from a custom code assembly?
Thanks...
Spyork
> Can I do this from a custom code assembly?
Yes. Here's how. ( http://msdn.microsoft.com/sql/sqlwarehouse/reportingservices/default.aspx?pull=/library/en-us/dnsql2k/html/erscstcode.asp )

> Should I do this from a custom code assembly?
Yes. Unless you want to rewrite your code in the funny little Reporting Services text editor dialog that accepts VB code.

- Ian

Data retrieval is much slower in RS than in management studio

A call to a stored procedure completes in 13 seconds when ran from within SQL Server Management Studio. A report whose data source uses the same stored procedure can take as long as 10 minutes to run. The same parameter values are used in both the direct call and the report. The execution log says almost all of that time is spent on data retrieval. How could that be? What might be the cause?If you didn't already, try profiling your sql instance right before you run your report. Have tsql statements shown so you can see a better breakdown of each command executing. This might show that the parameters used for the report are actually different than what you're expecting.|||I'll give it a try. However, looking at the Parameters column from the ExecutionLog table confirms that the values are the same. The report has the same parameters as the stored proc and simply passes the its parameter values along to the stored proc.|||

I set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.

When ran via RS, there are over 50x the number of reads.

From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds

This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds

|||

Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)

Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.

|||I notice you're not specifying a schema in your call to the stored procedure; do you have multiple versions of the SalesByDivision proc? If so, it might be defaulting to the one you want when you're connected in SQL Management Studio and an older or incorrect version when run under the context of the reporting services report. If not, I would trace the individual SQL statements as already mentioned and look for the statement(s) with highest reads to tune.|||

Thanks for the questions.

Yes, I copied the SQL from profiler then ran that in SQL MS.

These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.

|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||

WITH RECOMPILE

Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITH RECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.

|||Thanks for getting back with the solution buckenn. This was really helpful.|||

This is FANTASTIC!!

I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.

Data retrieval is much slower in RS than in management studio

A call to a stored procedure completes in 13 seconds when ran from within SQL Server Management Studio. A report whose data source uses the same stored procedure can take as long as 10 minutes to run. The same parameter values are used in both the direct call and the report. The execution log says almost all of that time is spent on data retrieval. How could that be? What might be the cause?If you didn't already, try profiling your sql instance right before you run your report. Have tsql statements shown so you can see a better breakdown of each command executing. This might show that the parameters used for the report are actually different than what you're expecting.|||I'll give it a try. However, looking at the Parameters column from the ExecutionLog table confirms that the values are the same. The report has the same parameters as the stored proc and simply passes the its parameter values along to the stored proc.|||

I set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.

When ran via RS, there are over 50x the number of reads.

From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds

This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds

|||

Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)

Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.

|||I notice you're not specifying a schema in your call to the stored procedure; do you have multiple versions of the SalesByDivision proc? If so, it might be defaulting to the one you want when you're connected in SQL Management Studio and an older or incorrect version when run under the context of the reporting services report. If not, I would trace the individual SQL statements as already mentioned and look for the statement(s) with highest reads to tune.|||

Thanks for the questions.

Yes, I copied the SQL from profiler then ran that in SQL MS.

These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.

|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||

WITH RECOMPILE

Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITHRECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.

|||Thanks for getting back with the solution buckenn. This was really helpful.|||

This is FANTASTIC!!

I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.

Data retrieval is much slower in RS than in management studio

A call to a stored procedure completes in 13 seconds when ran from within SQL Server Management Studio. A report whose data source uses the same stored procedure can take as long as 10 minutes to run. The same parameter values are used in both the direct call and the report. The execution log says almost all of that time is spent on data retrieval. How could that be? What might be the cause?If you didn't already, try profiling your sql instance right before you run your report. Have tsql statements shown so you can see a better breakdown of each command executing. This might show that the parameters used for the report are actually different than what you're expecting.|||I'll give it a try. However, looking at the Parameters column from the ExecutionLog table confirms that the values are the same. The report has the same parameters as the stored proc and simply passes the its parameter values along to the stored proc.|||

I set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.

When ran via RS, there are over 50x the number of reads.

From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds

This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds

|||

Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)

Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.

|||I notice you're not specifying a schema in your call to the stored procedure; do you have multiple versions of the SalesByDivision proc? If so, it might be defaulting to the one you want when you're connected in SQL Management Studio and an older or incorrect version when run under the context of the reporting services report. If not, I would trace the individual SQL statements as already mentioned and look for the statement(s) with highest reads to tune.|||

Thanks for the questions.

Yes, I copied the SQL from profiler then ran that in SQL MS.

These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.

|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||

WITH RECOMPILE

Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITH RECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.

|||Thanks for getting back with the solution buckenn. This was really helpful.|||

This is FANTASTIC!!

I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.

data reader ?

is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)?

---------------

ex:

create sp1

as

select * from tbl1

select * from tbl2

---------------

how can i use a data reader to read the items from tbl2?

If you have SqlServer 2005 (inc Express), you can use MARS -MultipleActiveResultSets. It's only available with SqlClient, so won't work with Oracle, or MySQL etc.|||

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

|||

Use a DataSet in this instance to retrieve your results. Then, you can retrieve the items out of the two tables that will be returned.

|||

It looks like you can support mulitple resultsets with a DataReader as well although I've always used a DataSet in this instance. See the following article.

http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/#S2

|||

TATWORTH:

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

As I understand it, that's only the case when using the SqlDataSource control, although I can't remember whether it can only hold the first resultset, or the last....

sql

Data not displayed in Preview Tab, but is displayed in Data Tab

This has happened on more than one occasion. I have defined a stored
procedure type datasource with 12 parameters. When I execute the SP in the
Data tab, the resulting rows are correctly displayed. When I use the same
parameters when previewing the report, no data is displayed. I have found
that empty string parameters need to be <blank> on the data tab, but need to
be '' on the preview tab, so I know about this little gotcha.
Has anyone else had this problem and found a solution. I have check the rdl
file to make sure that the datasource and fields are correct. I have a table
and a pie chart on the report and both are empty when I preview it.
===================== Thanks!
Keith
p.s. Get off the couch and exercise!I have answered my own question.
This might come in handy for a few people. If one of your parameters is a
string and the SP allows an empty string, then make sure to check the
Parameter definition box "Allow Blank Value". otherwise if you try to send
it a blank or '' (empty string), then SQL profiler shows the parameter as
N'''. This solved my problem and I am now getting data.
=====================Thanks!
Keith
p.s. Get off the couch and exercise!
"Keith" wrote:
> This has happened on more than one occasion. I have defined a stored
> procedure type datasource with 12 parameters. When I execute the SP in the
> Data tab, the resulting rows are correctly displayed. When I use the same
> parameters when previewing the report, no data is displayed. I have found
> that empty string parameters need to be <blank> on the data tab, but need to
> be '' on the preview tab, so I know about this little gotcha.
> Has anyone else had this problem and found a solution. I have check the rdl
> file to make sure that the datasource and fields are correct. I have a table
> and a pie chart on the report and both are empty when I preview it.
>
> =====================> Thanks!
> Keith
> p.s. Get off the couch and exercise!

Data Manipulation

HI everyone,

I have a column STATUS of type bit(1,0) , i would like to manipulate it(without editing the database) in the Store Procedure in such a way that

if
STATUS =1 // return result STATUS= 'Active '
Else if
STATUS = 0 // return result STATUS = 'Inactive'
End if

My Stored Procedure Is As Follow

SELECT
STATUS + '--' + NAME AS STATUSNAME
FROM M_USER
END

Desired Result When Populated Into a Listbox Should Be As Follow

Active--Tester
Inactive--Tester1

Thanks In Advance for the Help Provided!

SELECT
 CASE When Status = 1 Then 'Active'
 ELSE 'Inactive'
 END As STATUSNAME
FROM M_USER

:)

JB|||Thanks JBelthoff for the reply,

I follow ur example but its still not working , the code are as follow,

IF (@.MASTER = 'NRICSEARCH')
BEGIN
SELECT
CASE WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS ACTIVE,
USER_ID,
NRIC + '--' + NAME+ '(' + CONVERT(VARCHAR, ACTIVE) + ')' AS NRICNAME
FROM M_USER
ORDER BY NRIC

--
Column ACTIVE is of type bit.
NRICNAME is used in the listbox as datatextfield

TheCurrent Output from the current code in the listbox is as follow,

123--Ben(0)
124--Andy(1)

TheDesired Output in the listbox is as follow,

123--Ben(ACTIVE)
124--Andy(INACTIVE)

Apparantly, The CASE is not working.|||To get a 1 column result set in your desired output.......

SELECT CAST(USER_ID As Varchar) + '--' + NAME + '(' +
CASE
WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END
+ ')' As NRICNAME
FROM M_USER

:-D

Enjoy!

JB|||Hi JB,

Its working now, thanks a lot.

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to use.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
Paul G
Software engineer.
Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.
|||ok, thanks for the information.
"Tibor Karaszi" wrote:

> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
>
>

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to us
e.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
Paul G
Software engineer.Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to
use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.|||ok, thanks for the information.
"Tibor Karaszi" wrote:

> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
>
>

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to use.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
--
Paul G
Software engineer.Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.|||ok, thanks for the information.
"Tibor Karaszi" wrote:
> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> > Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> > following field insertion into a table, just wondering what statements to use.
> > table name = tabletest
> > *****************************
> > * primary key * varchar * integer *
> > *****************************
> > * 1 * test * 34 *
> > *****************************
> > * 2 * test2 * 21 *
> > *****************************
> > The input to the procedure is a 2, and I want the procedure to replace
> > the 21 with another integer (say 5) while leaving the rest of the row
> > unchanged, still contains string test2 and primary key 2.
> > Thanks.
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>