Wierd Concatenation Issue into varchar(max) variable

• David F

  Old Hand

  Points: 360

  More actions

  November 7, 2012 at 6:09 pm

  #393555

  Hello everyone, well I'm trying to utilize dynamic SQL to do some ETL processes on a database I'm working with. I was told that varchar(max) could probably more than I could ever need for my dynamic SQL string... but it seems its truncating the string when I try to concatenate

  FOR EXAMPLE:

  First I tried this and the string was truncated after the 2nd line.

  Declare @archiveColumnSQL varchar(max), @archiveTable varchar(max), @archiveSQL varchar(max)

  Set @archiveColumnSQL = '4000 character string'

  Set @archiveSQL = 'Insert Into Database.dbo.[' + @archiveTable + '] ( '

  Set @archiveSQL = @archiveSQL + @archiveColumnSQL + ', [Record_Change_Date] ) ' <---- CONCATENATION STOPS HERE

  Set @archiveSQL = @archiveSQL + ' Select ' + @archiveColumnSQL + ', ''' + @changeDate + ''' As [Record_Change_Date] From TWCSalesForce.dbo.[' + @reportTable + '] T1 '

  Set @archiveSQL = @archiveSQL + ' Where 1=1 And T1.[Record_ID] IN ( Select [Record_ID] From ##sfUpdatedRows )'

  Exec (@archiveSQL)

  [/Code]

  Then I tried this and the string was truncated after the first line

  Set @archiveColumnSQL = '4000 character string'

  Set @archiveSQL = 'Insert Into Database.dbo.[' + @archiveTable + '] ( ' <---- NOW CONCATENATION STOPS HERE

  Set @archiveSQL = @archiveSQL + @archiveColumnSQL + ', [Record_Change_Date] ) Select ' + @archiveColumnSQL + ', ''' + @changeDate + ''' As [Record_Change_Date] From TWCSalesForce.dbo.[' + @reportTable + '] T1 '

  Set @archiveSQL = @archiveSQL + ' Where 1=1 And T1.[Record_ID] IN ( Select [Record_ID] From ##sfUpdatedRows )'

  Exec (@archiveSQL)

  So if you look at the above, just assume the @archiveColumnSQL is a string with a lenght of just short of 4K. In the 1st

  I'm using a local procedure that writes the @archiveSQL to a log file and that is how i can tell that the concatenation is failing.

  Any ideas of why my concatenation??

  Thanks

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  November 7, 2012 at 6:10 pm

  #1556979

  Your literals are causing it, cast them to varchar(MAX)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]