quote:

---

Originally posted by laddu

I have a sql job which will execute the below stored proc.

Basically it Refresh the data by truncating the tables and using BCP to import the data.
Job was running fine but today got a error message

Invalid length parameter passed to the substring function. [SQLSTATE 42000] (Error 536). The step failed.


Any idea on this?


USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[Refresh_tables]
As

DECLARE @_error INT

declare
@FilePath varchar(1000) ,
@ArchivePath varchar(1000) ,
@FmtPath varchar(1000) ,
@FileNameMask varchar(1000) ,
@FileName varchar(1000) ,
@File varchar(1000) ,
@FmtFile varchar(1000) ,
@StartTime datetime ,
@EndTime datetime ,
@TruncateCount int,
@InsertCount int,
@cmd varchar(2000),
@tablename varchar(100)

select @FilePath = '\\xxxxxxxxxxxxxxxxxx'
select @ArchivePath = '\\xxxxxxxxxxxxxxxxxxxxxxxx'
select @fmtPath = 'xxxxxxxxxxxxxxxxxx'

select @FileNameMask = '*extract.txt'

if object_id('tempdb..#Dir') is not null
drop table #Dir

create table #Dir (s varchar(8000))
SELECT @_error = @@error

/*****************************************************************/
-- Import file
/*****************************************************************/

select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'

while exists (select * from #Dir)
begin
select @StartTime = getdate()

select @tablename = left(min(s),PATINDEX('%_extract%', min(s))-1) from #dir
select @TruncateCount = rows FROM sysindexes WHERE id = OBJECT_ID(@tablename) AND indid < 2

exec ('truncate table ' + @tablename)

select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @FmtFile = @fmtPath + @tablename + '.fmt'

select @cmd = 'bulk insert '
select @cmd = @cmd + @tablename
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FORMATFILE=''' + @FmtFile + ''''
select @cmd = @cmd + ', KEEPNULLS)'

exec (@cmd)
select @InsertCount = @@ROWCOUNT

select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
exec master..xp_cmdshell @cmd

select @EndTime = getdate()

Insert into Refresh_log
(Tablename,
FilePath,
FileName,
StartTime,
TruncateCount,
EndTime,
InsertCount)
values
(@tablename,
@FilePath,
@FileName,
@Starttime,
@TruncateCount,
@EndTime,
@InsertCount)

-- remove filename just imported
delete #Dir where s = @FileName
end

drop table #Dir

RETURN @_error

/* This is called anywhere we have an error inside the transaction */
ERROR_HANDLING:
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
select 'Error - Tables not refreshed'
END

RETURN @_error


GO

---