CREATE VIEW using Transact-SQL

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)

CREATE VIEW using Transact-SQL

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 11:20:40

How can I drop and recreate a view in a stored procedure?

The following code I'm trying is giving me error


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[viwLinkedServerFileOnSAC083]'))
 DROP VIEW [dbo].[viwLinkedServerFileOnSAC083]

CREATE VIEW dbo.viwLinkedServerFileOnSAC083
 SELECT *
 FROM  FileUploadLocationForLanghill...Sheet1$ AS Sheet1$_1
[WITH CHECK OPTION]

Error:
Msg 156, Level 15, State 1, Procedure uspRefreshDataFileView, Line 101
Incorrect syntax near the keyword 'VIEW'.
Msg 102, Level 15, State 1, Procedure uspRefreshDataFileView, Line 104
Incorrect syntax near 'WITH CHECK OPTION'.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 11:21:51

Only way is to use dynamic SQL. Better option is not to do it at all.

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 11:25:29

The reason why i want to do it is because the view is pointing to a Excel file using linked server and unless I do not alter the view and save it again after a new file format is placed then it does not read the new file format properly.
I can try dynamic sql if there is no other way?

quote:
Originally posted by robvolk

Only way is to use dynamic SQL. Better option is not to do it at all.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-02 : 11:25:43

Why not just use ALTER VIEW?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 11:27:58

Tried that too, it doesn't work


ALTER VIEW [dbo].[viwLinkedServerFileOnSAC083]
AS
SELECT *
FROM  FileUploadLocationForLanghill...Sheet1$ AS Sheet1$_1

Error:
Msg 156, Level 15, State 1, Procedure uspRefreshDataFileView, Line 105
Incorrect syntax near the keyword 'VIEW'.

quote:
Originally posted by darkdusky

Why not just use ALTER VIEW?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 11:49:44

Ok, I have done using dynamic SQL now.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources