Michael Maddox - Software Development

Monday, March 15, 2010

Using SMO (and failing) to get the SqlDataType of a UserDefinedDataType

Apparently SMO can't get the SqlDataType of a UserDefinedDataType.

SQL Server Management Objects (SMO) is a pretty powerful API into Microsoft SQL Server. I've been pretty happy using it in various scenarios over the years.

Recently, I was surprised to find out that SMO can't get the SqlDataType underlying a UserDefinedDataType. This is reproducible using the Microsoft Pubs sample database.

Attempt #1:

string databaseName = "pubs";
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[databaseName].ToString();

SqlConnection oneSqlConnection = new SqlConnection(connectionString);
ServerConnection oneServerConnection = new ServerConnection(oneSqlConnection);
Server oneServer = new Server(oneServerConnection);
Database oneDatabase = new Database(oneServer, databaseName);
oneDatabase.Refresh();

UserDefinedDataType tidUserDefinedDataType =
new Microsoft.SqlServer.Management.Smo.UserDefinedDataType(oneDatabase, "tid", "dbo");
bool initializeDidSucceed = tidUserDefinedDataType.Initialize(true);

In this scenario, for reasons unknown to me, initializeDidSucceed is false!

Now, if we try to access tidUserDefinedDataType.SystemType which seems like the appropriate data item, we get this exception:

Microsoft.SqlServer.Management.Smo.PropertyNotSetException: To accomplish this action, set property SystemType.
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.get_SystemType()

Attempt #2:

string databaseName = "pubs";
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[databaseName].ToString();

SqlConnection oneSqlConnection = new SqlConnection(connectionString);
ServerConnection oneServerConnection = new ServerConnection(oneSqlConnection);

Server oneServer = new Server(oneServerConnection);
Database oneDatabase = new Database(oneServer, databaseName);
oneDatabase.Refresh();

Table oneTable = new Table(oneDatabase, "titles", "dbo");
oneTable.Refresh();

Now the following values are returned:

oneTable.Columns[0].Name                             = title_id
oneTable.Columns[0].DataType.Name               = tid
oneTable.Columns[0].DataType.SqlDataType    = UserDefinedDataType
oneTable.Columns[0].DataType.MaximumLength = 6

So, we can successfully get the length of the underlying data type, but we can't get the SqlDataType.

Attempt #3:

USE pubs

DECLARE @CurrentSchemaName VARCHAR(128)
DECLARE @UserDefinedDataTypeName VARCHAR(128)

SET @CurrentSchemaName = 'dbo'
SET @UserDefinedDataTypeName = 'tid'

SELECT name
FROM sys.types
WHERE user_type_id =
(
   SELECT system_type_id
   FROM sys.types
   INNER JOIN sys.schemas
       ON sys.types.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = @CurrentSchemaName
     AND sys.types.name = @UserDefinedDataTypeName
)