Creating view on remote database

  • Hi all,

    I am trying to automate a process by creating or Altering a view dynamically using a stored procedure.

    1. I tried creating a view in the following manner

    Declare @sql Varchar(4000)

    SET @sql = ' Create View dbname.dbo.Viewname As

    Select * from tablename'

    EXEC ExecuteSql @sql

    It is not allowing me to create the View in the remote database.

    2. Then I tried the other way

    Declare @sql Varchar(4000)

    SET @sql = 'Use Dbname Go Create View dbo.Viewname As

    Select * from tablename'

    EXEC ExecuteSql @sql

    It is not working either. I have to use the dynamic SQL as I am using Distributed Database .

    I am using SQLServer 2000. I am using now osql utility to accomplish the task. Is there any other workaround for this issue.

    Any help will be really appreciated.

  • Create the stored procedure on the remote server and call it from your local server.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • Dude,

    Right now I have that method only. Is there anyother way we can accomplish that task. The reason is even I create the database dynamically in the procedure (as part of a automation process). It is not allowing me to create the procedure either in the remote database. Right now I have a generic procedure in Model database, so that it gets created when ever new Db is created. The only limitation here is I am limited to width of the SQL string.

    Please advise if you have any other solutions. Thanks for you help ......

  • Try using xp_cmdshell and osql to log into the remote db with your sql command.

    Gary Johnson

    DBA

    Sr. DB Engineer




    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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply