Thanks to the help of a Java programmer, the connection to the SQL server is finally working again after more than a year.
I had migrated to SQL Server 2019 at the time.
Now there is a new problem, which previously worked with SQL Servre 2014 and the supplied JDBC driver.
When calling the procedure, I get a syntax error message. See picture
I’ve had this problem before and after a few tries it was resolved. Why? no idea.
Only this time the problem doesn’t seem to solve itself.
Who can help.
Hi @PatrikS , I just put together a whole post on workarounds for this, but have deleted it because it was misleading, as actually the problem might just be the “Evaluate” button.
Have you tried ignoring the Evaluate and just pressing OK and attempting to execute the node to see if it works?
Evaluate cannot work with stored procedures, because it wraps it in a select statement so as to limit the “preview” return to 10 rows. That is invalid syntax for a stored proc, but it doesn’t necessarily mean the node won’t execute your code properly when it executes as part of your workflow.
If you don’t have any joy and you really need to evaluate, then my workaround is the use of an additional DB SQL Executor node to create a temp table and populate it with the stored procedure such as in my example here with a stored proc I created which simply returns a list of dates from the AdventureWorks2017 database:
Hopefully it will work for you without having to do all that… i.e. just ignore the evaulate button.
The node is not executed I get the following message.
WARN DB Query Reader 0:48 Node created an empty data table.
Your approach is too complicated. The table contains many columns and the procedure is called 10 times with different parameters.
That on the face of it sounds like better news than the “Invalid syntax” message that evaluate is producing… ie the SQL call isn’t actually failing, so…
can you confirm that if you run the following in SQL Server Management Studio it returns any rows?
execute [dbo].[SP_X_PS_GetPrognose_GTS_FOR_ALL_WS_By_Date] '2010-09-01',2,3,5,1
I’d say you can ignore the fact evaluate fails but you can use it to see what parameters it is passing to your stored proc, which is hopefully helpful
I have been using these and similar procedures for years in a web application as well as with ArcGIS and Python for geostatic evaluations.
It’s not because of the procedures. Last year, after many attempts, everything worked. Then we switched to SQL 2019 over a year ago and nothing worked. Now the login via the Microsoft SQL Connector Node is finally working again.
And again the DB Query Node does not work.
hi @PatrikS , so does your answer mean that the specific SQL I asked you to run in SSMS returns rows or not?
I checked it out. You could scream. In my access fontend it doesn’t work in the SSMS. I guess it’s due to the datetime.
Hi @PatrikS , oh the joy of products that do some “translation” behind the scenes, and obscure the results! Anyway, glad to hear that you have hopefully found a solution.
Unfortunately, that’s not the case. In the SSMS I don’t get any data back for any of the procedures. I still have no idea what it is.
If you are able (and want) to upload a txt file containing one of the stored procs, I’m happy to glance over it to see if I can suggest why it may not return results. Sometimes a second set of eyes is all it needs.
Although my first suggestion would be to create a “bare bones” stored proc that simply accepts the parameters and returns something (anything) and then gradually build up again from there until it breaks!
I have located the problem, it is due to the datetime parameter. I hand over e.g. 2020-09-01 in Knime. If I pass the date in the SSMS to the procedure, I do not get any data. However, if I hand over 01.09.2020 in the SSMS to the procedure, I will receive data.
Now I have to revise the Python script which determines the date for me. Because from this I get the date in 2020-09-01 format.
I’ll try that out now. Is definitely the solution to the problem.
Since the SQL procedures work with Acces and Python, I don’t want to change them. There are too many processes involved. If so, it must be possible to solve it through knime. Damn date format
I report whether it worked.
Thank you for your help and tips.
You’re welcome @PatrikS. I agree with you that dates can be frustrating, but I’m glad to hear you are on the way to resolving.
(if all your other scripts are otherwise working though, then possibly using KNIME to convert date from one format to another ought to be straightforward using String to DateTime (using old format) [ assuming that your dates are held as Strings at the point you are calling your stored proc] and then DateTime to String (using new format). It means you wouldn’t have to touch any other code at least!)
Yes is due to the format of the date see test in figure
ja das war es, hab mein Pythonscript umgestellt so das ich das Datum im DD.MM.YYY
Y Format bekomme.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.