SetDefaultInitFields "TextBody" for Stored Procedure
Following the suggestion in Michael Wories column, have found really nice performance optimization using SetDefaultInitFields (in my case, for the datatype parameter of columns).
Tried to use SetDefaultInitFields for the "TextBody" parameter of Stored Procedures, and I get an error saying that "TextBody" is not a valid property for a StoredProcedure. Any thoughts on what might be the issue?
Thanks!
Andy B
[453 byte] By [
AndyB90] at [2007-12-22]
I tried this and it works fine.
Server server = new Server();
server.SetDefaultInitFields(typeof(StoredProcedure), "TextBody");
I would think that TextBody would come over by default anyway, but I don't know for sure.
Regards,
Dave
Hi David,
Thanks for the suggestion.
I was running the SQLServer Profiler and (in a foreach loop) I believe watching the code go do a fetch to sql server for each time it needed the TextBody for a stored procedure.
Yes, that's the line I had tried in my code...and got the error (so I had to comment it out):
// Set the smo optimations (to set the fields to fetch on an initialization of object)
m_Server1.SetDefaultInitFields(
typeof(Microsoft.SqlServer.Management.Smo.Column),StringConstant.ColumnDataTypeParameter);// Not determined why adding the TextBody parameter gives an error (as this is a valid parameter)//server1.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure),// "TextBody");m_Server1.SetDefaultInitFields(
typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter),StringConstant.ColumnDataTypeParameter);Some additional details...
1.) With having the line:
m_Server1.SetDefaultInitFields(typeof Microsoft.SqlServer.Management.Smo.StoredProcedure), "TextBody");
in the code, I get the exception:
"unknown property TextBody" when I call the Microsoft.SqlServer.Management.Smo.StoredProcedure storedProcedure1.TextBody (i.e. string myTextBody = storedProcedure1.TextBody).
The InnerExcpetion has details _COMPlusExceptionCode -532459699.
2.) If the line:
m_Server1.SetDefaultInitFields(typeof Microsoft.SqlServer.Management.Smo.StoredProcedure), "TextBody");
is removed from the code, then I do not get this error. However, in Sql Server Profiler, I see that each time through the foreach loop, it queries the database for the TextBody value:
for (int storedProcedureIndex = 0; storedProcedureIndex < database1.StoredProcedures.Count; storedProcedureIndex++)
{
Microsoft.SqlServer.Management.Smo.StoredProcedure storedProcedure1 =
(Microsoft.SqlServer.Management.Smo.StoredProcedure)database1.StoredProcedures[storedProcedureIndex];
// Ignore system stored procdures
if (String.Compare(storedProcedure1.Schema, StringConstant.Sys, true,
System.Globalization.CultureInfo.InvariantCulture) == 0)
continue;
// Create the output object
MyStoredProcedureType storedProcedureType1 =
new MyStoredProcedureType();
storedProcedureType1.Name = storedProcedure1.Name;
storedProcedureType1.SchemaName = storedProcedure1.Schema;
storedProcedureType1.TextBody = storedProcedure1.TextBody;
--
Thoughts on the best way to resolve the issue?
Thanks!
Andy B
That is a pretty weird error, because if the StoredProcedure type didn't have a TextBody property, you would get a compile-time error and not a run-time error if indeed the error is occurring on the line you mention:
string myTextBody = storedProcedure1.TextBody
I re-created your example with a few adjustments as below and got it to work fine. I am not sure what the problem is:
using Microsoft.SqlServer.Management.Smo;
Server server = new Server();
server.SetDefaultInitFields(typeof(StoredProcedure), "TextBody");
server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
StoredProcedureCollection storedProcedures = server.Databases[0].StoredProcedures;
foreach (StoredProcedure sp in storedProcedures)
{
if (!sp.IsSystemObject)
{
MyStoredProcedureType mySP = new MyStoredProcedureType(sp);
}
}
where...
public class MyStoredProcedureType
{
private string _name;
private string _schemaName;
private string _textBody;
public MyStoredProcedureType(StoredProcedure sp)
{
_name = sp.Name;
_schemaName = sp.Schema;
_textBody = sp.TextBody;
}
}
Maybe my above code may shed some light on the problem.
Regards,
Dave
Hi Dave,
Thanks for the sample and thoughts. I ran your code and also don't have the issue. Must be something in my code...I'll scratch my head a bit more.
Regards,
Andy B.
ps: Also gracias for the if (!sp.IsSystemObject) tip!
Perhaps Typeof() was missing left parenthesis?
Chip
The reason this code works
Server server = new Server();
server.SetDefaultInitFields(typeof(StoredProcedure), "TextBody");
server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
StoredProcedureCollection storedProcedures = server.Databases[0].StoredProcedures;
foreach (StoredProcedure sp in storedProcedures)
{
if (!sp.IsSystemObject)
{
MyStoredProcedureType mySP = new MyStoredProcedureType(sp);
}
}
is because the second line server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject"); over writes the first so all you are setting is IsSystemObject because this function takes string params it should look more like:
server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject","TextBody");
but this will not work because TextBody is not the correct string to use. The available strings are
AnsiNullsStatus
BodyStartIndex
CreateDate
ForReplication
ID
ImplementationType
IsEncrypted
IsSystemObject
QuotedIdentifierStatus
Recompile
Startup
Text
By using
server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject","Text");
I was able to cut the time from 11 secs to 4 secs. Upon further testing I discovered if I got all the fields it cut the processing time down to .3 secs using server.SetDefaultInitFields(typeof(StoredProcedure), true); meaning get all the fields or if I explicitly got all the fields by naming them all I would also get the speed increase, but if I happen to remove any field in the list my performance went back to 4 secs. So I would do some testing but my results show if you want the body text you should probably get all the fields for the stored procedures.
I've just been looking into this and got it to work fine. My only question is, can you use this method of creating a stored procedure to add comments to the SP? I generally add my comments at the top of the SP so I don't think putting them in the textbody is good, not that I know if that is possible.
Thanks
Jon
