異庫數據操作USE [MES] GO /****** Object: StoredProcedure [dbo].[SP_PO_Scan] Script Date: 11/14/2008 11:53:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_PO_Scan] @WO nvarchar(50), @Model nvarchar(50), @PN nvarchar(50), @CB nvarchar(50), @KB nvarchar(50), @Line nvarchar(10) as BEGIN declare @Sql nvarchar(2000) if not exists(select 1 from master..sysservers where srvname='srv_lnk') begin --exec sp_dropserver 'srv_lnk','droplogins' --exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','172.20.104.111' exec sp_addlinkedserver @server='srv_lnk',@srvproduct='',@catalog='MfdScan',@provider='SQLOLEDB',@provstr='DRIVER={SQL Server};Initial Catalog=MfdScan;SERVER=172.20.100.22;UID=MfdScan;PWD=123456;' exec sp_addlinkedsrvlogin 'srv_lnk','false' set XACT_ABORT on exec master..xp_cmdshell 'isql /S"172.20.100.22" /U"MfdScan" /P"123456" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output exec master..xp_cmdshell 'net start msdtc',no_output end --select * from OPENDATASOURCE('SQLOLEDB','Data Source=172.20.100.12;User ID=sa;Password=15975382460').MES.dbo.[Res_User_Temp] --delete from OPENDATASOURCE('SQLOLEDB','Data Source=172.20.100.12;User ID=sa;Password=15975382460').MES.dbo.[Res_User_Temp] declare @Sql nvarchar(1000) set @Sql='delete from OPENDATASOURCE(''SQLOLEDB'',''Data Source=172.20.100.22;User ID=sa;Password=159753'').MES.dbo.[Res_User_Temp] where CB=''三廠''' exec(@Sql) set @Sql='insert into OPENDATASOURCE(''SQLOLEDB'',''Data Source=172.20.100.22;User ID=sa;Password=159753'').MES.dbo.[Res_User_Temp] '+ 'select bh as Account_Id,xm as Full_Name,xb as Sex,hf as Married,zw,byyx as CB,BM,[by1] as KB,xiabia as XB,dbo.getbc(getdate(),bh,bc),jb as CBZX,fzr as SJZG,by2 as Email,bdr as JCRQ from employee' exec(@Sql)END
GO
/****** Object: StoredProcedure [dbo].[SP_PO_Scan] Script Date: 11/14/2008 11:53:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_PO_Scan]
@WO nvarchar(50),
@Model nvarchar(50),
@PN nvarchar(50),
@CB nvarchar(50),
@KB nvarchar(50),
@Line nvarchar(10)
as
BEGIN
declare @Sql nvarchar(2000)
if not exists(select 1 from master..sysservers where srvname='srv_lnk')
begin
--exec sp_dropserver 'srv_lnk','droplogins'
--exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','172.20.104.111'
exec sp_addlinkedserver @server='srv_lnk',@srvproduct='',@catalog='MfdScan',@provider='SQLOLEDB',@provstr='DRIVER={SQL Server};Initial Catalog=MfdScan;SERVER=172.20.100.22;UID=MfdScan;PWD=123456;'
exec sp_addlinkedsrvlogin 'srv_lnk','false'
set XACT_ABORT on
exec master..xp_cmdshell 'isql /S"172.20.100.22" /U"MfdScan" /P"123456" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
exec master..xp_cmdshell 'net start msdtc',no_output
end
--select * from OPENDATASOURCE('SQLOLEDB','Data Source=172.20.100.12;User ID=sa;Password=15975382460').MES.dbo.[Res_User_Temp]
--delete from OPENDATASOURCE('SQLOLEDB','Data Source=172.20.100.12;User ID=sa;Password=15975382460').MES.dbo.[Res_User_Temp]
declare @Sql nvarchar(1000)
set @Sql='delete from OPENDATASOURCE(''SQLOLEDB'',''Data Source=172.20.100.22;User ID=sa;Password=159753'').MES.dbo.[Res_User_Temp] where CB=''三廠'''
exec(@Sql)
set @Sql='insert into OPENDATASOURCE(''SQLOLEDB'',''Data Source=172.20.100.22;User ID=sa;Password=159753'').MES.dbo.[Res_User_Temp] '+
'select bh as Account_Id,xm as Full_Name,xb as Sex,hf as Married,zw,byyx as CB,BM,[by1] as KB,xiabia as XB,dbo.getbc(getdate(),bh,bc),jb as CBZX,fzr as SJZG,by2 as Email,bdr as JCRQ from employee'
exec(@Sql)END