Question

Having trouble with a custom SQL query that works perfectly in Quick Fields (8.3), but reports an error in Workflow (9.1)

Workflow

Updated December 20, 2023

asked on December 4, 2014

The repository, WF and LF servers are all on the same VM (LF and WF 9.1.1 on Server 2012 R2). The queried database is on a different SQL server, on a different VM. There are no known authentication issues.

This is the query, which I copied exactly from the Manage Metadata section in QF:

SELECT * FROM Lntmu13.matter

WHERE mat_no = ?

When I pasted this in a custom query Workflow activity, it reports this error when I test it: Incorrect syntax near ‘?’

I am not trained in writing the queries, so I don’t know where to go from here.

0 0

Replies

replied on December 4, 2014

What are you using for the value for the "?" parameter?

0 0

replied on December 4, 2014

A WF token - %(RetrieveFieldValues_Matter Number)

0 0

replied on December 5, 2014 • Show version history

What did you copy the query out of to paste it into the Query editor? Is it possible that you included an unprintable character? I find that this is often the case if I copy directly from a web page. Have you tried to just manually type your Query rather than pasting it from someplace else?

0 0

replied on December 5, 2014

Thanks Bert, I copied it from the script that works in QuickFields. But I just re-typed it as you suggested, but unfortunately, no joy. Thanks anyway!

0 0

replied on December 5, 2014

Can you add a Track Tokens right before this activity to check what value you're getting from Retrieve Fields?

0 0

replied on December 5, 2014

Hi Miruna,

I did that as advised and ran a test doc through the WF. All tokens picked up correct values. The value I used for %(RetrieveFieldValues_Matter Number) was 21000.008 which is an actual matter number in the SQL db I am trying to query. The query displayed the same error - Incorrect syntax near ‘?’ - and the WF terminated at that point.

0 0

replied on March 7, 2016

I am also having the same issue as the above request, but noticed that there was no definitive answer to the issue.

I am using MS SQL Express to store Department Travel Request information, being used with Forms and using Workflow to Delete, add or change records.

To Delete records I am having trouble with the Custom Query. If I have hard code values into the query it works perfectly when Tested. But when I use Parameters using the '?' it returns the error "Incorrect syntax near '?'".

See attached for screen shots of both types.

I have manually typed in the query (eg not copied/pasted). I am an absolute beginner at this stuff, so does anyone have any ideas?

cheers

Grant

Test Custom Query Syntax error with Paramaters.png

Test Custom Query Syntax working without Paramaters.png (7.84 KB)

| Download

Test Custom Query Syntax error with Paramaters.png (18.69 KB)

| Download

0 0

replied on November 8, 2017

Good morning,

I'm running into the same issue too. I posted a question here: https://answers.laserfiche.com/questions/131172/Custom-Query-Editor and then came across this post.

Lloyd and Grant, did you ever get this resolved on your side?

Thank you,

Raul Gonzalez

0 0

replied on November 8, 2017

UPDATE: in my case I had to adjust the parameter naming as stated in this post: https://answers.laserfiche.com/questions/131172/Custom-Query-Editor#131174

Thank you,

Raul Gonzalez

0 0

replied on December 20, 2023 • Show version history

Looks like the data connection needs to be ODBC and NOT Direct. Before doing that, first try changing the parameter FROM '?' to something with this format @parameterName. Example:

To be clear: Direct SQL connection you can use parameters, ODBC connections you can only use '?' as parameter name.

0 0

You are not allowed to follow up in this post.

Question

Question

Having trouble with a custom SQL query that works perfectly in Quick Fields (8.3), but reports an error in Workflow (9.1)

Replies

Sign in to reply to this post.