Error 208

Hi,
every morning about 06.03 AM in DB PROBA there is the follow error:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 6/1/2006
Time: 6:03:22 AM
User: N/A
Description:
name database: PROVA
Error: 208, Severity: 16, State: 0
Invalid object name '#tmp'.
Someone can help me to understand the reason of the error...
Thanks a lotLuca (artematico@.yahoo.it) writes:
> every morning about 06.03 AM in DB PROBA there is the follow error:
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 6/1/2006
> Time: 6:03:22 AM
> User: N/A
> Description:
> name database: PROVA
> Error: 208, Severity: 16, State: 0
> Invalid object name '#tmp'.
> Someone can help me to understand the reason of the error...
I would check for scheduled jobs running at this time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I assume that this is a scheduled job
The job is looking for a temporary table #tmp, this object does not
exists, it either got dropped, is out of scope or got never created
Open up QA and run this
select * from #tmp
you will see this error message
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tmp'.
Take a look at the code and try to figure out if it's the object is
misspelled by mistake #tmp instead of #temp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Luca wrote:
> Hi,
> every morning about 06.03 AM in DB PROBA there is the follow error:
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 6/1/2006
> Time: 6:03:22 AM
> User: N/A
> Description:
> name database: PROVA
> Error: 208, Severity: 16, State: 0
> Invalid object name '#tmp'.
> Someone can help me to understand the reason of the error...
> Thanks a lot|||SQL Menace ha scritto:

> I assume that this is a scheduled job
> The job is looking for a temporary table #tmp, this object does not
> exists, it either got dropped, is out of scope or got never created
> Open up QA and run this
> select * from #tmp
> you will see this error message
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#tmp'.
> Take a look at the code and try to figure out if it's the object is
> misspelled by mistake #tmp instead of #temp
I run this
select * from '#tmp
but there is the same error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Luca wrote:|||Yes you will get that error since the temp table does not exist for
your connection
Whatever code runs at that time, open it up and look where #tmp is
referenced
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Luca wrote:
> SQL Menace ha scritto:
>
> I run this
> select * from '#tmp
> but there is the same error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#temp'.
>|||SQL Menace ha scritto:

> Yes you will get that error since the temp table does not exist for
> your connection
> Whatever code runs at that time, open it up and look where #tmp is
> referenced
The code runs at 06.00 AM, how can I look where #tmp is referenced?
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Luca wrote:|||Enterprise Manager-->management-->SQL server Agent-->Jobs
You will see a bunch of icons in blue (succes) or Red (failed)
One of the red ones is the one that uses #tmp (you can also scroll to
the right and look at the next run date column, that will have the date
and time there)
The double click on the job, click on the steps tab and double click
step1 (it will be blue)
If the code looks like this EXECUTE master.dbo.xp_sqlmaint N'-PlanID
2C6BA936-F50A-42F9-AFA5-B88BC0251AF3 -Rpt "d:\MSSQL\LOG\DB Maintenance
Plan10.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10
-RmUnusedSpace 50 10 ' then it is s maintainance plan and maybe you ran
out of space on your hard disk and the object could not be created
Delete stuff you don't need or move some tables to another
filegroup/hard drive (if you have multiple drives)
Or put tempdb on it's own hardrive and the LOG files also on their own
hard drive
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Luca wrote:
> SQL Menace ha scritto:
>
> The code runs at 06.00 AM, how can I look where #tmp is referenced?
>|||SQL Menace ha scritto:

> Enterprise Manager-->management-->SQL server Agent-->Jobs
> You will see a bunch of icons in blue (succes) or Red (failed)
> One of the red ones is the one that uses #tmp (you can also scroll to
> the right and look at the next run date column, that will have the date
> and time there)
There isn't any red icons, they are all blu
> The double click on the job, click on the steps tab and double click
> step1 (it will be blue)
> If the code looks like this EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 2C6BA936-F50A-42F9-AFA5-B88BC0251AF3 -Rpt "d:\MSSQL\LOG\DB Maintenance
> Plan10.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10
> -RmUnusedSpace 50 10 ' then it is s maintainance plan and maybe you ran
> out of space on your hard disk and the object could not be created
> Delete stuff you don't need or move some tables to another
> filegroup/hard drive (if you have multiple drives)
> Or put tempdb on it's own hardrive and the LOG files also on their own
> hard drive
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Luca wrote:|||Luca (artematico@.yahoo.it) writes:
> There isn't any red icons, they are all blu
You may have to inspect the job history. The job could be set up so
that it continues with the next step even on error. Or it runs a second
time, and this time it's successful.
In any case, as long everything else appears to be working normally on
your sever, it may not be worth the effort to track this down.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx