本站首页    管理页面    写新日志    退出


«November 2019»
12
3456789
10111213141516
17181920212223
24252627282930


公告
暂无公告...

我的分类(专题)

日志更新

最新评论

留言板

链接


Blog信息
blog名称:VFP及Sql Server拙笔
日志总数:46
评论数量:107
留言数量:0
访问次数:385072
建立时间:2005年5月12日




[编程及数据库]MSSql访问远程数据库
文章收藏,  网上资源,  软件技术,  电脑与网络

老瓷 发表于 2012-12-29 15:05:15

--第一部分(要点)--永久访问方式(需对访问远程数据库进行经常性操作)时设置链接数据库Exec sp_addlinkedserver 'MyLinkServer','','SQLOLEDB','远程服务器名或ip地址'--或:Exec sp_addlinkedserver '远程服务器',N'SQL Server'Exec sp_addlinkedsrvlogin 'MyLinkServer','false',null,'用户名','密码' --返回本地服务器中定义的链接服务器列表Exec sp_linkedservers--返回表的字段中,其中对MSSQL而言:SRV_NAME(链接服务器的名称),SRV_DATASOURCE(与指定链接服务器对应的服务器名) --访问表时以四部分名称形式--查询示例Select * From MyLinkServer.数据库名.dbo.表名 --导入示例Select * Into 表 From MyLinkServer.数据库名.dbo.表名 --以后不再使用时删除链接服务器Exec sp_dropserver 'MyLinkServer','droplogins'   --以用户名登录时--或:Exec sp_dropserver 'MyLinkServer'   --未以用户名登录时 --连接远程/局域网数据(OpenRowSet/OpenQuery/OpenDataSource)--1、OpenRowSet--查询示例Select * From OpenRowSet('SQLOLEDB','SQL服务器名';'用户名';'密码',数据库名.dbo.表名)--生成本地表Select * Into 表 From OpenRowSet('SQLOLEDB','SQL服务器名';'用户名';'密码',数据库名.dbo.表名)--把本地表导入远程表Insert OpenRowSet('SQLOLEDB','SQL服务器名';'用户名';'密码',数据库名.dbo.表名)Select *From 本地表--更新本地表Update bSet b.列A=a.列AFrom OpenRowSet('SQLOLEDB','SQL服务器名';'用户名';'密码',数据库名.dbo.表名) As a Inner Join 本地表 bOn a.column1=b.column1 --OpenQuery用法需要创建一个连接--首先创建一个连接创建链接服务器Exec sp_addlinkedserver 'MyLinkServer','','SQLOLEDB','远程服务器名或IP'--查询Select * From OpenQuery(MyLinkServer,'Select * From 数据库.dbo.表名')--把本地表导入远程表Insert OpenQuery(MyLinkServer,'Select * From 数据库.dbo.表名')Select * From 本地表--更新本地表Update bSet b.列B=a.列BFrom OpenQuery(MyLinkServer,'Select * From 数据库.dbo.表名') As a Inner Join 本地表 b On a.列A=b.列A --3、OpenDataSourceSelect * From OpenDataSource('SQLOLEDB','Data Source=远程服务器名或IP;User ID=登陆名;Password=密码').test.dbo.roy_ta--把本地表导入远程表Insert OpenDataSource('SQLOLEDB','Data Source=远程服务器名或IP;User ID=登陆名;Password=密码').数据库.dbo.表名Select * From 本地表 --第二部分(实际示例)--OpenRowSet使用OLEDB的一些例子Select * From OpenRowSet('SQLOLEDB','Server=(local);PWD=***;UID=sa;','Select * From TB.dbo.school') As tSelect * From OpenRowSet('SQLOLEDB','Server=(local);PWD=***;UID=sa;',TB.dbo.school) As tSelect * From OpenRowSet('SQLOLEDB','Server=(local);Trusted_Connection=yes;',TB.dbo.school) As tSelect * From OpenRowSet('SQLOLEDB','(local)';'sa';'***','Select * From TB.dbo.school') As tSelect * From OpenRowSet('SQLOLEDB','(local)';'sa';'***',TB.dbo.school) As tSelect * From OpenRowSet('SQLOLEDB','(local)';'sa';'***','Select school.id As id1,people.id As id2 From TB.dbo.school Inner Join TB.dbo.people On school.id=people.id') As t --OpenRowSet使用SQLNCLI的一些例子(SQLNCLI在SqlServer2005以上才能使用)Select * From OpenRowSet('SQLNCLI','(local)';'sa';'***','Select * From TB.dbo.school') As tSelect * From OpenRowSet('SQLNCLI','Server=(local);Trusted_Connection=yes;','Select * From TB.dbo.school') As tSelect * From OpenRowSet('SQLNCLI','Server=(local);UID=sa;PWD=***;','Select * From TB.dbo.school') As tSelect * From OpenRowSet('SQLNCLI','Server=(local);UID=sa;PWD=***;',TB.dbo.school) As tSelect * From OpenRowSet('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB','Select * From dbo.school') As t --OpenRowSet其他使用Insert OpenRowSet('SQLNCLI','Server=(local);Trusted_Connection=yes;','Select name From TB.dbo.school Where id=1') Values('ghjkl') /*要不要Where都一样,插入一行*/Update OpenRowSet('SQLNCLI','Server=(local);Trusted_Connection=yes;','Select name From TB.dbo.school Where id=1') Set name='kkkkkk'Delete From OpenRowSet('SQLNCLI','Server=(local);Trusted_Connection=yes;','Select name From TB.dbo.school Where id=1') --OpenDataSource使用SQLNCLI的一些例子Select * From OpenDataSource('SQLNCLI','Server=(local);UID=sa;PWD=***;').TB.dbo.school As tSelect * From OpenDataSource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB').TB.dbo.school As t --OpenDataSource使用OLEDB的例子Select * From OpenDataSource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').TB.dbo.school As t --OpenDataSource其他使用Insert OpenDataSource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school(name) Values('ghjkl') /*要不要Where都一样,插入一行*/Update OpenDataSource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school Set name='kkkkkk'Delete From OpenDataSource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school Where id=1 --OpenQuery使用OLEDB的一些例子Exec sp_addlinkedserver 'MyLinkServer','','SQLOLEDB','(local)'Exec sp_addlinkedsrvlogin 'MyLinkServer','false',null,'sa','***'Select * From OpenQuery(MyLinkServer, 'Select *  From TB.dbo.school') --OpenQuery使用SQLNCLI的一些例子Exec sp_addlinkedserver 'MyLinkServerA','','SQLNCLI','(local)'Exec sp_addlinkedsrvlogin 'MyLinkServerA','false',null,'sa','***'Select * From OpenQuery(MyLinkServerA,'Select *  From TB.dbo.school') --OpenQuery其他使用Insert OpenQuery(MyLinkServerA,'Select name From TB.dbo.school Where id=1') Values('ghjkl')  /*要不要Where都一样,插入一行*/Update OpenQuery(MyLinkServerA,'Select name From TB.dbo.school Where id=1') Set name='kkkkkk'Delete OpenQuery(MyLinkServerA,'Select name From TB.dbo.school Where id=1')


阅读全文(2740) | 回复(0) | 编辑 | 精华
 



发表评论:
昵称:
密码:
主页:
标题:
验证码:  (不区分大小写,请仔细填写,输错需重写评论内容!)



站点首页 | 联系我们 | 博客注册 | 博客登陆

Sponsored By W3CHINA
W3CHINA Blog 0.8 Processed in 0.031 second(s), page refreshed 144326835 times.
《全国人大常委会关于维护互联网安全的决定》  《计算机信息网络国际联网安全保护管理办法》
苏ICP备05006046号