When my script executes a stored procedure, SQL Server returns a syntax error, "specific error #170. Incorrect syntax near ','. " When I do the same thing in Query Analyzer using the same data, it is successful.

Document ID : KB000027970
Last Modified Date : 14/02/2018
Show Technical Document Details

A comma in the procedure should have been followed by a string. The query that built the procedure used concatenation to build the string. Some of the operands, concatenated to create the string, were null. Since the Data Transformer Server issues "SET CONCAT_NULL_YIELDS_NULL ON" at connection, whenever an operand was null, the resulting string was null. Query Analyzer used whatever the current setting of "sp_dboption concat null yields null" was which in this case was off.

To be sure this setting is always as required for the stored procedure, the workaround is to add SET CONCAT_NULL_YIELDS_NULL OFF to the stored procedure.

See Microsoft? SQL Server Books Online? for more information about this property.