DB Connection (Microsoft SQL Server) ignoring database context

[KVanselow]

Hello Community,

I am in a setup with a Microsoft SQL Server on one side and trying to SELECT and UPDATE / INSERT data based on different databases on this server.

With the tDBConnection I can specifiy the Database but it seems like it is not considering that specification:

1. I discovered through one job-run the job is writing two tables in two different databases although the database is specified via context variable and the same in both connections
2. next to that i discovered that within a tDBInput-Statement using inner join the job is jumping between databases and joining the table x from database 1 with table y from database 2. > this I try to prevent now using "USE [db_name]" within my select-statement

But how do I prevent 1.?

I am getting errors when trying to specify the table for the update / insert-action via [db_name].[dbo].[tablename]. " > INVALID OBJECT NAME

Any suggestions why the database-context for the connection is not working and how to specify the correct database for both querys to DBInput and DBOutput?

I am using Talend Open Studio 7.1 at the moment.

[David_Beaty]

Hi @Kerstin Vanselow​ 

Yes, the tDBConnection lets you specify the database, as you showed in the screen shot, but once you select the tDBConnection_1 as the connection to use in the tDBOutput_2 component, the only thing you can specify there is the table name.

Suggestions:

• Create 2 DB connections, one for the read and one for the write.
• Don't use the syntax of "USE [database]" in your selection - this "hides" what is really going on.

Thanks

David

[gjeremy1617088143]

could you specify db name and table name please ? and the value of context.db_name ?

[KVanselow]

But why is it not using this database but rather another one on the same host ? Is this a bug ? I am still confused @David Beaty​ .

Both databases have the same setup, meaning tables and fields. One is rather the Test-Database [db_name_test] and the other the database with production data [db_name]. But both have the table [Protocol] but when I use the dbconnection on database [db_name] it is inserting the change into [db_name_test].[Protocol]

[David_Beaty]

Hi,

I suspect then, that the context variable for the database is not getting updated from the test database to the live database when its run.

Thanks

David

[gjeremy1617088143]

You have to Check Your context value and when you launch the job, be sure you use the good context, context group have multiple configuration like : dev,preprod, prod.

[KVanselow]

This is the funny thing, @guenneguez jeremy​ : my context-variables are fine - the process was working perfectly with three other tables and selects so far until now, when I introduced a new table on the SQL Server ...

So the context is definitely correct.

[gjeremy1617088143]

Hi KVanselow, you can do one thing to ensure you have the good value in your context :

in your job put a tJava component with the folowing syntax :

System.out.println(context.db_host);

System.out.println(context.db_name);

and you'll see in the console if you really use the good values.

[KVanselow · Accepted solution]

 

Finally I got the reason: I was working with a subjob and forgot the small checkbox: "transmit whole context". Aaargh.