Creating and Dropping Rules - SQL Server Forums

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

Transact-SQL (2000)

Creating and Dropping Rules

Author

Topic

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2003-03-28 : 16:17:45

I have a create and drop script for some rules, and for some reason, in my create rule script, i get a syntax error.

Here is the drop script, which works fine:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VersionNumber]') and OBJECTPROPERTY(id, N'IsRule') = 1)
Drop Rule VersionNumber
GO

Here is the create scripts that generates the error:

 if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VersionNumber]') and OBJECTPROPERTY(id, N'IsRule') = 1)
CREATE RULE VersionNumber
	AS @col >= 1 AND @col <= 255
go

Is there some other way that I should be checking to see if the rule already exists? Or am I just missing somethat that is causing the error in my statements above?

Thanks in advance,
Scooter McFly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 16:27:15

Why are you checking to see if it exists twice? Just check if it exists for the drop, then create it.

I don't know why you are getting a syntax problem though because it looks fine to me. If the exact same code works with if exists then it should work with if not exists.

Tara

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2003-03-28 : 16:38:38

Yeah, you would think that just adding a NOT in that statement would work since the drop statement works, but, I can't seem to figure this one out. (And on a friday afternoon, I would rather be drinking right now)

Reason I need to check if it exists in the create script is incase someone has already run it and it already exists, then I don't want them to get any errors. (It saves me headaches later on, except this is giving me more of a headache right now)

Scooter McFly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 16:41:33

But these two scripts shouldn't be separated, they should be in one script so that the drop runs first then the create. If you do it this way, no one will get an error.

I wonder if you have encountered a bug and that's the reason why it is failing. You might want to play around with the select statement to determine what exactly is going on.

Tara

Edited by - tduggan on 03/28/2003 16:41:49

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2003-03-28 : 16:53:21

They are seperate so that if a client ever wants to un-install one component and not the others that go with the application, we can then call the Drop script that will destroy all of the objects associated with that component. Reverse logic for the Create, and that should explain the seperate scripts.

What is strange, is if you run this statement, it Prints an 'X' like it is supposed to

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VersionNumber]') and OBJECTPROPERTY(id, N'IsRule') = 1)
PRINT 'X'

Scooter McFly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 17:09:51

Maybe if you add BEGIN and END that it will work. Like this:

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VersionNumber]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
CREATE RULE VersionNumber
AS @col >= 1 AND @col <= 255
END
go

If it does work, I'm not sure why. Let me know.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 17:11:23

Also, it's fine if you want to have these separate for the reason that you indicate, but for the CREATE script you should also include the DROP statement, that's if my previous possible solution doesn't work out for you. So for your DROP script, do the drop. For your CREATE script, do the drop and the CREATE.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-29 : 08:53:04

This is a somewhat shorter syntax (say that 3 times fast) that does the existence check, and drops if needed:

IF object_id('VersionNumber') > 0 DROP RULE VersionNumber

You can also write the rule as:

CREATE RULE VersionNumber AS @col1 BETWEEN 1 AND 255

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources