Production issue with varchar(max)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Production issue with varchar(max)

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2012-06-26 : 12:56:16

I thought varchar(max) will store 2 gb data, but here I have a comma delim string that I am passing to a stored proc, the stored proc var is defined @empIds as varchar(max) but the string truncates. What could be the problem? This is a production issue and very urgent.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 13:04:21

Do you mean the display truncates in Management Studio?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rama108
Posting Yak Master

115 Posts

Posted - 2012-06-26 : 13:24:59

This is weird. I was in a hurry so I did not explain the situation properly. I had a variable that I defined as varchar(3000), but today it turned out that that the length of the data that holds into that variable is way longer, so there was an exception on the production site. So I changed the varchar(3000) to varchar(max). When executing the SP and passing in the params to test, it showed that the varchar(max) variable is truncating the data. I know that the print has the character limitations, but when actually executing it gave me an error "Incorrect syntax near '2'" and so I know that it is truncating. It is still giving me the same error. But after changing to max, the site is working properly. I am executing the SP manually in SSMS like so: What is wrong with this?

MySP 255, null, '44279,44280,91792,129414,44282,44283,44284,100899,44286,44287,44289,44290,202494,44291,44292,61847,44293,44294,44295,44296,64497,44298,44299,44300,72739,44301,95010,44302,109738,44303,44304,44307,44308,174857,44309,44310,196211,96926,61117,44311,158222,44312,44313,143485,44314,103264,44315,44316,193173,44317,169649,123644,44318,44319,126043,83314,86373,191121,44321,44322,44323,44324,66408,44327,57905,95662,44330,102028,44331,44332,44333,44334,44335,96596,174850,96597,90867,44336,143480,57731,72761,60926,44337,44338,129065,44339,44340,44341,44342,44343,44344,44345,94996,191049,191070,71269,94988,103265,44347,100000,44348,44349,95000,57889,46059,101944,44350,94728,57880,44352,44353,86109,44355,44356,143488,148482,72748,162078,166433,46052,198953,44358,169650,44361,44362,44363,44364,44365,44366,44369,72747,57884,72744,44370,197317,44371,148480,109739,44372,44373,58002,44374,44375,97972,44376,169651,44377,44378,44379,44380,174854,44381,172439,44382,44383,80156,44384,79936,44385,46024,95598,44386,70696,44387,44388
,191072,191298,97956,44390,44391,88686,44392,44393,44395,83068,44394,44396,103266,44397,95606,44399,44400,44401,44402,201962,44403,44405,44406,44407,200864,44408,72477,44409,44411,44412,44414,44415,103267,44416,109426,44417,44418,44419,44420,44421,94997,151497,81143,194422,44422,138637,44423,44424,81140,97152,44425,44426,44427,44428,44429,44430,44431,203396,201970,94991,148442,44432,44433,44434,44435,44437,44438,103722,129415,191048,44439,44440,191069,44441,98333,111529,44443,44445,44446,175089,77418,201039,44448,135986,143532,44449,44451,44452,44453,44454,196205,44456,44457,44458,44459,44460,143509,44462,44464,61850,44465,44466,44467,44468,44469,44470,81142,44471,44472,196012,44473,59225,197587,61033,44474,44475,44476,44764,44477,102116,57903,57911,57924,44479,44480,109788,73106,61257,151511,44485,103718,44486,44487,44488,44489,44491,44492,153473,44493,44494,44495,44496,44497,44498,66410,44499,44500,158223,44501,44502,44504,96598,79934,44505,143508,132478,205719,44507,44508,44509,44510,44511,103268,82912,4451
2,44513,44514,130646,44515,191067,44516,44517,44519,44520,96601,44521,44522,44523,44524,91785,44525,191075,136556,57893,103269,130647,44527,44528,44531,44532,44534,94984,44535,44536,57921,44537,44538,44540,44541,44542,44543,104540,83757,44544,44545,44546,158485,44547,79938,99999,44548,44549,46026,44550,44551,156525,44552,44553,44554,44556,94978,44577,44557,44559,44560,44561,130484,44562,198954,143541,191073,44563,44565,44566,44567,44568,44569,91784,44570,44571,61116,201679,44572,128841,44573,44574,103270,44575,61031,97979,44578,44579,44580,44581,44582,174565,44583,44584,44585,61030,44587,44588,44589,44590,44591,72742,44592,44593,44594,44595,44596,44598,44599,95659,103271,44600,97975,44601,143469,59618,191074,109421,44603,44604,65025,95009,95602,44605,44606,94999,44607,57882,44609,44611,158217,44612,44613,44614,44615,96927,60939,44616,44617,79935,91890,135135,44622,44623,57935,44624,44625,44626,44627,44628,98330,155156,44629,101939,44630,44632,44633,44634,44635,44636,95005,44637,44638,44639,44641,44642,44643,44
644,198526,44646,164876,44647,44648,44649,126047,44650,44651,44652,44653,44654,103272,44655,44656,143486,101947,44657,46027,44658,44659,44660,61851,44661,44662,45218,44664,143507,44665,44666,44667,44669,44670,44671,44673,44674,44675,44676,44677,57887,44678,44679,44680,44681,170533,77530,44682,44683,201967,44684,46056,202496,94986,44685,44686,44687,44688,44689,86112,44690,175091,44691,44692,44693,62557,44694,44695,201958,205716,44696,44697,96923,151508,126506,44699,72762,64476,44700,44701,44702,44703,44704,125823,130483,44705,44706,103273,44708,44709,44710,44711,103274,61221,44714,143482,44715,57912,44716,44717,44718,169652,44719,109881,44720,61227,44722,98332,148350,44723,148443,44724,44725,76879,44726,65316,44728,44729,44730,44731,96603,166461,44733,44747,96594,44734,63652,86115,44735,90875,44737,44738,44739,45092,44740,44741,44742,44743,79937,44744,44745,98335,158220,103275,44748,44749,201965,44750,95663,44751,151502,131117,44752,153470,66412,96933,44754,44755,123375,123652,44756,44757,44758,44759,196013,447
60,44761,44762,91786,94979,44765,44766,77421,44767,44768,44769,44770,44771,44772,44773,44774,44776,100895,44777,44778,75686,205528,44779,44780,44781,109880,151494,44782,44783,44784,44785,44786,44787,44788,44790,44791,44792,44793,44794,57822,69306,72755,79940,44796,44797,131123,100003,166484,60938,44798,58001,103169,44799,44800,59212,44801,44802,44803,44804,96931,72475,44805,57906,44807,44808,44809,101942,44810,44811,44812,44813,44814,44815,44816,44817,44818,187589,44819,44820,91884,44821,196716,44822,44824,126313,44825,44826,204393,61226,44827,143531,142581,196107,44828,130479,151495,57920,46028,46029,44830,88682,126196,44831,44832,72759,44833,198955,57937,44834,101955,44835,44836,44837,143540,44838,44839,44840,44842,143476,44843,44844,103276,137391,62572,76876,123643,44847,101956,44848,44850,44851,86372,44852,44853,44854,44855,44856,44857,44858,44859,147391,44860,44861,44863,44864,44867,44868,44869,44870,60946,44871,44873,44874,44875,44876,65318,44877,44878,44879,44880,44881,44882,44883,44884,110328,44885,448
86,44887,44888,44889,132479,44892,44893,44894,44895,44896,196014,101758,44897,103277,44898,61263,143474,65023,44899,44900,72745,44901,110031,195101,44903,44904,44905,44906,44907,57931,96925,44909,83763,197585,44910,44911,44914,44915,44916,101740,57899,44918,44919,44921,44922,44923,44925,155157,72749,123653,44927,44928,44929,44930,202651,94980,148485,97153,109742,44931,44932,45105,44933,96924,44934,205526,44935,127741,86251,91788,80671,44936,100001,131121,44937,44938,44939,44941,44942,44943,44944,95604,148484,57881,72740,44947,95665,44949,153468,98331,61225,44950,44951,77423,64477,44952,96809,44954,44955,151503,44956,127840,44957,44958,95004,44959,103327,94987,44960,44961,58334,44962,44963,75680,44964,44965,44966,44967,44968,44969,62568,44971,44972,44973,44974,190387,82395,44976,44977,105239,194644,44978,44979,44980,44981,44982,192976,204786,44983,44984,191054,72758,44986,44987,44746,73644,130485,44989,53637,44990,44991,44992,44993,44994,44995,44996,143471,151504,44997,82919,44998,44999,45000,45001,57970,45004,
45005,148477,205531,135132,46030,45007,45009,45010,45011,94994,194359,45013,45014,45015,94981,45016,86116,45018,123651,135989,45020,45021,202443,46031,202444,80082,156524,45022,105246,45024,45025,62566,76878,45026,201672,45027,45028,45029,45030,45031,109743,45032,45033,94982,45035,45036,45037,45038,109416,64499,45040,45041,45042,202492,103279,45043,45044,45045,196015,45046,45047,82145,45048,57892,45049,45050,45051,78287,95001,45053,45054,45055,45057,45064,45058,45059,45060,45061,45062,45063,158219,95003,123710,45065,105412,45066,45067,45068,148440,69308,97978,45069,201968,45071,45072,45073,45074,45075,45076,45077,137389,45078,198835,45079,205708,45080,45081,45082,94993,45083,193175,45084,45085,57883,45087,57925,103215,45088,103280,45089,59224,45090,45091,45093,45094,45095,45096,45097,45098,45099,45100,45101,94995,135987,88683,45103,77422,45104,45107,103281,155146,45108,45109,169653,143472,45110,45111,148437,45112,45113,103282,57891,98334,45114,45115,45116,45117,45118,45119,45120,45121,45124,45125,45127,97974,4
5128,45129,97352,72756,126197,45130,94983,205713,191071,156522,103283,45132,45133,45134,45135,61849,83312,45136,45137,45138,45140,105241,45141,75682,169654,105408,105240,71065,181973,132477,77242,45143,45144,45145,158221,45146,59209,57936,45156,82143,45148,45149,45150,45151,45152,143477,191046,103719,45153,45154,166478,151501,45159,45160,101950,166479,45161,45162,95011,148434,45163,196108,79609,45164,81139,45165,45166,46033,105410,45167,45168,45169,169655,58335,45131,45170,45171,123374,45172,45173,45174,45175,45176,45177,45179,45181,45182,65315,191525,45183,45184,45185,45186,156527,103721,45187,45189,45190,45191,45192,104192,45193,45194,61035,62567,45196,45198,196101,59435,45200,45201,45202,132481,198437,63653,90866,174924,69505,109741,97306,169656,79611,45204,57879,45205,45206,143529,45208,45209,45210,45211,45212,45213,110283,174566,45214,97151,45215,45216,45217,45219,45221,45222,45223,45224,166477,80081,45226,137390,126314,45227,47006,45228,45229,69684,95007,45231,45232,45233,45234,45235,45236,65024,102027,4
6057,71273,45239,143470,155145,131118,94990,45241,45242,45243,126046,77419,45244,75685,79931,45245,45246,126161,100898,57930,46034,100896,45247,45248,45249,45250,45251,45252,60934,45253,45254,45255,45256,45257,45258,174086,45259,45260,76881,195831,45263,45265,45266,45267,45268,88684,45269,45270,45271,103285,103286,46035,45272,45273,45274,57914,45275,45276,45277,45278,45279,45280,45281,45282,59210,97429,45283,45284,95583,96932,45285,155150,45286,109418,105242,130481,45288,205615,45289,45290,151506,45291,45292,45294,45295,45296,166237,45298,45299,45300,196192,45301,76873,45303,45304,45305,45307,45308,71275,95664,45309,135133,45310,45311,46036,45312,45313,45314,45315,59936,45316,45317,131119,45318,45319,166480,45324,94992,45325,45326,45327,45328,72743,45329,45330,191055,95002,45331,45332,204602,45333,45334,45335,45336,45337,45338,45339,45340,45341,45343,57926,45344,45346,45347,91796,100894,101949,45349,105245,45350,45351,45352,45353,45354,45355,45356,45358,45359,45360,45361,169657,45362,105459,45363,45365,45366,6
9312,148432,174849,45368,45369,45370,45372,191409,45373,196292,96600,45374,45377,45378,45379,45380,45381,101941,46053,45382,45383,45384,45385,103288,57919,45387,45388,201966,45389,99998,45390,80670,45391,45392,45393,45394,77420,45395,45396,61848,45397,45398,61845,45399,45400,45401,45402,45403,45404,61727,132480,45406,45407,45408,130482,45409,45410,135136,45412,45413,100897,61844,45414,103289,166487,45415,45416,57923,45417,45418,151509,45419,57913,69678,45421,45422,45423,45424,45425,83104,45427,104466,109425,45429,57934,45430,45367,45431,45432,45433,127029,45437,45438,45439,45440,96773,45441,103290,45442,169658,45443,45444,45445,45446,45447,45448,77424,45449,143487,45450,148481,45451,45452,137520,45455,45456,97433,45457,45458,196209,69310,45460,45461,45462,45463,126311,45464,45465,143475,166485,205368,45467,45468,45003,45469,45470,45471,86254,191053,131120,45473,46038,45474,57932,45475,45476,45477,46039,45478,45479,137392,101940,166223,58005,157724,82907,45480,94128,45481,96575,125822,45482,45483,96772,45485,45
487,174925,45490,46041,45492,45493,45495,45496,46040,45497,45498,45494,151510,45500,45501,76877,154799,45504,96775,57663,72757,45506,45505,45507,45508,45509,45510,197180,45512,174852,61256,45513,45514,45515,45516,45517,45287,158218,45518,105413,45519,86252,45520,45521,45523',2012

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 13:30:56

I don't understand your post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources