sql语句master..xp_cmdshell 'C:\LogParserImportData\newwin2003.pl d:\temp2\rtetrade2\iislog\2009-11-18\ex091116.txt d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt 06:00:00 23:59:59'newwin2003.pl# this program takes two parameters:
# the first: file name to be read
# the second: destination file name#handle window 2003 log file, original format:
#date, time, cs-method,cs-uri-stem,cs-uri-query,cs-username,c-ip,cs(user-agent),cs(referral),sc-status,sc-bytesopen (EP, $ARGV[0]);
open (DD, ">$ARGV[1]");binmode(EP);$patern="[";
for ($i=1; $i<10; $i++){
$patern=$patern.chr($i);
}
for ($i=11; $i<13; $i++){
$patern=$patern.chr($i);
}
for ($i=15; $i<32; $i++){
$patern=$patern.chr($i);
}
for ($i=127; $i<161; $i++){
$patern=$patern.chr($i);
}$patern=$patern."]";#get date from argument <servername_exyymmdd.log> add 1/26/06
$parmDate=substr($ARGV[0],length($ARGV[0])-6,2);
while (<EP>){
# remove return and newline at end, has to do this for removing ending +
s/[\r\n]//g; @fields=split(/ /);
# add the cookie field description
if (/^#/){
if (@fields[0] eq "#Version:"){
substr($_,index($_, "#Version:"))="\n#Version: 1.0\n";
} if (@fields[0] eq "#Fields:"){
substr($_,index($_, "#Fields:"))="\n#Fields: date time c-ip cs-username cs-method cs-uri-stem cs-uri-query sc-status sc-bytes cs(User-Agent) cs(Cookie) cs(Referer)\n";
}
print DD $_;
next;
} # skip
if($#fields<10){
next;
} # 11 is the total number of fields, @field[7] might be the user-agent ---09/15/2005
if (@fields[1] lt $ARGV[2] ||($#fields - $[ +1)!=11 || @fields[7] eq "-") {
# print "next";
# print $_;
next;
}
# skip records if date is previous date by chance
#get date from fileld[0], add 1/26/06
$strDate=substr($fields[0],length($fields[0])-2,2);
if ($strDate ne $parmDate) {
next;
}
if (@fields[1] ge $ARGV[3]){
print "end";
last;
} if(/$patern/){
print BB $_."\n";
next;
}
# skip records with sc-status not 304 or 200
if ( not ($fields[9] eq "304" || $fields[9] eq "200")) {
print BB $_."\n";
next;
} # skip records with cs-method not = GET or POST
if ( not ($fields[2] eq "GET" || $fields[2] eq "POST")) {
print BB $_."\n";
next;
} # ignore records with cs-uri-stem has /im/,/images/,'.gif' a image requst --add on 10/27/2005,modify on 1/26/2006
if($fields[3]=~/\/im\//i || $fields[3]=~/\/images\//i ||$fields[3]=~/\.gif/i ){
next;
}
# ignore records with cs-uri-stem has 'css';'xml';'class';'js' --add on 10/27/2005,modify on 1/26/2006
if($fields[3]=~/\.css/i || $fields[3]=~/\.xml/i || $fields[3]=~/\.class/i || $fields[3]=~/\.js/i ){
next;
}
# ignore records with ip has '208.215.%''172.20.%','172.30.%','206.222.%'--add on 10/27/2005,modify on 1/26/2006
if($fields[6]=~/208.215./i || $fields[6]=~/172.20./i || $fields[6]=~/172.30./i || $fields[6]=~/206.222./i ){
next;
} # modify the user id field and add cookie field
# user id
print DD @fields[0];
print DD " ".@fields[1];
print DD " ".@fields[6]; if (@fields[5]=~ /:/){
print DD " ".substr(@fields[5],0,index(@fields[5], ":"));
}
else{
print DD " -";
}
print DD " ".@fields[2];
print DD " ".@fields[3];
print DD " ".@fields[4];
print DD " ".@fields[9];
print DD " ".@fields[10];
print DD " ".@fields[7];
if (@fields[5]=~ /:/){
print DD " ".substr(@fields[5],index(@fields[5], ":")+1)
}
else{
print DD " -";
} print DD " ".@fields[8]."\n";
}要解析的log文件ex091116.txt#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2009-11-16 00:03:32
#Fields: date time cs-method cs-uri-stem cs-uri-query cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-bytes
2009-11-16 00:03:32 GET /PortAnalyzer/ETFProcess.html CustId=&CLogin=&CType= -:002125832981286193 172.28.84.50 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1) - 200 10927
2009-11-16 00:04:14 POST /PortAnalyzer/Port.aspx CustId=&CLogin=&CType= -:002125832985248623 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://us.etrade.com/e/t/mrnstar/portfolioanalyzeroverview2 200 60447
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/tab_portfolioanalyzer2.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 1749
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/DotRule_175.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 339
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACUSstocks.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 345
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/i_print2.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 945
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/i_notes.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 642
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACFStocks.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACBonds.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 345
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACCash.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/Stylebox_Valuation.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 558
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACNotC.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344要求把解析结果保存到d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt中,结果
1,如果在sql server中执行语句master..xp_cmdshell 'C:\LogParserImportData\newwin2003.pl d:\temp2\rtetrade2\iislog\2009-11-18\ex091116.txt d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt 06:00:00 23:59:59', 没有任何结果,并且执行时间没完没了,取消都取消不了。
2,如果在sql server所在的服务器中打开cmd,然后执行命令C:\LogParserImportData\newwin2003.pl d:\temp2\rtetrade2\iislog\2009-11-18\ex091116.txt d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt 06:00:00 23:59:59,就马上会打开C:\LogParserImportData\newwin2003.pl,然后也没有输出结果。请问.pl的脚本到底该怎么用?这个系统是以前遗留下来的,一直都可以用,现在我维护,换了个数据库服务器,就成这样了,不能用了。。
# the first: file name to be read
# the second: destination file name#handle window 2003 log file, original format:
#date, time, cs-method,cs-uri-stem,cs-uri-query,cs-username,c-ip,cs(user-agent),cs(referral),sc-status,sc-bytesopen (EP, $ARGV[0]);
open (DD, ">$ARGV[1]");binmode(EP);$patern="[";
for ($i=1; $i<10; $i++){
$patern=$patern.chr($i);
}
for ($i=11; $i<13; $i++){
$patern=$patern.chr($i);
}
for ($i=15; $i<32; $i++){
$patern=$patern.chr($i);
}
for ($i=127; $i<161; $i++){
$patern=$patern.chr($i);
}$patern=$patern."]";#get date from argument <servername_exyymmdd.log> add 1/26/06
$parmDate=substr($ARGV[0],length($ARGV[0])-6,2);
while (<EP>){
# remove return and newline at end, has to do this for removing ending +
s/[\r\n]//g; @fields=split(/ /);
# add the cookie field description
if (/^#/){
if (@fields[0] eq "#Version:"){
substr($_,index($_, "#Version:"))="\n#Version: 1.0\n";
} if (@fields[0] eq "#Fields:"){
substr($_,index($_, "#Fields:"))="\n#Fields: date time c-ip cs-username cs-method cs-uri-stem cs-uri-query sc-status sc-bytes cs(User-Agent) cs(Cookie) cs(Referer)\n";
}
print DD $_;
next;
} # skip
if($#fields<10){
next;
} # 11 is the total number of fields, @field[7] might be the user-agent ---09/15/2005
if (@fields[1] lt $ARGV[2] ||($#fields - $[ +1)!=11 || @fields[7] eq "-") {
# print "next";
# print $_;
next;
}
# skip records if date is previous date by chance
#get date from fileld[0], add 1/26/06
$strDate=substr($fields[0],length($fields[0])-2,2);
if ($strDate ne $parmDate) {
next;
}
if (@fields[1] ge $ARGV[3]){
print "end";
last;
} if(/$patern/){
print BB $_."\n";
next;
}
# skip records with sc-status not 304 or 200
if ( not ($fields[9] eq "304" || $fields[9] eq "200")) {
print BB $_."\n";
next;
} # skip records with cs-method not = GET or POST
if ( not ($fields[2] eq "GET" || $fields[2] eq "POST")) {
print BB $_."\n";
next;
} # ignore records with cs-uri-stem has /im/,/images/,'.gif' a image requst --add on 10/27/2005,modify on 1/26/2006
if($fields[3]=~/\/im\//i || $fields[3]=~/\/images\//i ||$fields[3]=~/\.gif/i ){
next;
}
# ignore records with cs-uri-stem has 'css';'xml';'class';'js' --add on 10/27/2005,modify on 1/26/2006
if($fields[3]=~/\.css/i || $fields[3]=~/\.xml/i || $fields[3]=~/\.class/i || $fields[3]=~/\.js/i ){
next;
}
# ignore records with ip has '208.215.%''172.20.%','172.30.%','206.222.%'--add on 10/27/2005,modify on 1/26/2006
if($fields[6]=~/208.215./i || $fields[6]=~/172.20./i || $fields[6]=~/172.30./i || $fields[6]=~/206.222./i ){
next;
} # modify the user id field and add cookie field
# user id
print DD @fields[0];
print DD " ".@fields[1];
print DD " ".@fields[6]; if (@fields[5]=~ /:/){
print DD " ".substr(@fields[5],0,index(@fields[5], ":"));
}
else{
print DD " -";
}
print DD " ".@fields[2];
print DD " ".@fields[3];
print DD " ".@fields[4];
print DD " ".@fields[9];
print DD " ".@fields[10];
print DD " ".@fields[7];
if (@fields[5]=~ /:/){
print DD " ".substr(@fields[5],index(@fields[5], ":")+1)
}
else{
print DD " -";
} print DD " ".@fields[8]."\n";
}要解析的log文件ex091116.txt#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2009-11-16 00:03:32
#Fields: date time cs-method cs-uri-stem cs-uri-query cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-bytes
2009-11-16 00:03:32 GET /PortAnalyzer/ETFProcess.html CustId=&CLogin=&CType= -:002125832981286193 172.28.84.50 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1) - 200 10927
2009-11-16 00:04:14 POST /PortAnalyzer/Port.aspx CustId=&CLogin=&CType= -:002125832985248623 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://us.etrade.com/e/t/mrnstar/portfolioanalyzeroverview2 200 60447
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/tab_portfolioanalyzer2.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 1749
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/DotRule_175.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 339
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACUSstocks.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 345
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/i_print2.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 945
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/i_notes.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 642
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACFStocks.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACBonds.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 345
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACCash.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/Stylebox_Valuation.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 558
2009-11-16 00:04:14 GET /PortAnalyzer/images/MSTAR/pm2003/ETF_ACNotC.gif - - 69.114.254.149 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-US;+rv:1.9.1.5)+Gecko/20091102+Firefox/3.5.5+(.NET+CLR+3.5.30729) https://etrade.morningstar.com/PortAnalyzer/Port.aspx 200 344要求把解析结果保存到d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt中,结果
1,如果在sql server中执行语句master..xp_cmdshell 'C:\LogParserImportData\newwin2003.pl d:\temp2\rtetrade2\iislog\2009-11-18\ex091116.txt d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt 06:00:00 23:59:59', 没有任何结果,并且执行时间没完没了,取消都取消不了。
2,如果在sql server所在的服务器中打开cmd,然后执行命令C:\LogParserImportData\newwin2003.pl d:\temp2\rtetrade2\iislog\2009-11-18\ex091116.txt d:\temp2\rtetrade2\iislog\2009-11-18\Filtered_ex091116.txt 06:00:00 23:59:59,就马上会打开C:\LogParserImportData\newwin2003.pl,然后也没有输出结果。请问.pl的脚本到底该怎么用?这个系统是以前遗留下来的,一直都可以用,现在我维护,换了个数据库服务器,就成这样了,不能用了。。
估计得用格式文件读取
如何使用BCP导出格式文件 BCP不仅可以根据表、视图导入导出数据,还可以配合格式文件对导入导出数据进行限制。格式文件以纯文本文件形式存在,分为一般格式和xml格式。用户可以手工编写格式文件,也可以通过BCP命令根据表、视图自动生成格式文件。 EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:\currency_format1.fmt -c -T' 上述命令将currency表的结构生成了一个格式文件currency_format1.fmt,下面是这个格式文件的内容。 9.0
3
1 SQLCHAR 0 6 "\t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "\r\n" 3 ModifiedDate 这个格式文件记录了这个表的字段(共3个字段)类型、长度、字符和行分割符和字段名等信息。 BCP还可以通过-x选项生成xml格式的格式文件。 EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:\currency_format2.fmt -x -c -T'
xml格式文件所描述的内容和普通格式文件所描述的内容完全一样,只是格式不同。
CREATE PROC [dbo].[CopyLogFile]
@WebServer VARCHAR(20)
,@Byname VARCHAR(20)
,@Domain VARCHAR(20)
,@UserName VARCHAR(20)
,@PassWord VARCHAR(20)
,@FileName VARCHAR(100)
,@StartTime VARCHAR(10)
,@EndTime VARCHAR(10)
,@SourcePath VARCHAR(100)
,@DestiPath VARCHAR(100)
,@PerlFile VARCHAR(100)
,@DestiDB VARCHAR(30)
,@DestiTable VARCHAR(30)
AS
/*
-- Procedure: CopyLogFile
-- Database: WebReport
-- Reference:
ExecCmdShell P
GetFileSize P
LogError U
-- Server: rtwebstatdb7
-- User: dbo
-- Purpose:
copy the log file from the given server to local,
then parse it to a new file by the perl program,
at last insert the data of the new file to database.
-- Steps:
-- Input:
@WebServer VARCHAR(20) --server name like rtproduct1
,@Byname VARCHAR(20) --byname of the server,like FSN1
,@Domain VARCHAR(20) --domain of the server
,@UserName VARCHAR(20) --username of the server
,@PassWord VARCHAR(20) --password of the username
,@FileName VARCHAR(100) --the log file name which will be copied from the server to local
,@StartTime VARCHAR(10) --the parameter of the perl program invoked in the proc
,@EndTime VARCHAR(10) --the parameter of the perl program invoked in the proc
,@SourcePath VARCHAR(100)--the path of the log file
,@DestiPath VARCHAR(100)--the path to which the log file will be copied
,@PerlFile VARCHAR(100) --the perl program
,@DestiDB VARCHAR(30) --the database of destination
,@DestiTable VARCHAR(30)--the table name of destination
-- Output: none
-- Return:
-- Failure return:
-- Success return:
-- Revisions:
-- Modify [1]: 2007-11-26,Albert Meng,Created
*/
BEGIN
SET NOCOUNT ON
DECLARE @Sql VARCHAR(8000)
,@Date VARCHAR(10)
,@FullFileName VARCHAR(100)
,@InsertStartTime DATETIME
,@InsertEndTime DATETIME
,@Error INT
,@RowCount INT
,@FileSizeMB NUMERIC(10,2)
,@identity INT
,@Retry INT
SELECT @Date = CONVERT(VARCHAR(10),GETDATE(),120)
,@DestiPath = @DestiPath +'\'+@WebServer+@SourcePath+'\'+@Date
,@FullFileName = @DestiPath + '\' + @FileName
,@SourcePath = '\\'+@WebServer+'.'+@Domain+@SourcePath
,@Retry = 15
,@Error = 1
WHILE @Error <> 0 AND @Retry >= 1
BEGIN
BEGIN TRAN
SET @Retry = @Retry - 1
--net use
SET @Sql = 'NET USE '+@SourcePath+' '+@PassWord+' /USER:'+@WebServer+'\'+@UserName
EXEC @Error = ExecCmdShell @Sql
IF @Error <> 0 AND @Retry >= 1
BEGIN
ROLLBACK TRAN
WAITFOR DELAY '00:02:00'
CONTINUE
END
/*
--check whether the source file exists
CREATE TABLE #FileExists(FileExists BIT,Directory BIT,ParentDirectoryExists BIT)
SET @Sql = @SourcePath+'\'+@FileName
INSERT #FileExists
EXEC [master]..xp_fileexist @Sql
SELECT @Error = FileExists FROM #FileExists
DROP TABLE #FileExists
IF @Error <> 1 GOTO Error
*/
--xcopy
SET @Sql = 'XCOPY '+@SourcePath+'\'+@FileName+' '+ @DestiPath + '\ /f /y /z /i'
EXEC @Error = ExecCmdShell @Sql
SET @identity = @@IDENTITY
IF @Error <> 0 AND @Retry >= 1
BEGIN
ROLLBACK TRAN
WAITFOR DELAY '00:02:00'
CONTINUE
END
COMMIT TRAN
END
IF @Error <> 0 GOTO Error
--get size of the file
EXEC GetFileSize @FullFileName,@FileSizeMB output
UPDATE LogError
SET ErrorInfo = CONVERT(VARCHAR(20),@FileSizeMB) + 'MB ' + ErrorInfo
FROM LogError WITH(rowlock)
WHERE Id = @identity
--rename *.log *.txt
SET @Sql = 'RENAME ' + @FullFileName + ' ' + REPLACE(@FileName,'.log','.txt')
EXEC @Error = ExecCmdShell @Sql
IF @Error <> 0 GOTO Error
--invoke perl program
SELECT @FileName = REPLACE(@FileName,'.log','.txt')
,@FullFileName = REPLACE(@FullFileName,'.log','.txt')
,@Sql = @PerlFile+' '+@FullFileName+' '+@DestiPath + '\Filtered_'+@FileName+' '+@StartTime+' '+@EndTime
EXEC @Error = ExecCmdShell @Sql
IF @Error <> 0 GOTO Error .............Error:
SET @Sql = 'DEL ' + @DestiPath + ' /q'
EXEC master..xp_cmdshell @Sql,no_output
SET @Sql = 'RD ' + @DestiPath + ' /q'
EXEC master..xp_cmdshell @Sql,no_output
END 调用的时候这样的EXEC CopyLogFile 'rtetrade2','RTETRADE2','morningstar.com','logreport','123456','ex091116.log','06:00:00','23:59:59','\iislog','d:\temp2','C:\LogParserImportData\newwin2003.pl','tempdb','POWEREDBYLog'
公司机密,看完删了哈。
装个perl环境啊...........否则怎么执行pl文件