Keine Felder für dynamische SQL-gespeicherte Prozeduren in SSRS mit SET FMTONLY

Question

Ich habe das folgende SP, das korrekt funktioniert, wenn es alleine ausgeführt wird:

USE [Orders]
GO
SET FMTONLY OFF; 

CREATE PROCEDURE [dbo].[Get_Details_by_Type]

@isArchived varchar(10),
@Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON hinzugefügt, um zusätzliche Ergebnismengen bei
    -- SELECT-Anweisungen nicht zu beeinträchtigen.
    SET NOCOUNT ON;
    declare @sqlQuery nvarchar(max)
          IF(@isArchived = 'ALL')
            BEGIN
                set @sqlQuery  = 'SELECT *  FROM [dbo].[Orders] 
              WHERE ' + @Type + ' != € 
                ORDER BY [IDNumber]'
                exec sp_executesql @sqlQuery
            END
        ELSE
            BEGIN
            set @sqlQuery  = 'SELECT * FROM [dbo].[Orders] 
          WHERE ' + @Type + ' != € AND [isArchived] = ' + @isArchived + ' ORDER BY [IDNumber]'
            exec sp_executesql @sqlQuery
        END
END
SET FMTONLY ON; 

Das Problem, das ich habe, ist, dass wenn ich ein DataSet für einen SSRS-Bericht hinzufüge, keine Felder/Spalten im Abschnitt "Felder" angezeigt werden. Ich vermute, dass dies auf den dynamischen SQL zurückzuführen ist?

Wie kann ich das lösen?

Answer 1

Das Problem
Gespeicherte Prozeduren, die Dynamisches SQL und Temp-Tabellen enthalten, sind der Fluch von Zauberern wie SSRS und ORM-Generatoren wie Linq2SQL und EF Reverse Engineering-Tools.

Dies liegt daran, dass die Tools SET FMTONLY ON; (oder neuerdings sp_describe_first_result_set) vor dem Ausführen der PROC ausführen, um das von der PROC erzeugte Ergebnisset-Schema abzuleiten, damit Zuordnungen für die ReportViewer-Benutzeroberfläche generiert werden können. Weder FMTONLY ON noch sp_describe_first_result führen die PROC tatsächlich aus.

Zum Beispiel macht das Tool etwas wie:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

Einige Workarounds:

• Manuelles Bearbeiten der RDL / RDLC-Datei, um die tatsächlichen Spaltennamen und -typen des Ergebnissets einzufügen.
• Vorübergehend die echte Prozedur fallen lassen und sie durch eine ersetzen, die einen Datensatz von null oder mehr Zeilen mit den tatsächlichen Datentypen und Spaltennamen zurückgibt, die von der echten Prozedur zurückgegeben wurden, den Assistenten ausführen und dann die echte Prozedur zurücksetzen.
• Vorübergehend SET FMTONLY OFF; als erste Zeile in der PROC hinzufügen - das wird die Ausführung der PROC erzwingen. Setzen Sie die ursprüngliche PROC wieder zurück, wenn Sie fertig sind (wobei Ihre PROC möglicherweise wegen vom Tool übergebenen Null- oder Platzhalterparametern fehlschlagen kann). Außerdem wird FMTONLY abgeschafft
• Am Anfang der PROC ein Dummy-Statement hinzufügen, das das tatsächliche Schema des/die zurückgegebenen Ergebnisset/s zurückgibt, eingebettet in einen bedingten Zweig, der niemals ausgeführt wird.

Hier ist ein Beispiel für den letzten Hack:

CREATE PROCEDURE [dbo].[Get_Details_by_Type]
  @isArchived varchar(10),
  @Type varchar(50)
AS
BEGIN
   -- Nur für Tools mit FMTONLY ON
   IF 1 = 2
     BEGIN
       -- Dies sind die tatsächlichen Spaltennamen und -typen, die von der echten Prozedur zurückgegeben werden
       SELECT CAST('' AS NVARCHAR(20)) AS Col1, 
              CAST(0 AS DECIMAL(5,3)) AS Col2, ...
     END;
