On Error Next, continue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

On Error Next, continue

Author

Topic

texasweb
Starting Member

11 Posts

Posted - 2007-08-02 : 08:03:54

My Stored Procedure just crashes, i need it to move past the error.
I take 55 files per day and take 3 fields from a large csv file to record rain amounts for each building. Here is the sproc
ALTER PROCEDURE [dbo].[spBuildingTools_TexasCleanWater_Job_Import] (
@intModNr int = '0'
)
as

DECLARE @intReturnCode int -- Error Messages
DECLARE @strFTP_SVR_NA char(20) -- FTP Server Name
DECLARE @strFTP_DIR_NA varchar(200) -- First part of FTP Directory
DECLARE @strFTP_FIL_NA varchar(50) -- FTP file name
DECLARE @strFTP_FIL_EXT char(4) -- FTP file extension
DECLARE @strFTP_ID char(20) -- Login ID for FTP Server
DECLARE @strFTP_PW char(20) -- Password for FTP Server
DECLARE @strLCL_DIR_NA varchar(200) -- Local Directory Name on the SQL Server
DECLARE @strLCL_DIR_NAA varchar(200) -- Local Directory for the Loc_Sys_Nr
DECLARE @strDate char(8) -- Date in string format
DECLARE @strString varchar(200) -- Used for the CMD Shell directory checking to see if the directory is there.
DECLARE @intResult int -- Runs the cmd shell dir command
DECLARE @strFTP_URL_ADR VARCHAR(500)
DECLARE @strInsSQL VARCHAR(2000)
DECLARE @strFTP_DIR_NA2 char(50)--Dynamic part of address, loc_sys_nr
DECLARE @X INT -- Counter
DECLARE @Y INT -- Counting from 1 to @X incremented by 1

SET @strFTP_SVR_NA = '153.2.133.183'
SET @strFTP_DIR_NA = 'SECURED\UPSAPPS\DENG0183\TEXASCLEANWATER\'
SET @strFTP_FIL_NA = 'DBASE.CSV'
SET @strFTP_ID = 'APPS_DENG0183_TCW'
SET @strFTP_PW = '0183_TCW'
SET @strLCL_DIR_NAA = 'd:\UPSDATA\DENG0183\TEXASCLEANWATER'
SET @strString = 'del ' + rtrim(@strLCL_DIR_NA) + '\*.* /S/Q'
EXEC @intResult = master..xp_cmdshell @strString, no_output
SET @X = (SELECT count(DISTINCT(LOC_SYS_NR)) FROM TTCWPCS) --gets record count or slics
SET @Y = '0' --Base for counting up to @X

declare @intErrorCode int
set @intErrorCode = @@error

--First statement, (WHILE) starts a loop that will get each center that is in the ttcwpcs table. These buildings are the operational buildings in the state of Texas.
TRUNCATE TABLE DENG0183.dbo.TTCWREC

WHILE @Y <> @X --Start of Loop

BEGIN
SET @Y = @Y + 1
SET @strFTP_DIR_NA2 =(SELECT LOC_SYS_NR
FROM TTCWPCS
WHERE UNQ_ID_NR=@Y)

SET @strFTP_URL_ADR = @strFTP_DIR_NA+rtrim(@strFTP_DIR_NA2)
SET @strLCL_DIR_NA = @strLCL_DIR_NAA+'\'+ rtrim(@strFTP_DIR_NA2)

SET @strString = 'dir ' + rtrim(@strLCL_DIR_NA)
EXEC @intResult = master..xp_cmdshell @strString, no_output

IF (@intResult <> 0)
BEGIN

SET @strString = 'md ' + rtrim(@strLCL_DIR_NA)
EXEC @intResult = master..xp_cmdshell @strString, no_output
END

