USE [DB_B]
GO
/****** Object: StoredProcedure [dbo].[SP_Upload_FZSRV] Script Date: 2017/2/18 下午 02:14:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20170217
-- Description: 環境 DB_B LinkServerName =FZSRV.DB_C
-- EXEC SP_Upload_FZSRV
-- a.讀取FileControl 控制檔
-- b.gen 出table的語法
-- c.連到DB_C去產生table
-- e.將DB_B的資料copy到DB_C
-- f.更新FileControl 控制檔
-- =============================================
ALTER PROCEDURE [dbo].[SP_Upload_FZSRV]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TABLE_NAME nvarchar(100)
DECLARE @SQL nvarchar(max)
DECLARE @IsExist char(1)
DECLARE @IsErrFlag char(1)
DECLARE @ReturnSQL as nvarchar(max)
DECLARE @spname varchar(50)
DECLARE @ERROR int
DECLARE @ROWCOUNT int
DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @spname='SP_Upload_FZSRV'
SET @IsErrFlag = 'N'
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'BEGIN **********************' , '0' , '' , ''
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'CHECK FileControl BEGIN' , '0' , '' , ''
Declare cur_TABLE Cursor for
select TABLE_NAME from FileControl where IsUpload = '2' and len(TABLE_NAME) >= 10 order by TABLE_NAME
Open cur_TABLE
Fetch next from cur_TABLE
Into @TABLE_NAME
while (@@fetch_status = 0)
begin
print '*****************************************'
print '******** '+ @TABLE_NAME+ ' ******************'
print '*****************************************'
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Gen Script ' + @TABLE_NAME + ' BEGIN', '0' , '' , ''
--先到FZSRV產生Table
print '產生Create Table語法 start --------------------------------------'
--下兩行暫時無法使用，待
--SET @SQL = ' Declare @CreSQL as varchar(max) exec [SP_Creat_Table] ''DB_B'',''' + @TABLE_NAME +''',@CreSQL OUTPUT'
--EXEC sp_executesql @SQL,@CreSQL = @ReturnSQL OUTPUT
SET @SQL = 'EXEC [SP_Creat_Table] 'DB_B','TABLE_NAME',@ReturnSQL OUTPUT '
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Gen Script ' + @TABLE_NAME + ' START', '0' , '' , @SQL
BEGIN TRY
EXEC [SP_Creat_Table] 'DB_B','TABLE_NAME',@ReturnSQL OUTPUT
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Gen Script ' + @TABLE_NAME + ' END', '0' , '' , @ReturnSQL
SET @IsErrFlag = 'N'
END TRY
BEGIN CATCH
SET @IsErrFlag = 'Y'
SET @ERROR = @@ERROR
SET @ROWCOUNT = @@ROWCOUNT
SET @ERROR_MESSAGE = ERROR_MESSAGE()
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Gen Script ' + @TABLE_NAME + ' ERROR', @ERROR , @ERROR_MESSAGE , ''
END CATCH
print '產生Create Table語法 end --------------------------------------'
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Create Table ' + @TABLE_NAME + ' BEGIN', '0' , '' , '@IsErrFlag=' +@IsErrFlag
if @IsErrFlag = 'N'
begin
print ''
print 'Create Table語法 start --------------------------------------'需遠端的SERVER要提供exec用來執行程式 (請參考【SQL】 sp_exec )
SET @SQL = 'EXEC [FZSRV].[master].dbo.sp_exec ' + @ReturnSQL
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Create Table ' + @TABLE_NAME + ' START', '0' , '' , @SQL
BEGIN TRY
EXEC [FZSRV].[master].dbo.sp_exec @ReturnSQL
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Create Table ' + @TABLE_NAME + ' END', '0' , '' , ''
SET @IsErrFlag = 'N'
END TRY
BEGIN CATCH
SET @IsErrFlag = 'Y'
SET @ERROR = @@ERROR
SET @ROWCOUNT = @@ROWCOUNT
SET @ERROR_MESSAGE = ERROR_MESSAGE()
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Create Table ' + @TABLE_NAME + ' ERROR', @ERROR , @ERROR_MESSAGE , ''
END CATCH
print 'Create Table語法 end --------------------------------------'
end
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' BEGIN', '0' , '' , '@IsErrFlag=' +@IsErrFlag
if @IsErrFlag = 'N'
begin
print ''
print 'Insert data start --------------------------------------'
SET @SQL = 'INSERT INTO [FZSRV].DB_C.dbo.' + @TABLE_NAME
SET @SQL = @SQL + ' select * from DB_B.dbo.' + @TABLE_NAME
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' START', '0' , '' , @SQL
BEGIN TRY
EXEC (@SQL)
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' END', '0' , '' , ''
SET @IsErrFlag = 'N'
END TRY
BEGIN CATCH
SET @IsErrFlag = 'Y'
SET @ERROR = @@ERROR
SET @ROWCOUNT = @@ROWCOUNT
SET @ERROR_MESSAGE = ERROR_MESSAGE()
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' ERROR', @ERROR , @ERROR_MESSAGE , ''
END CATCH
print 'Insert data end --------------------------------------'
print ''
end
--更新上傳記號
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload=Y BEGIN', '0' , '' , '@IsErrFlag=' + @IsErrFlag
if @IsErrFlag = 'N'
begin
SET @SQL = ' UPDATE DB_B.dbo.FileControl SET IsUpload = ''Y'' WHERE TABLE_NAME=''' + @TABLE_NAME +''' '
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload=Y START', '0' , '' , @SQL
BEGIN TRY
EXEC (@SQL)
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload=Y END', '0' , '' , ''
SET @IsErrFlag = 'N'
END TRY
BEGIN CATCH
SET @IsErrFlag = 'Y'
SET @ERROR = @@ERROR
SET @ROWCOUNT = @@ROWCOUNT
SET @ERROR_MESSAGE = ERROR_MESSAGE()
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload=Y ERROR', @ERROR , @ERROR_MESSAGE , ''
END CATCH
end
Fetch next from cur_TABLE
Into @TABLE_NAME
end
close cur_TABLE
deallocate cur_TABLE
INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'FINISH **********************' , '0' , '' , ''
END
