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