would someone else try this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Holmes - 24 Jul 2008 14:38 GMT

I am getting an error i don't understand. I would like for someone else to try and repeat it if possible. I am on
Sql2005sp2.

The goal is to take a string and split it based on a delimiter. The following is the split function. You may recognize
the form. I found it from this NG but i don't remember where. tblCalendar is my source of numbers so don't let that
throw you.

CREATE FUNCTION [dbo].[split](@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (ORDER BY Id)-1 idx,
SUBSTRING(@text, Id, CAST(CHARINDEX(@delimiter, @text + @delimiter, Id) - CAST(Id AS BIGINT) AS INT)) value
FROM tblCalendar
WHERE Id <= CONVERT(INT, LEN(@text)) AND SUBSTRING(@delimiter + @text, Id, 1) = @delimiter
)

This exercises the above function:
SELECT * FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')

The following causes this error:
Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value 'æˆrž]Q...........' to data type int.

declare @VehicleID INT
DECLARE vehicle_field_cursor CURSOR
FOR
SELECT Value
FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')
OPEN vehicle_field_cursor
FETCH NEXT FROM vehicle_field_cursor INTO @VehicleID
print @VehicleID
CLOSE vehicle_field_cursor
Deallocate vehicle_field_cursor

I have another version of split that is very procedural that has the same signature as this one and it runs quite well.
I have had to revert to it in my code because of this error.

I would greatly appreciate it if someone would take the time to see if this error happens for them as well. And if
possible find some explanation and/or remedy.

thanks

dan

Reply to this Message

Plamen Ratchev - 24 Jul 2008 14:51 GMT

This works just fine on SQL Server 2005 SP2 (64). Check if your table
tblCalendar is a good source for numbers.

Here is what I used for the test:

CREATE TABLE tblCalendar (id INT PRIMARY KEY);

INSERT INTO tblCalendar (id)
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 1000;

GO

CREATE FUNCTION [dbo].[split](@text VARCHAR(MAX), @delimiter VARCHAR(20) = '
')
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (ORDER BY Id)-1 idx,
SUBSTRING(@text, Id, CAST(CHARINDEX(@delimiter, @text + @delimiter,
Id) - CAST(Id AS BIGINT) AS INT)) value
FROM tblCalendar
WHERE Id <= CONVERT(INT, LEN(@text)) AND SUBSTRING(@delimiter +
@text, Id, 1) = @delimiter
)

GO

SELECT * FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00
AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00 AM,07/25/2008 01:30:00
PM|0,0|,,|1', '|')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Dan Holmes - 24 Jul 2008 15:01 GMT

Did you try the cursor code too? The split code works for me it is the cursor that fails.

I am on Sql 32bit. Perhaps that makes a difference.

Here is version info

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
    Feb 26 2008 18:15:01
    Copyright (c) 1988-2005 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

> This works just fine on SQL Server 2005 SP2 (64). Check if your table
> tblCalendar is a good source for numbers.
[quoted text clipped - 36 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 24 Jul 2008 15:34 GMT

Yes, and it works fine, prints the number 341. Tested on both 64 and 32 bit,
no issues.

Microsoft SQL Server 2005 - 9.00.3068.00 (X64)
Feb 26 2008 23:02:54
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Dan Holmes - 24 Jul 2008 16:52 GMT

> Yes, and it works fine, prints the number 341. Tested on both 64 and 32
> bit, no issues.
[quoted text clipped - 13 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

The only thing i notice is your windows is different than mine.

Reply to this Message

Daniel Crichton - 24 Jul 2008 16:34 GMT

Dan wrote on Thu, 24 Jul 2008 09:38:17 -0400:

> I am getting an error i don't understand. I would like for someone
> else to try and repeat it if possible. I am on Sql2005sp2.

> The goal is to take a string and split it based on a delimiter. The
> following is the split function. You may recognize the form. I found
> it from this NG but i don't remember where. tblCalendar is my source
> of numbers so don't let that throw you.

> CREATE FUNCTION [dbo].[split](@text VARCHAR(MAX), @delimiter
> VARCHAR(20) = ' ')
[quoted text clipped - 7 lines]
> WHERE Id <= CONVERT(INT, LEN(@text)) AND SUBSTRING(@delimiter +
> @text, Id, 1) = @delimiter )

> This exercises the above function:
> SELECT * FROM dbo.split(
> '341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00
> AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00 AM,07/25/2008 01:30:00
> PM|0,0|,,|1'
> , '|')

> The following causes this error:
> Msg 245, Level 16, State 1, Line 11
> Conversion failed when converting the varchar value
> 'æˆrž]Q...........' to data type int.

> declare @VehicleID INT
> DECLARE vehicle_field_cursor CURSOR
[quoted text clipped - 10 lines]
> CLOSE vehicle_field_cursor
> Deallocate vehicle_field_cursor

> I have another version of split that is very procedural that has the
> same signature as this one and it runs quite well. I have had to revert to
> it in my code because of this error.

> I would greatly appreciate it if someone would take the time to see if
> this error happens for them as well. And if possible find some
> explanation and/or remedy.

Forgive me if I'm reading this wrong, but Value from the function is a
string returned using the SUBSTRING call, right? And you're trying to read
it into the @VehicleID variable which is an Integer. Are you really getting
that error with the above single call, or are you getting it when running
against your data which might have a value being passed into the function
which is in a different format?

I've tested both the simple call to the Split function and your cursor code
above on SQL Server 2005 32bit and it works fine, the cursor part returning
a single value of 341.

Signature

Dan

Reply to this Message

Dan Holmes - 24 Jul 2008 16:55 GMT

> Dan wrote on Thu, 24 Jul 2008 09:38:17 -0400:
>
[quoted text clipped - 63 lines]
> above on SQL Server 2005 32bit and it works fine, the cursor part returning
> a single value of 341.

Yes that is right. An implicit conversion of a string to an int. The code i sent is what it is failing on.

Reply to this Message

Alex Kuznetsov - 24 Jul 2008 16:54 GMT

> I am getting an error i don't understand. I would like for someone else to try and repeat it if possible. I am on
> Sql2005sp2.
[quoted text clipped - 47 lines]
>
> dan

Try running this:

---declare @VehicleID INT
declare @VehicleID NVARCHAR(1000)
DECLARE vehicle_field_cursor CURSOR
FOR
SELECT Value
FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,
07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')
OPEN vehicle_field_cursor
FETCH NEXT FROM vehicle_field_cursor INTO @VehicleID
print @VehicleID
CLOSE vehicle_field_cursor
Deallocate vehicle_field_cursor

Reply to this Message

Dan Holmes - 24 Jul 2008 18:09 GMT

>> I am getting an error i don't understand. I would like for someone else to try and repeat it if possible. I am on
>> Sql2005sp2.
[quoted text clipped - 65 lines]
> CLOSE vehicle_field_cursor
> Deallocate vehicle_field_cursor

Returns
",,"

If i add static or read_only or fast_forward to the cursor then the declaration as an int works too. It is just the OP
form that fails. Unfortunately there is too much code to change to mass change all the cursors to static.

If it helps the contents of what can't be converted is different on every execution.

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value 'æˆ‡
)FËa...........' to data type int.

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value 'æˆGþ4¿N¿m...........' to data type int.

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value 'æˆ>*ÈVÿb...........' to data type int.

