how to create a linked table in SQL-Server 7.0 ?

[geramis]

in MS-Access there is an option to create a linked table, so that the table in the local database is only a reference to a table in a foreign database.

I wan't to create a table in my local database
(SQL-Server 7.0), that is a read-only link to an foreign table in an outside dBase file.

How do i do it???

 

[guljar]

see books online for details.
sp_addlinkedserver (T-SQL)
Creates 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 SQL Server, remote stored procedures can be executed.

Syntax
sp_addlinkedserver [@server =] 'server' [, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name'] [, [@datasrc =] 'data_source']
[, [@location =] 'location'] [, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']

Arguments
[@server =] 'server'
Is the name of the linked server to create. server is sysname, with no default.
[@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.
[@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.
[@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 Values
0 (success) or 1 (failure)

Result Sets
sp_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.



Remarks
The 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; therefore, there may be more than one row for a data source type. The 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 - - 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 -
(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 version 7.0 in SQL Server version 6.x, run the \Mssql7\Install\Instcat.sql script against the version 6.x. This script is essential for running distributed queries against a SQL Server 6.x server.


--------------------------------------------------------------------------------

Permissions
Execute permissions default to members of the sysadmin and setupadmin fixed server roles.

Examples
A. Use the Microsoft OLE DB Provider for SQL Server
This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.

USE master

GO

EXEC sp_addlinkedserver

'SEATTLESales',

N'SQL Server'

GO



B. Use the Microsoft OLE DB Provider for Jet
This 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 master

GO

-- 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 master

GO

EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'

GO



C. Use the Microsoft OLE DB Provider for Oracle
This 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 master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'LONDON Mktg',

@srvproduct = 'Oracle',

@provider = 'MSDAORA',

@datasrc = 'MyServer'

GO

-- OR to use no named parameters:

USE master

GO

EXEC sp_addlinkedserver

'LONDON Mktg',

'Oracle',

'MSDAORA',

'MyServer'

GO



D. Use the Microsoft OLE DB Provider for ODBC with the data_source parameter
This 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 master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'SEATTLE Payroll',

@provider = 'MSDASQL',

@datasrc = 'LocalServer'

GO

-- OR to use no named parameters:

USE master

GO

EXEC sp_addlinkedserver

'SEATTLE Payroll',

'',

'MSDASQL',

'LocalServer'

GO



E. Use the Microsoft OLE DB Provider for ODBC with the provider_string parameter
This 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 master

GO

-- 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 master

GO

EXEC sp_addlinkedserver

'LONDON Payroll',

'',

'MSDASQL',

NULL,

NULL,

'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'

GO



F. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named OrclDB that uses the Microsoft OLE DB Provider for Oracle and replaces the OracleDB string (data_source) with the name of the SQL*Net alias.

USE master

GO

sp_addlinkedserver

'OrclDB',

'Oracle',

'MSDAORA',

'OracleDB'

GO



G. Use the Microsoft OLE DB Provider for Jet on an Excel Spreadsheet
To 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'

GO



H. Use the Microsoft OLE DB Provider for Indexing Service
This 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'

GO

USE pubs

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'yEmployees')

DROP TABLE yEmployees

GO

CREATE TABLE yEmployees

(

id int NOT NULL,

lname varchar(30) NOT NULL,

fname varchar(30) NOT NULL,

salary money,

hiredate datetime

)

GO

INSERT yEmployees VALUES

(

10,

'Fuller',

'Andrew',

$60000,

'9/12/98'

)

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME = 'DistribFiles')

DROP VIEW DistribFiles

GO

CREATE 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) = 1998

GO

SELECT *

FROM DistribFiles

GO

SELECT Directory,

FileName,

DocAuthor,

hiredate

FROM DistribFiles D, yEmployees E

WHERE D.DocAuthor = E.FName + ' ' + E.LName

GO



I. Use the Microsoft OLE DB Provider for Jet to access a text file
This 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. Refer to the Jet documentation for information about creating a schema.ini file.

--Create a linked server

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'

GO



--Set up login mappings

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

GO



--List the tables in the linked server

EXEC sp_tables_ex txtsrv

GO



--Query one of the tables: file1#txt

--using a 4-part name

SELECT *
FROM txtsrv...[file1#txt]



See Also
Configuring Linked Servers sp_serveroption
sp_addlinkedsrvlogin System Tables
sp_addserver System Stored Procedures
sp_dropserver






(c) 1988-98 Microsoft Corporation. All Rights Reserved.

 

[geramis]

thank's a lot for your advice BUT ...
i need to run queries on a remote DBase file
(does SQL linked server support an appropriate service provider?)
if not - how can i run a distributed query???
i wish to avoid data import.

thanks .

 

[Setya]

Dear Mr. Geramis,
I think you can use provider for FoxPro tables to replace the connection information for Access tables.
As second alternative you can establish a connection to your DBase tables via ODBC then using OLEDB provider for ODBC in your sp_addlinkedserver parameters.

Hope that helps

Setya

 

[geramis]

i managed to find the parameters for sp_addlinkedserver with dBase file over the net

thanks for the helpful tips

 

[tlbroadbent]

Guljar,

May I suggest that it is better to provide links to articles, BOL entries, white papers, etc. than to post entire articles in Tek-Tips. The reference to SQL BOL is good. If you want to provide additional help in locating the text, find the link for the article in the MSDN Library at Microsoft.com. For example, the following link is the BOL article about sp_addlinkedserver.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.