Question

WF Custom Query 0 results but SSMS Query is successful? Syntax incorrect?

Workflow

Updated December 11, 2017

asked on May 23, 2014 • Show version history

I am attempting to query a SQL table using Workflow 9.1.1 custom query activity. It is using a partial folder name gathered from a pattern matching token. When I test the query in the designer, using the same partial folder name in the parameter value field, it results in "0 row(s) affected."

I've run the same query in the SQL Server Managment Studio with success. I've attached screenshots of both results.

Is there an issue with the syntax in Workflow as compared to the SSMS syntax?

Thanks in advance for your help!

0 0

Answer

SELECTED ANSWER

replied on May 23, 2014 • Show version history

Can you take out the quotes around @NN? And move the % into the parameter definition right after the token?

0 0

Replies

replied on May 23, 2014

Are you using ODBC? In that case, your parameter needs to be called "?" because that's all ODBC accepts.

If not using ODBC, I think your parameter needs a name, not just "@".

That said, I think it will work if you change the query to "NAME like ?" and then set the ? parameter to %(PatternMatching_tkn_partial name from LF Folder)%

0 0

View 8 previous replies

replied on May 23, 2014

Hi Miruna,

I'm using a direct SQL connection. Using the '@name' parameter doesn't make a difference, unfortunately.

0 0

replied on May 23, 2014

The following query works for me for a direct connection to SQL. Can you post a screenshot of your query and parameter setup?

1 0

replied on May 23, 2014

Here is my query:

0 0

replied on May 23, 2014

I'm using the % as a wildcard to find the Name that starts with my pattern match.

0 0

SELECTED ANSWER

replied on May 23, 2014 • Show version history

Can you take out the quotes around @NN? And move the % into the parameter definition right after the token?

0 0

replied on May 23, 2014

Miruna,

You have done it! That works now. Thank you very much!

Regards,

Kyle

0 0

replied on August 30, 2017

Miruna,

I have the same problem and followed your instructions exactly, but still can't get the query to return results.

Since these posts were from 2014, I'm wondering if a bug was introduced somewhere between then and now.

Maybe I'm missing something else? Any info would be greatly appreciated! Thank you!

Custom_Query.png (18.96 KB)

| Download

0 0

replied on August 31, 2017

Is this at run time or while testing?

1 0

replied on August 31, 2017

I've just been testing thus far.

0 0

replied on August 31, 2017

Dangit. I just realized that if I don't replace the "%" in the testing dialog, the results return as expected. This is why you don't Laserfiche at 1AM :)

Thanks for leading me in the right direction, Miruna! Hope you are well!

0 0

replied on August 31, 2017

Haha, yeah, that was pretty much my guess.

1 0

replied on December 8, 2017 • Show version history

Thank you everyone for providing detailed info on this post - I really did try to follow it as it's exactly what I'm looking for. Mine 'should' be simple...

Writing the custom query SQL 2014/LF WF 9.2.0.167. It's fine directly run in the database, but doesn't like the ? in LF so I've tried naming the parameter, putting quotes (single and double around it). The results will always be numeric - the hints listed above and that's not working.

Additionally, I do need to add this to the query: ORDER BY modify_timestamp DESC

I took it off while trying to get the first part working. I'm in need of the most recent result in the table.

Thanks for your help!

UPDATE: I figured it out :-)

Here's a screenshot of my answer in hopes it helps someone else! It really was simple - using the @ option instead of ?!!

0 0

You are not allowed to follow up in this post.

Question

Question

WF Custom Query 0 results but SSMS Query is successful? Syntax incorrect?

Answer

Replies

Sign in to reply to this post.