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
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.
Either re-write the query so it doesn't need to execute dynamic sql, or if that is not possible use nvarchar(max)
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
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.
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
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
Hi Thomas, Thats correct. but i missed to mention that I am using sql server 2000 in my production.
16 People are following this question.
