June 26, 2012 at 10:54 am
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.
June 26, 2012 at 11:00 am
We really can't answer your question based on what you have posted and we can't see from here what you see there either. When you say the string value is getting truncated, where is it getting truncated?
June 26, 2012 at 11:01 am
How do you know it is being truncated?
June 26, 2012 at 11:08 am
Check your data type on any parameters ,whether in a proc or n the code in your application.
Ive stepped in that myself where a parameter fornmy sqladapter was the wrong size.
Ive also seen unintended truncation when you append varchars together in sql without making sure all are varchar max
Lowell
June 26, 2012 at 11:10 am
SSMS doesn't display the entire field. I think that it only displays 255 characters by default. If you are trying to view the contents of the variable in SSMS using either a SELECT or PRINT statement, it will truncate the display even though the actual value hasn't been truncated.
Try the following instead:
SELECT @YourVariable FOR XML PATH('')
When you click on the link in the results, it will open up as an XML document and will display up to 2GB.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2012 at 11:23 am
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
June 26, 2012 at 11:31 am
We will need to see the proc as well. Where exactly are you getting the error?
June 26, 2012 at 11:55 am
You might want to look at the final number in your string. It looks like your closing quote mark is in the wrong place. If that isn't the case... you need to find out using DATALENGTH() how long the string is outside of the procedure and then again inside of it.
If all else fails... Do the SELECT @String FOR XML PATH('') that was listed earlier. Beyond that, not much help can we give without code to look at.
ramadesai108 (6/26/2012)
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
June 26, 2012 at 12:23 pm
The closing quote is correct. After the long string, I have a year, '2012' as a variable. If I run the proc as follows then there are no problems:
myproc 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',2012
June 26, 2012 at 12:26 pm
Can't help you as long as we can't see what you see. Would help to see the DDL for the procedure in question.
June 26, 2012 at 12:31 pm
ramadesai108 (6/26/2012)
The closing quote is correct. After the long string, I have a year, '2012' as a variable. If I run the proc as follows then there are no problems:myproc 255,null,'<snip>
did you get that string from a copy and paste from SSMS results?
that's the exact issue drew allen was mentioning: no matter what, the most characters SQL server will display in a grid or in text is 8000 characters, and only 256 by default unless you change the settings.
even when teh data is bigger.
you need to do something to put it in variables instead of a long string if you are playing with big strings in SSMS:
declare @bigtext varchar(max)
SELECT @bigtext = SomeColumn FROM SomeTable
EXEC myproc 255,null, @bigtext
Lowell
June 26, 2012 at 12:32 pm
Also, I forgot to mention that it is a dynamic query and here is the SP:
Create PROCEDURE [dbo].[MySP]
(
@OrgId int,
@DivId int = null,
@EmpId nVarChar(MAX) = NULL,
@year int
)
AS
DECLARE @sql nVARCHAR(MAX)
DECLARE @cnts int
DECLARE @ParmDefinition nvarchar(50);
SET @sql = N'
SELECT @cnt =COUNT(DISTINCT e.OverallScore)
FROM Evals ev INNER JOIN
eval
ON ev.Id = e.Id INNER JOIN
orgs o ON e.EmpId = orgs.Id INNER JOIN
Divis d ON o.id = d.Id
INNER JOIN Lead l On o.Id = l.Id
WHERE (orgs.Id = '+ CAST(@OrgId as VARCHAR) +')
AND o.IsActive = 1
AND ((e.Year = '+ CAST(@year as VARCHAR) +'))'
IF (@DivId <> null)
BEGIN
SET @sql = @sql + ' AND (d.Id = '+ CAST(@DivisionId AS VARCHAR)+' )'
end
IF (@EmpId IS NOT NULL)
BEGIN
SET @sql = @sql + ' AND (e.EmpId IN ('+@EmpId+') )'
END
SET @ParmDefinition = N'@cnt int OUTPUT'
EXECUTE sp_executesql @sql, @ParmDefinition, @cnt=@cnts OUTPUT
June 26, 2012 at 12:33 pm
Have to agree with Lynn.... there's a lot of potential issues that could cause truncation, but without the procedure source, table create scripts, and data population scripts so we can also work on it, there's not a lot we can do.
June 26, 2012 at 12:54 pm
Can't see anything wrong with the SQL itself, other than I can't run it since I don't have your tables. I would change your CAST's to use NVARCHAR instead of VARCHAR as there is an implicit conversion on the VARCHAR to NVARCHAR.
June 26, 2012 at 3:37 pm
i had the same issue with varchar(max) when using it to create dynamic SQL, when executing my SP i relieved syntax errors, i run the SP with select (@SQL) and noticed that the query was cut short. to over come this i just split up the bits of query in to sections then combined then together to execute hey presto worked fine.
I didn't take much notice of how much data there was but there was not 2gb 🙂
***The first step is always the hardest *******
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply