在同个域中或相同的网段中使用SqlServer2000连接远程机器数据库的Jobs

写个有点复杂的Jobs
---连接远程机器数据库 在同个域中或相同的网段中
----------------------------------------------------------------------------------------------------------
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','数据库名'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用户名','密码'
go
--创建查询删除临时表
create table #TempReportData
(
orderID int not null,
AddDate DateTime null
)
select * from #TempReportData
drop table #TempReportData
--查询
----------------------------------------------------------------------------------------------------------
select OrderID,left(AddDate,10) from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID
select * from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID_bak
--使用临时表保存数据
----------------------------------------------------------------------------------------------------------
insert into #TempReportData(orderID,AddDate) select orderID,AddDate from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID
insert into srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID(orderID,AddDate) select orderID,AddDate from #TempReportData
----得到分成比例和货币转换
----------------------------------------------------------------------------------------------------------
declare @ICPercent float --行业客户分成
declare @SIPercent float--行业代理分成
declare @MoneyToBB int --货币转换
select @ICPercent =Value from varshop.dbo.BASE_INFO where valuename = 'SI' --设置客户分成比例
select @SIPercent =Value from varshop.dbo.BASE_INFO where valuename = 'IC' --设置客户代理分成比例
select @MoneyToBB =Value from varshop.dbo.BASE_INFO where valuename = 'MoneyToBB' --设置货币转换
--游标循环执行逻辑
----------------------------------------------------------------------------------------------------------
declare @CrsrVar cursor --声明游标
declare @pOrderID int --订单号
set @crsrvar=Cursor For select OrderID from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID
open @crsrvar
fetch next from @CrsrVar into @pOrderID
--进行逻辑处理，执行存储过程等。
while (@@fetch_status=0) --可惜t-sql没有do while 语句
begin
fetch next from @CrsrVar into @pOrderID
--进行逻辑处理，执行存储过程等。
end
close @CrsrVar
deallocate @CrsrVar
--转移数据后删除原表数据
----------------------------------------------------------------------------------------------------------
insert srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID_bak(OrderID,AddDate) select OrderID,AddDate from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID
delete from srv_lnk.BenQShopNew.dbo.Shop_ReturnOrderID

生成0到1之间的随机数字表达式 rand(checksum(newid())) 这样的好处是每行的随机数都不同了 Created by jecray