打開另一個數據庫的表
1.
select * from openrowset('sqloledb','sprogram';'develop';'11111','select * from kind')
2.
select * from opendatasource('sqloledb','data source=sprogram;user ID=develop;Password=11111').new_ks.dbo.kind
所以有
select * from openrowset('sqloledb','sprogram';'develop';'11111','select * from kind') a
left join dbo.tb b
on a.id=b.id
1.
select * from openrowset('sqloledb','sprogram';'develop';'11111','select * from kind')
2.
select * from opendatasource('sqloledb','data source=sprogram;user ID=develop;Password=11111').new_ks.dbo.kind
所以有
select * from openrowset('sqloledb','sprogram';'develop';'11111','select * from kind') a
left join dbo.tb b
on a.id=b.id
--访问不同电脑上的数据库(远程只要联好网就一样)--如果经常访问或数据量大,建议用链接服务器--创建链接服务器
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用户名','密码'
go--查询示例
select * from srv_lnk.数据库名.dbo.表名--导入示例
select * into 表 from srv_lnk.数据库名.dbo.表名go
--以后不再使用时删除链接服务器
exec sp_dropserver 'srv_lnk','droplogins'
--如果只是临时访问,可以直接用openrowset
--查询示例
select * from openrowset('SQLOLEDB'
,'sql服务器名';'用户名';'密码'
,数据库名.dbo.表名)
--导入示例
select * into 表 from openrowset('SQLOLEDB'
,'sql服务器名';'用户名';'密码'
,数据库名.dbo.表名)
join 另一台服务器在本服务器的别名.数据库名.用户名.要查询的表名 B
on A.连接关键字=B.连接关键字
update opendatasource('sqloledb','data source=aaa;user id=sa;password=').suijun.dbo.invoice
set transtate=inserted.transtate from inserted where inserted.invno=invno
后面的Invno字段怎么表示才不会出错?刚才因为上班了,没有及时结贴,现在出现问题了,希望一并解决,然后结贴,谢谢!
from opendatasource('sqloledb','data source=aaa;user id=sa;password=').suijun.dbo.invoice a,inserted i where a.invno=i.invno
from opendatasource('sqloledb','data source=aaa;user id=sa;password=').suijun.dbo.invoice a,inserted i where a.invno=i.invno这条语句测试出错,提示“Invalid object name 'a'.”
有没有好方法处理这个问题?
为什么用了链接 sp_addlinkedserver,sp_addlinkedsrvlogin就不能开事物?
错误提示:
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: 无法在此会话中启动更多的事务。]
update inv set transtate=inserted.transtate
from Srv_Suijun.suijun.dbo.invoice inv,inserted
where inv.invno=inserted.invno 错误提示:
Could not open table '"suijun"."dbo"."invoice"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: 多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。]
create table tb(id int primary key,a varchar(10))
insert tb select 1,'a'
union all select 2,'b'
union all select 3,'c'
go--然后选择一个服务器,创建另一个服务器的链接服务器
--并写上如下触发器
create trigger tr_update on tb
for update
as
set xact_abort on --注意这句
update a set a=a.a+'_update'
from srv_lnk.tempdb.dbo.tb a,deleted d
where a.id=d.id
go--使用这个触发触发器
update tb set a='aa_new' where id=3--结果很成功