Hi all,
I am having a weird issue after we upgraded our DB server to SQL 2005.
I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005.
The error statement pointed to:
select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))
The error message is:
Invalid length parameter passed to the SUBSTRING function.
Anyone knows what's the difference for LEFT function between sql 2000 and 2005?
Thanks
Bill
I would imagine CHARINDEX is either returning a NULL or a 0 (see below). It may be better storing the resulting of the CHARINDEX in a variable before running the query (if it is the same for all values), or testing the value returned before doing the left. Or use ISNULL if it is returning NULL. I could not find any documentation suggesting differences between the function in 2000 vs 2005. Has your database compatibility level changed?
Clarity Consulting (www.claritycon.com)
http://blogs.claritycon.com/blogs/the_englishman/default.aspx
Clarity Consulting (www.claritycon.com)
CHARINDEX link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_left_7910.asp
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Arguments
expression1
Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.
Return Types
int
Remarks
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or later. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.
If expression1 is not found within expression2, CHARINDEX returns 0.
|||Hi Shughes,
Thanks for your response.
The problem was not caused by NULL or 0. I set up trace and found that it actually caused by another statement.
-- select @.pos = charindex('United States Dollar', @.sourcedoc)
-- select @.len = len(@.sourcedoc) - @.pos
select @.doc = substring(@.sourcedoc, @.pos, @.len)
-- exec spReportSQLError 'Tracing', @.doc
It seems this statement will generate different result running in SQL job or in management studio.
Here is the trace results:
Error Details:(from SQL Job: wrong data)
-
Error Date: Feb 6 2006 12:43PM
Error Number: 0
Error Severity: 0
Error State: 0
Error Procedure: None
Error Line: 0
Error Message: No details info.
Other Info: # The daily noon exchange rates for major foreign currencies are published every business day at about 1 # p.m. EST. They are obtained from market or official sources around noon, and show the rates for the # various currencies in Canadian dollars converted from US dollars. The rates are nominal quotations - # neither buying nor selling rates - and are intended for statistical or analytical purposes. Rates # available from financial institutions will differ.
#
Date (<m>/<d>/<year>),01/27/2006,01/30/
Error Details(Run in management studio(Correct data)):
Error Date: Feb 6 2006 12:43PM
Error Number: 0
Error Severity: 0
Error State: 0
Error Procedure: None
Error Line: 0
Error Message: No details info.
Other Info: United States Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457
Argentine Peso (Floating Rate),0.3733,0.3729,0.3725,0.3712,0.3716,0.3729,0.3727
Australian Dollar,0.8624,0.8577,0.8660,0.8598,0.8628,0.8591,0.8507
Bahamian Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457
Brazilian Real,0.5201,0.5181,0.5174,0.5138,0.5132,0.5171,0.5242
Chilean Peso,0.002178,0.002183,0.002173,0.002158,0.002156,0.002171,0.002183
Chinese Renminbi,0.1423,0.1419,0.1419,0.1414,0.1418,0.1423,0.1422
Colombian Peso,0.000506,0.000505,0.000504,0.000503,0.000505,0.000507,0.000507
Croatian Kuna,0.1893,0.1882,0.1895,0.1880,0.1886,0.1883,0.1870
Czech. Republic Koruna,0.04920,0.04875,0.04907,0.04843,0.04849,0.04848,0.04844
Danish Krone,0.1865,0.1854,0.1863,0.1847,0.1853,0.1847,0.1836
East Caribbean Dollar,0.4265,0.4253,0.4252,0.4238,0.4249,0.4264,0.4259
European EURO,1.3919,1.3836,1.3906,1.3786,1.3833,1.3786,1.3713
Fiji Dollar,0.6689,0.6676,0.6640,0.6647,0.6643,0.6688,0.6687
African Financial Community Franc (CFA),0.002122,0.002109,0.002120,0.002102,0.002109,0.002102,0.002091
Pacific Financial Community Franc (CFP),0.01166,0.01159,0.01165,0.01155,0.01159,0.01155,0.01149
Ghanaian Cedi,0.000126,0.000126,0.000126,0.000125,0.000125,0.000126,0.000126
Guatemala Quetzal,0.15058,0.15017,0.15036,0.14988,0.15027,0.15079,0.15060
Honduran Lempira,0.06073,0.06056,0.06054,0.06034,0.06050,0.06071,0.06064
Hong Kong Dollar,0.147927,0.147512,0.147461,0.146989,0.147382,0.147865,0.147674
Hungarian Forint,0.005534,0.005495,0.005526,0.005479,0.005512,0.005502,0.005479
Icelandic Krona,0.01851,0.01840,0.01833,0.01806,0.01812,0.01816,0.01816
Indian Rupee,0.02607,0.02598,0.02602,0.02583,0.02588,0.02598,0.02595
Indonesian Rupiah,0.000122,0.000122,0.000122,0.000122,0.000122,0.000123,0.000124
Israeli New Shekel,0.2476,0.2463,0.2450,0.2443,0.2439,0.2441,0.2435
Jamaican dollar,0.01784,0.01780,0.01781,0.01869,0.01874,0.01783,0.01780
Japanese Yen,0.009793,0.009735,0.009785,0.009674,0.009665,0.009643,0.009632
Malaysian Ringgit,0.3059,0.3051,0.3050,0.3040,0.3046,0.3063,0.3064
Mexican Peso,0.1097,0.1096,0.1095,0.1093,0.1090,0.1093,0.1095
Moroccan Dirham,0.1273,0.1267,0.1271,0.1259,0.1265,0.1265,0.1259
Myanmar (Burma) Kyat,0.1954,0.1942,0.1943,0.1937,0.1937,0.1944,0.1934
Neth. Antilles Guilder,0.6446,0.6429,0.6426,0.6406,0.6422,0.6444,0.6437
New Zealand Dollar,0.7837,0.7802,0.7840,0.7816,0.7879,0.7878,0.7805
Norwegian Krona,0.1722,0.1700,0.1719,0.1707,0.1721,0.1714,0.1704
Pakistan Rupee,0.01917,0.01907,0.01911,0.01904,0.01909,0.01917,0.01914
Panamanian Balboa,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457
Peruvian New Sol,0.3460,0.3458,0.3450,0.3449,0.3452,0.3479,0.3484
Philippine Peso,0.02187,0.02181,0.02193,0.02190,0.02196,0.02208,0.02214
Polish Zloty,0.3641,0.3623,0.3641,0.3609,0.3615,0.3605,0.3590
Russian Rouble,0.04098,0.04066,0.04068,0.04051,0.04063,0.04062,0.04056
Singapore Dollar,0.7060,0.7019,0.7050,0.6998,0.7001,0.7015,0.7038
Slovak Koruna,0.03725,0.03706,0.03721,0.03696,0.03702,0.03693,0.03671
Slovenian Tolar,0.005809,0.005778,0.005805,0.005754,0.005775,0.005760,0.005727
South African Rand,0.1867,0.1862,0.1879,0.1864,0.1878,0.1880,0.1874
South Korean Won,0.001182,0.001179,0.001190,0.001185,0.001176,0.001182,0.001190
Sri Lanka Rupee,0.01123,0.01120,0.01120,0.01117,0.01119,0.01123,0.01123
Swedish Krona,0.1507,0.1498,0.1504,0.1491,0.1489,0.1487,0.1474
Swiss Franc,0.8967,0.8894,0.8945,0.8878,0.8900,0.8861,0.8806
Taiwanese New Dollar,0.03588,0.03578,0.03577,0.03565,0.03575,0.03574,0.03570
Thai Baht,0.02941,0.02926,0.02940,0.02904,0.02903,0.02910,0.02906
Trinidad & Tobago Dollar,0.1833,0.1831,0.1845,0.1823,0.1823,0.1840,0.1835
Tunisian Dinar,0.8578,0.8524,0.8570,0.8488,0.8510,0.8500,0.8468
New Turkish Lira,0.8647,0.8643,0.8651,0.8612,0.8634,0.8664,0.8624
Pound Sterling,2.0342,2.0240,2.0377,2.0269,2.0357,2.0213,2.0006
Venezuelan Bolivar,0.000534,0.000533,0.000533,0.000531,0.000532,0.000534,0.000534
I am still trying to figure out how this was caused.
Thanks
Bill
|||? Hi Bill, It's hard to know without knowing the complete code and the data it operates on. But I suspect that this was a potential bug in your code all along, and you just were lucky until now. The SQL Server query optimizer is free to reorganize your query and evaulate it in any order it wants. That can give you great performance benefits - but it may also cause unexpected bugs. I'll illustrate this with the example you posted in your first post (yes, I did see the follow-up, but this code takes a lot less typing <g>). Have a look at this query: SELECT LEFT(MyColumn, CHARINDEX(',', MyColumn) - 1) FROM MyTable WHERE CHARINDEX(',', MyColumn) > 0 You might be inclined to say that this is safe - after all, the WHERE will exclude all rows without a comma, and the LEFT function will evaluate fine for the remaining rows. Right? WRONG!!!! The optimizer is free to evaluate the query in any order it sees fit. So it might decide to do the SELECT first, then use the WHERE to filter the results. And BOOM!! you get an error for the first row with no comma in MyColumn. What might have happened is that in your real query, which is probably more complex than the example above, a new optimizing technique (that was not available to the SQL Server 2000 query optimizer) was chosen to evaluate your query, leading to this result. -- Hugo Kornelis, SQL Server MVP <Bill YU@.discussions.microsoft.com> schreef in bericht news:7d07a949-c4d2-4fa9-af97-bad706de680d@.discussions.microsoft.com... Hi all, I am having a weird issue after we upgraded our DB server to SQL 2005. I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005. The error statement pointed to: select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1)) The error message is: Invalid length parameter passed to the SUBSTRING function. Anyone knows what's the difference for LEFT function between sql 2000 and 2005? Thanks Bill|||
Hi Hugo,
Here are my 3 SPs:
CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')
AS
BEGIN
set nocount on
declare @.objRef int,@.resultcode int
exec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT
if @.resultcode = 0
begin
exec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False
exec @.resultcode = sp_OAMethod @.objRef, 'Send',null
execute sp_OAGetProperty @.objRef, 'responseText'
end
exec sp_OADestroy @.objRef
END
GO
CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)
AS
BEGIN
set nocount on
declare @.cc varchar(250), @.bcc varchar(250)
declare @.errormsg varchar(Max), @.subject varchar(250)
select @.cc = '',
@.bcc = '',
@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'
+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')
select @.errormsg = 'Error Details:' + char(13) + char(10)
+ '-' + char(13) + char(10)
+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10)
+ ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)
+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)
+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)
+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)
+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)
+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)
+ isnull((' Other Info: ' + @.TraceInfo), '')
-- Insert central log table
exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',
@.recipients = 'dba@.builddirect.com',
@.copy_recipients = @.cc,
@.blind_copy_recipients = @.bcc,
@.body = @.errormsg,
@.subject = @.subject,
@.importance = 'High',
@.body_format = 'Text'
set nocount off
END
GO
CREATE PROCEDURE uspExtractExchangeRate
AS
BEGIN
set nocount on
declare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len int
declare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j int
declare @.table table(Currency varchar(100), Rate decimal(12,6))
create table #TodayExchangeRate(response varchar(MAX))
begin try
insert #TodayExchangeRate
exec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv'
select @.doc = response from #TodayExchangeRate
drop table #TodayExchangeRate
select @.pos = charindex('United States Dollar', @.doc)
select @.len = len(@.doc) - @.pos
select @.doc = substring(@.doc, @.pos, @.len)
--exec uspReportSQLError 'Tracing', @.doc
select @.i = 1, @.j = -1
while (1=1)
begin
select @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)
if @.j = 0 break
select @.row = substring(@.doc, @.i, @.j - @.i)
select @.reversedrow = REVERSE(@.row)
select @.currency = left(@.row, charindex(',', @.row)-1)
select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))
insert @.table(Currency, Rate)
select @.currency, @.rate
select @.i = @.j + 2
end
end try
begin catch
--exec uspReportSQLError
end catch
-- save into exchangeRates table
set nocount off
END
GO
Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional.
Hope this help.
Thanks
Bill
Why are you using SQL to parse the comma-separated string? This is much more easier to do on the client. You can do any of the following:
1. You can replace the SP with a DTS or SSIS package that imports the CSV information to a table
2. Or you can take the CSV file and import using BCP
3. Or use an ActiveX task from SQLAgent
Any of these approaches will be much more robust and simpler. OLE automation SPs should generally be avoided due to their overhead in using on the server and since you are running this from a job anyway it is better to isolate the process from server.
Btw, the error is probably due to bad data or incorrectly formed row.
|||Thanks Hugo.
Bill
|||Hi Umachandar,
This is a legacy task, I run this job from Internal DB server not production, and I did not want to develop and maintain any codes other than sql scripts.
I just happened to have this problem, seems SQL 2005 has a different behavior here.
Thanks
Bill
|||You will have to post a repro script to determine if this is a bug in SQL Server 2005 or not. Otherwise it is hard to tell by just looking at the code since this can be due to bad input. Does the same code run fine in SQL Server 2000 with the same input?|||Yes,
I run this job for more than two years, never had a problem.
The issue happened after we upgraded to SQL 2005.
Interestingly, if I assign the CSV text to a variable from TEMP table, it works fine.
Now, I have to manually execute the same SP from Management Studio daily.
Bill
No comments:
Post a Comment