Hello! 

I currently have a stored procedure in MSSQL that takes in some parameters and inserts into or updates a SQL view. I’m using a SQLExecutor transformer to run that stored procedure, passing in the feature attributes from FME as parameters for the procedure. The code in the transformer looks something like this:

EXECUTE Tschema_name].eprocedure_name]
   @id = '@Value(id)'
  , @name = '@Value(name)'
  , @description = '@Value(description)'

And the stored procedure looks like this:

ALTER PROCEDURE Cschema_name]._procedure_name]
	@id nvarchar(100),
	@name nvarchar(250),
	@description nvarchar(250)
AS
BEGIN
	IF ((select count(*) from )schema_name]._view_name] where id=@id) =1)
	BEGIN
		UPDATE Uschema_name]._view_name]
		 SET
			 name = @name,
			 description = @description
		 WHERE id = @id
		END
		ELSE
		BEGIN
		INSERT INTO Tschema_name]._view_name]
		 VALUES (@id, @name, @description)
	END

Generally this works, but some of my attribute values (eg name and description) are long blocks of text with various special characters. The first issue was apostrophes (‘) but I bypassed this issue by running a StringReplacer and replacing all instances of a single quote with two single quotes (‘’). The issue I can’t seem to fix is forward slashes (/). It breaks the process and tells me that the string gets broken at the point of the first forward slash. If the data was something like this:

'Replaced on 7/20/23'

the error would be something like this:

  , @description = 'Replaced on 7'. Provider error `(-2147217900) Unclosed quotation mark after the character string 'Replaced on 7'.'

I tried replacing all instances of the forward slash with a backslash in front of it (\/) to try to escape the character, but it didn’t work. When I replaced the forward slash with something else entirely, the process ran smoothly.

I thought this might be an error on the SQL side, but when I insert some random data with forward slashes using the stored procedure within SQL, I have no issues, it’s just when I try to run it from FME like this that it breaks. Any suggestions? I am still using FME Workbench 2022.1.