sqlserver 即席连接外部数据

最新推荐文章于 2022-03-13 17:47:26 发布

取壳羊

最新推荐文章于 2022-03-13 17:47:26 发布

阅读量436

点赞数

分类专栏： sqlServer 文章标签： sqlserver 连接外部数据

本文链接：https://blog.csdn.net/java_w/article/details/45619873

版权

sqlServer 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

--开启
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

alter PROCEDURE lf_GroupName
AS
declare @groupId int,@groupName varchar(20)
declare mycursor cursor for

select G.groupId as groupId,GS.groupName as groupName from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.1.30;User ID=sa;Password=sa123456!'
).xxxx.dbo.[Group] G
left join [group] GS on G.groupId=GS.groupId
where GS.groupid = 49

open mycursor
fetch next from mycursor into @groupId,@groupName
while(@@fetch_status = 0)
begin

update OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.1.30;User ID=sa;Password=sa123456!'
).xxxx.dbo.[Group] set groupName= @groupName where groupId=@groupId

fetch next from mycursor into @groupId,@groupName

end
close mycursor
deallocate mycursor
GO