declare @变量
set @变量='Data Source=远程ip;User ID=sa;Password=密码'select * into 本地库名..表名 from OPENDATASOURCE(
'SQLOLEDB',
@变量
).库名.dbo.表名
set @变量='Data Source=远程ip;User ID=sa;Password=密码'select * into 本地库名..表名 from OPENDATASOURCE(
'SQLOLEDB',
@变量
).库名.dbo.表名
@远程ip varchar(100),
@用户 varcahr(100),
@密码 varchar(100)
as
SET ANSI_NULL_DFLT_ON on //注意
SET ANSI_WARNINGS on select * from OPENDATASOURCE(
'SQLOLEDB',
'Data Source='+@远程ip+';User ID='+@用户+';Password='+@密码
).库名.dbo.表名
declare @paras nvarchar(100)
set @paras ='Data Source=远程ip;User ID=sa;Password=密码'
set @str='select * into 本地库名..表名 from OPENDATASOURCE(
''SQLOLEDB'',' + ''''+@paras + ''').库名.dbo.表名'
exec(@str)
declare @paras nvarchar(100)
set @paras =N'Data Source=远程ip;User ID=sa;Password=密码'
set @str=N'select * into 本地库名..表名 from OPENDATASOURCE(
''SQLOLEDB'',' + ''''+@paras + N''').库名.dbo.表名'
exec(@str)
@远程ip varchar(100),
@用户 varchar(100),
@密码 varchar(100)
as
exec('
SET ANSI_NULL_DFLT_ON on
SET ANSI_WARNINGS on select * from OPENDATASOURCE(
''SQLOLEDB'',
''Data Source='+@远程ip+';User ID='+@用户+';Password='+@密码+'''
).库名.dbo.表名')
@远程ip varchar(100),
@用户 varchar(100),
@密码 varchar(100),
@变量 varchar(100)set @变量='Data Source='+@远程ip+';User ID='+@用户+';Password='+@密码
exec('
SET ANSI_NULL_DFLT_ON on
SET ANSI_WARNINGS on select * from OPENDATASOURCE(
''SQLOLEDB'',
''Data Source='+@变量+'''
).库名.dbo.表名')
''SQLOLEDB'',
''Data Source='+@变量+'''
).库名.dbo.表名')
SELECT * FROM #TEMP 提示找不到#temp
''SQLOLEDB'',
''Data Source='+@变量+'''
).库名.dbo.表名')
SELECT * FROM #TEMP
@DATASOURCE varchar(100),
@USER varchar(100),
@PASSWORD varchar(100),
@CONNECTION varchar(100)SELECT @DATASOURCE='XZZ'
SELECT @USER='SA'
SELECT @PASSWORD='1111'set @CONNECTION='Data Source='+@DATASOURCE+';User ID='+@USER+';Password='+@PASSWORD
exec('
SET ANSI_NULL_DFLT_ON on
SET ANSI_WARNINGS on select * INTO #TEMP from OPENDATASOURCE(
''SQLOLEDB'',
''Data Source='+@CONNECTION+'''
).FDZZ.dbo.LBD')
SELECT * FROM #TEMP结果:
服务器: 消息 17,级别 16,状态 1,行 5
SQL Server 不存在或访问被拒绝
declare @paras nvarchar(100)
set @paras = 'Data Source=192.168.42.12;User ID=sa;Password=hiuoihiooiio'
set @str='select * into ##tp from OPENDATASOURCE(
''SQLOLEDB'',' + ''''+@paras + ''').batest.dbo.table1'
exec(@str)
select * from ##tp
drop table ##tp----你隻要更改set @paras 成為你自己的連接字符即可
select '不在'