在T-SQL语句中访问远程数据库(openrowset/opendatasource/openquery)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

1、启用Ad Hoc Distributed Queries

在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务,因为这个服务不安全所以SqlServer默认是关闭的

启用Ad Hoc Distributed Queries的方法

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource'

的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用

sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细

信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
启用Ad Hoc Distributed Queries的方法,执行下面的查询语句就可以了:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
使用完毕后,记得一定要要关闭它,因为这是一个安全隐患,切记执行下面的SQL语句

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 

 

2、使用示例

复制代码
复制代码
-- 创建链接服务器 
exec  sp_addlinkedserver    ' ITSV  ' '   ' ' SQLOLEDB  ' ' 远程服务器名或ip地址  '  
exec  sp_addlinkedsrvlogin  ' ITSV  ' ' false  ' , null ' 用户名  ' ' 密码  '  

-- 查询示例 
select   *   from  ITSV.数据库名.dbo.表名 

-- 导入示例 
select   *   into  表  from  ITSV.数据库名.dbo.表名 

-- 以后不再使用时删除链接服务器 
exec  sp_dropserver   ' ITSV  ' ' droplogins  '  

-- 连接远程/局域网数据(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  b 
set  b.列A = a.列A 
from   openrowset ' SQLOLEDB  ' ' sql服务器名  ' ' 用户名  ' ' 密码  ' ,数据库名.dbo.表名) as  a  inner   join  本地表 b 
on  a.column1 = b.column1 

-- openquery用法需要创建一个连接 

-- 首先创建一个连接创建链接服务器 
exec  sp_addlinkedserver    ' ITSV  ' '   ' ' SQLOLEDB  ' ' 远程服务器名或ip地址  '  
-- 查询 
select   *  
FROM   openquery (ITSV,   ' SELECT *  FROM 数据库.dbo.表名  '
-- 把本地表导入远程表 
insert   openquery (ITSV,   ' SELECT *  FROM 数据库.dbo.表名  '
select   *   from  本地表 
-- 更新本地表 
update  b 
set  b.列B = a.列B 
FROM   openquery (ITSV,   ' SELECT * FROM 数据库.dbo.表名  ' as  a  
inner   join  本地表 b  on  a.列A = b.列A 

-- 3、opendatasource/openrowset 
SELECT     *  
FROM     opendatasource ' SQLOLEDB  ' ,   ' Data Source=ip/ServerName;User ID=登陆名;Password=密码  '  ).test.dbo.roy_ta 
-- 把本地表导入远程表 
insert   opendatasource ' SQLOLEDB  ' ,   ' Data Source=ip/ServerName;User ID=登陆名;Password=密码  ' ).数据库.dbo.表名 
select   *   from  本地表
复制代码
复制代码

 

 

3、自己写的例子

 

复制代码
复制代码
-- openrowset使用OLEDB的一些例子
select   *   from   openrowset ( ' SQLOLEDB ' , ' Server=(local);PWD=***;UID=sa; ' , ' select * from TB.dbo.school ' as  t
select   *   from   openrowset ( ' SQLOLEDB ' , ' Server=(local);PWD=***;UID=sa; ' ,TB.dbo.school)  as  t
select   *   from   openrowset ( ' SQLOLEDB ' , ' Server=(local);Trusted_Connection=yes; ' ,TB.dbo.school)  as  t
select   *   from   openrowset ( ' SQLOLEDB ' , ' (local) ' ; ' sa ' ; ' *** ' , ' select * from TB.dbo.school ' as  t
select   *   from   openrowset ( ' SQLOLEDB ' , ' (local) ' ; ' sa ' ; ' *** ' ,TB.dbo.school)  as  t
select   *   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  t
select   *   from   openrowset ( ' SQLNCLI ' , ' Server=(local);Trusted_Connection=yes; ' , ' select * from TB.dbo.school ' as  t
select   *   from   openrowset ( ' SQLNCLI ' , ' Server=(local);UID=sa;PWD=***; ' , ' select * from TB.dbo.school ' as  t
select   *   from   openrowset ( ' SQLNCLI ' , ' Server=(local);UID=sa;PWD=***; ' ,TB.dbo.school)  as  t
select   *   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  t
select   *   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    ' ITSV ' '' ' SQLOLEDB ' , ' (local) '  
exec  sp_addlinkedsrvlogin  ' ITSV ' ' false ' , null ' sa ' ' *** '
select   *   FROM   openquery (ITSV,   ' SELECT *  FROM TB.dbo.school  '

-- openquery使用SQLNCLI的一些例子
exec  sp_addlinkedserver    ' ITSVA ' '' ' SQLNCLI ' , ' (local) '  
exec  sp_addlinkedsrvlogin  ' ITSVA ' ' false ' , null ' sa ' ' *** '
select   *   FROM   openquery (ITSVA,   ' SELECT *  FROM TB.dbo.school  '

-- openquery其他使用
insert   openquery (ITSVA, ' select name from TB.dbo.school where id=1 ' values ( ' ghjkl ' ) /* 要不要where都一样,插入一行 */
update   openquery (ITSVA, ' select name from TB.dbo.school where id=1 ' set  name = ' kkkkkk '
delete   openquery (ITSVA, ' select name from TB.dbo.school where id=1 ' )
复制代码
分类:  ASP.NET
本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/p/3529891.html ,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
6天前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之数据集成时源头提供数据库自定义函数调用返回数据,数据源端是否可以写自定义SQL实现
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
2天前
|
SQL 存储 数据库
性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
|
8天前
|
SQL Oracle 关系型数据库
|
7天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
33 3
|
12天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。
|
13天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
13天前
|
SQL Java 关系型数据库
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
24 0
|
13天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
24 0
|
17天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
15天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制