跨服务器查询,驱动建立的链接服务器调用存储

时间:2020-01-17 18:44来源:江苏十一选五手机版数据库
我们知道SQLSERVER建立链接服务器可以选择的驱动程序非常多,最近发现使用ODBC 的Microsoft OLE DB 驱动程序建立的链接服务器,调用存储过程过程时,参数不能为NULL值。 [SQL SERVER] 跨服务器

我们知道SQL SERVER建立链接服务器可以选择的驱动程序非常多,最近发现使用ODBC 的 Microsoft OLE DB 驱动程序建立的链接服务器, 调用存储过程过程时,参数不能为NULL值。

[SQL SERVER] 跨服务器查询

否则就会报下面错误提示:

方法一:
用OPENDATASOURCE
下面是个跨SQLServer查询的示例
Select TableA.*,TableB.* From OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerA;User ID=UserID;Password=Password'
         ).databaseAName.dbo.TableA
Left Join
 OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerB;User ID=UserID;Password=Password'
         ).databaseBName.dbo.TableB On TableA.key=TableB.key

对应的英文错误提示为:

下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

EXEC xxx.xxx.dbo.Usp_Test NULL,NULL,'ALL'

方法二(也可以在企业管理器里添加 linkServer):
sp_addlinkedserver
创建一个链接的服务器,使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后,此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft? SQL Server?,则可执行远程存储过程。

Msg 7213, Level 16, State 1, Line 1

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

The attempt by the provider to pass remote stored procedure parameters to remote server 'xxx' failed. Verify that the number of parameters, the order, and the values passed are correct.

权限
执行许可权限默认授予 sysadmin 和 setupadmin 固定服务器角色的成员。

对应的中文错误提示为:

简单示例:
//创建linkServer
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','服务器名'
//登陆linkServer
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用户名','密码'
//查询linkServer的数据库DataBaseA的表TableA
Select * From srv_lnk.DataBaseA.dbo.TableA
//List the tables in the linked server
EXEC sp_tables_ex txtsrv

EXEC xxx.xxx.dbo.Usp_Test NULL,NULL,'ALL'

 

Msg 7213, Level 16, State 1, Line 1

示例
A. 使用用于 SQL Server 的 Microsoft OLE DB 提供程序
使用用于 SQL Server 的 OLE DB 创建链接服务器
下面的示例创建一台名为 SEATTLESales 的链接服务器,该服务器使用用于 SQL Server 的 Microsoft OLE DB 提供程序。

提供程序将远程存储过程参数传递给远程服务器 'xxx' 的尝试失败。请确保参数的数目、顺序和所传递的值正确。

USE master
GO
EXEC sp_addlinkedserver
    'SEATTLESales',
    N'SQL Server'
GO

EXEC xxx.xxx.dbo.Usp_Test '','','ALL' 或者给参数赋予一个合适的值则不会报任何错误。另外使用Microsoft OLE DB Provider for SQL Server驱动方式建立的链接服务器调用存储过程时参数就可以为NULL。

在 SQL Server 的实例上创建链接服务器
此示例在 SQL Server 的实例上创建一台名为 S1_instance1 的链接服务器,该服务器使用 SQL Server 的 Microsoft OLE DB 提供程序。

以上所述是小编给大家分享的SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值的全部内容,希望大家喜欢。

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB', @datasrc='S1instance1'

B. 使用用于 Jet 的 Microsoft OLE DB 提供程序
此示例创建一台名为 SEATTLE Mktg 的链接服务器。

 

说明  本示例假设已经安装 Microsoft Access 和示例 Northwind 数据库,且 Northwind 数据库驻留在 C:MsofficeAccessSamples。

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:MSOfficeAccessSamplesNorthwind.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:MSOfficeAccessSamplesNorthwind.mdb'
GO

C. 使用用于 Oracle 的 Microsoft OLE DB 提供程序
此示例创建一台名为 LONDON Mktg 的链接服务器,该服务器使用用于 Oracle 的 Microsoft OLE DB 提供程序,并且假设此 Oracle 数据库的 SQL*Net 别名为 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. 将 data_source 参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
此示例创建一台名为 SEATTLE Payroll 的链接服务器,该服务器使用用于 ODBC 的 Microsoft OLE DB 提供程序和 data_source 参数。

 

说明  在执行 sp_addlinkedserver 之前,必须在服务器上将指定的 ODBC 数据源名称定义为系统 DSN。

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. 将 provider_string 参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
此示例创建一台名为 LONDON Payroll 的链接服务器,该服务器使用用于 ODBC 的 Microsoft OLE DB 提供程序和 provider_string 参数。

 

