Hi,
Using SetDefaultInitFields fails for a Column when specifying "ExtendedProperties" as a field. note: SetDefaultInitFields seems to be ok with "DataType" as a field.
The exception is "unknown property ExtendedProperties".
Thoughts on what might be the issue with loading the ExtendedProperties
Thanks,
Andy B
Following is a snapshot of the test code:
System.Data.SqlClient.SqlConnection dbConnection1 = new System.Data.SqlClient.SqlConnection();
dbConnection1.ConnectionString =
"Data Source=.\\SQLExpress;Integrated Security=SSPI";dbConnection1.Open();
Microsoft.SqlServer.Management.Common.
ServerConnection serverConnection1 = new Microsoft.SqlServer.Management.Common.ServerConnection((System.Data.SqlClient.
SqlConnection)dbConnection1);Microsoft.SqlServer.Management.Smo.
Server server1 = new Microsoft.SqlServer.Management.Smo.Server(serverConnection1); // Set the smo optimations (to set the fields to fetch on an initialization of object) //String [] fields = { "DataType" }; // OK String[] fields = { "DataType", "ExtendedProperties" }; // Fails server1.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), fields);
SetDefaultInitFields fails for a Column using ExtendedProperties
Philippe Cand
Some more info...
I was trying to use SetDefaultInitFields, because of performance issues with retrieving the ExtendedProperties data. The following code is a method that, when the line "int count = column1.ExtendedProperties.Count;" is included, slows performance considerably (time displayed is HH:MM:SS.THT format.
Performance data:"int count = column1.ExtendedProperties.Count;" ***NOT COMMENTED OUT*** (SLOWER)
Database: Project, Table: a59b47a2-0fd3-48e1-ac57-ec689001415d, time: 00:00:01.6811455
Database: (0), Table: ShapeMaster, Column: Id, time: 00:00:00.1744386
Database: (0), Table: ShapeMaster, Column: Name, time: 00:00:00.1775661
Database: (0), Table: ShapeMaster, Column: TableName, time: 00:00:00.1702191
Database: (0), Table: ShapeMaster, Column: MetaData, time: 00:00:00.1696564
Database: (0), Table: ShapeMaster, Column: CategoryId, time: 00:00:00.1692572
Database: (0), Table: ShapeMaster, Column: PropertyGroupsId, time: 00:00:00.1958003
Database: (0), Table: ShapeMaster, Column: DataSourceSynchronizationRateId, time: 00:00:00.1753907
Database: (0), Table: ShapeMaster, Column: Created, time: 00:00:00.1696698
Database: (0), Table: ShapeMaster, Column: LastUpdated, time: 00:00:00.1705845
Performance data:"int count = column1.ExtendedProperties.Count;" ***COMMENTED OUT*** (FASTER)
Database: Project, Table: a59b47a2-0fd3-48e1-ac57-ec689001415d, time: 00:00:01.1943747
Database: (0), Table: ShapeMaster, Column: Id, time: 00:00:00.0000748
Database: (0), Table: ShapeMaster, Column: Name, time: 00:00:00.0000438
Database: (0), Table: ShapeMaster, Column: TableName, time: 00:00:00.0000424
Database: (0), Table: ShapeMaster, Column: MetaData, time: 00:00:00.0001089
Database: (0), Table: ShapeMaster, Column: CategoryId, time: 00:00:00.0000444
Database: (0), Table: ShapeMaster, Column: PropertyGroupsId, time: 00:00:00.0000522
Database: (0), Table: ShapeMaster, Column: DataSourceSynchronizationRateId, time: 00:00:00.0000491
Database: (0), Table: ShapeMaster, Column: Created, time: 00:00:00.0000435
Database: (0), Table: ShapeMaster, Column: LastUpdated, time: 00:00:00.0000427
++++++++++ CODE METHOD ++++++++++++++++++
private static void ProcessColumns(Microsoft.SqlServer.Management.Smo.Database database1,
Microsoft.SqlServer.Management.Smo.Table table1,
System.IO.StreamWriter streamWriter1)
{
int columnCount = (table1.Columns != null) table1.Columns.Count : 0;
for (int columnIterated = 0; columnIterated < columnCount; columnIterated++)
{
#if
DEBUG_CONSOLE_LOG_TIME_COLUMNSSystem.Diagnostics.
Stopwatch stopWatch1 = new System.Diagnostics.Stopwatch();stopWatch1.Start();
#endif
Microsoft.SqlServer.Management.Smo.
Column column1 = table1.Columns[columnIterated]; String name = column1.Name; int maximumLength = column1.DataType.MaximumLength; int numericPrecision = column1.DataType.NumericPrecision; int numericScale = column1.DataType.NumericScale; if (column1.ExtendedProperties != null){
int count = column1.ExtendedProperties.Count; // HAVING THIS IN, OR OUT, SIGNIFICANTLY AFFECTS PERFORMANCE// for (int i = 0; i < count; i++)
// { // Microsoft.SqlServer.Management.Smo.ExtendedProperty extendedProperty1 = // column1.ExtendedProperties}
#if
DEBUG_CONSOLE_LOG_TIME_COLUMNSstopWatch1.Stop();
streamWriter1.Write(
String.Format(System.Globalization.CultureInfo.InvariantCulture, "Database: (0), Table: {1}, Column: {2}, time: {3}\r\n", database1.Name,table1.Name, column1.Name, stopWatch1.Elapsed));
#endif
}
}
}
Matt_343
sana ul haq
After a few pointers from Ciprian Gerea in another thread(http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1151306&SiteID=1&mode=1), I finally cracked how to do this!
ScriptingOptions scriptingOptions = new ScriptingOptions();
scriptingOptions.ExtendedProperties = true;
database.PrefetchObjects(typeof(Table), scriptingOptions);
This also seems to prefetch the ExtendedProperties on the Columns as well as the Tables.