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.
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;
}
} 
Talk To Other Members
RE: Smo stored procedure creation.
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