Cursor causes SSMS to crash

I have a cursor that generates one record of JSON text from a group of tables. The cursor has been making SSMS crash. The script runs for a time then SSMS fails. Below is the code that I have written that is causing the crash.

```
DECLARE @ROW_ID int -- Here we create a variable that will contain the ID of each row.

DECLARE JSON_CURSOR CURSOR -- Here we prepare the cursor and give the select statement to iterate through
FOR

SELECT -- Our select statement (here you can do whatever work you wish)
ROW_NUMBER() OVER (ORDER BY NAME_2-1,NAME_2-2,FIELD_1-1,FIELD_1-2) AS ROWID
FROM
(
SELECT
FIELD_1-1
,FIELD_1-2
,NAME_1-1
,NAME_1-2
FROM
(
SELECT FIELD_1-1,FIELD_1-2,NAME,VALUE
FROM TABLE_1
WHERE NAME IN ('NAME_1-1','NAME_1-2')
) AS SRC
PIVOT
(
MAX(VALUE_1) FOR NAME IN ([FIELD_1-1],[FIELD_1-2])
) AS PVT
) AS T0
LEFT JOIN TABLE_2 AS [P] ON T0.NAME_1-2=P.NAME_2-2;

OPEN JSON_CURSOR -- This charges the results to memory

FETCH NEXT FROM JSON_CURSOR INTO @ROW_ID -- We fetch the first result

WHILE @@FETCH_STATUS = 0 --If the fetch went well then we go for it
BEGIN

SELECT * FROM
(
SELECT -- Our select statement (here you can do whatever work you wish)
FIELD_2-1
,FIELD_2-2
,FIELD_1-1
,FIELD_1-2
,T0.NAME_1-1
,ROW_NUMBER() OVER (ORDER BY FIELD_2-1,FIELD_2-2,FIELD_1-1,FIELD_1-2) AS ROWID
FROM
(
SELECT
FIELD_1-1
,FIELD_1-2
,NAME_1-1
,NAME_1-2
FROM
(
SELECT FIELD_1-1,FIELD_1-2,NAME,VALUE
FROM TABLE_1
WHERE NAME IN ('NAME_1-1','NAME

-
First, it looks like you are doing a ton of extra work here. The query in the cursor is a subset of the query in the WHILE loop, and the data does not change at any point in time. So it is just executing that same query over and over again, just for a different row. It should be far more efficient to store the results of the initial query into a local temp table and then just use that for both the CURSOR and WHILE loop queries:

CREATE TABLE #Data
(
  [RowID] INT NOT NULL IDENTITY(1, 1),
  [FIELD_2-1] {data_type},
  [FIELD_2-2] {data_type},
  [FIELD_1-1] {data_type},
  [FIELD_1-2] {data_type},
  [T0.NAME_1-1] {data_type}
);

DECLARE @TotalRows INT;

INSERT INTO #Data ([FIELD_2-1], [FIELD_2-2], [FIELD_1-1], [FIELD_1-2],
                   [T0.NAME_1-1])
  SELECT [FIELD_2-1], [FIELD_2-2], [FIELD_1-1], [FIELD_1-2], [T0.NAME_1-1]
  FROM (
        SELECT  -- Our select statement (here you can do whatever work you wish)
            FIELD_2-1
            ,FIELD_2-2
            ,FIELD_1-1
            ,FIELD_1-2
            ,T0.NAME_1-1
        FROM
            (
            SELECT 
                FIELD_1-1
                ,FIELD_1-2
                ,NAME_1-1
                ,NAME_1-2
            FROM 
                (
                SELECT FIELD_1-1,FIELD_1-2,NAME,VALUE
                FROM TABLE_1
                WHERE NAME IN ('NAME_1-1','NAME_1-2')
                ) AS SRC
            PIVOT
                (
                MAX(VALUE_1) FOR NAME IN ([FIELD_1-1],[FIELD_1-2])
                ) AS PVT
            ) AS T0
        LEFT JOIN TABLE_2 AS [P] ON T0.NAME_1-2=P.NAME_2-2
        ) AS T1
    ORDER BY [FIELD_2-1], [FIELD_2-2], [FIELD_1-1], [FIELD_1-2];

SET @TotalRows = @@ROWCOUNT;

-
Then, you can get rid of the CURSOR entirely since it is only used to get a total rowcount anyway, and change the WHILE loop to be simple counter.

-
Finally, putting those two pieces together, the WHILE loop becomes:

DECLARE @Index INT = 1,
        @Records VARCHAR(8000);

WHILE (@Index <= @TotalRows)
BEGIN

  SET @Records = (

  SELECT [FIELD_2-1], [FIELD_2-2], [FIELD_1-1], [FIELD_1-2], [T0.NAME_1-1], [RowID]
  FROM   #Data
  WHERE  [RowID] = @Index
  FOR JSON PATH, ROOT('FIELD_2-1')
                 );

  SET @Index += 1;
END;

All that being said:

• You should probably not be re-declaring the @Records variable each iteration of the loop. Declare it once before the loop and just set it each time within the loop.

• You do not need the @Body variable as it does not do anything. You merely set it to the value of @Records, which is a waste of CPU and RAM (and time).

• Per each iteration of the loop, you execute sp_OACreate, but you never call sp_OADestroy (or whatever that is). I am guessing that this ends up creating many objects in memory. You might also need to execute sp_OAMethod one more time, just before the Destroy to close out the request. You will need to check around to see if that is the case. You do NOT want to leave open any orphaned network sockets.

-
While you might actually get this working, using the OLE Automation stored procedures (i.e. sp_OA* ) is rather risky. They have been deprecated since the release of SQL Server 2005, in favor of using SQLCLR instead. There are many advantages to using SQLCLR instead of the OLE Automation procs, including:

• being able to use NVARCHAR(MAX) instead of being stuck with VARCHAR(8000) and NVARCHAR(4000). In fact, you can even send XML. The OLE Automation procs do not handle any of the datatypes added post SQL Server 2000.

• better security

• better memory handling

• more stable

You can use the .NET HttpWebRequest class. Or, if you do not want to code anything, a pre-done SQLCLR function exists in SQL# (which I created). The function is INET_GetWebPages and it handles a large variety of scenarios (i.e. you can pass in custom headers, send content as GET or POST, etc). However, just to be clear, INET_GetWebPages is only available in the Full version (i.e. not in the Free version)

• Still, it does seem odd that it is SSMS that crashes and not SQL Server itself. If cleaning up the code as suggested above (all except switching to using SQLCLR) does not fix the error, you can always comments out the EXEC sp_OA statements. Start with commenting out all of the EXEC sp_OA statements and the IF statement that follows each one. Then, if that gets the proc to no longer error, start uncommenting each EXEC / IF pair of statements one by one (except the first set: if you uncomment the sp_OACreate, then you must pair that with an sp_OADestroy in the same scope!!)

All of those suggestions aside, the actual issue that caused the crash (as we discovered in the chat) ended up being the sheer number of result sets returned to SSMS. There are 2775 rows returned by the query in the first code block of the question. Within the context of the stored procedure, no result sets are returned to the client; all r

StackExchange Database Administrators Q#212770, answer score: 5