Question

Numbers returning varchar causing SQL query errors

Workflow

Updated August 10, 2018

asked on August 9, 2018

Hello,

I have an issue where I am pulling a value from a forms business process and passing that value to an SQL update/insert statement in workflow. Any iteration of this value in any step of the forms process exists as a number field. Yet every time I retrieve this value and attempt to use it in an insert statement into a SQL Server field of type int I get an error of "Conversion failed when converting the varchar value to a data type int", and nothing I do will change this error, even replacing the value with a new token I created that is literally just the value 1 or any other integer value, I get the same error. Using the token calculator to modify the original value or any other value I make to make sure it is a number if it somehow is null or a string, to an integer value returns the same error. I have set the default value of this field to 0, I have literally entered a numeric character into the parameter field when testing the query, and get the same error EVERY time. What am I doing wrong????

0 0

Replies

replied on August 9, 2018

Out of curiosity, when you tried using a token you created in Workflow, did you set the integer or number token tag and remove the string tag?

0 0

replied on August 9, 2018

I did, but I don't want to create a custom token, I just want to pull the value from the business process instance.

0 0

replied on August 9, 2018

I know, I was just curious if a token with the Integer/Number tag had the same problem because that would help narrow down the source of the issue.

0 0

replied on August 9, 2018

What about adjusting the SQL statement to convert the value from VARCHAR to INT? Also, what is the field type that you are pulling from?

If it's text, I'm assuming that is the problem, so you'd need to make a change to the SQL statement.

0 0

replied on August 9, 2018

I have tried using CAST(token_val As int) and CONVERT(int, token_val) in the SQL statement, both results in the same error still. The field type is "Number" as stated originally. It is not text, I have even deleted the original and re-created it as a number field several times.

0 0

replied on August 9, 2018

Can you post screenshots of your Insert Data activity configuration?

0 0

View 5 previous replies

replied on August 10, 2018

I was running this off a custom query which I have changed several times, see screenshot of just the update statement which I get the same error in this statement or an insert statement. Let me know if you need to see more.

0 0

replied on August 10, 2018

I would start by testing with all the values hard coded. If that works, then add in the tokens 1 by 1 testing after each new token is added. Verify that the token is working before adding the next. When you run into the error, then you know which token to specifically work on to try and resolve the issue.

1 0

replied on August 10, 2018

I have already done this, adding an integer value in the parameter field will allow it to run without error. However any token value, either taken from a business process token, or creating a token manually using "Assign Token Values" or "Token Calculator" and referencing that token results in the same error returned.

0 0

replied on August 10, 2018 • Show version history

Ben,

If you are working with a SQL database, have you tried using a Direct configuration instead of ODBC?

With a Direct connection setup you would use @ parameters instead of ? parameter names, for example @P1, @P2, etc., and the data types might behave better.

In my experience you get much better results with a direct connection when dealing with SQL databases.

1 0

replied on August 10, 2018

Could you track the "Int16" tokens right before this activity? Alternatively, if you type numbers directly into the parameters for the Customer Query, does it work?

(The symptoms you're describing make it sound like your token might not exist, so the literal string is being passed in)

1 0

replied on August 10, 2018

Typing number directly into the parameter works, but even creating a token and assigning it a number value does not work. I will try direct connection next as recommended by Jason.

0 0

replied on August 10, 2018

Hi Ben,

I think a direct connection will be more reliable (I get far fewer errors, false negatives, etc.) but I don't know that it will fix anything.

I still think it is a good thing to change, but I also think Miruna's suggestion of tracking the token is an important step in narrowing down the source of the problem.

1 0

replied on August 10, 2018 • Show version history

You could also try building the SQL Query as a dynamic varchar and then doing EXEC

Something like

DECLARE @sqlCommand varchar(1000)
DECLARE @access_val_num varchar(75)
DECLARE @access_change_date varchar(75)
DECLARE @emp_acc_nme varchar(75)
DECLARE @hardware_sn varchar(75)
DECLARE @hardware_imei varchar(75)
DECLARE @hardware_manufacturer varchar(75)
DECLARE @emp_current_id_val varchar(75)
DECLARE @resource_val_id varchar(75)

SET @access_val_num = ?
SET @access_change_date = ?
SET @emp_acc_nme = ?
SET @hardware_sn = ?
SET @hardware_imei = ?
SET @hardware_manufacturer = ?
SET @emp_current_id_val = ?
SET @resource_val_id = ?

SET @sqlCommand = 'UPDATE [emp_access] SET [access_val_num] = ' + @access_val_num + ', [access_change_date] = ' + @access_change_date + ', [emp_acc_nme] = ' + @emp_acc_nme + ', [hardware_sn] = ' + @hardware_sn + ', [hardware_imei] = ' + @hardware_imei + ', [hardware_manufacturer] = ' + @hardware_manufacturer + ' WHERE [emp_current_id_val] = ' + @emp_current_id_val + ' AND [resource_val_id] = ' + @resource_val_id + ';'

EXEC (@sqlCommand)

Just make sure to add any character handling (such as quotes around strings)

0 0

You are not allowed to follow up in this post.

Question

Question

Numbers returning varchar causing SQL query errors

Replies

Sign in to reply to this post.