topic Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue in Talend Studio

SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Thu, 14 Sep 2017 14:22:40 GMT

When ever i create a connection string to SQL server named instance by hand like this jdbc:sqlserver://localhost\sql2016;DatabaseName=TMM12; it works fine, but when-ever i click the finish button and try to read the queries, it will show my some connection error. And when i get back to my database connection string setting, there will be extra colon :, after the named instance, which is i guess wrong. Take a look at my screenshot to see and why Talend, concatenate that connection always. It is a blocking for me because I cannot connect to my database.

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Fri, 15 Sep 2017 08:12:26 GMT

Hello,

The "String of Connection" is grey out. It will be filled in the connection details automatically. Please see my screenshot.

Could you please try to use:
Login: XXX
Password:
Server: localhost/ip address
Port: 1433
Database: XXX
Schema:
Additional Parameters: instance=SQLEXPRESS and check if nlmauth.dll is placed in the JRE\bin directory?

Here is online document about:TalendHelpCenter:Centralizing database metadata

Best regards

Sabrina

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Fri, 15 Sep 2017 15:04:50 GMT

Wow.. thank you very much. I think i went through this documentation but i
haven't seen about the "instance" additional parameters.

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Fri, 15 Sep 2017 15:24:36 GMT

Sorry but i revert my answer. IT STILL DOES NOT WORK. I thought it was working when i found out that I was connecting to un-named instance of SQL express, and when i disable it as shown on the picture below, the connection fails

As you can see, i got to instance of SQL Server running, when i disable the un-named instance, the connection on talend does not work, so i guess the additional parameters that you provided does not really work.

Still failing connection with instance on the additional parameterTCP IP

So i guess that connection string should be really updated on it's format in order for it to work, as i described on my first message.

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Mon, 18 Sep 2017 03:37:40 GMT

Hello,

Can you successfully connect to your sql server through client without using talend tool?

Your SQL Server Configuration Manager TCP/IP is Enabled? Could you please make sure that TCP connections to the port are not blocked by a firewall?

Please refer to this document:https://stackoverflow.com/questions/24592717/error-connection-refused-connect-verify-the-connection-properties and let us know if it is OK with you.

Best regards

Sabrina

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Anonymous — Mon, 18 Sep 2017 05:11:14 GMT

Yes, i can connect to it using other tool, but the connection format should be as shown on my previous email jdbc:sqlserver://localhost\sql2016;DatabaseName=TMM12;

The talend tool generate extra colon after SQL2016, that is why the connection is wrong. If you look at this screenshot https://community.talend.com/yutwg22796/attachments/yutwg22796/integrating/9916/1/connection-bug.png

Re: SQL server with named instance, always concatenate the semi-colon (for the port), which is I guess an issue

Spretorius — Thu, 06 Sep 2018 16:14:58 GMT

For this to work you will have to use the below method

Server: Servername;instanceName=sql01.

Port: (Must be blank)

So your string will look something like this

jdbc:sqlserver://Server;instanceName=sql01;DatabaseName=DB;integratedSecurity=true