none
Invalid length parameter passed to the SUBSTRING function

    Question

  • 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

    Monday, February 06, 2006 6:52 PM

Answers

  • 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.

    Monday, February 06, 2006 7:13 PM
  • 
    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
     
     

    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

    Monday, February 06, 2006 10:14 PM
  • As stated above in multiple posts, you cannot take chance with invalid parameter to the LEFT string function. The SUBSTRING function usage appears to be OK.

    The following demo shows how to correct it with the NULLIF function. You can use other methods too.

    declare @row varchar(256), @reversedrow varchar(256) set @row = 'United States Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) -- United States Dollar 1.1457 set @row = 'Hungarian Forint,0.005534,0.005495,0.005526,0.005479,0.005512,0.005502,0.005479' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) -- Hungarian Forint 0.005479 set @row = 'United States Dollar 1.1457' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) /* Msg 537, Level 16, State 2, Line 12 Invalid length parameter passed to the LEFT or SUBSTRING function. */ set @row = 'United States Dollar 1.1457' set @reversedrow = REVERSE (@row) select left(@row, NULLIF(charindex(',', @row)-1,-1)), REVERSE(left(@reversedrow, NULLIF(charindex(',', @reversedrow)-1,-1))) -- NULL NULL

    NULLIF blogpost:

    http://www.sqlusa.com/bestpractices2005/nullif/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, February 06, 2010 7:54 PM
    Moderator

