none
How to find out the reason of MS SQL Server error: Incorrect syntax near '=' RRS feed

  • Question

  • Hello!

    I have a CITECT SCADA system by Schneider Electric, which monitors the process of passing the hot rolled metal pieces (slabs) through the production line with hot metal detectors (sensors).  Each time when a slab cross the sensor the CITECT SCADA triggers one of the functions which executes the appropriate stored procedure in MS SQL Server Express 2014 for inserting a new record in the table ToSP_RASSORT or updating already existing record.

    System works good, but sometimes I have data loss in the table ToSP_RASSORT cells. The CITECT events log file is full of error messages like "SQL Error - Incorrect syntax near '=' ...".

    I tried to log server events with SQL Server profiler but I hadn't any such kind of errors there.

    Could you please advice me how to figure out what is going wrong and why I get these errors in CITECT SCADA log and how can I catch them on the SQL Server side.

    Any your help will be appreciated very much.

    Examples of error message and SQL server events log you can find below. If you need any additional information please let me know.

    CITECT log file

    2019-09-20 12:55:07.316 +03:00 Information Slab_FG2_ON: SQL Error (4 - Database error) on Handle (28036). ERROR [42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Invalid syntax near construct "=" ..

    SQL Server Profiler info for this time period

    SP:CacheMiss	CITECT	61	11016		5			94414	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0										53118113	20801 - AQ							
    SQL:BatchStarting	CITECT	61	11016		5	MTRDB		94413	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0																		
    SQL:StmtStarting	CITECT	61	11016		5	MTRDB		94415	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0												1	0			-1	0	
    SP:Starting	CITECT	61	11016		5	MTRDB		94416	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0						Proc_Update_Rassort_Slab_FG2_ON				1378103950	8272 - P	1	1	5				
    SP:Starting	CITECT	61	11016		5	MTRDB		94417	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		UPDATE [dbo].[ToSP_RASSORT] SET P2D=CAST(@PD1 AS CHAR(1)), NSLAB=CAST((@PD2) AS NUMERIC(2,0)), V03=CONVERT (char(8), CAST(CONVERT(datetime, @IN_DATE + @IN_TIME +'.000',121) AS Time)), DP2P1=CAST((@FG1toFG2TimeDiff)/1000 AS NUMERIC(5,0)), ONSLAB=CAST((@MaxNumCalc) AS NUMERIC(4,0)) WHERE ID=@SlabIDRecRassort	1328116	1	261993006983						upd_ToSP_RASSORT				370100359	21076 - TR	23	2	5				
    SP:Completed	CITECT	61	11016		5	MTRDB		94418	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		UPDATE [dbo].[ToSP_RASSORT] SET P2D=CAST(@PD1 AS CHAR(1)), NSLAB=CAST((@PD2) AS NUMERIC(2,0)), V03=CONVERT (char(8), CAST(CONVERT(datetime, @IN_DATE + @IN_TIME +'.000',121) AS Time)), DP2P1=CAST((@FG1toFG2TimeDiff)/1000 AS NUMERIC(5,0)), ONSLAB=CAST((@MaxNumCalc) AS NUMERIC(4,0)) WHERE ID=@SlabIDRecRassort	1328116	1	261993006983	0			2019-09-20 12:55:07.313		upd_ToSP_RASSORT		4		370100359	21076 - TR	23	2	5				
    SP:Completed	CITECT	61	11016		5	MTRDB		94419	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0	0			2019-09-20 12:55:07.313		Proc_Update_Rassort_Slab_FG2_ON		5		1378103950	8272 - P	1	1	5				
    SQL:StmtCompleted	CITECT	61	11016		5	MTRDB		94420	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0	0		0	2019-09-20 12:55:07.313	1		31	5	0			1	0			-1	0	
    SQL:BatchCompleted	CITECT	61	11016		5	MTRDB	0 - OK	94421	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:07.313		EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON @PD1='1', @PD2=1, @IN_DATE='2019-9-20', @IN_TIME='12:55:07', @FG1toFG2TimeDiff=39059, @MaxNumCalc=8, @SlabIDRecRassort=204440		1	0	0		0	2019-09-20 12:55:07.313			31	5	0									
    RPC:Starting	CITECT	64	11016	0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00	5	MTRDB		94422	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:08.280		exec sp_reset_connection		1	0					1	sp_reset_connection												
    RPC:Completed	CITECT	64	11016	0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00	5	MTRDB		94423	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-20 12:55:08.280		exec sp_reset_connection		1	0	0		0	2019-09-20 12:55:08.280	1	sp_reset_connection	0		0									


    Best regards, Oleksandr.

    Sunday, September 22, 2019 12:15 PM

Answers

  • Hi Oleksandr Husiev,

     

    Thank you for your issue .

     

    Could you please share us your  original script (triggers  and stored procedure) and provide table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 2:25 AM

All replies

  • Add Error event

    Where does it happen? EXEC MTRDB.dbo.Proc_Update_Rassort_Slab_FG2_ON

    or 

    UPDATE [dbo].[ToSP_RASSORT] SET P2D=CAST.....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 12:48 PM
    Answerer
  • So did you include the event Error:Exception in your trace definition?

    I'm sorry, but we know little of your environment and this CITECT SCADA system, so we would need a really good crystal ball to say where the error might be. Maybe that application is talking to some other SQL Server instance that you are tracing? Maybe your trace filter excludes the process where this error occurs? Maybe the application is confused and logs an old error from yesterday? Maybe... the possibilities are endless for us who know nothing at all.

    And if there is a bug in the application cause this error, you would have to talk with the vendor. So it may be better to open a case with the directly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 22, 2019 12:56 PM
  • Hi Oleksandr Husiev,

     

    Thank you for your issue .

     

    Could you please share us your  original script (triggers  and stored procedure) and provide table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 2:25 AM
  • Hello everyone!

    Thank you very much for your advises.

    When I set the following settings in the SQL Sever Profiler it helped me to catch the error:

    Turned on events: Error:Exception, RPC:Starting RPC:Completed, SQL:BatchStarting and SQL:BatchCompleted.

    Example of caught error:

    RPC:Starting	CITECT	6760	5	MTRDB		35680	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.937		exec sp_reset_connection		0	0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00				1	sp_reset_connection				
    RPC:Completed	CITECT	6760	5	MTRDB		35681	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.937		exec sp_reset_connection		0	0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00	0	0	2019-09-23 13:27:52.937	1	sp_reset_connection	0		0	
    Exception	CITECT	6760	5	MTRDB	102	35683	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator	15	2019-09-23 13:27:52.937	1	Неправильный синтаксис около конструкции "=".	66155	0										
    SQL:BatchStarting	CITECT	6760	5	MTRDB		35682	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.937		UPDATE dbo.Main SET FS5DateTimeIn=CONVERT(datetime,'2019-9-23 13:27:52.000',121),  FS4toFS5TimeDiff='5963', FS5_SlabsPause='181230' WHERE ID=		0										
    SQL:BatchCompleted	CITECT	6760	5	MTRDB	1 - Error	35684	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.937		UPDATE dbo.Main SET FS5DateTimeIn=CONVERT(datetime,'2019-9-23 13:27:52.000',121),  FS4toFS5TimeDiff='5963', FS5_SlabsPause='181230' WHERE ID=		0		0	0	2019-09-23 13:27:52.937			0	0	0	
    SQL:BatchStarting	CITECT	6760	5	MTRDB		35685	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.940		UPDATE dbo.ToSP_RASSORT SET V07=CONVERT (char(8), CAST(CONVERT(datetime,'2019-9-23 13:27:52.000',121) AS Time)),DF2F1=CAST((5963)/1000 AS NUMERIC(5,0)) WHERE ID=208133		0										
    SQL:BatchCompleted	CITECT	6760	5	MTRDB	0 - OK	35686	1	RS-SERVER		RS-SERVER\Operator	0X01050000000000051500000047030900A4472211B75BD6C4E9030000	RS-SERVER	Operator	0	64	RS-SERVER\SQLEXPRESS	RS-SERVER\Operator		2019-09-23 13:27:52.940		UPDATE dbo.ToSP_RASSORT SET V07=CONVERT (char(8), CAST(CONVERT(datetime,'2019-9-23 13:27:52.000',121) AS Time)),DF2F1=CAST((5963)/1000 AS NUMERIC(5,0)) WHERE ID=208133		0		0	0	2019-09-23 13:27:52.940			32	5	8

    Now I understand where to dig in the code.

    Thanks again in advance!

    Best regards, Oleksandr.

    Wednesday, September 25, 2019 1:54 PM
  • I am sorry that if I have no your code , it might be difficult for me to provide your solution . Could you please share us more information?

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 9:40 AM