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
Oracle SQL Order by in Subquery Problems!
How to Select The Record with The 2Nd Highest Salary in Database Oracle
Postgres: Select All Row with Count of a Field Greater Than 1
Counter_Cache Has_Many_Through SQL Optimisation, Reduce Number of SQL Queries
How to Perform a SQL 'Not In' Query Faster
Sql Server 2008 Database Engine Login Failed for Administrator User in Windows 7
Selecting The Row of Table Except The First One
Drop All Tables Sharing The Same Prefix in Postgres
Sql Server 2005:Charindex Starting from The End
Why Is Select Count(*) Slower Than Select * in Hive
Efficient Time Series Querying in Postgres
Does Introducing Foreign Keys to MySQL Reduce Performance
Rails - Distinct on After a Join
Count of Unique Values in a Rolling Date Range for R
Sql Query to Find Last Day of Current Month