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.
The message "DBCC execution completed" most likely comes from the DBCC command:
DBCC CHECKIDENT(ResultTable,RESEED,0)
You can try to add a WITH NO_INFOMSGS argument to suppress the informational messages. Like this:
DBCC CHECKIDENT(ResultTable,RESEED,0) WITH NO_INFOMSGS
I'd recommend to run the stored procedure in SSMS to see what kind of output / results it generates before and after the changes.
Overall, the DBCC CHECKIDENT(RESEED) is rather ugly. Maybe you can get rid of it. For example, TRUNCATE TABLE works much much faster than DELETE and it resets the IDENTITY column as well. Just as you want.