SMO Stored Procedure Alter fail

Question

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...

What happens? Are you getting an error? If so: please let us know what **exactly** that error is! We can't read neither your screen, nor your mind .... — marc_s, Mar 05 '13 at 09:56
Yes I am getting an error, like I mentioned. The alter fails. The datatype of the parameter does not change. — coffeeak, Mar 05 '13 at 09:58
Can you **please** give us the **exact and complete** error message? — marc_s, Mar 05 '13 at 10:29
@marc_s There is no error message, per say. The ＂error" I am referring to is that the datatype is not successfully altered. The code does not throw any exception. This is why I did not give the exact and complete error message, there is none. Its drivng me nuts! — coffeeak, Mar 05 '13 at 15:33
@marc_s Thanks for the comment btw. Sorry cant provide a detaIled error msg. — coffeeak, Mar 05 '13 at 15:36

score 0 · Answer 1 · answered Mar 05 '13 at 10:03

0

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.

answered Mar 05 '13 at 10:03

jordanhill123

4,142
2
31
40

Thanks for the comment. The uid I am using has alter permission on the stored procedure. – coffeeak Mar 05 '13 at 10:08

coffeeak · Answer 2 · 2013-03-06T08:01:37.313

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

Have you edited that into your *question* so that it now makes *no sense* as a question because the code it contains is correct? If so, **please** don't do that. Please back out your edit. By all means post the corrected code in your *answer* — Damien_The_Unbeliever, Mar 06 '13 at 07:49
@Damien_The_Unbeliever Thanks for the advice! I am new to forums, so sorry for the mistake. I will change it now. — coffeeak, Mar 06 '13 at 07:58
@Damien_The_Unbeliever Is it ok the way I added the corrected code? Or is there anything else I shoudl add/remove/modify? thanks~ — coffeeak, Mar 06 '13 at 08:02
That looks fine. Just remember that, besides trying to get answers to our own issues, we're also trying to leave behind questions and answers that others can search for when they have similar issues. So it's actually helpful to leave the wrong code around. — Damien_The_Unbeliever, Mar 06 '13 at 08:04
@Damien_The_Unbeliever Yes, that actually makes sense. Thanks for pointing it out to me. I won't do that mistake again :) — coffeeak, Mar 06 '13 at 08:05

SMO Stored Procedure Alter fail

2 Answers2