openrowset 使用变量参数问题

[liubocy]

Declare @ip varchar(20)
Declare @t TABLE( aa int, bb varchar(20) )

set @ip = '192.168.1.85' 
insert into @t select aa,bb from openrowset( 'SQLOLEDB', @ip; 'sa'; '',test.dbo.test) 

select * from @t

请帮忙给个可以正确运行的语句

[lxyzxq2008]

[Quote=引用 6 楼 dawugui 的回复:]

SQL code
不同服务器数据库之间的数据操作

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

--查询示例
select * from ITSV.数……
[/Quote]这个给力啊～

[liubocy]

Declare @ip varchar(20)
Declare @t TABLE( aa int, bb varchar(20) )
DECLARE @sql NVARCHAR(4000)

set @ip = '192.168.1.85' 
SET @sql = 'select aa, bb from openrowset( ''SQLOLEDB'', '''+ @ip +'''; ''sa''; '''',test.dbo.test)'

INSERT INTO @t EXEC(@sql)
SELECT * FROM @t

/*
服务器: 消息 197，级别 15，状态 1，行 9
无法在向表插入变量时将 EXECUTE 用作源。
*/

另如何把@t表中数据插入到test表中？

[橡胶轮胎行业数字化高总]

使用组合字符串。
示例：
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS a;
如果你要使用变量IP，那么只能是将字符串连接后，使用EXEC执行。

[liubocy]

[Quote=引用 1 楼 gaojier1000 的回复:]
如果你要使用变量IP，那么只能是将字符串连接后，使用EXEC执行[/Quote]
用exec没办法把数据插入到@t表中的吧

[Shawn]

#1. EXEC只能执行拼好的字符串，无法传参
#2. sp_executesql虽然可以传参，但只能执行参数化查询，而且不能传递表变量
#3. 楼主可考虑用存储过程实现:

CREATE PROCEDURE proc_test
	@ip VARCHAR(20)
AS
BEGIN
	DECLARE @t TABLE(aa int, bb varchar(20))
	DECLARE @sql NVARCHAR(4000)
	SET @sql = 'select aa, bb from openrowset( ''SQLOLEDB'', '''+ @ip +'''; ''sa''; ''123456'',test.dbo.test)'
	--PRINT @sql
	INSERT INTO @t EXEC(@sql)
	SELECT * FROM @t
END

[AcHerat]

Declare @ip varchar(20)
Declare @t TABLE( aa int, bb varchar(20) )
Declare @sql varchar(4000)

set @ip = '192.168.1.85' 
set @sql = 'select aa, bb from openrowset( ''SQLOLEDB'', '''+ @ip +'''; ''sa''; ''sa密码'',test.dbo.test)'
insert into @t exec (@sql) 

select * from @t

[华夏小卒]

insert into @t exec (@sql)

[dawugui]

不同服务器数据库之间的数据操作

--创建链接服务器 
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 本地表 

[橡胶轮胎行业数字化高总]

use test
create table test(aa int,bb varchar(10))
insert test
select 1,'BBBBBB' union all
select 2,'adsfas' union all
select 3,'WWWWsd'
go

Declare @ip varchar(20) 
Declare @t TABLE( aa int, bb varchar(20) ) 
DECLARE @sql NVARCHAR(4000) 
set @ip = '192.168.2.100' 
SET @sql = 'select aa, bb from openrowset( ''SQLOLEDB'', '''+ @ip +'''; ''sa''; ''123'',test.dbo.test)' 
INSERT INTO @t EXEC(@sql) 
SELECT * FROM @t
drop table test


(3 row(s) affected)

(3 row(s) affected)
aa          bb
----------- --------------------
1           BBBBBB
2           adsfas
3           WWWWsd

(3 row(s) affected)

[橡胶轮胎行业数字化高总]

楼主的SQL Server是2k版本的的吧。我使用了2005和2008都没有问题。

如果是2k版本，建议楼主不要采用表变量，直接采用物理表，创建后再删除的方式。

[liubocy]

用的是sql2000，XP xp3操作系统。
改用物理表后，一开始是报msdtc不可用这个错误，在开启Distributed Transaction Coordinator这个服务，并设置好组件服务中msdtc的安全配置，出现下面的报错。

Declare @ip varchar(20)
DECLARE @sql NVARCHAR(4000)

set @ip = '192.168.1.85' 
SET @sql = 'select aa, bb from openrowset( ''SQLOLEDB'', '''+ @ip +'''; ''sa''; '''',test.dbo.test)'

INSERT INTO test(aa,bb) EXEC(@sql)
SELECT * FROM test

/*
该操作未能执行，因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。
[OLE/DB provider returned message: 新事务不能登记到指定的事务处理器中。 ]
OLE DB 错误跟踪［OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a］。
*/

在网上查了下，根据http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html这个里面的设置了下，错误依然。

[幸运的意外]

将openrowset放到整个字符串里，用动态sql的思想运行吧。