Create view in SQL using VBscript need Help

[colby]

I am trying to create a view on the server on the fly
I use the following on one line to test if the view exists before creating it

Connection.Execute ("if exists (select * from sysobjects where id = object_id(N'[dbo].[myview]') and OBJECTPROPERTY(id, N'IsView') = 1)
create View dbo.myview AS SELECT cp_id, container_id, cp_value FROM container WHERE (cp_id = 666)&quot

all I get Back is

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'View'.


/colby/newfund.asp, line 35

I have written it to SQL Query analyzer
and get the same messge.

I Know Create view must be the first statement in a query batch. But all I want is to check first.

Can anyone help?
Thanks

 

[jitter]

Are you using the Go keyword between batches?

 

[bassguy]

HI, I would suggest putting it into a stored procedure then calling it from your asp, just a thought

tal McMahon

 

[colby]

Thanks very much for the help but I came accross the answer by accident I got the script generator in server enterprise manager to script the view.
I now send it in two lines by using the drop view at the end of the first line.