Smo stored procedure creation.

Question

Smo stored procedure creation.

pdbowling (Programmer)

(OP)

29 Jul 14 10:04

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

pdbowling (Programmer) · Answer 1 · 2014-08-19 11:35:22

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";

Come Join Us!

Posting Guidelines

Jobs

Smo stored procedure creation.