dan

Reply to this Message

Dan Holmes - 24 Jul 2008 18:30 GMT

>>> I am getting an error i don't understand. I would like for someone
>>> else to try and repeat it if possible. I am on
[quoted text clipped - 101 lines]
>
> dan

I have found more eccentricities about this. I switched from an inline TVF to this:
CREATE FUNCTION [dbo].[split](@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(max))
AS
BEGIN
INSERT INTO @retarray
    SELECT ROW_NUMBER() OVER (ORDER BY Id)-1 idx,
    SUBSTRING(@text, Id, CAST(CHARINDEX(@delimiter, @text + @delimiter, Id) - CAST(Id AS BIGINT) AS INT)) value
    FROM tblCalendar
    WHERE Id <= CONVERT(INT, LEN(@text)) AND SUBSTRING(@delimiter + @text, Id, 1) = @delimiter
return
END

Cursor code from OP works fine.

Also, using the inline version if the cursor declare has an order by idx then it works as well.

DECLARE vehicle_field_cursor CURSOR
FOR
SELECT Value
FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,
07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')
--add order by
ORDER BY idx

Using Alex's idea of changing the datatype i noticed that when the datatype was nvarchar the ",," from idx seven is
returned. If the cursor is static the 341 from idx 0 is returned. Sql is acting like it changes the first row returned
based on the declaration of the cursor or the data type expected as the return.

I think this has a hint of sql bug in it.

dan

Reply to this Message

Plamen Ratchev - 24 Jul 2008 18:42 GMT

Why do you think this is a bug? Tables are unordered sets and unless you
specify ORDER BY when querying the table you should not expect rows to be
returned in any particular order. I missed in your initial post that you did
not have ORDER BY, but that is really the problem with your code.

Also, not sure why you do all this. Since you have the index of each
element, just add a WHERE clause to query idx = 0.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Dan Holmes - 24 Jul 2008 18:54 GMT

> Why do you think this is a bug? Tables are unordered sets and unless you
> specify ORDER BY when querying the table you should not expect rows to
[quoted text clipped - 8 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Why would the behavior change based on and inline function or multi-statement? Why would the cursor declaration matter?
it is those inconsistencies that evoke the bug thought.

dan

Reply to this Message

Plamen Ratchev - 24 Jul 2008 19:01 GMT

The query optimizer retrieves data based on the most efficient plan it has.
Different code can produce different plan and rows can be retrieved
different ways to execute the plan. This is why you see inconsistency. But
this is expected, because rows in a table have no order. Only using ORDER BY
guarantees consistent results.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 19:11 GMT

> Why would the behavior change based on and inline function or multi-statement?
> Why would the cursor declaration matter?
> it is those inconsistencies that evoke the bug thought.

The optimizer is a complex beast. If you don't tell it how to order the
results, it can decide to do so arbitrarily based on literally hundreds of
different factors (maybe more). Think of it as a choose-your-own-adventure
book. And don't ever assume anything without an ORDER BY. This was more
predictable in 2000 (e.g. a view with ORDER BY would almost always return
the rows in the "correct" order, even if your query against the "ordered
view" did not include ORDER BY), but still it has never been guaranteed, and
the changes to the optimizer in 2005 and beyond make it even less
predictable.

Because the order of the results is by definition an unordered set, you
can't complain that there is a bug if you get an arbitrary order in the
return if you don't use order by. The "fix" obviously is to use order by.
Or else simply treat the result as ORDER BY NEWID() because it may as well
be. It is much safer to make that assumption than to rely on a particular
behavior you observe to apply to multiple new scenarios.

Reply to this Message