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.

[Anonymous]

Hi

You can't select data from different database on the same tDBInput componnent (only one connection established), as a workaround, use two tDBInput components to query data from different Database, and then do join on tMap.

Regards

Shong

[David_Beaty]

Hi,

I would suggest you create two tDBConnection components, one as the source/select connection and one as the desination/insert connection. That would probably just good practice, but also will help you, in this case, isolate the problem.

Also, remember that in database select statements, you should fully qualify the table name:

SELECT col1, col2,col3

FROM {database}.{schema}.{table}

Thanks

David

[KVanselow]

Thanks for your feedback, @Shicong Hong​ and @David Beaty​ .

There is a misunderstanding though: i do not want to select from two different databases but only from one. And the select is not a problem since I use the statement "USE [db_name] SELECT * FROM [table]" now altough the db_name is already specified in the connection.

BUT there is no option to have "USE *" on tdboutput so it is inserting my values in the wrong database now [e.g. db_name_2] - even if the connection is still on [db_name].

Any suggestions?

[gjeremy1617088143]

Hi, is your tdbOutput is relied to the good tDbConnection ? Maybe you'll have to delete the component and recreate a new one relied to your tDbConnection component. Could you send screenshots of your job?

[KVanselow]

I am providing a screenshot (in german). You see the connection open the db is used to do the update later on. But the table I specify in tDBConnection_1 is not updated through the tDBOutput_2 but rather a different table.

[gjeremy1617088143]

ok, is your tDBconnection_1 is correctly set, are you using shared db on your tDBConnection_1 ?

[gjeremy1617088143]

cause shared Db can overwrite settings of a tDBConnection.

[gjeremy1617088143]

"But the table I specify in tDBConnection_1 " :

you can only specify db on TdbConnection not tables, and in tDBOutput you can specify the table on table field, the db is automaticly recognized by the tDBConnection link.

[KVanselow]

Hey @guenneguez jeremy​ , thanks for all your valuable feedback!

Yes, the setup is a shared db.

I am confused, since the DBConnections lets me specify a database as well .. at least this is what I thought it would do ?!