You are viewing limited content. For full access, please sign in.

Question

Question

Custom Query set amount of rows

Workflow Laserfiche
Updated August 2, 2018
asked on August 1, 2018

Hi,

I have a Custom Query in the Workflow that only returns 4 rows. When I run the same query in SSMS I see 10 rows. The activity is setup to return All Rows. I have also tried setting it up anew in a new workflow.

The interesting thing is that if I use a parameter the query returns 4 rows, but if I set my WHERE condition with the exact value then it returns the expected 10 rows.

Any suggestions on what could be the problem?

1 0

Answer

SELECTED ANSWER
replied on August 2, 2018 • Show version history

You shouldn't have to declare the @ parameter in the query itself. If you were using ODBC that would explain why it wasn't working.

I believe ? is primarily for ODBC, but @ is what you would use for SQL direct.

1 0

Replies

replied on August 1, 2018

How do you have the parameters configured? It would help to know what data type it is as well.

0 0
View 7 previous replies
replied on August 1, 2018

I am actually trying to develop a short Forms Audit workflow that will help me manage User Tasks a little better across Forms. If it was the parameter type then it doesn't make sense why does the 'hard coded' value work (If I replaced '%?%' with '%lbell%')?

 

Thanks Jason!

 

0 0
replied on August 1, 2018 • Show version history

What if you try the Microsoft SQL parameter symbol, @, instead of ?, and then have your wildcards in the value, like so:

1 0
replied on August 1, 2018

This is what I was going to suggest. I never had much luck with ? parameter names.

0 0
replied on August 2, 2018

Thank you Andrew and Jason! I got this error, so I assume I have to define the variable at some point in the query?

0 0
replied on August 2, 2018

Can you provide a screenshot of the configuration you had when you received the error? It is hard to say what the cause might be without knowing how it is set up.

0 0
replied on August 2, 2018

By config do you mean this or the data source?

0 0
replied on August 2, 2018

The data source as well, sorry for not clarifying. If it is a SQL database I highly recommend using Direct as the connection type instead of ODBC.

ODBC is great when you have an Oracle db or an Excel spreadsheet, or some other data source, but SQL works best with a direct connection in my experience.

1 0
replied on August 2, 2018

Great tip Jason, thank you! So if I declare it in the query (this is why I normally use ? instead of @ for Custom Query actions) it works, but then it doesn't allow for using the parameters or tokens (I have to write it into the query itself using SET):

Let me try changing to direct connection since it is ODBC now.

0 0
SELECTED ANSWER
replied on August 2, 2018 • Show version history

You shouldn't have to declare the @ parameter in the query itself. If you were using ODBC that would explain why it wasn't working.

I believe ? is primarily for ODBC, but @ is what you would use for SQL direct.

1 0
replied on August 2, 2018

Well you are exactly right, thank you for clarifying that!! It does work now!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...