I am developing an application to communicate between an industrial controller and a Microsoft SQL Server database. I have been given a stored procedure as a starting point that exists in the database.

The database is supplied on a test PC with logins etc already set up. The job of the stored procedure is to take a string from the PLC in a format such as (Number1,Date1,Date2,Bool1;(Number2,Date3,Date4,Bool2), and translate that into a table that accepts data in that format.

For example:

Number | Date | Date | Bool
-------+------+------+------
   1   | 2302 | 2402 | false
   2   | 2303 | 2403 | true

Where the string supplied for this would look like

(1,2302,2403,0);(2,2303,2403,1) 

which is supplied as one string parameter to the stored procedure.

The code to talk to the database from the controller uses the TDS protocol and has already been provided by the PLC vendor with examples of how to use it.

The application code just now is sending fixed strings to the database stored procedure to test the response and look at the format of the data returned to see if we can decode the data to determine the response of the database so we can gauge if it is a success or not when inserting the data.

Everything looks to be working on the database side. When I call the stored procedure with the fixed string, I get a response and when I check the database, the expected values are inserted into the table and everything looks ok.

Now we tried to increase the number of rows of data that we were sending and quickly found that the response from the database was increasing exponentially which eventually ended up in a buffer overflow on the controller end. I piped the response into a string so I could look at it and the results were something like the following

:Checking identity information: current identity value
‘9’.DESKTOP-9O8SHG7DeserializeIOLDataj«à DBCC execution completed. If
DBCC printed error messages, contact your system
administrator.DESKTOP-9O8SHG7DeserializeIOLDataj«Ä5:Checking identity
information: current identity value
‘9’.DESKTOP-9O8SHG7DeserializeIOLDataj«à DBCC execution completed. If
DBCC printed error messages, contact your system
administrator.DESKTOP-9O8SHG7DeserializeIOLDataj«Ä5:Checking identity
information: current identity value
‘9’.DESKTOP-9O8SHG7DeserializeIOLDataj«à DBCC execution completed. If
DBCC printed error messages, contact your system administrator.

To me this looks like the output from one of the instructions inside the stored procedure, but I don’t see where it comes from or how to turn it off. At the end of the stored procedure, there is a select statement that is supposed to return a number indicating the success of the script and that is appended to the end of the output like this

DeserializeIOLDataj!&ResultÑÿÁyþà

Which will contain the table header and the value from the select statement which is all I was expecting back.

An abridged example of the stored procedure is provided for reference so you can see roughly what it does.

ALTER PROCEDURE [dbo].[DeserializeIOLData] 
    @WorkOrder nvarchar(50), 
    @SerialData nvarchar(max)
AS
BEGIN

BEGIN TRY
    SET NOCOUNT ON;
    
    DECLARE @WorkOrderID VARCHAR(50)
    SET @WorkOrderID = @WorkOrder

    DECLARE @serialIOLData NVARCHAR(MAX)
    SET @serialIOLData = @SerialData

    -- Declare temporary tables for processing of the data internally
    DECLARE @TempTable TABLE(TempData NVARCHAR(MAX));
    DECLARE @ProcessTable TABLE(TempData NVARCHAR(MAX));

    -- Convert the data string into a table array for processing purposes
    INSERT INTO @TempTable 
        SELECT * 
        FROM STRING_SPLIT(@serialIOLData,';')

    -- Remove extra formatting characters from the SQL data received
    UPDATE @TempTable 
    SET TempData = REPLACE(TempData,'(','') -- Remove ( Chars

    UPDATE @TempTable 
    SET TempData = REPLACE(TempData,')','') -- Remove ) Chars


    -- Create temporary variables here to store the various fields of the results in so we can extract them out 1 at a time
    DECLARE @MyCursor CURSOR; -- Pointer variables for working with a table through loop processing
    DECLARE @MyField NVARCHAR(MAX);

    DECLARE @Temp_WorkOrderID VARCHAR(50)
    DECLARE @Temp_IOLSerialID VARCHAR(50)
    DECLARE @Temp_Welder_ID VARCHAR(50)
    DECLARE @Temp_WeldRecipe INT
    DECLARE @Temp_WeldResult INT
    DECLARE @Temp_IOL_StartDate DATETIME2(7)
    DECLARE @Temp_IOL_WeldDate DATETIME2(7)
    DECLARE @Temp_IOL_FinishDate DATETIME2(7)
    DECLARE @Temp_IsComplete INT
    DECLARE @Temp_IOLStatus INT
    DECLARE @Temp_Reject_Code INT
    DECLARE @Temp_WorkValue VARCHAR(max)
    DECLARE @SerialCount INT
    DECLARE @Temp_ChangedOn DATETIME2(7)
    SET @SerialCount = 1

    -- This code loops through all the rows in the temporary table and processes each of them to extract the CSV data from the string
    -- into the final format for insertion into the end table
    BEGIN
        SET @MyCursor = CURSOR FOR
            SELECT TempData FROM @TempTable      

        OPEN @MyCursor 

        FETCH NEXT FROM @MyCursor INTO @MyField

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Delete all results from the WorkTable and reseed the index
            -- We do this because we are using the auto increment property of the ID column to identify the position of each of the variables
            DELETE FROM ResultTable

            DBCC CHECKIDENT(ResultTable, RESEED, 0)

            DELETE FROM ResultTable

            -- Split the single result field into the component variables
            INSERT INTO ResultTable 
                SELECT * FROM STRING_SPLIT(@MyField,',')
            --SELECT * FROM ResultTable 

            -- Set a fixed value here since all the records have the same ID
            SET @Temp_WorkOrderID = @WorkOrderID

            -- Get the individual values from the Result table and cast/map them to the correct variables
            -- SET @Temp_WorkValue = (SELECT TOP 1 * FROM @ResultTable)
            -- SET @Temp_IOLSerialID = @Temp_WorkValue

            -- Replace the serial count from the read data string with a counter that we increment in this procedure to enforce the
            -- linearity of the count and allow us to remove the serial number from the sent data and save on string length

            SET @Temp_IOLSerialID = @SerialCount
            SET @SerialCount = @SerialCount + 1 -- Increment by 1 for the next record

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 1)
            SET @Temp_Welder_ID = @Temp_WorkValue

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 2)
            SET @Temp_WeldRecipe = CONVERT(INT, @Temp_WorkValue)

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 3)
            SET @Temp_WeldResult = CONVERT(INT, @Temp_WorkValue)

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 4)
            SET @Temp_IOL_StartDate = CONVERT(DATETIME2(7), @Temp_WorkValue)

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 5)
            SET @Temp_IOL_WeldDate = CONVERT(DATETIME2(7), @Temp_WorkValue)

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 6)
            SET @Temp_IOL_FinishDate = CONVERT(DATETIME2(7), @Temp_WorkValue)

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 7)
            SET @Temp_IsComplete = (SELECT CONVERT(INT, @Temp_WorkValue))

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 8)
            SET @Temp_IOLStatus = (SELECT CONVERT(INT, @Temp_WorkValue))

            SET @Temp_WorkValue  = (SELECT TempData FROM ResultTable WHERE ID = 9)
            SET @Temp_Reject_Code = (SELECT CONVERT(INT, @Temp_WorkValue))

            SET @Temp_ChangedOn = (SELECT GETDATE())

            INSERT INTO Tbl_IOL (WorkOrderID, IOLSerialID, Welder_ID, WeldRecipe, WeldResult, IOL_StartDate, IOL_WeldDate, IOL_FinishDate, IsComplete, IOLStatus, Reject_Code, ChangedOn)
            VALUES (@Temp_WorkOrderID, @Temp_IOLSerialID, @Temp_Welder_ID, @Temp_WeldRecipe, @Temp_WeldResult, @Temp_IOL_StartDate, @Temp_IOL_WeldDate, @Temp_IOL_FinishDate, @Temp_IsComplete, @Temp_IOLStatus, @Temp_Reject_Code, @Temp_ChangedOn)

            DELETE FROM ResultTable

            FETCH NEXT FROM @MyCursor INTO @MyField 
        END; 

        CLOSE @MyCursor ;
        DEALLOCATE @MyCursor;
    END;

    DECLARE @Result INT
    SET @Result = 1;

    SELECT @Result AS 'Result';

END TRY

There is a temporary table in the DB that this stored procedure uses to process the strings which is being filled and deleted and reindexed every time for each result set that needs to be inserted. For me the erroneous output must be in the cursor loop towards the bottom end of the table since this is bit of code that runs more when we increase the number of rows.

Hopefully there is an ECHO OFF statement or something similar we can use to get rid of the excess output or a better way to rework the above stored procedure.

Fingers crossed you guys have some good ideas as I am totally stuck.

Appreciate the responses and effort as always.