Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 SQL 7 bug

Author  Topic 

Tim
Starting Member

392 Posts

Posted - 2002-08-13 : 02:09:14
Here's an obscure bug I ran into if anyone is interested:


SELECT * FROM sysobjects
WHERE OBJECTPROPERTY(id,N'IsUserTable') = 1


Will return tables created when merge replication is configured on a SQL Server 7 database, even though in enterprise manager they are clearly shown as system tables.

Have to code it like this:


SELECT * FROM sysobjects
WHERE OBJECTPROPERTY(id,N'IsUserTable') = 1
and OBJECTPROPERTY(id, N'IsMSShipped') <> 1


Even though the tables don't "Ship" with SQL Server... they are not there at all unless you configre merge replication.






----
Nancy Davolio: Best looking chick at Northwind 1992-2000

<edit> to fix display </edit>

Edited by - robvolk on 08/13/2002 20:17:25

jasper_smith
SQL Server MVP &amp; SQLTeam MVY

846 Posts

Posted - 2002-08-13 : 05:59:44
It also includes dt_properties which is not really a user table either. It has always been prudent to check IsMSShipped property
because the xtype in sysobjects is not reliable. Also we shouldn't really be querying sysobjects when we have INFORMATION_SCHEMA views

 
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMsShipped')=0




HTH
Jasper Smith
Go to Top of Page

Tim
Starting Member

392 Posts

Posted - 2002-08-13 : 20:13:02
quote:
Also we shouldn't really be querying sysobjects when we have INFORMATION_SCHEMA views


So where can I retrieve similar information for stored procedures and triggers without querying system tables ??


SQL 7 does not always create the same objects at the subscriber as exist at the publisher when initialising a subscription using merge replication. To be sure everything came across I am scripting drop/creation of constraints, stored procs and triggers and executing after intialised.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-13 : 20:23:32
quote:
So where can I retrieve similar information for stored procedures and triggers without querying system tables ??
sp_help, sp_helptrigger, sp_helpconstraint and there are several other sp_help... procedures regarding replication info.

Go to Top of Page
   

- Advertisement -