1
Vote

Stored Procedures Creation Script output is been clopped

description

Hi

Your application is only returning the first 3941 characters of the stored procedure. You need to extend the string size of the variable that you are using.

You have probably set it to 5000, and deleting the first part of the SQL as containing USE … and SET ANSI_NULLS ON …

Regards
Tony

comments

Hunter wrote Jul 23, 2013 at 1:48 PM

Update on this comment.
Description of problem:
Use sys.sql_modules because definition is nvarchar(max) because it will not truncate long code.

In INFORMATION_SCHEMA.ROUTINES the ROUTINE_DEFINITION column is only nvarchar(4000) so if you try view the text of a long procedure and you will see that it is truncated.

To rectify this problem.
select definition from sys.sql_modules where object_id=object_id('YourProcedure')

update file "SQLDBInfo.cs" --> public List<StoredProcedure> GetStoredProcedures(string connectionString)
Replace line
//storedProcedure.Description = reader["ROUTINE_DEFINITION"].ToString();
with
storedProcedure.Description = GetDescription(storedProcedure.Name);

and add
    private string GetDescription(string storedProcedure,string connectionString)
    {
        string ToReturn = string.Empty;
            Database myDatabase = new SqlDatabase(connectionString);
            string sqlCommand = "select definition from sys.sql_modules where object_id=object_id('" + storedProcedure + "')";

            using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
            {
                using (IDataReader reader = myDatabase.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        ToReturn = reader["definition"].ToString();
                    }
                }
            }
        return ToReturn;
    }
Problem solved