Error! Msg 103, Level 15, State 4, Line 1

Question

Error! Msg 103, Level 15, State 4, Line 1

Anamika

SSCertifiable

Points: 6202
More actions
June 30, 2010 at 4:11 am

#237157

Hi,
I have a SP where i have to separate the values in to a table. it comes as a collection
when the values exceeds 128 it gives error like
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'OPI:'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N'0','XYZ',' is too long. Maximum length is 128.
exec [fnSplitString] [OPI:'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N],':',','
ALTER PROC [dbo].[fnSplitString](@textVARCHAR(8000)
,@delimiterVARCHAR(2) = ':,'
,@delimiter1VARCHAR(2) = ',')
AS
BEGIN
DECLARE @Strings TABLE
(
positionINT IDENTITY PRIMARY KEY
,TypeINT
,CodeVARCHAR(100)
,RateMoney
,DescriptionVARCHAR(100)
)
DECLARE @SepValueVARCHAR(50)
,@PositionINT
,@FlagBIT
,@TypeVARCHAR(10)
,@CodeVARCHAR(100)
,@RateVARCHAR(10)
,@DescriptionVARCHAR(100)
WHILE (@text!='')
BEGIN
SET @Position=CHARINDEX('\N',@text)
print 'POSITION ' + CONVERT(VARCHAR(3),@position)
SET @SepValue=SUBSTRING(@text,1,@Position)
SET @text= SUBSTRING(@text,@Position,LEN(@text))
SET @text= substring(@text,3,len(@text))
PRINT 'TEXT ' + @text
PRINT 'SepValue ' + @SepValue
SET @SepValue=replace(@SepValue ,'OPI:','')
SET @SepValue=replace(@SepValue ,'\','')
SET @Type =SUBSTRING(@SepValue,1,CHARINDEX(',',@SepValue))
SET @TYPE = REPLACE(@TYPE,'''','')
SET @TYPE = REPLACE(@TYPE,',','')
SET @SepValue = SUBSTRING(@SepValue,CHARINDEX(',',@SepValue)+1,LEN(@SepValue))
PRINT 'TYPE : ' + @TYPE
PRINT 'SepValue ' + @SepValue
SET @Code =SUBSTRING(@SepValue,1,CHARINDEX(',',@SepValue))
SET @Code = REPLACE(@Code,'''','')
SET @Code = REPLACE(@Code,',','')
PRINT 'Code: ' + @Code
SET @SepValue = SUBSTRING(@SepValue,CHARINDEX(',',@SepValue)+1,LEN(@SepValue))
SET @Rate =SUBSTRING(@SepValue,1,CHARINDEX(',',@SepValue))
SET @Rate = REPLACE(@Rate,'''','')
SET @Rate = REPLACE(@Rate,',','')
PRINT '@Rate: ' + @Rate
SET @SepValue = SUBSTRING(@SepValue,CHARINDEX(',',@SepValue)+1,LEN(@SepValue))
SET @Description =SUBSTRING(@SepValue,1,CHARINDEX(',',@SepValue))
SET @Description = REPLACE(@Description,'''','')
SET @Description = REPLACE(@Description,',','')
PRINT '@Description: ' + @Description
INSERT INTO @Strings
SELECT @Type
,@Code
,@Rate
,@Description
END
SELECT * FROM @Strings
END
i have given the code and execute statements. the values may come more to insert many rows.
how to resolve this? in addition any inputs to enhance the SP also welcome.
Thanks,
Regards,
Ami

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 1

it's your exec statement that is bogy ....

exec [fnSplitString] [OPI:'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N'0','XYZ','22.0','DES XYZ',\N'1','ABC',44.0','ABC DES',\N],':',','

should be something like

exec [fnSplitString] 'OPI:0,XYZ,22.0,DES XYZ,\N1,ABC,44.0,ABC DES,\N0,XYZ,22.0,DES XYZ,\N1,ABC,44.0,ABC DES,\N',':',','

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 2

He's correct. Your exec statement is the problem. You're missing a single quote right before 44.0.

EDIT: Make that before BOTH 44.0 bits.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Anamika

SSCertifiable

Points: 6202

July 1, 2010 at 2:18 am

#1187504

Many Thanks,

Ami

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

Anamika (7/1/2010)
Many Thanks,
Ami

Um... no... not yet. Take a look at the data you have. See all those "\N" thingies??? That's not supposed to be a part of the data. Those represent "newline" characters and you need to remove them from the data.

in addition any inputs to enhance the SP also welcome.

In that case, read the following article... it's a whole different world with code performance as the reward.

http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 5

Good catch. I missed the '/' character. I was assuming that maybe he meant for that to be the unicode character of N.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Anamika SSCertifiable Points: 6202 More actions · Answer 6

wow!

Tally tables are doing a wonderful job.

Thanks Jeff Moden and Brandie Tarvin

-Regards,

Ami

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

You bet. Thanks for the feedback, Ami.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 8

Glad we could help.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.