跨服务器进行数据查询中出现的问题

在最近的项目中由于出帐和计费的数据不在同一台服务器上,需要用到跨库查询.

现把查询语句给出

select * into tmptable from openquery([demo],''select * from demoinfo.dbo.出帐(''200812''')''),

这里需要对远程服务器进行注册

注册方式如下:

Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP

EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server2' --要访问的服务器

EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'thankyoubobby' --密码

Select * from DBVIP.pubs.dbo.orders

如果你要访问的远程服务器不能使用机器名进行访问的话,那么需要使用ip地址替换Server2来实现.

参考资料:http://www.cnitblog.com/MartinYao/archive/2007/04/12/25647.aspx

在具体使用中

SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )

UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101')
SET name = 'hello';

INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')
VALUES ('hello');

DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');

如果数据量比较小,那么ok,没有什么事了.如果数据量比较大,查询时间超长,报如下错误:

已以用户 SQLDATA 的身份执行。 链接服务器 "demo" 的 OLE DB 访问接口 "SQLNCLI" 报错。由于达到了资源限制，提供程序终止了执行。 [SQLSTATE 42000] (错误 7399) 无法对链接服务器 "demo" 的 OLE DB 访问接口 "SQLNCLI" 执行查询"''select * from demoinfo.dbo.出帐(''200812''')"。 [SQLSTATE 42000] (错误 7320). 该步骤失败。

Server:Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error.Execution terminated by the provider because a resource limit was reached.[OLE/DB provider returned message:Timeout expired]

那么首先在查询中使用DBCC TRACEON (7300, 3604) 语句，然后执行查询，则您可能会在错误信息中看到其他信息

我在使用了之后返回的错误信息是:

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
链接服务器"demo"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "查询超时已过期"。
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "demo" 的 OLE DB 访问接口 "SQLNCLI" 报错。由于达到了资源限制，提供程序终止了执行。
消息 7320，级别 16，状态 2，第 1 行
无法对链接服务器 "demo" 的 OLE DB 访问接口 "SQLNCLI" 执行查询''select * from demoinfo.dbo.出帐(''200812''')"。

则对超时时间进行修改.

使用以下代码将远程查询超时值设置为 0（无限等待）：
sp_configure 'remote query timeout', 0
go
reconfigure with override
go

参考资料:http://support.microsoft.com/kb/314530