Have you ever had one of those times where you spent hours trying different ways to make something work the way you envisioned, only to find that the solution was staring you in the face all along? I have those more often than I’d like to admit – and while the title of this post seems like a DBA 101 topic, the answer isn’t really all that simple. To hopefully help others (and perhaps my future self) I’ll document a recent one here. I’m sure there will be plenty more in the future as well!
The story goes that I was working on a stored procedure to generate views in other databases via dynamic sql, and the hangup was that you can’t create a view in another database – at least not easily. I realize that views can reference tables in other databases with no problem at all, but due to security restrictions and other issues outside the scope of this post, these views needed to exist in specific databases.
Creating a table in another database is a piece of cake because you can specify the database name in the CREATE TABLE statement:
CREATE TABLE databaseName.schemaName.tableName (...
Creating a view, however, is not so easy. You can’t specify a database name in the CREATE VIEW statement – doing so will get you a hot date with error 166. The only remark in Books Online is that “A view can be created only in the current database.”
Since these views were being generated with dynamic sql, my next thought was to issue a USE [OtherDatabase] statement before the CREATE VIEW and just pass all of that into sp_executesql, the idea being that USE will change the context of execution to the desired destination database and all will be well. Unfortunately things didn’t go according to plan:
This yields 3 errors – two for “Incorrect syntax near ‘GO’”, and another that “CREATE VIEW must be the first statement in a query batch.” If GO signals the end of a batch, then removing the GOs makes it all one batch, right? Not quite. Losing the GOs will still give you the error 111, because CREATE VIEW must come first.
So how can we do this? After much experimentation I figured out that you can specify the database sp_executesql runs on by prepending a database and schema name to it just like other objects – something so simple that I thought right past it. It’s kind of crazy to think that in all my years working with SQL Server I’ve never had a need to do it with dynamic sql until now, but that’s the case.
If you weren’t already aware of this, now you are. Hopefully it helps someone!
Nice trick!
Thanks! Hope it comes in handy sometime!
I’ve never heard of that. That is really nice to know. I’m planning to attend your backup/restore presentation in KC in a couple of weeks.
Glad you found it interesting, Dan. I can’t wait for KC – please be sure to introduce yourself!
Good!!
But I don’t think in real life you have to create view in this way.
Why we are not things as simple as we can. KISS is basic of all programming languages.
You might be right Sanjay, but if you can find a better way to create a view in one database from the context of another, I’d be most interested to see it!
Another similar way would be to create a stored procedure in the database DB1 which takes in a SQL command as an input parameter and executes it using dynamic sql.
Now execute this stored procedure from database DB2 and pass the command to create the view as the input SQL string parameter. The stored procedure would run on DB1 creating the view on that database.
– Create proc on database DB1
create procedure dbo.CreateView_DB1
@pSQL nvarchar(255)
as
begin
exec sp_executesql @pSql
end
go
– Execute the above stored proc from DB2.
exec DB1.dbo.CreateView_DB1 ‘create view vw_DB1 as select 1 as n’
You can use the above statement in a while loop with the SQL statement parameters being picked from a table. Hope that helps.
Hi Arvinder,
Yes that could work, but I would be *VERY* concerned about security, as that procedure will pretty much let anyone with access to it execute any code they want. It’s a giant security risk, so I couldn’t recommend it.
I was just showing how it could be done using a stored procedure on another database. But if you are so concerned about security, then you could hard code the “Create View…” and “select ” clauses of the statement in the stored procedure dbo.CreateView_DB1 and just pass the view name, the column list and the selection criteria as parameters. That should restrict the procedure to create only views whose definitions are controlled by the calling method on DB2. If you need to have views created dynamically on another DB, then you need to do this much. Unless you have the view definitions hard coded, in which case it wouldn’t remain dynamic then.
Cheers
Arvinder
Hey, thanks much!
I needed one more piece of flexibility; that of having the database itself be a variable. I used your thoughts to get this:
(@sql is my ready-to-run variable with the create view code)
set @sql = 'create view abc as select 1 as N'set @sql = 'exec ' + @DB + '..sp_executesql N''' + @sql + ''''
exec(@sql)
Hi Kurtis –
Yes it gets kinda hairy if you want the database to be variable, but weaving a web of nested dynamic statements is the best (and only) way to do it that I’ve found so far. Thanks for sharing!
[...] tables and generates a view that combines their contents using UNION ALL. I’ve actually blogged about an aspect of this before, but today I have a function containing a nice little trick [...]