DROP or CREATE script won't parse

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 2005 Forums

Transact-SQL (2005)

DROP or CREATE script won't parse

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-23 : 18:48:47

I'm trying to make a script that will recreate a table whether it exists already or not. I've figured how to do it if it is not around yet, and delete it if it has. My code so far:

BEGIN TRANSACTION
IF EXISTS (select * from INFO.dbo.vwAttr1) 
BEGIN DROP VIEW dbo.vwAttr1 END
ELSE BEGIN
CREATE VIEW dbo.vwAttr1 AS 
[normal code]
END
COMMIT TRANSACTION
GO

Now, I know that I'd have to write the script twice to actually recreate a table, but in the meanwhile, I get the following error message, but only when the "create view as" clause is in:

[/code]Msg 156, Level 15, State 1, Line 4: Incorrect syntax near the keyword 'view'.[/code]

How would I fix this? Also, could I just drop the else clause to make the second part unconditional?

Thanks,
Arithmomaniac

Ignorance is bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-23 : 18:57:43

Where's the error message?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 18:58:18

Read about the GO operator in Books Online.
Also, why are you so eager to create views "on the fly"?

Peter Larsson
Helsingborg, Sweden

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-23 : 19:06:40

Oops... Error is message 156 by the word 'view'. The post also finished with two questions:
1)How can I get this to work?
2) If it did/will work, and I just dropped the 'else begin' and 'end' clauses, would it still work?

I was asked by my employer to make one large script out of many small ones I've made, some with help here. I think he wants to regularly rebuild the views...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 19:11:50

I am guessing What you intend to do is:


IF EXISTS( SELECT * FROM sysobjects WHERE Type = 'V' AND NAme = 'vwAttr1')
   DROP VIEW dbo.vwAttr1 

Go

CREATE VIEW dbo.vwAttr1 
AS 
[normal code]


Go

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 19:13:18

quote:
Originally posted by Arithmomaniac

2) If it did/will work, and I just dropped the 'else begin' and 'end' clauses, would it still work?

Then you will be left with only the DROP view. But then again your condition to check for existence of view is incorrect.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-23 : 19:20:07

OK...but two things:

1) The transactions don't work now. Is the GO overriding them, since you can't roll it back once it's sent to the batch?
2) Why didn't the 'create view' work in the 'else' statement? (not that I need to know anymore)

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-23 : 19:22:04

By the way, the code did work. (I forgot to mention.) THanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources