I am trying to execute the "sp_setapprole" stored procedure using C# and connecting to a SQL 2000 Server. I want to test the use of application roles. I keep getting an error saying that I am not calling it right. I can find plenty of documentation on the SQL syntax, but I have found literally no examples of the correct way to execute the stored procedure from .NET C# code. Has anyone successfully done this, that would be willing to share their .NET syntax? Even a sample in VB will be ok for me.
Here is my current attempt:
string connString = "my connection stuff goes here";
SqlConnection conn = new SqlConnection();conn.ConnectionString = connString;
// Create the Command object.SqlCommand MyCmd = new SqlCommand();MyCmd.CommandText = "sp_setapprole";MyCmd.CommandType = CommandType.StoredProcedure;MyCmd.Parameters.Add("@rolename", SqlDbType.NVarChar).Value = "myTestRole";MyCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = "myPassWd";MyCmd.Connection = conn;
conn.Open();int result = MyCmd.ExecuteNonQuery();
There error states that "sp_setapprole was not invoked correctly".