SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 SQL 7 bug		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tim
Starting Member

Australia
392 Posts
Posted - 08/13/2002 :  02:09:14  Show Profile  Reply with Quote
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 & SQLTeam MVY

United Kingdom
846 Posts
Posted - 08/13/2002 :  05:59:44  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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

Australia
392 Posts
Posted - 08/13/2002 :  20:13:02  Show Profile  Reply with Quote
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

USA
15558 Posts
Posted - 08/13/2002 :  20:23:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000