March 3, 2010 at 1:40 pm
I having issue using script which create views in the batch. I am generating a views from the database based on all the tables and store those views on different database. I am using following code to generate views. It works when I run the script but when I use EXEC command inside the code I get error.. "Create view must be the first statement in the query batch"
DECLARE @BASEDB VARCHAR(50)
DECLARE @DESDB VARCHAR(50)
DECLARE @NAME varchar(50)
DECLARE @sql VARCHAR(2000)
SET @BASEDB = DB_NAME()
SET @DESDB = 'Foundation_SMF'
DECLARE VIEW_LIST CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
OPEN VIEW_LIST
FETCH NEXT FROM VIEW_LIST INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='USE '+@DESDB+' GO '
CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+' GO'
EXEC(@SQL)
--PRINT @sql
FETCH NEXT FROM VIEW_LIST INTO @NAME
END
CLOSE VIEW_LIST
DEALLOCATE VIEW_LIST
March 3, 2010 at 1:54 pm
Add a line break before your GO statements:
SET @sql ='USE '+@DESDB+'
GO '
CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+'
GO'
March 3, 2010 at 2:00 pm
I tried that but still getting an error..
March 3, 2010 at 2:16 pm
Please post the value of @sql and the error message.
March 3, 2010 at 2:55 pm
I added char(13) also but no success..
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
March 3, 2010 at 3:00 pm
balbirsinghsodhi (3/3/2010)
I added char(13) also but no success..Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
That's one out of two answers....
Still waiting for the other one.
March 3, 2010 at 3:04 pm
USE Foundation_SMF
GO
CREATE VIEW POP_PEERDEF_ALL_SERVICES AS SELECT * FROM Foundation_1002250720.DBO.POP_PEERDEF_ALL_SERVICES
GO
USE Foundation_SMF
GO
CREATE VIEW EPI_COSTS_CPT AS SELECT * FROM Foundation_1002250720.DBO.EPI_COSTS_CPT
GO
March 3, 2010 at 3:20 pm
I think I cannot use GO because varable which I am using will go out of scope.
March 3, 2010 at 3:22 pm
I tried another command EXEC SP_EXECUTESQL @sql and getting following error.
Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.
------------------------------------------
DECLARE @BASEDB VARCHAR(50)
DECLARE @DESDB VARCHAR(50)
DECLARE @NAME varchar(50)
DECLARE @sql NVARCHAR(2000)
SET @BASEDB = DB_NAME()
SET @DESDB = 'Foundation_SMF'
DECLARE VIEW_LIST CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
OPEN VIEW_LIST
FETCH NEXT FROM VIEW_LIST INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'CHECKING DATABASE ' + @BASEDB
SET @sql = N'USE ' + @DESDB + CHAR(13)
+ N'CREATE VIEW ' + @Name+ N' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME
EXEC sp_executesql @sql
PRINT CHAR(13)
--PRINT @sql
FETCH NEXT FROM VIEW_LIST INTO @NAME
END
CLOSE VIEW_LIST
DEALLOCATE VIEW_LIST
March 3, 2010 at 3:33 pm
the problem is the "use database" statment and the solution is to specify the database name as part of the view name.
Replace
SET @sql = N'USE ' + @DESDB + CHAR(13)
+ N'CREATE VIEW ' + @Name+ N' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME
With:
SET @sql = N'CREATE VIEW ' + @DESDB + '..' + @Name+ N' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME
SQL = Scarcely Qualifies as a Language
March 3, 2010 at 3:37 pm
Hi Carl...
I tried that too before. You cannot create view specify the name of the database as prefix
Here is the error.
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
March 3, 2010 at 3:52 pm
I figured out the issue. I think this is how we can do that.
EXEC Foundation_SMF..sp_executesql N'CREATE VIEW POP_PEERDEF_ALL_SERVICES AS SELECT * FROM Foundation_1002250720.DBO.POP_PEERDEF_ALL_SERVICES'
Thanks for you help.
March 3, 2010 at 4:21 pm
lmu92 (3/3/2010)
Add a line break before your GO statements:
SET @sql ='USE '+@DESDB+'GO '
CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+'
GO'
Did you even tried that code???
There are two simple ENTER after the GO command. Not CHAR(13). ENTER.
Your variable won't go out of scope since it's used to buid the string but not within the string.
March 3, 2010 at 4:24 pm
Yes, I tried that but did not work..
March 3, 2010 at 4:28 pm
How curious about object creation in another database ! I have done this for tables but never for views.
use master
go
CREATE VIEW tempdb.dbo.tables AS
SELECT * FROM msdb.INFORMATION_SCHEMA.TABLES;
Fails with error:
Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
Same error for 'CREATE PROCEDURE"
BUT
use master
go
CREATE table tempdb.dbo.foo (foo_id int )
go
CREATE index foo_x on tempdb.dbo.foo (foo_id )
go
Succeeds.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply