Problem using a Temporary Table with a Shared Connection in a SubJob

[Anonymous]

I am creating a temporary table in MSSQL in the main job. I store records on it and I read them without a problem.
When I call a subjob for further processing I get an error message stating that the temporary table does not exist.
I am sharing a connection between both jobs and all the components are set to use the same connection. It seems that the connection is not being shared or for some other reason the temporary table is not available to the subjob.
Am I setting the connection sharing wrong? Is there something else that I am missing?
- Thanks

[Anonymous]

Hi
I have reproduced this issue and it works fine.
There are some possibilities causing "Invalid Object name" error.
No1: Run this job and check in SQL Server Management Studio whether this table has been created.
No2: If the table has been created, check whether its schema displayed in SQL Server Management Studio is the same with the schema on tMSSQLConnection in your child job.
For example, the DB owner's default schema is called "ABC". But you type in "dbo" in the schema textfield on tMSSQLConnection.
No3: Make sure the Table Name you type in tMSSQLInput in your child job is correct.
Regards,
Pedro

[Anonymous]

Hi Pedro,
I checked the names and they are OK. I ran MSSQL Profiler and the schema and the database are the same in both jobs. I did find, though, that when the subjob is ran there are additional commands sent to SQL Server (Set Isolation Level, Set Implicit Transactions, etc). Thus, I am not sure if the connection is being preserved between both jobs.
I created 2 jobs to test this. On the main job I call a select statement twice on the temporary table and on the subjob I call the same select again. You can see that the selects on the main job are one after the other in the Profiler, but there are additional commands before the select of the subjob.
I do not know if there is a way that I can set the implicit transactions off from within Talend, I tried setting an additional parameter in the connection: TransactionMode=explicit. Anyways, I do not know if that is an issue that I have to address.
In summary, the problem seems to be either how Talend handles shared db connections on MS SQL; the way my SQL Server is setup (I am using MS SQL Serve 2008 R2); or the way the JDBC connection is being set.
Thanks for your support,
Alberto

[Anonymous]

Hi Alberto
I have tried many ways to reproduce this issue, but failed.
Does anybody encounter it before?
Regards,
Pedro

[Anonymous]

Hi Pedro,
I found the error. It is a bug with Dynamic Jobs: http://www.talendforge.org/bugs/view.php?id=21108.
The explanation is on this post: http://www.talendforge.org/forum/viewtopic.php?pid=60019#p60019
Regrettably it is marked as low priority. It makes the Dynamic Jobs less attractive. Now, I will have to create a huge TMAP to route the jobs.
Thanks a lot for your help.
Regards,
Alberto

[Anonymous]

Hi Alberto
Thanks for your feedback. That would explain it.
This time I change my job by using dynamic job and an interesting thing happens.
After running the job, it will never stop and nothing appears on console.
Anyway, all things are caused by dynamic job feature.
Regards,
Pedro