May 13, 2004 at 8:34 am
Hi All,
I am tring to create a view over multiple DBs on the same server. If I create the view in each individual database I have no problem. If I try to use the CREATE VIEW statement in the following code to create a view in all the DBs with one query I get the error (sic) "cannot refer to another database with the create view statement".
/////////////////////////////
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS',
'NORTHWIND', 'TEMPDB',)
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
SELECT @sql = 'CREATE VIEW ' DBNAME+ '.dbo.vwNewView AS SELECT tbl.ID, tbl.CreationDate, tbl.CreatedBy, NotCurrentDB.dbo.tbl.ContactID FROM tblContacts LEFT OUTER JOIN NotCurrentDB.dbo.tbl ON tblContacts.ID = NotCurrentDB.dbo.tbl.ContactID'
PRINT @sql
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND
NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end
Like I said the view runs fine if I create it in each DB. Is a copy object going to work if this won't?
May 13, 2004 at 1:02 pm
According to the BOL:
You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
Refer to Books OnLine, use Index tab, enter Create View, double click Create View in the list, choose the option where Title = Creating a View and Location = Creating and Maintaining Databases.
-SQLBill
May 13, 2004 at 4:00 pm
As Bill stated it looks like the problem is with creating the view in a different database than your current database. So what you need to do is shell out to isql or osql using xp_cmdshell and create your view from the correct database context.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
May 17, 2004 at 7:11 am
If you are running this interactively, and not as a job, then use could use Query Analyzer to generate a script to create all of the views, then cut and paste the results (which is the script) into a new window and execute them.
For example:
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT = Min(DBID) from master.dbo.SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM master.dbo.SYSDATABASES WHERE DBID = @IDENT
SELECT @sql = 'USE ' + @DBNAME + Char(13)
+ 'GO ' + Char(13)
+ 'CREATE VIEW dbo.vwNewView AS SELECT tbl.ID, tbl.CreationDate, tbl.CreatedBy, NotCurrentDB.dbo.tbl.ContactID FROM tblContacts LEFT OUTER JOIN DataImport.dbo.tbl ON tblContacts.ID = DataImport.dbo.tbl.ContactID'
PRINT @sql
PRINT ''
-- EXEC (@SQL)
select @IDENT=min(DBID) from master.dbo.SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end
Mike
May 17, 2004 at 7:29 am
Actually, if you saved the example script as "createscript.sql", you could execute the following from a command prompt, or place the following statements into a Windows batch file. The batch file could then be run as a scheduled job, if necessary. (Of course, use fully qualified pathnames for the input (-i) and output (-o) files.
C:> isql -S YourServer -U username -P password -d YourDatabase -i "createscript.sql" -o "newscript.sql" -n
C:> isql -S YourServer -U username -P password -d YourDatabase -i "newscript.sql" -n
C:> del newscript.sql
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply