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
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:
Hi @takbb,
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?
hi @takbb,
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.
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.
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
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!)
