SSRS subscription with cursor

Question

In my (ssrs) data-driven subscription I try to use cursor. The result that I need: number of letters should be equal to the lines the cursor returns. Example:

SET ANSI_WARNINGS ON 
DECLARE cur CURSOR FOR
SELECT * FROM #ForResult

OPEN cur
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM cur INTO @ID, @CommittedBy, @FlightNumber, @Dep, @DateBeg, @Carrier, @Arr
        IF @@FETCH_STATUS <> 0 BREAK

        SELECT CONVERT(VARCHAR(10), @DateBeg, 104)      AS [DateBeg]
             , CONVERT(VARCHAR(10), @DateBeg, 104)      AS [DateEnd]
             , @Dep                                     AS [AP]
             , @FlightNumber                            AS [FlightNumber]
             , 'email@y.ru' AS [Recip]
             , 'Отчет ' 
             + @FlightNumber
             + ' от ' + CONVERT(VARCHAR(10), @DateBeg)
             + ' а/к ' + @Carrier
             + ' ' + @Dep + ' - ' + @Arr 
             + ' // ' + @CommittedBy                    AS [Subject]        
    END
CLOSE cur
DEALLOCATE cur
SET ANSI_WARNINGS OFF

SET ANSI_WARNINGS solved problem Cannot read the next data row for the dataset

But subscription return only first cursor-result. How can I return all rows in different letters?

score 1 · Answer 1 · answered Oct 16 '19 at 11:01

1

Is this the only code you have, because in SSRS, the code works very different than TSQL. It should be case sensitive, parameters which are string may work differently. Make sure you have it in a SP with all ANSI settings properly defined and run them again

answered Oct 16 '19 at 11:01

SivaAI

52
2

1

SET ANSI_WARNINGS ON - solved the problem, but I get only one letter (first) – Сая Соколова Oct 16 '19 at 11:25

score 1 · Answer 2 · answered Oct 16 '19 at 15:12

Rather than selecting data in your cursor, INSERT it into a table variable, which will then accumulate the results

Then SELECT everything from the table variable once the cursor has finished inserting.

At the moment it will be returning the results of the first SELECT to SSRS and then essentially abandoning the procedure.

Alternatively, could you not just perform that SELECT directly from #ForResult? I.e. Something like:

SELECT
    CONVERT(VARCHAR(10), DateBeg, 104)      AS [DateBeg]
    , CONVERT(VARCHAR(10), DateBeg, 104)    AS [DateEnd]
    , Dep                                   AS [AP]
    , FlightNumber                          AS [FlightNumber]
    , 'emaily.ru' AS [Recip]
    , 'Отчет ' 
    + FlightNumber
    + ' от ' + CONVERT(VARCHAR(10), DateBeg)
    + ' а/к ' + Carrier
    + ' ' + Dep + ' - ' + Arr 
    + ' // ' + CommittedBy                  AS [Subject]
FROM #ForResult

SSRS subscription with cursor

2 Answers2