Question

Custom SQL Activity not working as expected

Workflow Laserfiche

Updated February 13, 2018

asked on June 9, 2016

Hi,

I have part of a workflow process that requires Inserting rows into a SQL table for later reference. Number of rows to be inserted can vary from 1 to possible 10 and more. I know that I can use a "For Each Value" step and run the standard "Insert Data" activity. In the case where the is many rows to be inserted I do not believe to be the most efficient mechanism though.

I though of using a "Custom Data" activity to do a bulk insert in one step. I would then use the "For Each Value" step to create a new Token with the formatting of all the values to be assigned to a @local_variable in SQL.

To do this I created a test Scrip in SQL management Studio and when I execute it in SQL Management Studio, it runs correctly and my rows are inserted instantly. Below is a sample of the SQL Script used:

DECLARE @Values VARCHAR(MAX)

SET @VALUES = 

'(''248028'', ''60'', ''120000'', ''SERVICE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM''), 
(''248028'', ''90'', ''150000'', ''SERVICE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM''), 
(''248028'', ''60'', ''180000'', ''SERVICE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM''), 
(''248028'', ''60'', ''120000'', ''MAINTENANCE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM''), 
(''248028'', ''72'', ''150000'', ''MAINTENANCE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM''), 
(''248028'', ''90'', ''180000'', ''MAINTENANCE PLAN'', ''C'', ''2016/05/30 10:51:05 AM'', ''2016/06/07 02:06:33 PM'')'

DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = 'INSERT INTO [dbo].[TQuotes]
					([Column1]
					,[Column2]
					,[Column3]
					,[Column4]
					,[Column5]
					,[Column6]
					,[Column7])
			 VALUES' + @Values 

EXEC(@SQL)

As stated, the above script runs perfectly in SQL Management Studio. My problem comes in when trying to use this code in the "Custom Data" activity. Below is the how I changed it to work in the workflow activity:

The @Values local variable is assigned by Workflow from the token I build up so there is no need to run the first DECLARE statement and it ends up looking like this in Workflow:

DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = 'INSERT INTO [Custom_LF_Data].[dbo].[TQuotes]
					([Column1]
					,[Column2]
					,[Column3]
					,[Column4]
					,[Column5]
					,[Column6]
					,[Column7])
			 VALUES' + @Values 


EXEC(@SQL)

Here is a screenshot of the Activity:

When Testing the activity I tried providing the raw data as defined in the initial code segment, but this gives the following error:

If I test if with the Value that without the extra single quotes, ie the Value that would have already been assigned to @Values variable had it been declared and set in SQL, I get the following error:

If I test it with only one row that needs to be added using the already Formatted SQL value, then it works:

('248028', '60', '180000', 'SERVICE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM')

So the problem seems to get the Custom activity to parse the data correctly that I pass it for a multi row case.

Is there anyone with some advice that might have encountered this before and have a work around?

0 0

Answer

SELECTED ANSWER

replied on June 9, 2016

Ok, so just a quick Update.

After my post I tried a couple more options and I managed to get it working. The problem was the Line Feeds after each value set. Once I wrote it out as one long string with no line feeds, it then accepted the values when passed in like this to the @Values variable:

('248028', '60', '120000', 'SERVICE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM'), ('248028', '90', '150000', 'SERVICE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM'), ('248028', '60', '180000', 'SERVICE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM'), ('248028', '60', '120000', 'MAINTENANCE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM'), ('248028', '72', '150000', 'MAINTENANCE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM'), ('248028', '90', '180000', 'MAINTENANCE PLAN', 'C', '2016/05/30 10:51:05 AM', '2016/06/07 02:06:33 PM')

And to point out the main reason why I though was a better approach, running the workflow to insert 6 rows using the standard "Insert Data" activity, being called for each row, the workflow average speed is 11 seconds. When calling the Custom Data activity passing the data in once, the workflow average speed on the same 6 records is less than 1 second.

0 0

replied on June 10, 2016

Just so you know why, when you are looping through multiple writes it has to setup the connection, setup the insert query, close the connection for each attempt. Every one of the setup and take downs take at least 1-2 seconds.

The one gotcha for using the method you discovered is that you can't inject any SQL code token for security reasons (this is a good thing!). To correct that, this is what writing Stored Procedures in SQL is for!

1 0

Replies

replied on February 13, 2018

Vincent, are you able to share a screenshot of your workflow?

0 0

You are not allowed to follow up in this post.

Question

Question

Custom SQL Activity not working as expected

Answer

Replies

Sign in to reply to this post.