September 10, 2010 at 1:31 pm
1 | DECLARE @DBNAME VARCHAR(50)
2 |
3 | SELECT @DBNAME=DB_NAME()
4 |
5 | print @DBNAME
6 |
7 | if(@DBNAME='mydb1')
8 | begin
9 | create view view as select * from [instance1].database1.dbo.table with(nolock)
10| end
11| if(@DBNAME='mydb2')
12| begin
13| create view view as select * from [instance2].database2.dbo.table with(nolock)
14| end
generates an error on running:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'view'.
September 10, 2010 at 1:42 pm
Try this (using dynamic sql..feel free to modify the values to your needs of course)
DECLARE @DBNAME VARCHAR(50)
DECLARE @SQL nvarchar(250)
SELECT @DBNAME=DB_NAME()
print @DBNAME
if(@DBNAME='master')
begin
SET @SQL = 'create view [view] as select * from dbo.sysprocesses with(nolock)'
EXEC(@SQL)
end
if(@DBNAME='msdb')
begin
SET @SQL = 'create view [view] as select * from dbo.sysjobs with(nolock)'
EXEC(@SQL)
end
September 10, 2010 at 1:44 pm
Dynamic sql works, just curious why pos (plain old sql) doesnt
September 10, 2010 at 1:52 pm
Probably the same reason it wouldn't work if you tried to create a SP that way. It needs to be the first line in the batch (i.e. need a GO before it). Can't find any mention of a View needing to be created this way, but that's what it looks like.
September 10, 2010 at 1:55 pm
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://qa.sqlservercentral.com/Forums/Topic984072-149-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2010 at 1:58 pm
ya, sorry bout that. its posted in the proper area now.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply