Report Parameter Issue - Error 42000

[Marcab]

I'm  having an issue with the parameters for a report I'm working on.

The query which is the data source for the report is a fairly complex one:

 

SELECT sub.AssignedCity, SUM(sub.TotalIndividuals) AS TotalIndividuals, SUM(sub.SumOfBoxes) As TotalBoxes, SUM(sub.SumOfMealsServed) As     TotalMealsServed, SUM(sub.SumOf60AndOverServed) AS TotalOver60Served, SUM(sub.SumOf18AndUnder) AS Total18AndUnderServed
FROM(

SELECT EntryMonth, EntryYear, AgencyID, AssignedCity, SUM(IndividualsServed) AS TotalIndividuals, SUM(Boxes) AS SumOfBoxes, SUM(MealsServed) AS SumOfMealsServed,,SUM(Over59Served) AS SumOf60AndOverServed, SUM(Over59Served) AS SumOf18AndUnder

FROM AgencyBoxes

GROUP BY EntryMonth, EntryYear, AgencyID, AssignedCity
UNION
SELECT EntryMonth, EntryYear, HouseholdID, AssignedCity, SUM(IndividualsServed) AS TotalIndividuals, SUM(Boxes) AS SumOfBoxes, SUM(MealsServed) AS SumOfMealsServed,
SUM(Over59Served) AS SumOf60AndOverServed, SUM(Over59Served) AS SumOf18AndUnder
FROM Boxes
GROUP BY EntryMonth, EntryYear, HouseholdID, AssignedCity
) sub
WHERE EntryMonth = @prmEntryMonth AND EntryYear = @prmEntryYear
GROUP BY AssignedCity

I'm getting an error saying that I must declare a scalar variable for the two parameters. I've never seen this error before, and I have no idea what the issue is.

I'm attaching all relevant files detailing report parameters, data source parameters, etc. Any advice would be very much appreciated.

[Mads]

Hello, Marcab

Thank you for the provided the screenshots.

The error comes from the SQL Server, but looking through the screenshots, the Report Parameter and SQL DataSource Parameter look correctly set up. 

From the pasted SQL query, there seems to be an extra comma at the inner SELECT statement, in the first part of the UNION.

To further troubleshoot the issue, maybe you could try directly writing integer values in the SQL statement to see if this returns data(like in the example below). You can also try to execute the query in different software like LINQpad to make sure the syntax is correct before trying it again in the report.

WHERE EntryMonth = 10 AND EntryYear = 2020

I hope this information is useful. Let me know if you solve it, and if not, let me know how the tests I suggested above worked out. Hopefully, we can solve this issue.

Regards, Mads Progress Telerik

[Mads]

Hello, Marcab

After some more research, we found this issue might be related to the ado.net provider used. When using ODBC there is an issue using named variables, which is pointed out in this StackOverflow post "SSRS error must declare scalar variable when using ODBC connection to SQL SERVER". Which provider is used in your project?

One solution if this indeed is the issue might be to store the queries as procedures, which support multiple named variables, as mentioned in this documentation from Microsoft, Binding Parameters by Name (Named Parameters). If using the SQL query without named variables, like I suggested trying in the previous post, work, this could probably be a solution to the issue.

Let me know how it works out. I look forward to hearing from you.

Regards, Mads Progress Telerik

[Marcab]

Thanks Mads!

I didn't notice the extra comma.

I originally tested the query with hard-coded values, and it returned the expected data. I didn't start experiencing any issues until I tried to parameterize the query.

[Marcab]

I'm also going to test the query in SSMS. If it's a SQL issue, it should show up there as well.  

[Marcab]

I did as you said, and removed the extra comma. I then substituted hard-coded values for the parameters.

The query worked as expected.

When I substituted the hard-coded values for parameters, I was back to the same issue.

I noticed something strange.

When I checked the properties window for the data source, it listed 'none' for parameters, despite the fact that I'd added parameters for the data source, and set those parameters to the report parameters.

I tried deleting the original data source, and adding a different one, only to get the same result. I don't understand why the data source parameters aren't being saved. I can't recall this ever happening before.

The query also works as expected in SSMS.

[Marcab]

One more thing...

The data source parameters 'disappear' when I click to preview the query as part of the configuration process for the data source.

[Mads]

Hi, Marcab

I have spent some more time looking into this, but I am not able to reproduce the issue where the data-source parameters disappear or getting the error mentioned in the first post.

Since I do not have your database available I am not able to run your query. But I tried creating a report with your query just to see if the parameters disappear when I execute the query in the SQL Wizard, or when I try to preview the report. In both cases, the SQL server returns errors of not finding the objects, which is expected and means I am not able to reproduce the error until the point the SQL server returns any response. 

 I tried creating a similar query to yours by using the AdventureWorks sample database, and it works as expected. Maybe you could try creating a report with this too and see if the parameters disappear, or if you run into errors. Whether you have the actual AdventureWorks database is not too important, as I am curious if the same behavior will happen as with your original query.

SELECT
  sub.ProductID,
  SUM(sub.OrderQty) AS OrderQty,
  SUM(sub.RowAmount) AS RowAmount
FROM (
    SELECT TOP 20
	  [Purchasing].[PurchaseOrderDetail].[ProductID],
	  SUM([Purchasing].[PurchaseOrderDetail].[OrderQty]) AS OrderQty,
	  COUNT([Purchasing].[PurchaseOrderDetail].[ProductId]) AS RowAmount
	FROM
	  [Purchasing].[PurchaseOrderDetail]
    GROUP BY
	  [Purchasing].[PurchaseOrderDetail].[ProductID]
	UNION
	SELECT TOP 20
	  [Sales].[SalesOrderDetail].[ProductID],
	  SUM([Sales].[SalesOrderDetail].[OrderQty]) AS OrderQty,
	  COUNT([Sales].[SalesOrderDetail].[ProductID]) AS RowAmount
	FROM
	  [Sales].[SalesOrderDetail]
	GROUP BY
	  [Sales].[SalesOrderDetail].[ProductID]
) sub
WHERE
  ProductID > @parameter1
  AND OrderQty > @parameter2
GROUP BY
  ProductID

When running this query through the Report Designer, I can monitor the activity of the SQL server through SSMS, and watch how the query looks from the point of view of the SQL server. In the image below you see how you can find this, in case you are unfamiliar with it.  Maybe you could try the same with your query and with my query above to see how it looks. Here is how I did this

I am sorry am not able to help you any further, but this is challenging when I am not able to reproduce the issue. If the troubleshooting above does not give any results, maybe you could try recording a video displaying the issue? And providing us with a sample of the database would be to great help, so we could try running your query as well.

Just to confirm, the product version in the ticket details is 14.2.20.1118. Is this the version you are using?

Regards, Mads Progress Telerik

[Marcab]

Thank you so much Mads for spending all of the time  you've put in helping me to resolve this issue. I really appreciate it.

I'll do as you suggested and let you know how things go.

[Marcab]

Hi Mads,

I solved my issue!

I realized, that you and I were testing under different conditions: you were testing (I presume) using a local instance of SQL Server instance and I've been testing using an Azure database. (This is a significant difference).

When I performed your test as detailed in your response, against my local copy of the AdventureWorks database, I got a successful result. (So I know my query and parameter set up wasn't the issue.)

This led me to start looking at my report's data source configuration.

I set up a brand new data source and set everything up accordingly (including advanced settings for login). 

When I ran my report afterwards, everything worked as expected.

The original data source had worked in the past, so I'm not exactly sure what changed.

Let me know if you'd like more detail regarding my solution.

Thanks again for all of your assistance with this. It's very much appreciated!

[Marcab]

Hi Mads,

I solved my issue!

I realized, that you and I were testing under different conditions: you were testing (I presume) using a local instance of SQL Server instance and I've been testing using an Azure database. (This is a significant difference).

When I performed your test as detailed in your response, against my local copy of the AdventureWorks database, I got a successful result. (So I know my query and parameter set up wasn't the issue.)

This led me to start looking at my report's data source configuration.

I set up a brand new data source and set everything up accordingly (including advanced settings for login). 

When I ran my report afterwards, everything worked as expected.

The original data source had worked in the past, so I'm not exactly sure what changed.

Let me know if you'd like more detail regarding my solution.

Thanks again for all of your assistance with this. It's very much appreciated!