< Blog

Business Intelligence Development Studio (BIDS) Error – Passing Variables to Stored Procedures, Part I

By Garth Jones

Every so often I run across errors involving passing variables to stored procedures when I’m using Business Intelligence Development Studio (BIDS). We use stored procedures for all of our SQL Server Reporting Services (SSRS) reports, so I’m more likely than most to see these errors.

I’ve documented in this blog post a couple of these error messages with quick fixes, and tomorrow I’ll tell you about some more error messages I’ve encountered.

Error Message #1

The following error message is telling me that I only passed one variable to my stored procedure when two or more variables are expected. To fix this problem simply adjust your query to pass all the variables and then refresh the field list.

Could not generate a list of fields for the query.

Check the query syntax, or click Refresh Fields on the query toolbar.

Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. (Microsoft SQL Server, Error: 257)

BIDS Error 1 – Passing Variables to Stored Procedures

Error Message #2

The following error message pertains to not passing any variables to your stored procedure. To fix this problem, adjust your query to pass all the variables and then refresh the field list.

Could not generate a list of fields for the query.

Check the query syntax, or click Refresh Fields on the query toolbar.

Procedure or function ‘WIR3_RBA_WarRangeFilterName’ expects parameter ‘@Coll’, which was not supplied. (Microsoft SQL Server, Error: 201)

BIDS Error 2 – Passing Variables to Stored Procedures

Now, compare this to the first error message where only one of two (or more) variables were passed. This error message makes it clear that no variables were passed to your stored procedure.