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.
| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2012-02-20 : 23:10:16
|
| how can i create a view in a database other than the one i'm in?i have a export script right now that select * into a table, then exports that file, zips it, and emails it. i want to performance tweak it to create a view instead, but the view needs to be in a specific db that the user will not be in...any ideas would be appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 14:04:26
|
you can just do likeCREATE VIEW viewnameASSELECT columns..FROM otherdb.schema.table so long as both dbs are in same server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-02-22 : 00:06:35
|
my issues is this, i'm in the MAIN db. and i need to create a view in the CLIENT db, but w/o switching db context. basically i wanna do it inside a proc, from a different db.)almost like:use maingodeclare @sql varchar(max)declare @myview varchar(50) = 'myview'set @sql = 'create view client.dbo.' + @myview + ' as select * from main..mytable'exec (@sql) issue is, mssql will not allow you to create a view outside of the current db context.know what i mean? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-22 : 00:10:26
|
| why do you need to create a view inside procedure?Between you can change dbcontext inside a dynamic sql call by the way------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
create a view in a different database