SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Invalid length parameter passed to the substring f
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
328 Posts

Posted - 03/03/2014 :  13:48:31  Show Profile  Reply with Quote
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


maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/03/2014 :  14:13:29  Show Profile  Reply with Quote
It seems the problem is with the line in RED.
If PATINDEX returns 0 value then it would be 0 -1 = -1, which is invalid parameter for LEFT function.


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






!_(M)_!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2014 :  14:53:11  Show Profile  Reply with Quote
change it to this
left(min(s),PATINDEX('%_extract%', min(s)+'_extract')-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000