可以从 SQL Server 2008 中的存储过程返回到 .net 应用程序的字符串的最大长度 [英] Maximum length of string which can be returned from stored proc in SQL Server 2008 to .net apps

问题描述

我从存储过程(在 SQL Server 2008 中)返回一个静态字符串，如下所示:

I am returning a static string from a stored procedure (in SQL Server 2008) as below:

select 'abcdefgh.........xyz'

如果静态字符串长度超过某个限制(例如:8kb)，则仅将部分字符串(例如:7kb)返回给 .net 应用程序.

If the static string length is exceeding more than some limit (eg:8kb) then only partial string (eg:7kb) is returned to the .net apps.

虽然我尝试了不同的方法，比如将静态字符串分配给 varchar(max) 并选择变量，但仍然只返回部分字符串.

Though I tried in different ways like assigning static string to varchar(max) and selecting the variable, is still returning only partial string.

我应该返回最大为 5mb 的完整字符串.因此，主要关注点:

I should return complete string which could be of max of 5mb. So, main concerns:

1. 我可以从存储过程返回的最大字符串长度是多少
2. 如何将 5 mb 字符串从存储过程返回到 .net 应用程序.

我请求有人可以帮助我解决这个问题.请找到下面的代码

I request someone can help me to resolve this issue. please find the code below

 using (SqlCommand command = new SqlCommand(Source.GetExportRecordSP, Connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@CandidateRecordID ", SqlDbType.NVarChar, 32)).Value = record;
                try
                {
                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Open();
                    }
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                    if(reader.Read())
                        {
                            xmlRecord = new XmlDocument();
                            xmlRecord.LoadXml(reader.GetString(0));
                        }
                    }
                }
                catch (Exception Ex)
                {
                    Logging.WriteError(string.Format("Error while retrieving the Record \"{0}\" details from Database. Exception: {1} ", Ex.ToString()));
                    throw;
                }               
            }

提前感谢极客们.

推荐答案

感谢支持，我在http://www.sqlservercentral.com/Forums/Topic350590-145-1.aspx

修复是，声明一个变量，应该初始化为空字符串并与主字符串连接.

Fix is, declare a variable, and should be initlized to empty string and concatenated with the main string.

  DECLARE @test varchar(MAX);
set @test =''
 select  @test = @test + '<Invoice>.....'

如果字符串长度为 <8000，则无需上述方法即可工作.

If the string length is <8000 it will work without the above approach.

谢谢大家.

这篇关于可以从 SQL Server 2008 中的存储过程返回到 .net 应用程序的字符串的最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！