Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Smo stored procedure creation.

Status
Not open for further replies.

pdbowling

Programmer
Mar 28, 2003
267
US
Hello, Everyone.

I am new to SMO, so I do not know the accepted methodologies.

From my attempts, it appears that I am way off track on how to use this set of tools. Suggestions and guidance appreciated.

This is the error.
"An unhandled exception of type 'Microsoft.SqlServer.Management.Smo.FailedOperationException' occurred in Microsoft.SqlServer.Smo.dll"

This is the text for the .TextBody parameter of the StoredProcedure:
"SELECT AssetStatusID, Name, CreateDate FROM AssetStatus WHERE AssetStatusID = @AssetStatusID"

My code is below from Visual Studio 2012. The database, if important, is SQL Server 2008.

Code:
public StringCollection BuildSelectSproc(string selectSql, string databaseName) 
{
    ServerConnection srvConn = null;
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        srvConn = new ServerConnection(conn);
        Server srv = new Server(srvConn);
        Database database = srv.Databases[databaseName];
        StoredProcedure storedProcedure = new StoredProcedure(database, "AssetSelect");
        storedProcedure.TextBody = selectSql;
        StoredProcedureParameter parm = new StoredProcedureParameter(storedProcedure, "@AssetStatusID");
        storedProcedure.Parameters.Add(parm);  // Fails here.
        StringCollection sc = storedProcedure.Script(); // Fails here if I comment out the previous line.
        return sc;
    }
}
 
Hello, Everyone.

I resolved this a while back. I came here to post another question and thought I would update this post for future search engine users.

This won't compile out of the box since I am passing in variables to the method, but hopefully it will help someone in the future.

Code:
ServerConnection conn = new ServerConnection(serverName);
Server srv = new Server(conn);
var database = srv.Databases[databaseName];

var storedProcedure = new StoredProcedure(database, myStoredProcedureName);
storedProcedure.TextMode = false;

var parm = new StoredProcedureParameter(storedProcedure, "@" + myColumnName, SqlDataType.BigInt);
storedProcedure.Parameters.Add(parm);

storedProcedure.TextMode = true;
storedProcedure.TextBody = MySqlString;
storedProcedure.AnsiNullsStatus = true;
storedProcedure.ImplementationType = ImplementationType.TransactSql;
storedProcedure.Owner = "dbo";
storedProcedure.QuotedIdentifierStatus = true;
storedProcedure.Schema = "dbo";
storedProcedure.Startup = false;
storedProcedure.Name = "MySprocName";
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top