In dynamic Sql, , I reach the varchar limit is 800

• zoltix

  SSC Eights!

  Points: 813

  More actions

  January 9, 2003 at 4:00 am

  #159111

  Hi,

  In dynamic Sql, I have a problem when I try to generate a string for select clause. I reach the varchar limit that is 8000 characters. Therefore when I try to execute the query, I have any error messages. In deed, I try to copy some rows from production to archive database, The table does not have necessarily the same structure, also I think I need this string for the copy. Perhaps, there are other solutions for making that. Could you help me?

  EX:

  Declare @strSelect varcher(80000)

  Set strSelect=’field1, field2, field3………………………’

  Exec( insert into table (‘+ @strSelect +’) select ‘+ @strSelect +’ from …… ‘)

  @strSelect exceeds more 8000 chars

  Thank in advance.

• Scorpion_66

  SSCertifiable

  Points: 7891

  More actions

  January 9, 2003 at 8:45 am

  #445949

  You simply break your @strSelect into multiple variables, and do the concatenation, like you are, in the exec clause.

  Declare @strSelect1 varchar(8000),

  @strSelect2 varchar(8000)

  Set strSelect1=’field1, field2, field3………………………’

  Set strSelect2=’field28, field29, field30………………………’

  Exec( insert into table (‘+ @strSelect1 + @strSelect2 +’) select ‘+ @strSelect1 + @strSelect2 +’ from …… ‘)

  Etc...