Getting past 8000 char limitation in sp_executesql?

• Todd Robinson

  SSC Rookie

  Points: 30

  More actions

  October 12, 2004 at 9:55 am

  #182096

  I've been seeing this question a lot but haven't seen an answer that meets my needs. The thing I get caught up on is that in BOL, it says that the parameter you pass in, "The size of the string is limited only by available database server memory."

  So how do I build and get a 10,000 character SQL statement to execute? The reason I want to use sp_executesql is for its return code.

  Any ideas?

   

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  October 12, 2004 at 2:27 pm

  #526413

  As best I can tell, it allows >8k only when it is a constant:

  exec sp_executesql N' bla..

  bla...

  ...out to 20k' --<--end quote

  I have executed more than 8k with EXEC as in:

  EXEC ( @var1 + @var2 + @var3)

  I have also BCP'd image/text fields out to text OS files and submitted them with OSQL...