0

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

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

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.

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

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

  1. What is the max string length I can return from a stored procedure
  2. How to return 5 mb string from stored procedure to .net apps.

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;
                }               
            }

Thanks in advance geeks.

Ravi Nuthakki
  • 1
  • 1
  • 5
  • `varchar(max)` should be able to return over 8kb... what does your .NET code look like? My guess would be that the issue is in your data access layer, and not in your stored procedure. – Michael Fredrickson Feb 22 '12 at 19:43
  • Agree with @Michael, post your .Net code. – sisdog Feb 23 '12 at 03:15
  • Thanks Michal, please find below code, – Ravi Nuthakki Feb 23 '12 at 06:59
  • Thanks Michal, please find main query updated with code. 1 more thing i couldn't see the complete string in SSMS result pane(may be it is limited). While debugging, i was able to see 8000 bytes of data retrieved from the DB. – Ravi Nuthakki Feb 23 '12 at 07:07

2 Answers2

0

Since you appear not to be using an OLEDB connection (which has an 8k limit), I think the problem is in your procedure code.

Or, perhaps, the compatibility version of your database is set to something other than SQL Server 2008 (SQL Server 2000 could not return more than 8k using GetString()).

richardtallent
  • 34,724
  • 14
  • 83
  • 123
  • May i know where can i change compatability version of sql server 2008? But we observed the behaviour in prod environment, and other servers too. – Ravi Nuthakki Feb 23 '12 at 07:22
  • @SadHu, it's on the database properties (viewable from SQL Server management studio). For instructions on how to see it, see http://msdn.microsoft.com/en-us/library/bb933794%28v=sql.100%29.aspx. For instructions on how to change it, see here: http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.100%29.aspx – Bridge Feb 23 '12 at 09:49
0

Thanks for support, I found 1 fix for this at 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>.....'

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

Thanks all.

Ravi Nuthakki
  • 1
  • 1
  • 5