Connecting KNIME to SQL Server 2012

 

I wanted to write a quick note for other KNIME users or anybody who is trying to read data from SQL Server into your KNIME workflow.  This writeup is intended to supplement KNIME’s database documentation.

Specifically I will cover trying to setup KNIME 3.2.2 running on Windows to be able to connect to SQL Server 2012 using SQL Server Authentication with a JDBC driver.

This write up assumes your ID and password is setup to use SQL Server Authentication, as opposed to Windows Authentication.  You should verify you can log in with another tool.  I used Excel.  If you cannot connect with another tool you will need to do some troubleshooting steps to resolve this issue first. Typical things to check is if you really have the right password, if you are being blocked by a firewall on the client or server side, if SQL Server is setup to allow remote connections, and if the servers are setup under a local Windows account (as opposed to running as LOCAL SYSTEM).  There are additional steps that are not covered in if you are required to use Windows Authentication.  This write up is for SQL Server Authentication only.

To get started first download the Microsoft JDBC Driver 6.0 for SQL Server

Now unzip the download and put the unzipped folder under the KNIME install folder. My KNIME install was under C:\Program Files\KNIME

I ended up copying the entire folder Microsoft JDBC Driver 6.0 for SQL Server under C:\Program Files\KNIME

In KNIME go to File -> Preferences menu option, then go to KNIME then Databases This is the place you will need to configure KNIME.   You will be adding a file so KNIME can make a JDBC connection to SQL Server.

Click the Add File button and navigate down to C:\Program Files\KNIME\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre7 and select the file called sqljdbc41.jar

Now click OK.

Now lets test out if we can connect. Start a new workflow and add a Database Reader node.

Configure the node as follows:

Select the Microsoft SQL Server JDBC driver from the drop down.

Then enter in your Database URL that follows the format of jdbc:sqlserver://<host>:<port>/<database_name>.

Enter your User ID, password and your SQL query. Make sure all your column names have a names. For example, if you have a query like SELECT COUNT (*) FROM YourTable you will get an error. Use SELECT COUNT(*) AS MyCount FROM YourTable and you will be OK.

Right click on the node and run it.

Troubleshooting voice over IP calls on Comcast internet.

 

I use Microsoft Skype for Business to make voice over IP calls for work.  I like this technology as it helps me maintain a flexible and mobile work style.  I was not happy when I noticed the call quality of my voice over IP calls was degrading over a period of a few months.  I wanted to share some troubleshooting steps I found useful:

1.  Try rebooting your Comcast modem. 

2.  Try connecting your laptop and use your cell phone’s mobile hotspot.  This can help you isolate if the issue is coming from your laptop/VoIP provider or with your ISP or internal network.  In my case, my call using my cell phone’s hotspot was perfect, so I focused my effort to my local network/ISP.

3.  Try checking your jitter and latency. 

I used this site to check my jitter and latency.  https://www.megapath.com/speedtestplus/ When I was having a problem my jitter was at 20 ms.   After the issue was resolved my jitter was 1ms

4.  Try testing your signal strength and quality with Comcast internet. 

Log on to your Comcast modem by typing 192.168.100.1 in your browser.  If you never logged on to your Comcast modem the default Id is admin and the default password is password.   

Now click on Connection then XFINITY Network.

Scroll down and find the Channel Bonding Values and find the values for SNR (Siginal to Noise Ratio) , Downstream Power Level and Upstream Power Level.

Signal to Noise Ratio (SNR)

If you have 256 QAM then it is recommended you are higher than 33 dB.
If you have 64 QAM then it is recommended you are higher than 27 dB.       If you have 64 QAM then it is recommended you are higher than 27 dB. 

Downstream Power Level

The recommended range is between -7 dBmV to +7 dBmV. 
Lower than -15 dBmV & Higher than +15 dBmV  is out of tolerance

Upstream Power Level

The recommended signal levels are +35 dBmV to +49 dBmV.   Above 52 dBmV is out of tolerance.

If any of your values are not with in the recommended range you should call up Comcast and ask if they can confirm the signal strength and quality on your line.   A level two Comcast technician has the ability to log on to your Comcast modem and see these signal strength metrics.  There is chance they will need to send a technician to check the coaxial cable outside of your home.  In my case the issue was caused by a sever kink in the coaxial cable outside.

5.  Working with Comcast technical support.

In my experience when I call Comcast technical support I am asked if I would like to participate in a brief survey after the call.   I find that answering “Yes” I often get connected to the “offshore team” as their accents a small increase in background static kind of give them away.  The offshore team tends to be much more helpful, pleasant, and overall much more capable technically.  When not agreeing to take the brief survey I tend to get an American call center agent who seem to be following a ridged script.  I find they tend to be unhelpful, lazy, and bureaucratic. On one call the America call center agent gave me information that was flat out not true and borderline on fraud when he told me he could not help me further but I could sign up for a line protection service for an additional monthly fee.   Do yourself a favor and answer “Yes” if you are asked to do a brief survey.