April 4, 2011 at 12:55 pm
Hi
I'm not sure what to call this error --- it could just be me --- .:doze:
I have tried running the ffg scripts in SQL Server 2005.
select i.imp_code as 'Import Number',convert(nvarchar(10),imp_date_created,103) as 'Import Date', right(imp_original_file_name, charindex('_rof', reverse (imp_original_file_name), 0)-1) as 'File Name', count(1) as [Accounts]
--select top 10 *
from acc_account a with (nolock)
join imp_import i with (nolock)
on a.imp_code=i.imp_code
where a.bck_id = 119
and a.date_taken_on between '2011-03-01 08:00:00' and '2011-05-01 08:00:00'
group by i.imp_code, convert(nvarchar(10),imp_date_created,103), right(imp_original_file_name, charindex('_rof', reverse (imp_original_file_name), 0)-1)
order by 1
select convert(nvarchar(10),imp_date_created,103) as 'Import Date',i.imp_code as 'Import Number', right(imp_original_file_name, charindex('_rof', reverse (imp_original_file_name), 0)-1) as 'File Name', count(1) as [Accounts]
--select top 10 *
from acc_account a with (nolock)
join imp_import i with (nolock)
on a.imp_code=i.imp_code
where a.bck_id = 119
and a.date_taken_on between '2011-03-01 08:00:00' and '2011-05-01 08:00:00'
group by convert(nvarchar(10),imp_date_created,103),i.imp_code, right(imp_original_file_name, charindex('_rof', reverse (imp_original_file_name), 0)-1)
order by 1
with the first script i get the following error:
Msg 536, Level 16, State 2, Line 1
Invalid length parameter passed to the RIGHT function.
but the second script runs fine.
The only difference between the scripts (that I can see) is the position of the 'Import Date' column.
Here's a sample of what the output of the second statement:
Import Date Import Number File Name Accounts
01/03/2011 3318 TALKTALK_MEDIA_250211_STAGE_3.TXT 116
01/03/2011 3320 TALKTALK_MEDIA_STAGE_3.TXT 92
01/03/2011 3316 220211 STAGE3.TXT 92
01/03/2011 3317 TALKTALK_MEDIA_240211_STAGE_3.TXT 109
01/03/2011 3319 TALKTALK_MEDIA_260211_STAGE_3.TXT 116
01/04/2011 3385 BBERROR(CABLE)010411.TXT 808
03/03/2011 3332 TALKTALK_MEDIA_010311_STAGE_3.TXT 59
03/03/2011 3333 TALKTALK_MEDIA_280211_STAGE_3.TXT 79
03/03/2011 3334 BB_ERROR.TXT 1085
04/03/2011 3340 DATARETURN_CHRISREED_AC.TXT 5000
Not sure how much detail I can post here due to it being work data.
Would there be a problem with the order in which the columns are being referenced or have I done something silly?:blush:
April 5, 2011 at 1:36 am
Without seeing the input data, this is just a guess...
Your RIGHT statement is looking for "for_" (_rof reversed) in the import file name, and I'd bet that one of the file names doesn't have this string in the file name.
April 5, 2011 at 7:37 am
Thanks for that but I've had that error before.
I would have thought that would be the case. If it were, wouldnt the second script not run as well?
As you can see, the dates have not been changed, nor have I changed any of the table joins.
Still, if it were one of the filenames, then what is it in the second script that allows it to run with no errors?
Has anyone else had a similar issue?
April 5, 2011 at 7:41 am
Oh and 1 more thing....
If I leave out the column
convert(nvarchar(10),imp_date_created,103) as 'Import Date'
both scripts run fine.
Its only once I added in this column that I had the error. After I moved the position of the column the script worked fine.
April 5, 2011 at 8:13 am
I've no idea why one script runs, and the other doesn't. As you say, the only difference is a column that has nothing to do with the RIGHT function.
Are you sure you're running both scripts on the same data?
Are you running it against a moving target (you are using nolock)?
Having said that, the error message is pretty clear. The problem is with the length passed to the RIGHT function. The only way you can get that error is if you pass it a negative number, and the only way you can pass it a negative number is if your CHARINDEX returns 0.
I think you need break down the query, running it without the RIGHT function, but leaving in the CHARINDEX etc, then look for the row that returns a CHARINDEX of 0.
Without table definitions and sample data that causes the problem, it's difficult to offer much more help.
April 5, 2011 at 12:38 pm
Thanks for trying.
I thought someone else might have had the same problem. I ran the scripts at the same time and the data in the tables doesnt change often. The nolock is only for applications that might be reading from the data, but more out of habit.
I tried the scripts again today and its the same, but I've got the report that I wanted.
Thanks again. I would post table structures and the data if it were my own project but since this is work data....
April 5, 2011 at 1:14 pm
Do you have a test system where you can adjust the data the query should return? I'd be curious to know if it's a specific record (or subset of records) that's causing the error or if it's all of them - might shed some light.
Leonard
Madison, WI
April 6, 2011 at 11:15 am
I did a search on the entire table and yes there are a few records right at the top that dont include the 'for_' string. And both scripts do work on another database we have.:Whistling:
Whats baffling is that moving the position of the column has 'fixed' the error.Thats what threw me off. :pinch:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply