Issue connecting to MS SQL Server engine instance

Six Stars

Issue connecting to MS SQL Server engine instance

I came across the following issue, when connecting to MS SQL Server databases
If there are more, than one database engines running, one can specify which one to connect to in the "Additional JDBC Parameters" of tMSsqlRow with "instance=<database instance name", e.g. "instance=SQLEXPRESS" for the default instance of MS SQL Express.
See screenshot below.
This works fine, if there are more than one database engine instances running on SQL server. However surprisingly this does not work any more, if there is only one instance running. In that case one has to specific "" in "Additional JDBC Parameters" to succeed connecting to the database. This is a little bit awkward, because my job should connect to different database servers, with each of them having one or more than one instances running. My current workaround is to selectively fill "Additional JDBC Parameters" correctly using a context variable, with a context choosen depending on the target database. However this is quite a source of errors.
Did anyone come across this problem? Is there a better way to connect to the right SQL server enging, or is this maybe a bug, that needs fixing?
Many thanks in advance!

Moderator

Re: Issue connecting to MS SQL Server engine instance

Hi MarkusL,

Could you please upload your screenshot into forum again? We cannot see it on our side. What's the error info you are facing?
More information will be helpful for us to address your issue quickly.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Issue connecting to MS SQL Server engine instance

Sorry, I have some issues with attaching screenshots too. 
* This is where I specify the database instance, that I want to use: Screenshot (2)

* As said, this works fine, if there is more than one instance. If there is only one instance (exactly the one, that I specified), I get the following error. It's basically the same error as in case of wrong database name: Screenshot (1)

* The exactly same job works fine,
- if there is more than one database instance - which actually demonstrates to me, that the job should be ok
- or I enter "" in the "Additional JDBC parameter"

Screenshot (1)


Screenshot (2)
Moderator

Re: Issue connecting to MS SQL Server engine instance

Hi,
* The exactly same job works fine,
- if there is more than one database instance - which actually demonstrates to me, that the job should be ok
- or I enter "" in the "Additional JDBC parameter"

The setting of  screenshot (2) is correct.
Do you mean you cannot connect to MS SQL Server when set  Additional JDBC parameter="instance=SQLEXPRESS"?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Issue connecting to MS SQL Server engine instance

Hi Sabrina,
first many thanks for your fast response and for relentless support in this forum, that helped me already a lot!
To clarify my request:
* My job works:


                  Additional JDBC parameter set to            Data base setup
                  --------------------------------------          -------------------------------------------------------------------------------------

Case #1     ""                                                          One single SQL server database engine instance "SQLEXPRESS"

Case #2     "instance=SQLEXPRESS"                        Two SQL server database engine instances "SQLEXPRESS" and another one

In Case #2 specifying the database engine instance is mandatory to successfully connect to the datbase


* My job does NOT work:

                  Additional JDBC parameter set to            Data base setup
                  --------------------------------------          -------------------------------------------------------------------------------------

Case #3     "instance=SQLEXPRESS"                        One single SQL server database engine instance "SQLEXPRESS"

And this is the issue case, because I would like to have "instance=SQLEXPRESS" set, regardless how many instances are running on the server, including only the one and only.


Markus
Moderator

Re: Issue connecting to MS SQL Server engine instance

Hi,

Thanks for your clarification which is very clear for us.
Could you please indicate on which official product build version you got that?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Issue connecting to MS SQL Server engine instance

Software environment:

* TOS_ESB-win32-x86
Build id: V5.6.2_20150508_1414

* Windows 8.1

* Microsoft SQL Server Express (64-bit)
Build 12.0.2000.8

I will most likely migrate to Talend win64 v6.0.0 soon and will report back to this thread, if this issue persists or vanishes.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads