Табличные параметры для SSRS 2008

У нас есть требование создания отчетов SSRS, из которых нам нужно преобразовать многозначные строковые и целочисленные параметры в данные и передать их в хранимую процедуру. Хранимая процедура содержит несколько параметров табличного типа. Раньше мы использовали varchar(8000), но он также выходил за пределы ограничения по типу данных. Затем мы подумали о том, чтобы ввести концепцию datatable. Но мы не знали, как передавать значения из SSRS.

Мы нашли решение от GruffCode на Использование табличных параметров со службами отчетов SQL Server.

Решение решило мою проблему, и мы можем создавать отчеты. Однако иногда SSRS возвращает две следующие ошибки:

При обработке отчета произошла ошибка.
Не удалось выполнить запрос для набора данных "DSOutput".
Строковые или двоичные данные будут усечены. Заявление было прекращено.

И

При обработке отчета произошла непредвиденная ошибка.
Возникло исключение типа «System.OutOfMemoryException».

Я не уверен, когда и где это вызывает проблему.


table-valued-parameters reporting-services ssrs-2008
person Aditya    schedule 11.03.2013    source источник
comment
Первая ошибка часто является результатом несоответствия типов/длин. Сравните тип/длину данных вашей базы данных с тем, что вы ожидаете.   -  person Anthony Queen    schedule 12.03.2013

Ответы (2)


arrow_upward
2
arrow_downward

Подход, описанный в этом сообщении в блоге, основан на создании огромной строки в памяти, чтобы загрузить все выбранные значения параметра в экземпляр параметра с табличным значением. Если вы выбираете очень большое количество значений для передачи в запрос, я мог видеть, что это потенциально может вызвать «System.OutOfMemoryException» при попытке построить строку, содержащую операторы вставки, которые будут загружать параметр.

Что касается ошибки «строка или двоичные данные будут усечены», похоже, что она возникла в запросе или хранимой процедуре, которую отчет использует для сбора своих данных. Не видя, как выглядит этот t-sql, я не мог сказать, почему это происходит, но я предполагаю, что это также как-то связано с выбором очень большого количества значений параметров.

К сожалению, я не уверен, что для этого есть обходной путь, кроме попытки выяснить, можно ли найти способ выбрать меньше значений параметров. Вот пара грубых идей:

  1. Если у вас есть ситуация, когда пользователи могут выбрать несколько значений параметров или все значения параметров, вы можете сделать так, чтобы запрос просто принимал очень простое логическое значение, указывающее, что все значения были выбраны, а не отправку отчета. все значения через параметр.
  2. Вы также можете немного «уменьшить масштаб» значений ваших параметров и как-то сгруппировать их вместе, если они подходят для этого. Таким образом, пользователи будут выбирать из меньшего числа значений параметров, которые представляют группу отдельных значений, объединенных в один.
person Jesse Taber    schedule 12.03.2013

arrow_upward
0
arrow_downward

Я не сторонник использования параметра Text и EXEC в операторе SQL, как описано в статье, на которую вы ссылаетесь, поскольку это связано с внедрением SQL. Поведение SSRS по умолчанию с параметром с несколькими значениями заменяет список значений, разделенных запятыми, непосредственно вместо параметра при отправке запроса на сервер SQL. Это отлично работает для простых запросов IN, но может быть нежелательным в других местах. Это поведение можно обойти, установив значение параметра в DataSet на выражение =Join(Parameters!CustomerIDs.Value, ", "). После того, как вы это сделаете, вы можете получить табличную переменную, загруженную с помощью следующего SQL:

DECLARE @CustomerIDsTable TABLE (CustomerID int NOT NULL PRIMARY KEY)

INSERT INTO @CustomerIDsTable (CustomerID)
SELECT DISTINCT TextNodes.Node.value(N'.', N'int') AS CustomerID
FROM (
    SELECT CONVERT(XML, N'<A>' + COALESCE(N'<e>' + REPLACE(@CustomerIDs, N',', N'</e><e>') + N'</e>', '') + N'</A>') AS pNode
    ) AS xmlDocs
CROSS APPLY pNode.nodes(N'/A/e') AS TextNodes(Node)

-- Do whatever with the resulting table variable, i.e.,
EXEC rpt_CustomerTransactionSummary @StartDate, @EndDate, @CustomerIDsTable

Если вместо целых чисел используется текст, пара строк меняется следующим образом:

DECLARE @CustomerIDsTable TABLE (CustomerID nvarchar(MAX) NOT NULL PRIMARY KEY)

INSERT INTO @CustomerIDsTable (CustomerID)
SELECT DISTINCT TextNodes.Node.value(N'.', N'nvarchar(MAX)') AS CustomerID
FROM (
    SELECT CONVERT(XML, N'<A>' + COALESCE(N'<e>' + REPLACE(@CustomerIDs, N',', N'</e><e>') + N'</e>', '') + N'</A>') AS pNode
    ) AS xmlDocs
CROSS APPLY pNode.nodes(N'/A/e') AS TextNodes(Node)

-- Do whatever with the resulting table variable, i.e.,
EXEC rpt_CustomerTransactionSummary @StartDate, @EndDate, @CustomerIDsTable

This approach also works well for handling user-entered strings of comma-separated items.

person JamieSee    schedule 26.06.2015