Oct 182011
 

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.”

View_In_Another_DB_Fail

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:

Dynamic SQL Statement Fail

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.

View_Dynamic_Success

If you weren’t already aware of this, now you are. Hopefully it helps someone!

  24 Responses to “Creating Views in Another Database”

  1. Nice trick!

  2. 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.

  3. 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!

  4. 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.

  5. 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

  6. 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)

  7. […] 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 […]

  8. I’ve been so close, but you solved it, and much more.
    In your words…
    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.

    I’m a fan, and I get stuck on that kind of crap all the time. Thank you.

  9. Young man!!!

    Thank you very much for posting that example. Just like you, i had a similar
    problem.. Thank you once again.

    Michael.

  10. And almost 4 years to the day later, this saves my bacon. PRECISELY what I need to solve a problem this morning.

  11. Arggh. Hours of beating my head against the wall. This solves my problem of the day. Thank you!!! There are a few threads in places like stackoverflow that have responses that try to handle this issue but fail. Badly.

    Thanks again,
    Curt

  12. Thanks a lot; That was just as my problem too.

  13. I have been searching for hours for this solution. Thanks so much.
    JR

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)