Invalid Object name error

Question

Invalid Object name error

mar.ko

Default port

Points: 1451
More actions
November 6, 2015 at 9:32 am

#399290

We are running a stored proc from Pentaho's job scheduler on an hourly basis.
Strangely, about every 3 days, the job bombs on this error:
Execute SQL script.0 - Invalid object name '##TEMPTARG'.
That happens to be a global temp table.
I am trying to determine if this is a Pentaho bug or a SQL Server bug ?
Any and all comments appreciated !!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mar.ko (11/6/2015)
We are running a stored proc from Pentaho's job scheduler on an hourly basis.
Strangely, about every 3 days, the job bombs on this error:
Execute SQL script.0 - Invalid object name '##TEMPTARG'.
That happens to be a global temp table.
I am trying to determine if this is a Pentaho bug or a SQL Server bug ?
Any and all comments appreciated !!

I doubt this is a sql server bug. Whatever this job is doing it is looking for a global temp table and it doesn't exist. Sounds like an issue with the job or maybe Pentaho.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

global temp tables still get dropped when there are no more sessions at all, i believe.

from msdn i think, in my notes but not attributed:

a global temp table exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 3

Thanks for that Sean....that's been my inclinaton as well.

PDI in release 5.3 has been so "glitchy"....just tons of small issues.

The strange thing about this is that "prepareExecution" method in PDI is the one that failed, but the ##TEMPTARG was buried way down in the procedure so it wouldn't be referenced until the proc began running.

So this has the potential for being the case of an erroneous error message.

mar.ko Default port Points: 1451 More actions · Answer 4

Lowell - thanks much for that.

I think what is happening is that Pentaho is losing the connection to our SQL Server instance....

mar.ko Default port Points: 1451 More actions · Answer 5

Guys - I figured it out !!!

We had 3 separate databases ON THE SAME SQL SERVER INSTANCE !!!

Each database was trying to delete and then re-create that global temp table.

The error would randomly occur because the timing of the runs would sometimes occur at the same time.

Lesson learned: global temp tables can be dangerous !!