说明  有关 ODBC 连接字符串的更多信息,请参见 SQLDriverConnect 和如何分配句柄并与 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. 在 Excel 电子表格上使用用于 Jet 的 Microsoft OLE DB 提供程序
若要创建使用用于 Jet 的 Microsoft OLE DB 提供程序以访问 Excel 电子表格的链接服务器定义,请首先在 Excel 中创建一个命名的范围以指定要在 Excel 工作表中选择的行和列。然后,可将此范围的名称引用为分布式查询中的表名称。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:MyDataDistExcl.xls',
   NULL,
   'Excel 5.0'
GO

为了访问 Excel 电子表格中的数据,请将某个范围内的单元与某个名称相关联。通过将范围的名称用作表名称,可以访问指定的已命名范围。下列查询利用前面设置的链接服务器,可访问称为 SalesData 的命名范围。

SELECT *
FROM EXCEL...SalesData
GO

G. 使用用于检索服务的 Microsoft OLE DB 提供程序
此示例创建一台链接服务器,并且使用 OPENQUERY 从为检索服务启用的链接服务器和文件系统中检索信息。

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

H. 使用用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件
此示例创建一台直接访问文本文件的链接服务器,而没有将这些文件链接为 Access .mdb 文件中的表。提供程序是 Microsoft.Jet.OLEDB.4.0,提供程序字符串为"Text"。

数据源是包含文本文件的目录的完整路径名。schema.ini 文件(描述文本文件的结构)必须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息,请参见 Jet 数据库引擎文档。

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:datadistqry',
   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]

I. 使用用于 DB2 的 Microsoft OLE DB 提供程序
下面的示例创建一台名为 DB2 的链接服务器,该服务器使用用于 DB2 的 Microsoft OLE DB 提供程序。

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;'

 

方法三:
OPENQUERY
尽管查询可能返回多个结果集,但是 OPENQUERY 只返回第一个。

语法
OPENQUERY ( linked_server , 'query' )

参数
linked_server

一个标识符,表示链接的服务器的名称。

'query'

在链接的服务器中执行的查询字符串。

注释
OPENQUERY 不接受参数变量。

示例
下面的示例利用用于 Oracle 的 Microsoft OLE DB 提供程序针对 Oracle 数据库创建一个名为 OracleSvr 链接的服务器。然后,该示例对此链接的服务器使用一个直接传递查询。

说明  本示例假定已经创建了一个名为 ORCLDB 的 Oracle 数据库别名。

EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

方法四:
OPENROWSET
包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时,这种方法是一种替代方法,并且是一种使用 OLE DB 连接并访问远程数据的一次性的、特殊的方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 提供程序的能力,还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集,然而 OPENROWSET 只返回第一个。

语法
OPENROWSET ( 'provider_name'
    , { 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog.] [ schema.] object
        | 'query' }
    )

示例
A. 将 OPENROWSET 与 SELECT 语句及用于 SQL Server 的 Microsoft OLE DB 提供程序一起使用
下面的示例使用用于 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs 数据库中的 authors 表,该数据库在一个名为 seattle1 的远程服务器上。从 datasource、user_id 及 password 中初始化提供程序,并且使用 SELECT 语句定义返回的行集。

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

B. 将 OPENROWSET 与对象及用于 ODBC 的 OLE DB 提供程序一起使用
下面的示例使用用于 ODBC 的 OLE DB 提供程序以及 SQL Server ODBC 驱动程序访问 pubs 数据库中的 authors 表,该数据库在一个名为 seattle1 的远程服务器中。提供程序用在 ODBC 提供程序所用的 ODBC 语法中指定的 provider_string 进行初始化,定义返回的行集时使用 catalog.schema.object 语法。

USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
   pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO

C. 使用用于 Jet 的 Microsoft OLE DB 提供程序
下面的示例通过用于 Jet 的 Microsoft OLE DB 提供程序访问 Microsoft Access Northwind 数据库中的 orders 表。

 

说明  下面的示例假定已经安装了 Access。

USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:MSOfficeAccessSamplesnorthwind.mdb';'admin';'mypwd', Orders)
   AS a
GO

D. 使用 OPENROWSET 和 INNER JOIN 中的另一个表
下面的示例从本地 SQL Server Northwind 数据库的 customers 表中,以及存储在相同计算机上 Access Northwind 数据库的 orders 表中选择所有数据

 

说明  下面的示例假定已经安装了 Access。

USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:MSOfficeAccessSamplesnorthwind.mdb';'admin';'mypwd', Orders)
   AS o
   ON c.CustomerID = o.CustomerID
GO

编辑:江苏十一选五手机版数据库 本文来源:跨服务器查询,驱动建立的链接服务器调用存储

关键词: