February 11, 2009 at 11:29 pm
Dear All,
I have written procedure in which a dynamic query is created and exectued using sp_executesql.
have declared the string variable as nVarchar(4000) for generating dynamic query.
But the number of charecters may exceed 4000.
So what should be the data type for string variable..?. Shall I use nText..?
Thanks,
swmsan.
February 12, 2009 at 12:11 am
Hi There,
Why don't you use VARCHAR(MAX) this can take way more then the 4000 you are trying to use now.
stay away from ntext can cause other issues down the line.
Hope this helps
Regards
Stephen
February 12, 2009 at 12:15 am
You can declare a variable with varchar(8000) and use only exec statement.
For example:
DECLARE @sql varchar(8000)
set @sql = 'Your String'
exec (@sql)
let me know whether it meets your requirement.
Thanks
NRoy
February 12, 2009 at 5:44 am
Follow Stephen's advice. Use the MAX data type and keep working with sp_executesql if you have to do dynamic queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2009 at 10:55 am
Hi,
I also use some dynamically written queries and also ran into the limit of the maximum string length (VarChar(8000)).
I solved it by cutting down the whole query in separate parts not supposed to get bigger than 8000 characters, then concatenating them :
Declare @Query1 VarChar(8000)
Declare @Query2 VarChar(8000)
Declare @Query3 VarChar(8000)
Set @Query1 = ...
Set @Query2 = ...
Set @Query3 = ...
Execute(@Query1+@Query2+@Query3)
Hope this can help you,
Regards
Florent
February 13, 2009 at 11:07 am
Florent Rousseau (2/13/2009)
Hi,I also use some dynamically written queries and also ran into the limit of the maximum string length (VarChar(8000)).
I solved it by cutting down the whole query in separate parts not supposed to get bigger than 8000 characters, then concatenating them :
Declare @Query1 VarChar(8000)
Declare @Query2 VarChar(8000)
Declare @Query3 VarChar(8000)
Set @Query1 = ...
Set @Query2 = ...
Set @Query3 = ...
Execute(@Query1+@Query2+@Query3)
Hope this can help you,
Regards
Florent
And in SQL Server 2000, that is exactly a great solution for doing this. In SQL Server 2005/2008 though, you can take advantage of the MAX data type and you won't have to do the break down as you did.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2009 at 2:28 am
Hi,
I sent the script using 'nText' already..Actualy the problem was that, user copy and paste some values from some files. It cause problem while saving...it throws error saying 'Invalid Charecter fiound..!!' .
By the way, What kind of problems NTEXT causes..?
February 17, 2009 at 8:25 am
Grant Fritchey (2/13/2009)
And in SQL Server 2000, that is exactly a great solution for doing this. In SQL Server 2005/2008 though, you can take advantage of the MAX data type and you won't have to do the break down as you did.
I was indeed running this on SQL Server 2000, and kept doing the same on SQL Server 2005... ignoring the existence of this new MAX data type. I'll use it from now on and it will save me the hassle of breaking down the code in less than 8000 characters parts (which is always tricky when part of the code is dynamic and depends on user input). Thanks a lot!
February 17, 2009 at 10:29 pm
Hi Florent
It is working fine....
Thanks
Raam
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply