asked on
Error when running Report with a second dataset calling a stored procedure to pass user details
Hi I receive an error:
System.Data.SqlClient.SqlE
It occurs when running a report which users a second dataset. The dataset users the same trigger as the main dataset which works fine. I am using the second dataset to populate the Filter parameters.
The report actually runs ok if I run report in report builder. However if I upload to Report Manager and run it I get the error above.
Any ideas on why this is occurring?
It appears the Paramater @UserId might be null which is why I get the error. Is there something that stops the report from running stored procedures in the Parameters? Or an execution order which I need to change somewhere?
FYI the Parameters populate ok if I don't use the Stored Procedure. E.g. The list is populated with 100 items. But if I use the stored procedure the list is reduced to 9. However if the Stored Procedure does not run then 0 will be returned. Hence why I think the error is that null is being returned.
But I need to stored procedure to run so I can populate the filter with appropriate user details rather than the full list.
I am hoping this is just something that I am doing wrong. E.g. Do I need to add an execution order to make sure the stored procedure executed before the parameters populate in the report?
Many thanks
System.Data.SqlClient.SqlE
It occurs when running a report which users a second dataset. The dataset users the same trigger as the main dataset which works fine. I am using the second dataset to populate the Filter parameters.
The report actually runs ok if I run report in report builder. However if I upload to Report Manager and run it I get the error above.
Any ideas on why this is occurring?
It appears the Paramater @UserId might be null which is why I get the error. Is there something that stops the report from running stored procedures in the Parameters? Or an execution order which I need to change somewhere?
FYI the Parameters populate ok if I don't use the Stored Procedure. E.g. The list is populated with 100 items. But if I use the stored procedure the list is reduced to 9. However if the Stored Procedure does not run then 0 will be returned. Hence why I think the error is that null is being returned.
But I need to stored procedure to run so I can populate the filter with appropriate user details rather than the full list.
I am hoping this is just something that I am doing wrong. E.g. Do I need to add an execution order to make sure the stored procedure executed before the parameters populate in the report?
Many thanks
SSRS
Last Comment
Papa1NZ