Production issue with varchar(max)

  • 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.

  • 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?

  • How do you know it is being truncated?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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

  • We will need to see the proc as well. Where exactly are you getting the error?

  • 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

  • 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

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • 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.

  • 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