November 1, 2008 at 7:57 am
Hi,
I'm a beginner with visual studio 05 and sql server 05.
I have a store procedure who take one parameter:
@PostionsList nvarchar(4000)
this procedure brok off when i pass more of 4000 caracters of course !
i've try to change the nvarchar by ntext by when i execute this sample i have this message :
The text, ntext, and image data types are invalid for local variables.
declare @PositionsList ntext
set @PositionsList = '3870,2245,2139,2560,1800,2471,2059,3539,2364,1446,2308,
1468,2206,1354,2186,4059,4857,4858,3773,1993,2701,1460,3707,1532,1396,2381,1615,
1839,4884,3866,2202,2314,2610,3940,3814,2956,2386,4039,1053,2803,3058,3431,4085,
3950,1441,1395,2660,2174,1691,1031,3413,2078,3522,2502,2131,2564,3184,2430,2208,
1997,2013,1998,4063,3038,1273,2581,2705,1387,1385,2567,1459,3083,2415,1372,5035,
2002,3442,3742,2001,1730,2892,4639,1009,2024,1386,5047,2096,3471,3086,2895,4029,
3030,5039,3135,2237,3902,3816,2246,4929,3200,3102,4118,2171,1146,2401,2177,4839, .....moire and more....
print @PostionsList
whitch type must i use for to store a string who is big more of 8000 caracters !?
Thanks for your knowledge!
Christophe
November 1, 2008 at 9:17 am
Well, if you are on SQL Server 2005, you should know two things:
1) this is a SQL Server 7,2000 forum. The SQL 2005 forums are higher up on the list.
and
2) you can use VARCHAR(MAX), which can go up to 2GB, which is a lot more than 4000.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 10:10 am
Moved to 2005 forum.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 2, 2008 at 3:18 am
Hi both,
thanks for your comment and to moved the post to the correct forum 🙂 !
i've try to declare my variable to a varchar(max) but that doesn't work TOO!!!!
here is it my code now :
declare @PositionsList varchar(max)
set @PositionsList =
'
3870,2245,2139,2560,1800,2471,2059,3539,2364,1446,2308,
1468,2206,1354,2186,4059,4857,4858,3773,1993,2701,1460,3707,1532,1396,2381,1615,
1839,4884,3866,2202,2314,2610,3940,3814,2956,2386,4039,1053,2803,3058,3431,4085,
3950,1441,1395,2660,2174,1691,1031,3413,2078,3522,2502,2131,2564,3184,2430,2208,
1997,2013,1998,4063,3038,1273,2581,2705,1387,1385,2567,1459,3083,2415,1372,5035,
2002,3442,3742,2001,1730,2892,4639,1009,2024,1386,5047,2096,3471,3086,2895,4029,
3030,5039,3135,2237,3902,3816,2246,4929,3200,3102,4118,2171,1146,2401,2177,4839,
3655,1972,2790,2794,4631,4632,3746,2250,2604,1118,4949,5166,5167,1060,2619,5148,
2414,2384,4034,2050,2273,2881,3409,2249,3980,1128,3049,4054,1002,1087,2147,1305,
1508,3775,2654,1285,1037,2072,3419,2985,1415,3455,2472,2947,1424,1772,3698,2475,
1021,2485,1483,2432,2972,4624,1855,3640,1767,3633,1065,3044,3766,1134,1313,3088,
1235,1319,1374,5041,1881,3053,1367,3507,3195,1378,5043,3858,5048,1521,1473,3752,
5095,5045,5150,1161,1353,3533,5094,2891,2998,3149,1028,1264,2399,2753,1405,5503,
1373,1173,3511,3110,3008,1113,2334,4633,1425,1290,2605,4863,4869,3813,2446,2421,
3873,1289,3586,1909,1469,2350,2241,2662,3827,2081,3684,1345,2943,1454,1620,1428,
2083,1436,1445,4864,4865,2875,2015,2238,2426,2275,3534,2946,3501,2060,3074,1309,
1238,1351,3059,4679,1769,2914,2754,2099,3694,3520,3838,2775,1005,3525,1764,1768,
1912,3461,3002,1971,3094,3804,2822,4043,1457,1773,0000,
....more and more and more...
'
if i execute the code, the string is plit at 8000 caracters !!!!
Any idea !?
Thanks for your time
Christophe
November 2, 2008 at 8:07 am
SQL just doesn't work well with large strings. I'd suggest that you take another look at what you're doing.
Perhaps post what you're trying to accomplish with this string and we might be able to help you think of a different way to attack the problem. Some type of bulk insert looks like it would make more sense.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 2, 2008 at 12:17 pm
bernardchristophe (11/2/2008)
i've try to declare my variable to a varchar(max) but that doesn't work TOO!!!!...
if i execute the code, the string is plit at 8000 caracters !!!!
Actually, it is more likely that your text output display was just cutoff at 8000 characters in Management Studio, the text is probably still in there.
Try this:
declare @PositionsList varchar(max)
set @PositionsList =
'
3870,2245,2139,2560,1800,2471,2059,3539,2364,1446,2308,
1468,2206,1354,2186,4059,4857,4858,3773,1993,2701,1460,3707,1532,1396,2381,1615,
1839,4884,3866,2202,2314,2610,3940,3814,2956,2386,4039,1053,2803,3058,3431,4085,
3950,1441,1395,2660,2174,1691,1031,3413,2078,3522,2502,2131,2564,3184,2430,2208,
1997,2013,1998,4063,3038,1273,2581,2705,1387,1385,2567,1459,3083,2415,1372,5035,
2002,3442,3742,2001,1730,2892,4639,1009,2024,1386,5047,2096,3471,3086,2895,4029,
3030,5039,3135,2237,3902,3816,2246,4929,3200,3102,4118,2171,1146,2401,2177,4839,
3655,1972,2790,2794,4631,4632,3746,2250,2604,1118,4949,5166,5167,1060,2619,5148,
2414,2384,4034,2050,2273,2881,3409,2249,3980,1128,3049,4054,1002,1087,2147,1305,
1508,3775,2654,1285,1037,2072,3419,2985,1415,3455,2472,2947,1424,1772,3698,2475,
1021,2485,1483,2432,2972,4624,1855,3640,1767,3633,1065,3044,3766,1134,1313,3088,
1235,1319,1374,5041,1881,3053,1367,3507,3195,1378,5043,3858,5048,1521,1473,3752,
5095,5045,5150,1161,1353,3533,5094,2891,2998,3149,1028,1264,2399,2753,1405,5503,
1373,1173,3511,3110,3008,1113,2334,4633,1425,1290,2605,4863,4869,3813,2446,2421,
3873,1289,3586,1909,1469,2350,2241,2662,3827,2081,3684,1345,2943,1454,1620,1428,
2083,1436,1445,4864,4865,2875,2015,2238,2426,2275,3534,2946,3501,2060,3074,1309,
1238,1351,3059,4679,1769,2914,2754,2099,3694,3520,3838,2775,1005,3525,1764,1768,
1912,3461,3002,1971,3094,3804,2822,4043,1457,1773,0000,
...{etc, etc}...
'
Select Len(@PositionsList)
You should see a length that reflects all of the text that you assined to that variable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 1:01 am
Hi everybody,
thanks for your time for to begin !
if i do a select len(PostionsList) i obtain more of 8000 characters !
and often i can have more on 8000 characters !!!!
here is it the mechanism:
i have a store procedure who gets the PostionsList=> it's a number between 0 to 9999
i store the return value in the in a generic list<>
follow i build a string builder
i add a , at the end of EACH positions lists
and when my stringbuilder is complete i pass this variable to my store procedure !
Any idea for to have a type of variable where i can store more of 8000 characters of other idea !?
Thanks for all
Christophe
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply