备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2017-10-11 1132
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，独享型 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介：

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 ，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
数据库
SQL
数据安全/隐私保护
关键词：
SQL数据库
SQL访问
访问数据库
sql访问数据库
数据库t-sql
相关实践学习
使用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
吞吞吐吐的
目录
相关文章
游客ks4qonorzqbea
|
2天前
|
SQL Oracle 关系型数据库
sql语句创建数据库
在创建数据库之前，请确保你有足够的权限，并且已经考虑了数据库的安全性和性能需求。此外，不同的DBMS可能有特定的最佳实践和配置要求，因此建议查阅相关DBMS的官方文档以获取更详细和准确的信息。
游客ks4qonorzqbea
20 4
SarPro
|
14天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
SarPro
82 6
Lêvï
|
1天前
|
SQL Java 数据库连接
Java从入门到精通：2.3.2数据库编程——了解SQL语言，编写基本查询语句
Java从入门到精通：2.3.2数据库编程——了解SQL语言，编写基本查询语句
Lêvï
6 0
游客rtyzfbpgovwfg
|
3天前
|
SQL 缓存 数据库
sql 数据库优化
SQL数据库优化是一个复杂且关键的过程，涉及多个层面的技术和策略。以下是一些主要的优化建议： 查询语句优化： 避免全表扫描：在查询时，尽量使用索引来减少全表扫描，提高查询速度。 使用合适的子查询方式：子查询可能降低查询效率，但可以通过优化子查询的结构或使用连接（JOIN）替代子查询来提高性能。 简化查询语句：避免不必要的复杂查询，尽量使SQL语句简单明了。 使用EXISTS替代IN：在查询数据是否存在时，使用EXISTS通常比IN更快。 索引优化： 建立合适的索引：对于经常查询的列，如主键和外键，应创建相应的索引。同时，考虑使用覆盖索引来进一步提高性能。 避免过多的索引：虽然索引可以提高查询
游客rtyzfbpgovwfg
38 2
游客rtyzfbpgovwfg
|
3天前
|
SQL XML 数据库
sql导入数据库命令
在SQL Server中，数据库导入可通过多种方式实现：1) 使用SSMS的“导入数据”向导从各种源（如Excel、CSV）导入；2) BULK INSERT语句适用于导入文本文件；3) bcp命令行工具进行批量数据交换；4) OPENROWSET函数直接从外部数据源（如Excel）插入数据。在操作前，请记得备份数据库，并可能需对数据进行预处理以符合SQL Server要求。注意不同方法可能依版本和配置而异。
游客rtyzfbpgovwfg
12 2
桃李春风一杯酒
|
9天前
|
SQL 缓存 Java
Java数据库连接池：优化数据库访问性能
【4月更文挑战第16天】本文探讨了Java数据库连接池的重要性和优势，它能减少延迟、提高效率并增强系统的可伸缩性和稳定性。通过选择如Apache DBCP、C3P0或HikariCP等连接池技术，并进行正确配置和集成，开发者可以优化数据库访问性能。此外，批处理、缓存、索引优化和SQL调整也是提升性能的有效手段。掌握数据库连接池的使用是优化Java企业级应用的关键。
桃李春风一杯酒
22 4
喵~来学编程啦
|
10天前
|
SQL 数据库
数据库SQL语言实战（二）
数据库SQL语言实战（二）
喵~来学编程啦
27 0
LightOf
|
10天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
LightOf
37 3
陌陌谣
|
13天前
|
SQL 监控 数据库
数据库管理与电脑监控软件：SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化，如使用索引和调整查询语句，能有效提高数据库性能。同时，合理设计数据库结构，如数据表划分和规范化，也能增强管理效率。此外，利用Python脚本自动化收集系统性能数据，并实时提交至网站，可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性，满足用户需求。
陌陌谣
47 0
技术蜜糖罐
|
22天前
|
存储 关系型数据库 MySQL
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
技术蜜糖罐
44 0

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    SQL常见面试题总结2
  • 3
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 4
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 5
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 6
    大模型与数据分析：探索Text-to-SQL（上）
  • 7
    SQL，Group By 真扎心，原来是这样
  • 8
    大模型与数据分析：探索Text-to-SQL（中）
  • 9
    大模型与数据分析：探索Text-to-SQL（下）
  • 10
    NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]
  • 1
    一台MySQL数据库启动多个实例
    11
  • 2
    MongoDB数据恢复—MongoDB数据库文件被破坏的数据恢复案例
    15
  • 3
    数据库库表结构设计：原理、实例与最佳实践
    15
  • 4
    sql导入数据库命令
    12
  • 5
    Javaweb之数据库连接池以及lombok类库的详细解析
    18
  • 6
    数据库开发之图形化工具以及表操作的详细解析
    19
  • 7
    数据库恢复挂起之更换数据库
    15
  • 8
    数据库开发之mysql前言以及详细解析
    14
  • 9
    「杭州*康恩贝」4月26日PolarDB开源数据库沙龙，开启报名！
    14
  • 10
    Python与NoSQL数据库（MongoDB、Redis等）面试问答
    125

    • 相关课程

    更多
  • 快速掌握 MongoDB 数据库
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库核心概念
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • Excel文件转存到RDS数据库
  • 快速体验PolarDB开源数据库
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）