question

mayankbhargava avatar image
mayankbhargava asked

sql command getting truncated after max value is reached

hello everyone, I have a stored procedure where we declared a variable sql with nvarchar(4000) as datatype. Now, this variable stores a sql query coming from a result set of a table.Once the variable data length is reached, query gets truncated and execution of this stored procedure fails. below is the stored procedure: ALTER proc sp_singlevalued_selecttable_attributes_getall @debug bit=0 as declare @mdtype nvarchar(30) declare @tablename nvarchar(30) declare @select nvarchar(30) declare @mdname nvarchar(30) declare @sql nvarchar(4000) declare @counter INT declare @rows INT declare @resultTable table ( [indx] int identity (1, 1) not null primary key, [mdname] [nvarchar] (30), [tablename] [nvarchar](30) , [mdtype] [nvarchar] (30) , [select] [nvarchar] (30) ) SET NOCOUNT ON -- Get the RDS_DATA table from RdsData..RDS_MAP insert into @resultTable (mdname, mdtype, tablename, [select]) select MD_NAME, MD_TYPE, 'RdsData..' + RDS_DATA_TABLE as RDS_DATA_TABLE, 'RdsData..' + RDS_SELECT_TABLE as RDS_SELECT_TABLE from RdsData..RDS_MAP where (MD_MULTI_VALUE=0 and RDS_SELECT_TABLE !='') and BASE_TABLE = 'Asset' order by MD_NAME, MD_APPID -- Build the dynamic query select @sql = '' select @counter = 1 select @rows = COUNT(*) from @resultTable while (@counter <= @rows) begin select @mdname = mdname, @mdtype = mdtype, @tablename = tablename, @select = [select] from @resultTable where indx =@counter -- see if joining select table for code resolution is necessary set @sql = @sql + 'select UID, MD_KEY, TAG as VALUE, t1.UPDATE_STAMP,'''+@mdname+''' as MD_NAME, ' +@mdtype+' as MD_TYPE from ' + @tablename + ' t1 join ' + @select + ' t2 on t1.' + @mdname + ' = t2.TVALUE' if (@counter < @rows) select @sql = @sql + ' union all ' select @counter =@counter + 1 end exec sp_executesql @sql if @debug = 1 print @sql SET NOCOUNT OFF please suggest. regards, Mayank
sql-server-2000query-tuning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Either re-write the query so it doesn't need to execute dynamic sql, or if that is not possible use nvarchar(max)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For SQL 2000, the parameter for sp_executesql is actually ntext - this will handle more than 4000 characters, so declare your local variable as ntext. See : http://www.sommarskog.se/dynamic_sql.html#sp_executesql
1 Like 1 ·
hi kev, thanks, but i tried nvarchar(max) but it is not working, since maximum length for nvarchar is 4000 which is already reached. Rewriting the query again is a last option.
0 Likes 0 ·
As Thomas's answer suggests....what version are you running?
0 Likes 0 ·
If this still does not work for you, then I guess you will have to resort to using EXEC(), and put some fairly robust sql injection handling in there
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
In addition to @kev riley's answer... [`nvarchar(max)`][1] means there is no limit. Well, there is a limit, but it's 2GB of storage, or over 1 billion characters. If you want to provide a number for the maximum size of an `nvarchar`, then 4000 is the highest number you can provide when declaring an `nvarchar`. The documentation indicates that this has been the way since SQL 2005 [1]: http://msdn.microsoft.com/en-gb/library/ms186939.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've retagged :)
1 Like 1 ·
Hi Thomas, Thats correct. but i missed to mention that I am using sql server 2000 in my production.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.