Create View Using Linked Server Db in SQL Server

Create View using Linked Server db in SQL Server

You need to use the four part qualified name: linkedserver.database.schema.table

SELECT * FROM [1.2.3.4].Northwind.dbo.Customers

Here is an MSDN article about accessing object names.

You might want to try manually creating the view, rather than using the SQL Management tools:

CREATE VIEW [dbo].[sywx]
AS
    SELECT  *
    FROM    [1.2.3.4].Atia.dbo.IpPbxDCR
GO

I also recommend that you use a name, if possible, for the linked server rather than using the IP address.

Create a MS SQL View linking to a table in different host database

You should create a linked Server in your Server in which DatabaseA exists.

You can google how to create Linked Server in SQL Server

EXEC sp_addlinkedserver   
   @server='ServerNameOfDatabaseB', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='NCSUSPRODSQL02'

Once you create linked server then your view code would be something like the below.

CREATE VIEW Viewname
AS
SELECT *
FROM LinkedServerName.DatabaseB.SchemaName.TableName

How to create table/view with data from another SQL server?

You could create the linked server, if the server of third party vendor is SQL Server, follow the following steps. If the server of third party vendor is not SQL Server, you need to choose other data source, and sometimes need to install the OLE DB driver.

First, enter the connection path of the third party vendor.

Second, enter the account and password.

Third, create the view.

The query format will be

SELECT Column1, Column2 ...
FROM [server path].[db name].[dbo].[table name]

Reference: How to create and configure a linked server in SQL Server Management Studio

SQL Server how to create a view in a specific database on a remote server

To create a view in a specific database at a remote server, try something like this:

EXECUTE ('USE YourDatabase EXEC(''CREATE VIEW YourView AS SELECT 1 AS SomeColumn'')') AT YourRemoteServer

Copy all views from Linked Server into new database

One possible solution using your initial idea of writing the individual queries would be to get SQL to write the queries for you. Run something like

SELECT 'select * into ' + name + ' FROM linkedserver.database.schema.' + name
FROM linkedserver.database.sys.views

Copy the results, paste into editor, and run.

You can modify the query as needed. I was just showing the basic idea of using SQL to produce the query for you.

How to create views based on tables from different server?

Without knowing details, I'm not sure this is the best idea - but this would work for you. It requires four part naming and linked servers.

Here is the syntax for the view.

Create  VIEW [dbo].[vw_CrossServer]
AS

    SELECT * 
    FROM Server1.DatabaseName.Schema.tableA TA
        INNER JOIN Server2.DatabaseName.Schema.tableB TB ON TA.col = TB.col

GO

For this to work, you'll need to setup a linked server between the databases. Linked Server

Link also contains examples and other resources.

Can I create view in my database server from another database server

Yes, you can. First, you need to link to the other server, using something like sp_addlinkedserver.

Then you can access the data using 4-part naming. Here is an example:

create view v_server1_master_tables as
    select *
    from server1.master.information_schema.tables;

---

Related Topics