|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
EXEC Function........Hello All,
I'm looking to use the EXEC function to execute an SQL Command made up of concatenated varchar(8000) variables. I was of the belief that the EXEC function could handle upwards of an 8000 character SQL Statement, however, my statement keeps getting truncated at 8000. EXEC (@iMsg +@msgMode +@someVar + @someOtherVar ) Thoughts? Am I missing something? Thx. B. EXEC (@setProperty + @iMsg + @coma + @singleQuote + @msgMode + @singleQuote + @coma + @singleQuote + @someVar + @someOtherVar + @singleQuote) Tam OShanter (t**@oshanter.com) writes:
> I'm looking to use the EXEC function to execute an SQL Command made up of How do you know it's truncated at 8000 chars?> concatenated varchar(8000) variables. > > I was of the belief that the EXEC function could handle upwards of an 8000 > character SQL Statement, however, my statement keeps getting truncated at > 8000. > > EXEC (@iMsg +@msgMode +@someVar + @someOtherVar ) The above should indeed work. But if you are on SQL 2005 use varchar(MAX) instead. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland,
Thanks for your reply. The reason I know I'm getting a truncation is that the output of my string concatenation is being used to call CDOSYS to send an email message, specifically, setting the body property. Upon receipt of the message I can see the truncation. Any thoughts? Thx. Tam. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns981D5C2B8211Yazorman@127.0.0.1... > Tam OShanter (t**@oshanter.com) writes: >> I'm looking to use the EXEC function to execute an SQL Command made up of >> concatenated varchar(8000) variables. >> >> I was of the belief that the EXEC function could handle upwards of an >> 8000 >> character SQL Statement, however, my statement keeps getting truncated at >> 8000. >> >> EXEC (@iMsg +@msgMode +@someVar + @someOtherVar ) > > How do you know it's truncated at 8000 chars? > > The above should indeed work. > > But if you are on SQL 2005 use varchar(MAX) instead. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Tam OShanter (t**@oshanter.com) writes:
> Hi Erland, I would guess that the truncation happens later in the chain. But I am> Thanks for your reply. > > The reason I know I'm getting a truncation is that the output of my string > concatenation is being used to call CDOSYS to send an email message, > specifically, setting the body property. > Upon receipt of the message I can see the truncation. > > Any thoughts? very carefully to stay away from the combination of mail and SQL Server, so I hope someone else can jump in on that part. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics