Checking sysobjects for temp table

How can I check for the existence of a temp table before I attempt to drop
it?
Something like:
IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
DROP TABLE #temp
#temp exists but I cannot find a record for it in sysobjects. Neither can I
find it in tempdb (tempdb.dbo.sysobjects ).
Can anyone recommend how to handle this situation?Try:
if object_id('tempdb..#tmp') is not null
drop table #tmp
--
-Vishal
"David Frick" <dave@.frickcpa.com> wrote in message
news:uIYX89TTDHA.1992@.TK2MSFTNGP12.phx.gbl...
> How can I check for the existence of a temp table before I attempt to drop
> it?
> Something like:
> IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
> DROP TABLE #temp
> #temp exists but I cannot find a record for it in sysobjects. Neither can
I
> find it in tempdb (tempdb.dbo.sysobjects ).
> Can anyone recommend how to handle this situation?
>

CHECKDB and Object ID 0

Can someone please tell me what "Object ID 0" is? It does not exist in
sysobjects and I can find a reference in BOL or in the groups/forums...
I am trying to anlyze the first line returned from DBCC CHECKDB
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
the page header = (0:120).
Thanks!Hi Dave
Error 8909 is documented in Books Online which advises to check for hardware
problems and then restore from a good back.
John
"Dave" wrote:
> Can someone please tell me what "Object ID 0" is? It does not exist in
> sysobjects and I can find a reference in BOL or in the groups/forums...
> I am trying to anlyze the first line returned from DBCC CHECKDB
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> the page header = (0:120).
> Thanks!
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Hi Dave
I believe it is corrupted data.
John
"Dave" wrote:
> Yes, I saw that. We had a hard drive fail in a Raid 5 array.
> I am planning on doing the restore today, but I would still like to
> know what object "ojbect id 0" is. Any ideas?
>
> John Bell wrote:
> > Hi Dave
> >
> > Error 8909 is documented in Books Online which advises to check for hardware
> > problems and then restore from a good back.
> >
> > John
> >
> > "Dave" wrote:
> >
> > > Can someone please tell me what "Object ID 0" is? It does not exist in
> > > sysobjects and I can find a reference in BOL or in the groups/forums...
> > >
> > > I am trying to anlyze the first line returned from DBCC CHECKDB
> > >
> > > Server: Msg 8909, Level 16, State 1, Line 1
> > > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > > the page header = (0:120).
> > >
> > > Thanks!
> > >
> > >
>

Check Sysobjects Error during Snapshot

During the snapshot generation, I get this eror while the snapshot agent is
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:

> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:

> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>