1

I have a database that is used to track container contents as they are shipped in and out of a facility. I am attempting to create a report off of the table containing all of the cylinders. At present, the table only contains 60,000 rows. The report should pull all of the data and then group by:

RIS, Account, Yard, Row, Position

The details for each group will contain:

Cylinder, Gross Weight, Net Weight, Tare Weight, Unit of Measure, Material Type

There are three parameters on the report: RIS, Material Type and Unit of Measure. The first two parameters can contain multiple values while the Unit of Measure can only be one value at a time. The query for this report is:

SELECT * FROM InventoryData WHERE RIS IN (@RIS) ADN [Material Type] IN (@DMT) AND [Unit of Measure]=@UM

When I preview the report and select pounds for unit of measure (which should pull about 80% of the rows, I get the following error:

An error occurred during local report processing. The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.

After researching, some sites suggested that parameter sniffing may be the issue so I created a stored procedure that takes table value parameters and changed the query using the process at http://gruffcode.com/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services/. This setup still gives the same error. Can anyone shed some light on this error or what to do about it?

The code that assigns the variables is as follows:

Public const strSingleQuote = "'"
Public Shared Function BuildListInserts(ByVal variableName As String, ByVal paramValues As Object()) As String
  Dim insertStatements As New System.Text.StringBuilder()
  For Each paramValue As Object In paramValues
    paramValue = Trim(paramValue)
    InsertStatements.AppendLine(String.Format("INSERT INTO {0} VALUES ({1}{2}{1})",
    variableName, strSingleQuote, paramValue))
  Next
  Return insertStatements.ToString()
End Function

The return value of the function above is assigned to the parameters @risInserts and @dmtInserts within the query. The modified query looks like this:

exec('declare @risList stringlist ' + @risInserts + 'declare @dmtList stringlist ' + @dmtInserts + ' EXEC InventoryData @RIS=@risLIst, @DMT=@dmtList, @UnitOfMeasure=''' + @um + '''')

When the query is run while doing an SQL trace, the result on the SQL Server is:

declare @risList stringlist INSERT INTO @risList  VALUES ('GBD')
INSERT INTO @risList VALUES('GBD')
declare @dmtList stringlist  INSERT INTO @dmtList  VALUES ('D')
INSERT INTO @dmtList  VALUES ('E')
INSERT INTO @dmtList  VALUES ('Pu')
INSERT INTO @dmtList  VALUES ('U')
INSERT INTO @dmtList  VALUES ('N')
INSERT INTO @dmtList  VALUES ('Np')
INSERT INTO @dmtList  VALUES ('Th')
INSERT INTO @dmtList  VALUES ('C')
EXEC InventoryData @RIS=@risList, @DMT=@dmtList, @UnitOfMeasure='lb'

Which, when run in SSMS, produces the desired result set in about .75 seconds.

Yes, RIS and DMT can both be multiple values which is why I use "IN" instead of "="

2
  • Hi, are there any error messages in the sql server error log? Sep 26 '19 at 17:47
  • Nope, and if I run the identical queries directly on the server they work perfectly.
    – DrewB
    Sep 26 '19 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.