CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduressmo
coffeeak
coffeeak

Reputation: 3120

SMO Stored Procedure Alter fail

I am trying to alter the parameters in a stored procedure programatically in c#. I came across SMO and have been trying to use it but the alter keeps failing. Here is my code:

//alter parameter data type in stored procedure if parameter is not  varchar
public static void AlterSPParamInfo (string SPName,string parameterName,string connectionStringName)
{
    SqlConnection vSqlConnection = CreateSqlConnectionStr(connectionStringName);
    using(vSqlConnection)
        {
            ServerConnection vConnection = new ServerConnection(vSqlConnection);
            Server vServer = new Server(vConnection);
            Database vDatabase = vServer.Databases["HrSys"];
            var vTables = vDatabase.Tables;
            StoredProcedure sp = vDatabase.StoredProcedures[SPName];
            if(sp != null)
            {
                StoredProcedureParameter spParameter = sp.Parameters[parameterName];
                if(spParameter!=null)
                {
                    if(!spParameter.DataType.Equals(DataType.VarChar(50)))
                    {
                        spParameter.DataType = DataType.VarChar(50);
                        sp.QuotedIdentifierStatus = true;
                        try
                        {
                            sp.Refresh();
                            sp.Alter( );
                        }
                        catch(SqlServerManagementException ex)
                        {
                            //other code
                        }
                    }
                }         
 }

What am I missing here? Or is it that I cannot change a stored procedure's parameters this way? I have tried finding more information on google or MSDN but can't find any solution...

Upvotes: 0

Views: 1096

Answers (2)

coffeeak
coffeeak

Reputation: 3120

After much digging in many forums, I found the solution: Set sp.TextMode = false; The alter procedure is then successfully executed. StoredProcedure.TextMode Property

//alter parameter data type in stored procedure if parameter is not  varchar
public static void AlterSPParamInfo (string SPName,string parameterName,string connectionStringName)
{
    SqlConnection vSqlConnection = CreateSqlConnectionStr(connectionStringName);
    using(vSqlConnection)
        {
            ServerConnection vConnection = new ServerConnection(vSqlConnection);
            Server vServer = new Server(vConnection);
            Database vDatabase = vServer.Databases["HrSys"];
            var vTables = vDatabase.Tables;
            StoredProcedure sp = vDatabase.StoredProcedures[SPName];
            if(sp != null)
            {
                StoredProcedureParameter spParameter = sp.Parameters[parameterName];
                if(spParameter!=null)
                {
                    if(!spParameter.DataType.Equals(DataType.VarChar(50)))
                    {
                        spParameter.DataType = DataType.VarChar(50);
                        sp.QuotedIdentifierStatus = true;
                        try
                        {
                            sp.TextMode = false;
                            sp.Alter( );
                        }
                        catch(SqlServerManagementException ex)
                        {
                            //other code
                        }
                    }
                }         
 }

Upvotes: 0

jordanhill123
jordanhill123

Reputation: 4182

Is it possible that you have not granted Alter permissions on the stored procedure for the user ID used in your connection string?

Edit: This would be a DB side configuration to be done.

Upvotes: 0

Related Questions