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 DateImport NumberFile NameAccounts
01/03/20113318TALKTALK_MEDIA_250211_STAGE_3.TXT116
01/03/20113320TALKTALK_MEDIA_STAGE_3.TXT 92
01/03/20113316220211 STAGE3.TXT92
01/03/20113317TALKTALK_MEDIA_240211_STAGE_3.TXT109
01/03/20113319TALKTALK_MEDIA_260211_STAGE_3.TXT116
01/04/20113385BBERROR(CABLE)010411.TXT808
03/03/20113332TALKTALK_MEDIA_010311_STAGE_3.TXT59
03/03/20113333TALKTALK_MEDIA_280211_STAGE_3.TXT79
03/03/20113334BB_ERROR.TXT1085
04/03/20113340DATARETURN_CHRISREED_AC.TXT5000
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