Jason's 程設BLOG

-------------------
--(1)帳號加解密_記錄
-------------------
-- 加密
Select ENCRYPTBYPASSPHRASE(left(@@version,9), 'ID')
Select ENCRYPTBYPASSPHRASE(left(@@version,9), 'PWD')

-- 解密
declare @usr as varchar(500)
declare @pwd as varchar(500)
Select @usr = convert(varchar,DECRYPTBYPASSPHRASE(left(@@version,9),0x010000005C125DA0F15AF4E496C0BA736FECEE16F35E91C98FF8A01B))
Select @pwd = convert(varchar,DECRYPTBYPASSPHRASE(left(@@version,9),0x01000000A59B2CAEED8B634B761F0673636CCCD25214D5979DBF3190))

select @usr,@pwd

-------------------
--(2)BCP out
-------------------
declare @SQL varchar(max)
declare @MSG varchar(2000)

IF OBJECT_ID (N'tempdb.dbo.#T_ERR', N'U') IS NOT NULL DROP TABLE #T_ERR
CREATE TABLE #T_ERR
([cmd_order] int IDENTITY(1,1) NOT NULL
,[cmd_information] nvarchar(MAX) )

-- 匯出文字檔 
set @SQL='MASTER.dbo.xp_cmdshell ''bcp "select * from [link].DB.dbo.TAB" queryout D:\\ftp\\TAB.txt -T -S1.1.1.1 -c -k -t *`# -r **!!** '''
SET @MSG = '執行匯出文字檔BCP-'+@SQL
INSERT INTO #T_ERR(cmd_information) select @MSG

INSERT INTO #T_ERR(cmd_information) EXECUTE sp_executesql @stmt = @SQL --exec(@SQL)
SET @MSG = '執行匯出文字檔BCP-OK'
INSERT INTO #T_ERR(cmd_information) select @MSG

-- BCP OUT的LOG
select '[TAB] '+cmd_information from #T_ERR
where isnull(cmd_information,'')<>''
and cmd_information not like '1000 列成功地大量複製到主機檔案%'
and cmd_information not like '1000 列傳送到 SQL Server%'
order by cmd_order

truncate table #T_ERR

-------------------
-- (3)清空 / 新建 / 重建 TABLE
-------------------
declare @flg int
exec @flg = dbo.sp_TABLE_TRUNCATE 'DB.dbo.TAB','if (object_id(''DB.dbo.TAB'')is not null) drop table DB.dbo.TAB;create table DB.dbo.TAB(...)'

if @flg<>0
begin
	set @MSG = '執行[sp_TABLE_TRUNCATE]-有誤'
	goto ERROR
end

-------------------
--(4)BCP in
-------------------
-- 匯入文字檔
set @SQL='MASTER.dbo.xp_cmdshell ''bcp DB.dbo.TAB in D:\\ftp\\TAB.txt -U'+@usr+' -P'+@pwd+' -S2.2.2.2 -m 100 -c -k -t *`# -r **!!**'''
set @MSG = '執行匯入文字檔BCP['+@SQL+']'
INSERT INTO #T_ERR(cmd_information) select @MSG 

INSERT INTO #T_ERR(cmd_information) EXECUTE sp_executesql @stmt = @SQL --exec(@SQL)
set @MSG = '執行匯入文字檔BCP-OK'
INSERT INTO #T_ERR(cmd_information) select @MSG

-- 砍檔
set @SQL='MASTER.dbo.xp_cmdshell ''del /Q "D:\ftp\\TAB.txt"'''
SET @MSG = '執行砍檔['+@SQL+']'
INSERT INTO #T_ERR(cmd_information) select @MSG 

INSERT INTO #T_ERR(cmd_information) EXECUTE sp_executesql @stmt = @SQL --exec(@SQL)

-- BCP IN的LOG
select left('[TAB] '+cmd_information,490) from #T_ERR
where isnull(cmd_information,'')<>''
and cmd_information not like '1000 列成功地大量複製到主機檔案%'
and cmd_information not like '1000 列傳送到 SQL Server%'
order by cmd_order