【澳门金沙vip】跨服务器导入数据SQL语句

复制代码 代码如下: truncate table
TestTable EXEC sp_configure ‘show advanced options’, 1; — 开启高档配置
RECONFIGURE EXEC sp_configure ‘Ad Hoc Distributed Queries’,
1;–开启布满式查询 RECONFIGURE set IDENTITY_insert TestTable on –设置
某表允许插入依次增加量 INSERT INTO TestTable(ID,Date,Info 卡塔尔国SELECT
a.ID,a.Date,a.Info FROM OPENROWSET(‘SQ英雄联盟EDB’,’V’;’sa’;’123456′, ‘SELECT
* FROM TestDatabase.dbo.TestTable’) AS a set IDENTITY_insert TestTable
OFF –设置 不某表允许插入依次增加量 EXEC sp_configure ‘Ad Hoc Distributed
Queries’, 0;–关闭布满式查询 RECONFIGURE EXEC sp_configure ‘show
advanced options’, 0;–关闭高端配置 RECONFIGURE

1.1 启用本地读取设置

–启用
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′,
N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′,
N’DynamicParameters’, 1
GO

 

跨越服务器务器导入数据SQL语句 

 

跨过服务器务器导入数据SQL语句及其难点消除方案 跨越服务器务器导入数据SQL语句:

–自定义表名,在导入前创办表 SELECT * INTO TbName FROM
OPENROWSET(‘SQLOLEDB’,’192.168.0.7′;’sa’;’damon king’,DBName.dbo.TbName)

–跨越服务器务器询问示例 SELECT * FROM
OPENROWSET(‘SQ英雄联盟EDB’,’192.168.0.7′;’sa’;damon king’,DBName.dbo.TbName)按F5实施,若未启用’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 联机丛书中的
“外围应用配置器”。

那时,能够行使sp_configure来启用’Ad Hoc Distributed Queries’ :

–启用’Ad Hoc Distributed Queries’

EXEC SP_CONFIGURE ‘show
advanced options’,1 RECONFIGURE EXEC SP_CONFIGURE ‘Ad Hoc
Distributed Queries’,1 RECONFIGURE

当执行完导入后,最好将'Ad Hoc Distributed Queries' 关闭: --关闭'Ad Hoc Distributed Queries' 

澳门金沙vip, 

EXEC SP_CONFIGURE ‘Ad Hoc
Distributed Queries’,0 RECONFIGURE EXEC SP_CONFIGURE ‘show advanced
options’,0
RECONFIGURE

那儿实现了跨过服务器务器导入数据职分!