INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Smo stored procedure creation.

Smo stored procedure creation.

(OP)
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;
    }
} 

RE: Smo stored procedure creation.

(OP)
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"; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close