Question

Using Custom Query to execute a stored procedure

Workflow Forms

Updated August 30, 2018

asked on January 16, 2018

Hello,

We're trying to execute a stored procedure via a custom query. We have an ODBC connection. We're initiation the workflow via Forms. We pass nearly 3 dozen Forms variables from the Retrieve Business Process Variables tool. When we run a test form, we receive an error stating: "ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P2'."

We've done this on other Forms/Workflow combinations just fine. However, on those we were only passing one variable via the stored procedure. I'm a relative novice when it comes to SQL, so I'm not even sure we're doing it correctly. Perhaps we're using the wrong separator between the variables. This is an example of what it looks like: stored_procedure_name ?, 'variable 1', ?, 'variable 2', ?, 'variable 3' etc. Is this the correct way to pass multiples?

Thanks,

Michael

0 0

Answer

SELECTED ANSWER

replied on January 16, 2018 • Show version history

If I am reading your post right there should be no variable 1 or 2 in with the question marks. Should just be

procname ?,?,?

with the 3 question marks below mapped to their respective variables.

0 0

View 2 previous replies

replied on January 17, 2018

Chris, your answer was the one that worked after all. There was an additional variable in the stored procedure that I wasn't aware of that needed to be added to the query. Once I did that, I was able to get it to work. Now I need to figure out the part of pulling a newly created variable back out from the query...

Thank you everyone for your help in this. Much appreciated.

0 0

replied on January 18, 2018

For the return value all you need to do is select it and declare it.

On thing I always have to do though is hit the Test button in the activity in workflow. Only after you test it do you get the output in usable activities below. Also, the name always seem to come in blank for some reason.

0 0

replied on January 18, 2018

Thanks Chris,

Do I need to enter anything into the parameters below for that select?

0 0

replied on January 18, 2018

Yes, its the same. I assume you are passing parameters into a stored proc and returning a result. The output is the SELECT.

0 0

replied on January 18, 2018

Yes, we're passing variables collected by a form. It's an ODBC database. As such, using the code that we enter into the stored procedure, would the process to enter the data and then retrieve the new variable look like the insert below?

At the end of all the parameter statements, we have a final parameter name of ? and parameter value of WLUN_Number out.

Sorry for my lack of experience with SQL and much thanks for your assistance.

0 0

Replies

replied on January 17, 2018

Hi, in my case I'm using the following:

1 0

replied on January 17, 2018

Thank you all very much for your help so far. Making a bit of progress now (I hope). I reconfigured the query like @████████shows above. We're no longer getting the previous error, but now receive "ERROR [HY007][Microsoft][ODBC SQL Server Driver]Associated statement is not prepared.

0 0

replied on August 30, 2018

It looks like you can run an sp using one parameter with just the single question mark. When you add multiple parameters you have to assign them. In the example above the names @param1, etc have to be the exact names of the parameters as defined in the stored procedure. In my case the parameters had names like @Department, and @DocumentType. But with that it worked. This may be connection dependent as well, with ODBC acting in a different manner than a direct connection.

0 0

replied on January 16, 2018

Thanks @████████, I just tried your suggestion, but we're getting the same error. This is how it's laid out:

0 0

replied on January 16, 2018 • Show version history

Sorry, just meant that is a very generic explanation. Put EXEC before your name and put the name in brackets.

EXEC [procname] ?, ?....

Also, don't need to run a whole test form. Can you just hit the test button and type in dummy info to see if you get errors?

0 0

replied on January 16, 2018

Unfortunately, we're still getting the same error message.

0 0

View 1 previous reply

replied on January 16, 2018

Yes, the procedure ran fine in SQL Management Studio.

0 0

replied on January 17, 2018

Are your parameters listed in the same order as the procedure expects them? The order matters because they're not named parameters, so the SQL driver doesn't have anything else to match them.

0 0

replied on January 17, 2018

Yes, I've triple checked the order to make sure it's correct.

0 0

replied on January 17, 2018 • Show version history

I have one working (with one variable). This is it (see attached). You have to make sure that the workflow version and odbc version are the same: ie. 32 bit or 64 bit. Also very important that the data type and length you are bringing in is exactly the same in Laserfiche otherwise it will fail as well.

SONum is the name of the variable in the sql query. I have had that error when my data types did not match.

sample custom query.jpg (71.61 KB)

| Download

0 0

replied on January 17, 2018

We do have other workflows where it works. But those were only for working with one variable as shown below. That's why I initially built the new one in the same format. This workflow works just fine, so I'm assume that there aren't any compatibility issues between Laserfiche Workflow and the OBDC database.

0 0

replied on January 17, 2018

And here is another thread that may help you:

https://answers.laserfiche.com/questions/71130/Workflow-Custom-Query--Error-converting-datatype-nvarchar-to-date-Wf-910328

0 0

replied on January 17, 2018

Hi Micheal, if you have the option to use a SQL connection instead of an OBDC connection to your DB, I'd suggest you do that. That way you can create real parameter names and use them in the SQL Query as opposed to having to play around with a bunch of "?"s

0 0

You are not allowed to follow up in this post.

Question

Question

Using Custom Query to execute a stored procedure

Answer

Replies

Sign in to reply to this post.