All replies

  • 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.

    Monday, February 06, 2006 7:13 PM
  • 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

     

    Monday, February 06, 2006 8:52 PM
  • 
    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
     
     

    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

    Monday, February 06, 2006 10:14 PM
  • 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

     


    Monday, February 06, 2006 10:57 PM
  • 
    Hi Bill,
     
    This'll be hard to troubleshoot, since I have no idea what xp_OACreate 'Msxml2.XMLHTTP.4.0' does. And I don't have SQL Server 2005, so I can't run this.
     
    However, I do have a trouble-shooting suggestion: add some PRINT (or SELECT) statements to your main procedure to see what happens, what exact data is being returned from the calls to sp_OAMethod and what steps are taken during the string parsing process.
     
    Something like this:
     
    CREATE PROCEDURE uspExtractExchangeRate
    AS
    BEGIN
    set nocount on
    declare @currency varchar(100), @rate decimal(12,6), @pos int, @len int
    declare @doc varchar(8000), @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(8000))
    begin try
    insert #TodayExchangeRate
    exec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv'
    select @doc = response from #TodayExchangeRate
    SELECT @doc AS 'After uspGetXMLFromHTTP'
    drop table #TodayExchangeRate
     
    select @pos = charindex('United States Dollar', @doc)
    select @len = len(@doc) - @pos
    select @doc = substring(@doc, @pos, @len)
    SELECT @doc AS 'After stripping'
    --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 @i, @j, @row, @reversedrow
    select @currency = left(@row, charindex(',', @row)-1)
    select @rate = REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1))
    SELECT @currency, @rate
    insert @table(Currency, Rate)
    select @currency, @rate
    select @i = @j + 2
    end
    SELECT @i, @j, 'Completely done'
    end try
    begin catch
    --exec uspReportSQLError
    end catch
     
    -- save into exchangeRates table
    set nocount off
    END
    GO
    Checking the output of this debug-enabled version of the proc might reveal what's going on.
     
    --
    Hugo Kornelis, SQL Server MVP
     
    ----- Original Message -----
    Newsgroups: microsoft.private.forums.msdn.sqlserver.tsql
    Sent: Monday, February 06, 2006 11:59 PM
    Subject: Re: Invalid length parameter passed to the SUBSTRING function

    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


     

    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


    Tuesday, February 07, 2006 10:07 PM
  • 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.
    Wednesday, February 08, 2006 12:24 AM
  • Thanks Hugo.

    Bill

    Wednesday, February 08, 2006 5:04 AM
  • 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

    Wednesday, February 08, 2006 5:16 AM
  • 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?
    Thursday, February 09, 2006 6:57 PM
  • 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

    Thursday, February 09, 2006 10:48 PM
  • The Left() function errors out when you try to take the left negative number of a string. In SQL 2000 it automatically converted the negative number to zero because you can't take the left negative number of any string but SQL 2005 maintains the negative number and then errors out with "Invalid length parameter passed to the SUBSTRING function." It is invalid to have the left negative number of a string but the least MS could have done was bring forth what was being accepted in SQL 2000 for the same function. A solution would be to check to see if your length is < 0 and if it is make it zero because the Left(somestring, 0) returns an empty string and does not error.
    Monday, February 01, 2010 8:50 PM
  • As stated above in multiple posts, you cannot take chance with invalid parameter to the LEFT string function. The SUBSTRING function usage appears to be OK.

    The following demo shows how to correct it with the NULLIF function. You can use other methods too.

    declare @row varchar(256), @reversedrow varchar(256) set @row = 'United States Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) -- United States Dollar 1.1457 set @row = 'Hungarian Forint,0.005534,0.005495,0.005526,0.005479,0.005512,0.005502,0.005479' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) -- Hungarian Forint 0.005479 set @row = 'United States Dollar 1.1457' set @reversedrow = REVERSE (@row) select left(@row, charindex(',', @row)-1), REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1)) /* Msg 537, Level 16, State 2, Line 12 Invalid length parameter passed to the LEFT or SUBSTRING function. */ set @row = 'United States Dollar 1.1457' set @reversedrow = REVERSE (@row) select left(@row, NULLIF(charindex(',', @row)-1,-1)), REVERSE(left(@reversedrow, NULLIF(charindex(',', @reversedrow)-1,-1))) -- NULL NULL

    NULLIF blogpost:

    http://www.sqlusa.com/bestpractices2005/nullif/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, February 06, 2010 7:54 PM
    Moderator
  • I know this is old but there is something a little easier you can try. Chances are your CharIndex is returning a negative value because the string you are running it againsts is null. Try just putting the Charindex in an Absolut function. That way it will always be possitive. Depending how your data is entered it should do the trick.

    Example:

    ABS(CHARINDEX(',',@Row,1)-1))

    Monday, October 18, 2010 9:35 PM
  • NickTT -

    Thank you so much for taking time to post. I just moved our site to CF9, SQL 2008 R2, and Win Server 2008 and was experiencing this issue. Adding ABS solved the problem. I needed to grab everything before an underscore and using this worked:

    LEFT(NavTitle.titleName, ABS(PATINDEX('%[_]%', NavTitle.titleName) - 1)) AS title

    Tuesday, November 09, 2010 9:11 PM
  • I had a similar situation with a couple of deserializing functions and the ABS trick worked like a charm.

    /************************************************************************************************** 
    Example:  
    SELECT * 
    FROM util.CSVToVarChar('ID1,ID23,ID5')
    WHERE String > 'ID1'
    ***************************************************************************************************/ 
    CREATE FUNCTION util.CSVToVarChar ( 
     --Limiting the size to 8000 instead of MAX because a value has to be passed to the util.Numbers function 
     --and this seems more than enough for filtering purposes. 
     @CSVList VARCHAR(8000) 
    ) 
    RETURNS TABLE 
    AS 
     RETURN SELECT String = CAST(SUBSTRING(',' + @CSVList + ',', N.Number + 1, CHARINDEX(',', ',' + @CSVList + ',', N.Number + 1) - N.Number - 1) AS VARCHAR(MAX)) 
      FROM util.Numbers(1, 8000) AS N 
      WHERE SUBSTRING( ',' + @CSVList + ',', N.Number, 1 ) = ',' 
       AND N.Number < LEN(',' + @CSVList + ','); 
    
    The above function would work great as long as you don't filter on the calling query. If you do like in the example above, it will return values but also an error. Wrapping the call to CHARINDEX with an ABS did the trick with no apparent impact on performance.

    Friday, March 25, 2011 11:54 AM
  • Amazingly simple but effective solution! Thank You!
    Tuesday, September 18, 2012 4:59 PM