SetDefaultInitFields "TextBody" for Stored Procedure

SetDefaultInitFields "TextBody" for Stored Procedure

• Aaron Robertson-Hodder

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

  
  

• Alex Sandro

  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.

  
  

• Lowell

  Perhaps Typeof() was missing left parenthesis

  Chip

  
  
  

• TomGK2006

  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

  
  
  

• StealthyTofu

  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

  
  
  

• TX Gal

  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

  
  

• Jack2005_MSFT

  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!

  
  

• Valery Smirnov

  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

  
  
• SetDefaultInitFields "TextBody" for Stored Procedure