I am trying to use Knime coming from Alteryx, and I have already wasted more than an hour trying to connect to a MS SQL Server, with no luck!
I need to import a CSV file into SQL. I have set up a file reader node, run it and verified that it reads the CSV correctly.
Then I connect a DB Writer node, but I cannot configure it because “the dialaog cannot be opened for the following reason: database connection is not available”. But how am I supposed to set up the database connection if I cannot configure the node???
Then I tried to configure a “Microsoft SQL Server Connector”. I specified the hsotname, the port, the database name, and “native authentication”.
If I then run the node, I get “Execute failed: Unknown server host name 'MYSERVER\SQLEXPRESS”
What am I doing wrong? Could it be that some drivers are not installed? How do I verify that?
By the way, I can connect to that server using DataGrip and SQL Alchemy (with Python) just fine.
There is a brochure https://www.knime.com/knimepress/download-from-alteryx-to-knime
To write you need first use Microsoft SQL server connector node. Set it and execute.
When connect it to DB Writer port. Also, data from File or CSV Reader needs to be connected to the DB Writer port.
So the DB writer node needs two inputs: one with the data to be written (eg a CSV reader node) and another one from a DB Connector node, right?
The point is, I did try to run a SQL Server Connector node, but, as I said, it does not work. I get:
“Execute failed: Unknown server host name 'MYSERVER\SQLEXPRESS”
Note it doesn’t say there’s a problem with the password, but that it cannot find the server at all. Any suggestions? Could it be that I don’t have the JDBC drievrs required? How do I check that?
That always been confusing for me to connect to local SQLEXPRESS. I currently do not have one but work wit it couple years ago. See on internet what could be a problem. I remember that browsing service need to be active so on.
I present below some information on how to connect to a Microsoft SQL Server Express database; unlike most other software relying on JDBC, Knime requires you to input the port number, not the instance name. I hadn’t found this mentioned anywhere. It would be good to add this in some kind of FAQ and in the official documentation.
I am not sure if the same applies to full versions of SQL Server
I had been having issues connecting to a Microsoft SQL Server Express database.
In summary:
with other software using other JDBC drivers, I can specify the instance name, with no need to specify the port. Eg this works with PyCharm and DataGrip. It does NOT work with Knime. If my server name is MY_COOL_SERVER, a typical instance name would be SQLEXPRESS, so the URL in PyCharm would be jdbc:sqlserver://MY_COOL_SERVER\SQLEXPRESS
The Knime SQL Server Connector requires the port - it doesn’t let you specify the instance without the port; if I specify server name and port, it works. If I specify server name, instance and port (which is redundant), it does NOT work.
The Knime DB connector works if I specify the port name, e.g. jdbc:jtds:sqlserver://MY_COOL_SERVER:49732/my_database
The Knime DB connector doesn’t work if I specify the instance without the port name, e.g. jdbc:jtds:sqlserver://MY_COOL_SERVER\SQLEXPRESS/my_database
To find the port and make sure everything is configured properly, run SQL Server Configuration Manager:
SQL Server network configuration - protocols: make sure it’s all enabled (shared memory, TCP/IP, named pipes).
Then: TCP/IP - IP addresses: scroll to the bottom, IP ALL - TCP dynamic ports: that’s the port number you need
SQL server services: make sure that SQL Server and SQL Server Browser are running. Of course, if you can connect with DataGrip or another tool, chances are they are
Dear moderators, I dare suggest you should have probably done the opposite, ie move the old thread into the new one, simply because the title of the new one is more explicit and has the solution in the title itself. Knime’s documentation is already poor enough, as this thread shows - at least let’s try to make the forum more informative and easier to access.
“How to connect to SQL Server: you must specify the port not the instance” is very clear
“Trying to import a CSV into SQL Server” much less so.
Yes. The docs make no mention of the concept of instance.
In every other jdbc connection I have used with other software, I would always use
://SERVERNAME\INSTANCENAME
with INSTANCENAME typically being SQLEXPRESS
and not worry about the port.
I find it very odd that Knime requires the port, and even odder that it doesn’t work with both instance and port. Anyway, I hope this post will be useful for other users.
Sure, it doesn’t mention concept of instance but it does states it requires port.
Topic name modified to be clearer.
Instance name can be specified on JDBC Parameters tab.
That’s the odd bit - if I specify the instance name it does not work, even if I have also specified the port. That’s what caused me to waste a lot of time trying to sort this out