-- Der Rest der eigentlichen PROC kommt hier

FMTONLY ON / sp_describe_first_result_set werden vom Dummy-Bedingten getäuscht und gehen vom nie ausgeführten Zweig aus.

Außerdem würde ich empfehlen, aus Gründen Ihrer eigenen geistigen Gesundheit, dass Sie nicht SELECT * in Ihrer PROC verwenden - listen Sie stattdessen explizit alle tatsächlichen Spaltennamen auf, die von Orders zurückgegeben werden

Zum Schluss stellen Sie einfach sicher, dass Sie nicht die Anweisung SET FMTONLY ON; in Ihrer PROC (aus Ihrem obigen Code!) einschließen

END - Proc
GO **
SET FMTONLY ON; ** Dies gehört nicht zur Proc!

Answer 2

Wenn jemand immer noch auf dieses Problem stößt, habe ich ein ähnliches Problem mit ssrs und dynamischem SQL gelöst.

1. Für ssrs, um die Felder von der SP richtig zuzuordnen,
2. wählen Sie die Option "Text" unter Abfragetyp aus,
3. geben Sie den SP-Namen und Parameter ein, als ob Sie ihn aus einem SSMS-Fenster aufrufen würden. sp_YourStoredProc @Parameter1....@ParameterN
4. Klicken Sie auf die Schaltfläche Felder aktualisieren.
5. Wenn die Anweisung ausgeführt wird, werden die Felder aktualisiert und Ihre Matrix wird gefüllt.

Übrigens, ich verwende SQL 2012

Hoffe das hilft.

Answer 3

Nachfolgend finden Sie, was ich getan habe, um das Problem zu überwinden - Felder werden in SSRS nicht aufgelistet

1. Ursprünglich hatte ich eine gespeicherte Prozedur; und es gibt Daten zurück, wenn ich das Dataset ausführe. Aber die Felder werden in SSRS nicht aufgelistet

2. Ich habe den Text der gespeicherten Prozedur kopiert und das Dataset als Text anstelle von gespeicherter Prozedur gemacht. Siehe So aktualisieren Sie Felder für ein Dataset

3. Es traten Fehler auf und ich habe diese Fehler "ignoriert". Siehe Der Declare cursor-SQL-Konstrukt oder die Anweisung wird nicht unterstützt.

4. Jetzt habe ich überprüft, dass die Felder für SSRS befüllt sind

5. Jetzt habe ich das Dataset aktualisiert, um meine gespeicherte Prozedur zu verwenden

Nach den oben genannten Schritten führen Sie eine Aktualisierung des Datasets wie unten gezeigt durch:

Answer 4

Ich möchte keinen alten Thread wieder aufleben lassen, aber es hat mich verrückt gemacht, als wir unsere Berichte von SSRS 2005 auf SSRS 2016 aktualisierten, wo Stored Procedures openquery verwendeten.

Wir haben es auf Berichte eingegrenzt, die Felder mit leeren Werten hatten. Also haben wir dies am Anfang der Stored Procedure hinzugefügt:

SET CONCAT_NULL_YIELDS_NULL OFF;

wodurch wir nicht jedes Feld CASTen mussten.

Answer 5

Folgen Sie den unten angegebenen Schritten

•   Löschen Sie die Datenquelle. Erstellen Sie eine neue Datenquelle.
•   Löschen Sie den Datensatz. Erstellen Sie einen neuen Datensatz.
•   Verwenden Sie den Abfrage-Designer.
•   Fügen Sie den gültigen Parameter hinzu, wenn danach gefragt wird.
•   Es sollte ein Ergebnis für die bereitgestellten Parameter erhalten werden.
    Klicken Sie nicht auf "Feld aktualisieren".
•   Dann werden Berichtsparameter und Berichtsfeld angezeigt.
•   Jetzt sollte das Filterkriterium funktionieren.

ODER

**Fügen Sie das Berichtsfeld manuell hinzu. Es funktioniert.**

Wenn die gespeicherte Prozedur kein Schema-Daten oder Metadaten abrufen kann, sollten wir die Berichtsfelder manuell angeben.