Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
k006b
Starting Member
10 Posts |
Posted - 2005-04-27 : 15:10:13
|
Dear All,I have 2 databases say A1 & B1 on two different servers. I want to append new records from xyz table in B1 from A1. structure of xyz table are same on both databases.Is it possible to update using sql query ??Would appreciate your valuable HELP.rgds - K006B |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-27 : 15:18:24
|
Yes, you can select data from another server if you have a LINKED SERVER setup. You can also use OPENROWSET to get data from a database on another server.You can read about them in SQL Server Books Online.CODO ERGO SUM |
|
|
k006b
Starting Member
10 Posts |
Posted - 2005-04-27 : 15:25:58
|
Dear Jones,Thanks for ur quick response.I am a beginner to SQL, Can you please give me an example???thnx K006b |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-27 : 15:41:18
|
This smells like homework.JimUsers <> Logic |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-27 : 15:44:30
|
quote: Originally posted by k006b...I am a beginner to SQL, Can you please give me an example???...
This is a fairly involved subject, so you should read about them in SQL Server Books Online.CODO ERGO SUM |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-27 : 18:08:37
|
From Books Online:(sorry Microsoft)--Linked Server Info:quote: Transact-SQL Reference sp_addlinkedserverCreates a linked server, which allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server with sp_addlinkedserver, this server can then execute distributed queries. If the linked server is defined as Microsoft® SQL Server™, remote stored procedures can be executed.Syntaxsp_addlinkedserver [ @server = ] 'server' [ , [ @srvproduct = ] 'product_name' ] [ , [ @provider = ] 'provider_name' ] [ , [ @datasrc = ] 'data_source' ] [ , [ @location = ] 'location' ] [ , [ @provstr = ] 'provider_string' ] [ , [ @catalog = ] 'catalog' ] Arguments[ @server = ] 'server'Is the local name of the linked server to create. server is sysname, with no default.With multiple instances of SQL Server, server may be servername\instancename. The linked server then may be referenced as the data source for SELECT *FROM [servername\instancename.]pubs.dbo.authors. If data_source is not specified, server is the actual name of the instance.[ @srvproduct = ] 'product_name'Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not need to be specified.[ @provider = ] 'provider_name'Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL. The OLE DB provider is expected to be registered with the given PROGID in the registry.[ @datasrc = ] 'data_source'Is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000), with a default of NULL. data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider. When the linked server is created against the SQL Server OLE DB provider, data_source can be specified in the form of servername\instancename, which can be used to connect to a specific instance of SQL Server running on the specified computer. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific SQL Server instance to which the user will be connected.[ @location = ] 'location'Is the location of the database as interpreted by the OLE DB provider. location is nvarchar(4000), with a default of NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.[ @provstr = ] 'provider_string'Is the OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar(4000), with a default of NULL. provstr is passed as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.When the linked server is created against the SQL Server OLE DB provider, the instance can be specified using the SERVER keyword as SERVER=servername\instancename to specify a specific instance of SQL Server. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific SQL Server instance to which the user will be connected.[ @catalog = ] 'catalog'Is the catalog to be used when making a connection to the OLE DB provider. catalog is sysname, with a default of NULL. catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider.Return Code Values0 (success) or 1 (failure)Result Setssp_addlinkedserver returns this message if no parameters are specified:Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.sp_addlinkedserver used with the appropriate OLE DB provider and parameters returns this message: Server added.RemarksThe following table shows the ways that a linked server can be set up for data sources accessible through OLE DB. A linked server can be set up using more than one way for a given data source; there may be more than one row for a data source type. This table also shows the sp_addlinkedserver parameter values to be used for setting up the linked server.Remote OLE DB data source OLE DB provider product_name provider_name data_source location provider_string catalog SQL Server Microsoft OLE DB Provider for SQL Server SQL Server (1) (default) - - - - - SQL Server Microsoft OLE DB Provider for SQL Server SQL Server SQLOLEDB Network name of SQL Server (for default instance) - - Database name (optional) SQL Server Microsoft OLE DB Provider for SQL Server - SQLOLEDB Servername\instancename (for specific instance) - - Database name (optional) Oracle Microsoft OLE DB Provider for Oracle Any (2) MSDAORA SQL*Net alias for Oracle database - - - Access/Jet Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Full path name of Jet database file - - - ODBC data source Microsoft OLE DB Provider for ODBC Any MSDASQL System DSN of ODBC data source - - - ODBC data source Microsoft OLE DB Provider for ODBC Any MSDASQL - - ODBC connection string - File system Microsoft OLE DB Provider for Indexing Service Any MSIDXS Indexing Service catalog name - - - Microsoft Excel Spreadsheet Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Full path name of Excel file - Excel 5.0 - IBM DB2 Database Microsoft OLE DB Provider for DB2 Any DB2OLEDB - - See Microsoft OLE DB Provider for DB2 documentation. Catalog name of DB2 database (1 ) This way of setting up a linked server forces the name of the linked server to be the same as the network name of the remote SQL Server. Use server to specify the server.(2 ) "Any" indicates that the product name can be anything.The data_source, location, provider_string, and catalog parameters identify the database(s) the linked server points to. If any of these parameters are NULL, the corresponding OLE DB initialization property is not set.Note To use the Microsoft OLE DB Provider for SQL Server 2000 in SQL Server version 6.x, run the \Microsoft SQL Server\MSSQL\Install\Instcat.sql script against the version 6.x SQL Server. This script is essential for running distributed queries against an SQL Server 6.x server.In a clustered environment, when specifying file names to point to OLE DB data sources, use the universal naming convention name (UNC) or a shared drive to specify the locationPermissionsExecute permissions default to members of the sysadmin and setupadmin fixed server roles.ExamplesA. Use the Microsoft OLE DB Provider for SQL ServerCreating a linked server using OLE DB for SQL Server This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.USE masterGOEXEC sp_addlinkedserver 'SEATTLESales', N'SQL Server'GOCreating a linked server on an instance of SQL Server This example creates a linked server S1_instance1 on an instance of SQL Server, using the OLE DB Provider for SQL Server.EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct='', @provider='SQLOLEDB', @datasrc='S1\instance1'B. Use the Microsoft OLE DB Provider for JetThis example creates a linked server named SEATTLE Mktg.Note This example assumes that both Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.USE masterGO-- To use named parameters:EXEC sp_addlinkedserver @server = 'SEATTLE Mktg', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'GO-- OR to use no named parameters:USE masterGOEXEC sp_addlinkedserver 'SEATTLE Mktg', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\MSOffice\Access\Samples\Northwind.mdb'GOC. Use the Microsoft OLE DB Provider for OracleThis example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.USE masterGO-- To use named parameters:EXEC sp_addlinkedserver @server = 'LONDON Mktg', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'MyServer'GO-- OR to use no named parameters:USE masterGOEXEC sp_addlinkedserver 'LONDON Mktg', 'Oracle', 'MSDAORA', 'MyServer'GOD. Use the Microsoft OLE DB Provider for ODBC with the data_source parameterThis example creates a linked server named SEATTLE Payroll that uses the Microsoft OLE DB Provider for ODBC and the data_source parameter.Note The specified ODBC data source name must be defined as System DSN in the server before executing sp_addlinkedserver.USE masterGO-- To use named parameters:EXEC sp_addlinkedserver @server = 'SEATTLE Payroll', @provider = 'MSDASQL', @datasrc = 'LocalServer'GO-- OR to use no named parameters:USE masterGOEXEC sp_addlinkedserver 'SEATTLE Payroll', '', 'MSDASQL', 'LocalServer'GOE. Use the Microsoft OLE DB Provider for ODBC with the provider_string parameterThis example creates a linked server named LONDON Payroll that uses the Microsoft OLE DB Provider for ODBC and the provider_string parameter.Note For more information about ODBC connect strings, see SQLDriverConnect and How to allocate handles and connect to SQL Server (ODBC). USE masterGO-- To use named parameters:EXEC sp_addlinkedserver @server = 'LONDON Payroll', @provider = 'MSDASQL', @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'GO-- OR to use no named parameters:USE masterGOEXEC sp_addlinkedserver 'LONDON Payroll', '', 'MSDASQL', NULL, NULL, 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'GOF. Use the Microsoft OLE DB Provider for Jet on an Excel SpreadsheetTo create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\MyData\DistExcl.xls', NULL, 'Excel 5.0'GOIn order to access data from an Excel spreadsheet, associate a range of cells with a name. A given named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up as above.SELECT *FROM EXCEL...SalesDataGOG. Use the Microsoft OLE DB Provider for Indexing ServiceThis example creates a linked server and uses OPENQUERY to retrieve information from both the linked server and the file system enabled for Indexing Service.EXEC sp_addlinkedserver FileSystem, 'Index Server', 'MSIDXS', 'Web'GOUSE pubsGOIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'yEmployees') DROP TABLE yEmployeesGOCREATE TABLE yEmployees ( id int NOT NULL, lname varchar(30) NOT NULL, fname varchar(30) NOT NULL, salary money, hiredate datetime )GOINSERT yEmployees VALUES ( 10, 'Fuller', 'Andrew', $60000, '9/12/98' )GOIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'DistribFiles') DROP VIEW DistribFilesGOCREATE VIEW DistribFiles AS SELECT * FROM OPENQUERY(FileSystem, 'SELECT Directory, FileName, DocAuthor, Size, Create, Write FROM SCOPE('' "c:\My Documents" '') WHERE CONTAINS(''Distributed'') > 0 AND FileName LIKE ''%.doc%'' ') WHERE DATEPART(yy, Write) = 1998GOSELECT * FROM DistribFilesGOSELECT Directory, FileName, DocAuthor, hiredateFROM DistribFiles D, yEmployees EWHERE D.DocAuthor = E.FName + ' ' + E.LNameGOH. Use the Microsoft OLE DB Provider for Jet to access a text fileThis example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.--Create a linked serverEXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\data\distqry', NULL, 'Text'GO--Set up login mappingsEXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULLGO--List the tables in the linked serverEXEC sp_tables_ex txtsrvGO--Query one of the tables: file1#txt--using a 4-part name SELECT * FROM txtsrv...[file1#txt]I. Use the Microsoft OLE DB Provider for DB2This example creates a linked server named DB2 that uses the Microsoft OLE DB Provider for DB2.EXEC sp_addlinkedserver @server='DB2', @srvproduct='Microsoft OLE DB Provider for DB2', @catalog='DB2', @provider='DB2OLEDB', @provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'See AlsoConfiguring Linked Servers OLE DB Providers Tested with SQL Serversp_addlinkedsrvloginsp_addserversp_dropserversp_serveroptionsp_setnetnameSystem Stored ProceduresSystem Tables©1988-2000 Microsoft Corporation. All Rights Reserved.
--OPENROWSET information:quote: Transact-SQL Reference OPENROWSETIncludes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.SyntaxOPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } ) Arguments'provider_name'Is a character string that represents the friendly name of the OLE DB provider as specified in the registry. provider_name has no default value.'datasource'Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the provider's IDBProperties interface to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database(s). 'user_id'Is a string constant that is the username that is passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider.'password' Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider.'provider_string'Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information needed to initialize the provider.catalogIs the name of the catalog or database in which the specified object resides.schemaIs the name of the schema or object owner for the specified object.objectIs the object name that uniquely identifies the object to manipulate.'query'Is a string constant sent to and executed by the provider. Microsoft® SQL Server™ does not process this query, but processes query results returned by the provider (a pass-through query). Pass-through queries are useful when used on providers that do not expose their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces. For more information, see SQL Server OLE DB Programmer's Reference. RemarksCatalog and schema names are required if the OLE DB provider supports multiple catalogs and schemas in the specified data source. Values for catalog and schema can be omitted if the OLE DB provider does not support them. If the provider supports only schema names, a two-part name of the form schema.object must be specified. If the provider supports only catalog names, a three-part name of the form catalog.schema.object must be specified.OPENROWSET does not accept variables for its arguments.PermissionsOPENROWSET permissions are determined by the permissions of the username being passed to the OLE DB provider. ExamplesA. Use OPENROWSET with a SELECT and the Microsoft OLE DB Provider for SQL ServerThis example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.USE pubsGOSELECT a.*FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS aGOB. Use OPENROWSET with an object and the OLE DB Provider for ODBCThis example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.USE pubsGOSELECT a.*FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass', pubs.dbo.authors) AS aORDER BY a.au_lname, a.au_fnameGOC. Use the Microsoft OLE DB Provider for JetThis example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.Note This example assumes that Access is installed.USE pubsGOSELECT a.*FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS aGOD. Use OPENROWSET and another table in an INNER JOINThis example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.Note This example assumes that Access is installed.USE pubsGOSELECT c.*, o.*FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o ON c.CustomerID = o.CustomerID GOSee AlsoDELETEDistributed QueriesFROMINSERTOPENDATASOURCEOPENQUERYRowset FunctionsSELECTsp_addlinkedserversp_serveroptionUPDATEWHERE©1988-2000 Microsoft Corporation. All Rights Reserved.
That should about do it for you. If you can't figure it out from there, buy SQL Server for Dummies. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|