SET @strString = 'echo open '+rtrim(@strFTP_SVR_NA)+' > '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo user '+rtrim(@strFTP_ID)+' '+rtrim(@strFTP_PW)+'>> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo hash >> '+rtrim(@strLCL_DIR_NA)+'script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo lcd '+rtrim(@strLCL_DIR_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo cd '+rtrim(@strFTP_URL_ADR)+' >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo get '+rtrim(@strFTP_FIL_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo bye >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'ftp -v -n -s:'+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @intReturnCode = @intResult

IF (@intReturnCode<>0)
IF (@intResult<>0)
BEGIN
SET @strString = 'echo cd \\ >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo cd '+@strFTP_DIR_NA+' >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo get '+rtrim(@strFTP_FIL_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'echo bye >> '+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'ftp -v -n -s:'+rtrim(@strLCL_DIR_NA)+'\script.ftp'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'dir '+rtrim(@strLCL_DIR_NA)+ '\' + rtrim(@strFTP_FIL_NA)+''
EXEC @intResult = master..xp_cmdshell @strString, no_output
END

SET @strString = 'del '+rtrim(@strLCL_DIR_NA)+'\script.ftp /S/Q'
EXEC @intResult = master..xp_cmdshell @strString, no_output

DECLARE @strBISQL varchar(300)

if (@intReturnCode = 0)
begin
set @strBISQL=''
set @strBISQL=@strBISQL+'BULK INSERT DENG0183.dbo.ttcwraw_data '
set @strBISQL=@strBISQL+'FROM ''' +rtrim(@strLCL_DIR_NA) + '\dbase.csv'''
set @strBISQL=@strBISQL+' WITH '
set @strBISQL=@strBISQL+'('
set @strBISQL=@strBISQL+'DATAFILETYPE = ''CHAR'', FIRSTROW = 1, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'''
set @strBISQL=@strBISQL+') '
exec(@strBISQL)
IF (@@error<>0) set @intReturnCode = 1
if @@error <>0 goto err_handle
end

if (@intReturnCode = 0)
begin
set @strINSSql=''
print @@error
set @strINSSql=@strINSSql+'INSERT INTO DBO.TTCWREC(LOC_SYS_NR, PCP_AMT_NR,PCP_TM, PCP_DT) '
set @strINSSql=@strINSSql+'SELECT LOC_SYS_NR = '+@strFTP_DIR_NA2+' '
set @strINSSql=@strINSSql+',RAIN AS PCP_AMT_NR '
set @strINSSql=@strINSSql+',CAST(SUBSTRING(COL001,9,2) + '':'' + SUBSTRING(COL001,11,2) + '':00'' AS DATETIME)AS PCP_TM '
set @strINSSql=@strINSSql+',CAST(SUBSTRING(COL001,1,8)AS SMALLDATETIME)AS PCP_DT '
set @strINSSql=@strINSSql+'FROM dbo.ttcwraw_data '
exec(@strINSSql)

IF (@@error<>0)goto err_handle

end

TRUNCATE TABLE DENG0183.DBO.TTCWRAW_DATA

SET @strString = 'del '+rtrim(@strLCL_DIR_NA)+'\script.ftp /S/Q'
EXEC @intResult = master..xp_cmdshell @strString, no_output

SET @strString = 'del '+rtrim(@strLCL_DIR_NA)+'\dbase.csv.ftp /S/Q'
EXEC @intResult = master..xp_cmdshell @strString, no_output
SET @strString = 'del '+rtrim(@strFTP_DIR_NA)+'\script.ftp /S/Q'
EXEC @intResult = master..xp_cmdshell @strString, no_output

err_handle:
IF @Y = @X
BREAK
ELSE CONTINUE
END

-----------
I use a third party software to create the csv file and it is very hap hazard in creating dates or adding an extra field or two on a whim.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-08-02 : 20:41:23

Excuse me sir, why not do it in your presentation layer? I guess you might not need a third party tool. IMHO

Want Philippines to become 1st World COuntry? Go for World War 3...

mattyblah
Starting Member

49 Posts

Posted - 2007-08-02 : 22:46:12

have you looked into dts? this would alleviate a lot of this, and simplify things greatly.

texasweb
Starting Member

11 Posts

Posted - 2007-08-03 : 09:21:47

matt, dts is out due to limitations placed by support group. we run this a scheduled job.

texasweb
Starting Member

11 Posts

Posted - 2007-08-03 : 09:22:25

Jonas, I do not follow, do what in the presntation layer?

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-08-03 : 14:08:22

quote:

IF (@@error<>0) set @intReturnCode = 1
if @@error <>0 goto err_handle

1. You are using @@Error directly in consecutive statements. You should get the error into a variable and check because @@Error will return the error of last statement.

2. I see that you are already using 'Continue' to restart the loop. What else you want to do when you mean that 'move past the error'.
Do you want to process the remaining logic for the same iteration? Have you thought about the data inconsistency that may arise?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources