none
create view on linked server RRS feed

  • Question

  • I'm trying to create a view on a remote server with data from a local one. After doing some research, I found the "execute at". which I thought might be the answer. I run this from my local server but it keeps telling me that dbo.xtra_paycodes is an invalid object. How can I make this work or is there a better way?

    declare @CreateView nvarchar(1000);
    declare @myTable varchar(50);
    Set @myTable = '[dbo].[xtra_PayCodes]'
    set @CreateView = 'Create view NewView as select * From ' + @myTable


    EXEC (@CreateView) AT [MAIN.DATABASE.WINDOWS.NET];

    Thursday, August 15, 2019 1:31 PM

All replies

  • A linked Server Points to a SQL Server, not to a specific database on that server. By Default a Connection is made to the "master" System database and I guess there is no table named [dbo].[xtra_PayCodes] in "master" database.

    Why don't you connect directly with SSMS to that SQL Server to create the view?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 15, 2019 1:39 PM
    Moderator
  • I'm running this from a query window on my local server. when I type select * from dbo.xtra_PayCodes in the same window I get results. the remote server is [MAIN.DATABASE.WINDOWS.NET] where I want to create the view. When I include the line set @CreateView = 'Create view NewView as select * From ' + @myTable is when I get the error about my local table. What is the best way to create a view on the linked server with data from my local table I guess is the question. Thanks,
    Thursday, August 15, 2019 1:54 PM
  • Then create a linked server on the server [MAIN.DATABASE.WINDOWS.NET] pointing to your local server, then you can use the linked server in the view in server [MAIN.DATABASE.WINDOWS.NET].

    Really, your approach is not the best, connect directly to the that server to create objects.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 15, 2019 2:52 PM
    Moderator
  • Thanks Olaf,

    But if I connect to the remote azure database and create the object from there, how can I reference my local db table to pull the data? I don't think azure will let me use 4 part names like myserver.datawarehouse.dbo.xtra_PayCodes. So how would I create the view using a table from my other db?

    Thursday, August 15, 2019 2:57 PM
  • Linked server are not supported in SQL Azure databases, copy the table incl. data from your local on-premise SQL Server to SQL Azure.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 15, 2019 3:23 PM
    Moderator
  • I wish I could. the Azure db is vendor provided and we only have read access to it. I'm guessing my only hope is the elastic database query solution offered by Azure which isn't a solution as much as a jerry-rigged work around at best as far as I can tell. At least I only have one table, that method would be a nightmare if I had 20 or 30 tables to create external table definitions for.

    Thanks again Olaf

    Thursday, August 15, 2019 3:33 PM
  • we only have read access to it.

    The you can't create a view at all. You better rethink & redesign your Approach (whereby I don't exactly know what you are trying to archieve).

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 16, 2019 5:57 AM
    Moderator
  • Hi FLSojourner,

    As Olaf mentioned,  Azure SQL database doesn't support create linked.
     
    And you only have read access to Azure database, how could you create a view for the database? It seems that you need to describe your requirement more clearly. 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 7:53 AM
  • Hi Cathy,

    I only have read access to the tables. Views, procs etc will persist. That's why I'm curious as to how to create a view on the target azure db from my source sql 2012 db if possible. 

    Friday, August 16, 2019 2:42 PM
  • how to create a view on the target azure db

    By connecting to the SQL Azure database directly and run the CREATE VIEW on it, but from that view on Azure you can access your on-premise SQL Server only via on-premise data gateway.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 16, 2019 3:42 PM
    Moderator