SQL Server Integration Services, Data Type Mapping

SQL Server Integration Services (SSIS) utilize several type systems – some depending on the different data providers supported by the SSIS, some depending on the environment where the service is used, etc. When designing SSIS packages, especially in heterogeneous environments, where different data management platforms and tools are used, appropriate data type mapping is highly critical – just think of quantities, precision and scale. This article should provide you with some of the basics of how data types of the several type systems supported by SSIS can be used when integrating data from diverse source and type systems. The article also mentions some of the type-related shortcomings of SSIS and tries to help you correct work around them.

The SQL Server Integration Services Data Type Map

The following table provides a mapping of all data types used in SQL Server Integration Services; the mapping is represented from the SQL Server perspective. Data types that are, in fact, supported by other type systems represented in the table, but which do not map to a corresponding SQL Server data type, are not shown here.

Obviously, when I say all, I actually mean all, except the type system used by the SQL Server Native Client ODBC provider; I plan to cover that in a later post.

Also note that, rather than providing a data type compatibility matrix (which, to be honest, could have been even more useful), I've instead tried to provide a simplified, one-to-one data type compatibility map, that you can use to safely cast a value using a data type of one type system to the nearest data type in another type system.

Data Type
SQL Server	SSIS Variables	SSIS Pipeline Buffer	OLE DB	ADO.NET
bigint	Int64	DT_I8	LARGE_INTEGER	Int64
binary	Object*	DT_BYTES	n/a	Binary
bit	Boolean	DT_BOOL	VARIANT_BOOL	Boolean
char	String	DT_STR	VARCHAR	StringFixedLength
date	Object*	DT_DBDATE	DBDATE	Date
datetime	DateTime	DT_DBTIMESTAMP	DATE**	DateTime
datetime2	Object*	DT_DBTIMESTAMP2	DBTIME2	DateTime2
datetimeoffset	Object*	DT_DBTIMESTAMPOFFSET	DBTIMESTAMPOFFSET	DateTimeOffset
decimal	Object*** (< SQL 2012) Decimal (>= SQL 2012)	DT_NUMERIC	NUMERIC	Decimal
float	Double	DT_R8	FLOAT	Double
image	Object*	DT_IMAGE	n/a	Binary
int	Int32	DT_I4	LONG	Int32
money	Object*	DT_CY (OLE DB) DT_NUMERIC (ADO.NET)	CURRENCY	Currency
nchar	String	DT_WSTR	NVARCHAR	StringFixedLength
ntext	String	DT_NTEXT	n/a	String
numeric	Object*** (< SQL 2012) Decimal (>= SQL 2012)	DT_NUMERIC	NUMERIC	Decimal
nvarchar	String	DT_WSTR	NVARCHAR	String
nvarchar(max)	Object	DT_NTEXT	n/a	n/a
real	Single	DT_R4	FLOAT, DOUBLE	Single
rowversion	Object*	DT_BYTES	n/a	Binary
smalldatetime	DateTime	DT_DBTIMESTAMP	DATE**	DateTime
smallint	Int16	DT_I2	SHORT	Int16
smallmoney	Object*	DT_CY (OLE DB) DT_NUMERIC (ADO.NET)	CURRENCY	Currency
sql_variant	Object*	DT_WSTR**** (OLE DB) DT_NTEXT**** (ADO.NET)	****	Object****
table	Object*	n/a	*****
text	Object*	DT_TEXT	n/a	n/a
time	Object*	DT_DBTIME2	DBTIME2	Time
timestamp	Object*	DT_BYTES	n/a	Binary
tinyint	Byte	DT_UI1	BYTE	Byte
uniqueidentifier	String****** (OLE DB) Object****** (ADO.NET)	DT_GUID	GUID	Guid
varbinary	Object*	DT_BYTES	n/a	Binary
varbinary(max)	Object*	DT_IMAGE	n/a	Binary
varchar	String	DT_STR	VARCHAR	String
varchar(max)	Object*	DT_TEXT	n/a	n/a
xml	Object*	DT_NTEXT	*****

Type Systems

The following type systems are supported by SSIS:

• SQL Server Data Types – these are the essential data types supported by SQL Server; also known as built-in or system data types. Custom, CLR-based data types, are not discussed in this article, mostly because they are specific to SQL Server, specific to a particular version of SQL Server, or could be represented using system data types (e.g. by character or binary data types, or by XML);
• SSIS Variable Data Types – the data types of the underlying type system used by the SSIS service are exposed to the programming environment as .NET Framework data types. However, be aware that not all .NET Framework data types can be used by the SSIS variables. You can find a list of supported SSIS variable data types in the TypeCode Enumeration article on MSDN;
• SSIS Pipeline Buffer Data Types – the data types used by the pipeline buffer, the essential element of the Data Flow task, are exposed using a dedicated type system. This type system is different from the one used by the SSIS variables. As long as the Data Flow definition metadata corresponds to the metadata of the data sources involved in the Data Flow, you should not experience any problems with this particular type system, regardless of the data providers used by the Data Source or the Data Destination components. For additional information on SSIS pipeline buffer data types consult the Integration Services Data Types article on MSDN;
• OLE DB and ADO.NET Data Types – the data providers use their own type systems, which are exposed to the SSIS programming interface. These are also different from the rest of the type systems. When referring to these two data providers in this article, I specifically refer to their use in the Execute SQL Task. You can find more information on OLE DB data types and data type conversions on MSDN, beginning with Data Types in OLE DB (OLE DB). For more information about ADO.NET data types start with Data Type Mappings in ADO.NET.

The Base of All Bases

In the .NET Framework all data types are derived from Object. Therefore, all .NET data types are convertible to Object. In other words: wherever .NET data types are used and no other more appropriate data type is available, Object can be used instead.

SSIS variables utilize a subset of .NET data types, including Object; if the most appropriate data type is not available for your SSIS variable, use Object.

Why not use Object for just any SSIS variable? Think of debugging and logging. While most other data types implement at least one standard accessor that allows you to view or display the actual value, or to write it to a log, with Object you would have to add your own programmatic logic to convert the values to their actual type before they could be displayed or written to the log.

Exceptional Cases

DATE

Even though OLE DB documentation – for instance, the Data Type Support for OLE DB Date/Time Improvements article on MSDN (also available for SQL Server versions 2008 and 2008 R2) – suggests that the OLE DB DBTIMESTAMP be used for SQL Server DATETIME or SMALLDATETIME values, this data type does not seem to be supported in the Execute SQL Task. Attempts to pass SQL Server DATETIME or SMALLDATETIME values to (or from) an OLE DB DBTIMESTAMP parameter will result in the following error:

Executing the query "..." failed with the following error: "Invalid time format". 
Possible failure reasons: Problems with the query, "ResultSet" property not set 
correctly, parameters not set correctly, or connection not established correctly.

Alternatively, you can use the OLE DB DATE data type for DATETIME or SMALLDATETIME values. Be warned, though, that the precision of DATE is one second, which means that DATETIME values might be truncated. On the other hand, SMALLDATETIME values, whose precision is one minute, will not be affected.

ADO.NET is a more appropriate option in such cases, as it does not suffer from these limitations.

DECIMAL

Prior to SSIS 2012, DECIMAL was not a supported SSIS variable data type. Various workarounds have so far been proposed by other users, but to me the only sensible option for SSIS variables holding decimal values is the use of the Object data type; the values will be implicitly converted to the appropriate destination data type – for instance, when passed to an Execute SQL Task parameter. Starting with SQL Server 2012, DECIMAL is (finally) available for use with SSIS variables; so, at least this particular workaround is now a thing of the past.

The Decimal data type is available in OLE DB as well as ADO.NET data providers; however, only ADO.NET actually supports it in SSIS. The following exception is raised by SSIS when trying to use the DECIMAL data type in an Execute SQL Task using OLE DB (regardless of the fact that DECIMAL is listed as a supported data type in the Execute SQL Task editor in SSDT or BIDS):

The type is not supported.DBTYPE_DECIMAL

The OLE DB NUMERICAL data type is available and compatible; its precision and scale match the ones used for SQL Server DECIMAL (and NUMERIC) data types.

UNIQUEIDENTIFIER

Even though Guid is a native .NET data type, it is not part of the SSIS variable type system. You can create UNIQUEIDENTIFIER or Guid values using SSIS (e.g. using the Script Task or the Execute SQL Task), but in order to pass them to other SSIS components, you must resort to a "trick". For instance, to create a Guid value in a Script Task and then assign it to a SSIS variable of type String, use the following assignment:

Dts.Variables["my_guid_string_variable"].Value
 = "{" + Guid.NewGuid().ToString() + "}";

A Guid value cast to String and formatted this way can then be passed as a parameter to an Execute SQL Task, or to the SSIS Pipeline Buffer (e.g. in a Derived Column data flow component) – mapped to a Guid parameter, or a DT_GUID column. It will be implicitly cast to the appropriate type.

When UNIQUEIDENTIFIER values are returned from the Execute SQL Task, their type depends on the data provider used by the task. Attempts to assign a Guid value returned from ADO.NET to a String SSIS variable, will result in the usual type mismatch error:

The type of the value being assigned to variable "User::string_guid_variable" 
differs from the current variable type. Variables may not change type during 
execution. Variable types are strict, except for variables of type Object.

Again, you can prevent this by using Object as the SSIS variable data type.

SQL_VARIANT

It would be wrong to say that the SQL_VARIANT data type is not supported by the Execute SQL Task. On the other hand, you should be very careful with this; any OLE DB type, which is compatible with a SQL Server data type, which is in turn compatible with the SQL_VARIANT data type, can be used in the Execute SQL Task. However, having to know the type in advance contradicts the principal purpose of SQL_VARIANT – its ability to support a variety of data types in one variable, or in the same column. I would advise against using the OLE DB provider with the Execute SQL Task if you have to deal with SQL_VARIANT.

There is no equivalent data type for SQL_VARIANT in the type system used by the SSIS Pipeline Buffer. If you're using an OLE DB data source and destination, the Data Flow designer will automatically use the DT_WSTR type for SQL_VARIANT columns with the following warning:

The output "OLE DB Source Output" references an external data type that cannot 
be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR 
will be used instead.

Also note that the size of the column in the data flow metadata will be set by the editor based on the sample of rows provided at design time. At run time, you might encounter truncation errors, should the size of the actual data exceed the one set at design time.

If you're using an ADO.NET source, the Data Flow designer will automatically use the DT_NTEXT data type for SQL_VARIANT columns with the following warning:

The data type "System.Object" found on column "sql_variant_column" is not 
supported for the component "ADO NET Source" (201). This column will be 
converted to DT_NTEXT.

Personally, I would advise against using SQL_VARIANT columns in data flows at all, unless your own tests conclusively show that the data in your environment is read from the source and written to the destination correctly.

TABLE and XML

Even though the SQL Server TABLE type is supported by the .NET Framework, OLE DB, and ADO.NET – that is, via Table-valued Parameters – it is not supported as a data type in SSIS. Typically, in SSIS, in-flight set-oriented processing is performed using the Data Flow task; of course, if you can stage your data, and are not required to perform all data processing in a single data flow, you can move some of that set-oriented logic outside the SSIS process (for instance, updates or merges can be performed using the UPDATE and/or MERGE statements, executed from SSIS by using the Execute SQL Task).

XML is a complex native data type in SQL Server, and it can also be represented by a string. Unfortunately, in SSIS, things are not quite as simple as that. The .NET Framework implements several types based on the W3C XML Recommendation, ADO.NET also supports XML natively, and OLE DB supports the use of XML data – to some extent. I believe XML deserves special attention in SSIS, and I've covered it in more detail in my earlier post on SSIS and complex parameters.

Large Object Data

Large-object data types, such as VARCHAR(MAX), NVARCHAR(MAX) OR VARBINARY(MAX), are not fully supported by the Execute SQL Task, by neither the OLE DB, nor the ADO.NET, providers. By using the ADO.NET provider it is possible to pass IMAGE, NTEXT or VARBINARY(MAX) data to and from the Execute SQL Task, but neither VARCHAR(MAX), nor NVARCHAR(MAX), parameters are supported. It is, however, possible to work around these limitations by using more elaborate techniques, which I feel deserve a dedicated article, so keep watching this blog for more information on that particular subject.

Additional Notes

I have been meaning to write this article for years; mainly because I needed it (and still do), but also because I couldn't find a single online resource that would cover, in one place, all of the various type systems used in SSIS.

In the beginning, simply locating appropriate documentation quickly proved to be a daunting task; I've also tried to utilize online resources as much as possible – because, after all, this article too will be available online. I also wanted to test each combination, as life with IT has taught me not to take any statement, even if it's part of vendor documentation, for granted. Therefore, I created an SSIS solution in versions 2008 and 2012, which I have used to verify the compatibility of the different type systems. I've tried to be as careful as possible in my experimentation, making sure that I detected as many problems as possible before making any conclusions; however, I still feel there could be things that I might have overlooked.

Therefore, dear reader, if you happen to find any flaws in this (and any other of my texts dealing with data types), please, let me know.

ML

SQL Server 2012 FileTables, Text Files and Full-text Search

In July 2012, fellow SolidQ Mentor and Microsoft MVP, Davide Mauri (@mauridb), discovered an unexpected behavior of the SQL Server 2012 Full-text Search (FTS), and/or Statistical Semantic Search (SSS), while indexing documents placed in a SQL Server 2012 FileTable as TXT files. Instead of the language set in the definition of the full-text index (see CREATE FULLTEXT INDEX in SQL Server Books Online for details) the language determined by the Windows System Locale was used for full-text indexing.

In March 2013 the alleged bug was resolved by Microsoft as "Won't Fix", with the following explanation:

Posted by Microsoft on 6.3.2013 at 8:22

This is by design. In the attached script the Text IFilter would emit the System Locale for the work chunks. And we go by whatever the IFilter emits. The full-text index language will be used when we have a plain text column without the doctype column.

Regardless of the language set in the full-text index definition, the language emitted by the IFilter is used – both for the full-text index as well as for the statistical semantic indexes. Because TXT files do not store any additional metadata, such as the language used in the text, the IFilter resorts to the System Locale in order to "determine" the language. This means that if the language used in the files is different from the one used as the System Locale, the resulting full-text index might not contain correct, or complete, data, and if the language of the System Locale is not supported by the Statistical Semantic Search then the statistical semantic indexes will also not be populated. In other words: the actual language must be determined by other means.

The Solution

To allow multi-lingual documents, or simply documents written in a language different from the one set by the System Locale, that are stored in a FileTable, to be indexed in accordance with the language(s) used in them, these documents need to be stored using a format that supports additional metadata, such as the language of the text, which the corresponding IFilter must be able to retrieve during the full-text index population. For instance, Microsoft Word files, PDF files, or even XML files, allow you to set the language of either the entire document, or of its individual parts. The corresponding IFilters will respect these settings, prompting the use of appropriate word breakers and stemmers for FTS/SSS processing.

In the above Connect item, Davide used a Transact-SQL script to create a couple of text files, insert them into a FileTable, and then index them using both the "regular" full-text indexing as well as the statistical semantic indexing. The files contained English text, the language set for the full-text index was also English; however, in his environment the System Locale was set to Italian. The latter was then actually used for full-text index population, which is not what Davide (or me, for that matter) would have expected.

In the amended version of Davide's script I have used the XML format to store the documents as files, and inside the XML I specified the correct language of each document by using the xml:lang attribute. Download the script ^{(right-click, then Save target as...)} and try it out yourself.

ML

SSIS 2012 Bug with Windows Group Permissions

In October 2012 a bug was discovered in SQL Server 2012 Integration Services, specifically in the SSISDB Catalog, where permissions assigned to an SSISDB user mapped to a Windows Group login are not determined correctly at run time.

The SSISDB Catalog security model extends the native SQL Server security model to allow the permissions to be managed at various SSISDB object levels (e.g. folders, projects, packages, etc.). This extension is implemented inside the SSISDB database and is not fully integrated with the SQL Server security model. As a result, the actual SSISDB object permissions are determined per user at run time.

Fellow Microsoft MVP Phil Brammer (@PhilBrammer) pinpointed the source of the problem to an SSISDB catalog view, which he proposed be changed accordingly. As you can observe in the Connect item, the solution is very simple, but I am sure you can agree that implementing it would improve the usability of the SSISDB Catalog quite significantly. The item is still active, and if you are already using the new SSIS project deployment model, or are planning to use it at any time in the future, I urge you to vote on Connect for the problem to be corrected.

Is There a Workaround?

Until Microsoft resolves the issue, you can work around it by using the following approach, which I also described on Connect:

1. Create a new database role (for instance, named ssis_user) in the SSISDB database.
2. Add the login, based on the Windows NT group that you want to assign the permissions to, to this newly created database role.
3. Assign the appropriate permissions to the ssis_user SSISDB database role by using the SSISDB DCL procedures.

For instance, you can use the following SSMS Transact-SQL template to create the database role:

use SSISDB;
go

create role ssis_user
 authorization dbo;

alter role ssis_user
 add member <database_principal, sysname, Database principal>;
go

In SSMS, use the Ctrl + Shift + M keyboard shortcut to complete the script. You can find more information about SSMS Transact-SQL Templates in SQL Server Books Online.

ML

SSISDB Catalog Deep Dive at Bleeding Edge 2012

Between October 22^nd and 24^th 2012 the fifth installment of the Bleeding Edge conference took place in Laško, Slovenia. Considering the responses from the attendees as well as the speakers, the conference was once again a great success.

Congratulations to the organizers, and big thanks to the attendees! Special thanks go to the companies, who decided to send their developers and administrators to the event in spite of the current economic situation. With so many public, and privately owned, companies trying to reduce their costs by abandoning training altogether, it is these few, reasonable, employers who continue to drive the economy.

Considering its typical ROI, education is one of the least expensive investments these days; unfortunately too many CEO's fail to acknowledge this simple fact.

SSISDB Catalog Deep Dive

At this year's Bleeding Edge I presented a session on SSISDB catalog, the new Microsoft SQL Server 2012 feature used for storing SSIS solutions. Actually, the SSISDB catalog is much more than that; it provides an integrated environment for SSIS project deployment, maintenance, execution, and monitoring.

You can read more about the SSISDB catalog in SQL Server Books Online (you should start with the article entitled "SSIS Catalog"); the subject is also covered in the upcoming Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft^® SQL Server^® 2012, written by SolidQ mentors Dejan Sarka, Grega Jerkič, and yours truly.

At the session I received one question from the audience that I didn't feel comfortable answering just there and then. I was simply not quite sure whether the most obvious answer also represented an actually supported scenario. I've since been able to locate the appropriate solution, and have been able to confirm it in practice.

SSISDB Catalog Disaster Recovery and Migration

The question was simple:
How to migrate an SSISDB catalog from one server to another?

As far as SSISDB database disaster recovery is concerned, the answer is fairly simple: BACKUP and RESTORE are supported for the SSISDB database, and on the same instance no additional activities are required to facilitate the restore. By default, the SSISDB database is in full recovery mode, which means that both the full database backup as well as regular transaction log backups must be in place to correspond to the recovery mode, and allow point-in-time restores.

But what about SSISDB catalog migrations to a different SQL Server instance? Naturally, the procedure is documented in SQL Server Books Online, in the article entitled "Backup, Restore, and Move the SSIS Catalog".

Special consideration is required when the SSISDB database is restored on a SQL Server instance where the SSISDB catalog has not previously been created. To simplify the migration in such a case, I would suggest to first create a new SSISDB catalog (for instance, by using SSMS) as described in the SQL Server Books Online article entitled "Create the SSISDB Catalog", and then replace the newly created, empty, SSISDB database by restoring the actual one from the backup files.

Of course, you should not attempt any of this without first carefully studying the disaster recovery and migration article mentioned earlier. Remember: SSISDB catalog migration is not trivial; the creation of the SSISDB catalog consists of more activities than just the creation of the SSISDB database (two SQL Server Agent jobs are created, the appropriate security settings are put in place, the SSIS startup procedure is configured, and specific permissions are granted to allow the execution of SSIS CLR stored procedures).

I hope this answers the questions concerning SSISDB catalog disaster recovery and migrations. If there were any other questions raised at the session that I've not responded to yet, please, let me know.

ML

SQL Server 2012: Migrating BLOBs to FILETABLEs (MVP Mondays)

I have been invited by Melissa Travers to participate in the MVP Monday Series at the Microsoft MVP Award Program Blog.

The subject of my initial article is the migration of large data into FileTables, a new large data management feature introduced in Microsoft SQL Server 2012.

You can find the article (including sample code) at the following address:

• http://blogs.msdn.com/b/mvpawardprogram/archive/2012/07/23/sql-server-2012-migrating-blobs-to-filetables.aspx.

Actually, they've been moved here:

• https://docs.microsoft.com/en-us/archive/blogs/mvpawardprogram/sql-server-2012-migrating-blobs-to-filetables.

Enjoy!

ML

XML Query Composition in Practice

XML composition using XML Query is not what you might call a popular subject, not even a frequently discussed one; well, at least as far as SQL Server is concerned. In this blog, I have discussed XML retrieval on numerous occasions, I have also touched SQL Server XML performance characteristics, but XML composition has so far been stuck on the back burner. Until now, that is.

The subject of XML composition is covered prominently in the XQuery W3C Recommendation. It can be used not only to create new XML documents, but also to transform existing ones. Even though SQL Server currently doesn't implement the entire XQuery Recommendation, the essentials are covered, and have been available since SQL Server 2005.

A Little Bit of Background

SQL Server provides two XML composition methods natively:

• The FOR XML clause instructs the Database Engine to return the result of a SELECT query as an XML document; and
• XQuery, principally used to retrieve XML data, can also be used to create XML documents (or fragments).

The SQL Server implementation of XQuery supports two XML composition methods (please, copy them to SSMS):

• Using direct constructors – the XML document is created from a string, mimicking the resulting XML structure.
  E.g.:
  

  select cast(N'' as xml).query
    ('
    <collection>
     <item number="1">
      This is an item in the collection.
     </item>
    </collection>
    ')

• Using computed constructors – rather using a string representation to build the resulting XML document or fragment, the result is created using special XQuery instructions.
  E.g.:
  

  select cast(N'' as xml).query
    ('
    element  collection
    {
     element  item
     {
      text  {"This is an item in the collection."}
      ,attribute number { "1" }
     }
    }
    ')

After copying the above examples to SSMS, execute them. Compare the results.

Both methods support the use of two extension functions, sql:column and sql:variable, which allow the data to be added to the resulting XML document dynamically (e.g. from a SQL variable, or from a column in the available row set). The part of the expression where an empty string is converted to XML (i.e. cast(N'' as xml)) is used simply to provide a reference to an empty XML document, required by the query XML function.

The following XQuery Computed Constructors are available in SQL Server:

• element – instructing the Database Engine to construct an element node;
• attribute – instructing the Database Engine to construct an attribute node; and
• text – instructing the Database Engine to construct a text node.

Let this be enough background for now, as I will discuss more in upcoming posts. In this one, however, let's put XML composition to some good use, and also illustrate how to use it.

To help you get started in understanding this rather complex subject, I've prepared a T-SQL script, and you are free to use it to learn about XML composition. Please, open it and then copy it to a new query window in SSMS.

The script uses the data about the tables in your database, exposed in INFORMATION_SCHEMA system views, and builds an XML Schema document for each table. You can use the script to create XML Schema files for your tables that can then be used to transport the data using XML, or to provide a standard way of describing the tables in your SQL Server database. Of course, the principal purpose of the script is to demonstrate SQL Server XML composition.

But before executing any script on SQL Server, we should know exactly what it does.

Decomposing the XML Schema CTE

Let's have a look at the individual elements of the Common Table Expression (or CTE, for short) presented in the script.

The XML Namespaces

...
with xmlnamespaces
  (
  'http://www.w3.org/2001/XMLSchema' as xs
  )
...

We are building an XML Schema, therefore we need the appropriate namespace declarations.

The Data Type Map

...
 ,TypeMap -- Mapping SQL Server data types to XML Schema data types
  (
  SqlType  -- SQL Server Data Type
  ,XmlSchemaType -- XML Schema Data Type
  ,IsFixed
  )
 as
 (
 select 'bigint' as SqlType
  ,'xs:long' as XmlSchemaType
  ,1 as IsFixed
 union
 select 'binary'
  ,'xs:hexBinary'
  ,0
 union
 select 'bit'
  ,'xs:boolean'
  ,1
 union
...(shortened to improve readability)...
 select 'varbinary'
  ,'xs:hexBinary'
  ,0
 union
 select 'varchar'
  ,'xs:string'
  ,0
 union
 select 'xml'
  ,'xs:string'
  ,1
 )
...

SQL Server Data Types, used by table columns, need to be translated to XML Schema Data Types. The purpose of the IsFixed column in the CTE above, is to divide the data types into two groups:

• The data types that do not require dimensioning, such as bit, int, ntext, xml, etc. – for these the value of IsFixed = 1; and
• The data types that support dimensioning (length, or scale and precision), such as decimal, [n]varchar, [n]char, etc. for these the value of IsFixed = 0.

Nillability

...
 ,Nillability -- Mapping SQL Server nillability to XML Schema nillability
  (
  SqlNillable
  ,XmlNillable
  )
 as
 (
 select 'NO' as SqlNillable
  ,N'false' as XmlNillable
 union
 select 'YES'
  ,N'true'
 )
...

Column nillability must also be translated accordingly.

The Primary Key

...
 ,PrimaryKey
 as
 (
 select TABLE_CONSTRAINTS.TABLE_CATALOG as [Catalog]
  ,TABLE_CONSTRAINTS.TABLE_SCHEMA as [Schema]
  ,TABLE_CONSTRAINTS.TABLE_NAME as [Table]
  ,TABLE_CONSTRAINTS.CONSTRAINT_NAME as Name
  ,[Columns]
   = (
   select COLUMN_NAME as [@xpath]
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    where (KEY_COLUMN_USAGE.TABLE_CATALOG = TABLE_CONSTRAINTS.TABLE_CATALOG)
     and (KEY_COLUMN_USAGE.TABLE_SCHEMA = TABLE_CONSTRAINTS.TABLE_SCHEMA)
     and (KEY_COLUMN_USAGE.TABLE_NAME = TABLE_CONSTRAINTS.TABLE_NAME)
     and (KEY_COLUMN_USAGE.CONSTRAINT_NAME = TABLE_CONSTRAINTS.CONSTRAINT_NAME)
    order by KEY_COLUMN_USAGE.ORDINAL_POSITION
    for xml path('xs:field'), type
   )
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  where (TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY')
 )
...

The SQL Server Primary Key constraint is translated to the XML Schema key constraint. Only a single key element is supported in XML Schema, just like in a SQL Server table, and the key can either be single-column, or a composite key.

Unique Constraints

...
 ,UniqueConstraint
 as
 (
 select TABLE_CONSTRAINTS.TABLE_CATALOG as [Catalog]
  ,TABLE_CONSTRAINTS.TABLE_SCHEMA as [Schema]
  ,TABLE_CONSTRAINTS.TABLE_NAME as [Table]
  ,TABLE_CONSTRAINTS.CONSTRAINT_NAME as Name
  ,[Columns]
   = (
   select COLUMN_NAME as [@xpath]
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    where (KEY_COLUMN_USAGE.TABLE_CATALOG = TABLE_CONSTRAINTS.TABLE_CATALOG)
     and (KEY_COLUMN_USAGE.TABLE_SCHEMA = TABLE_CONSTRAINTS.TABLE_SCHEMA)
     and (KEY_COLUMN_USAGE.TABLE_NAME = TABLE_CONSTRAINTS.TABLE_NAME)
     and (KEY_COLUMN_USAGE.CONSTRAINT_NAME = TABLE_CONSTRAINTS.CONSTRAINT_NAME)
    order by KEY_COLUMN_USAGE.ORDINAL_POSITION
    for xml path('xs:field'), type
   )
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  where (TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE')
 )
...

Multiple unique constraints in a SQL Server table can be translated to multiple XML Schema unique constraints. These can also be single-column, or composite constraints.

Self-referencing Foreign Keys

...
 ,SelfReference
 as
 (
 select FOREIGN_KEY.TABLE_CATALOG as [ForeignCatalog]
  ,FOREIGN_KEY.TABLE_SCHEMA as [ForeignSchema]
  ,FOREIGN_KEY.TABLE_NAME as [ForeignTable]
  ,FOREIGN_KEY.CONSTRAINT_NAME as ForeignKeyName
  ,PRIMARY_KEY.CONSTRAINT_NAME as PrimaryKeyName
  ,[ForeignColumns]
   = (
   select COLUMN_NAME as [@xpath]
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    where (KEY_COLUMN_USAGE.TABLE_CATALOG = FOREIGN_KEY.TABLE_CATALOG)
     and (KEY_COLUMN_USAGE.TABLE_SCHEMA = FOREIGN_KEY.TABLE_SCHEMA)
     and (KEY_COLUMN_USAGE.TABLE_NAME = FOREIGN_KEY.TABLE_NAME)
     and (KEY_COLUMN_USAGE.CONSTRAINT_NAME = FOREIGN_KEY.CONSTRAINT_NAME)
    order by KEY_COLUMN_USAGE.ORDINAL_POSITION
    for xml path('xs:field'), type
   )
  from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
   inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS FOREIGN_KEY
     on FOREIGN_KEY.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
   inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS PRIMARY_KEY
     on PRIMARY_KEY.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
  where (FOREIGN_KEY.CONSTRAINT_TYPE = 'FOREIGN KEY')
   and (FOREIGN_KEY.TABLE_CATALOG = PRIMARY_KEY.TABLE_CATALOG)
   and (FOREIGN_KEY.TABLE_SCHEMA = PRIMARY_KEY.TABLE_SCHEMA)
   and (FOREIGN_KEY.TABLE_NAME = PRIMARY_KEY.TABLE_NAME)
 )
...

Foreign Key constraints are supported by XML Schema; however, in our particular case, we are creating schemas for individual tables. Therefore, only self-referencing foreing keys will be translated – i.e. keys, that reference one or more columns in the same table.

The Columns

...
select case
  when (TypeMap.IsFixed = 1)
   then cast(N'' as xml).query
     ('
     element  xs:element
     {
      attribute name {sql:column("COLUMNS.COLUMN_NAME")}
      ,attribute type {sql:column("TypeMap.XmlSchemaType")}
      ,attribute nillable {sql:column("Nillability.XmlNillable")}
     }
     ')
  when (COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null)
   and (COLUMNS.CHARACTER_MAXIMUM_LENGTH != -1)
   then cast(N'' as xml).query
     ('
     element  xs:element
     {
      attribute name {sql:column("COLUMNS.COLUMN_NAME")}
      ,attribute nillable {sql:column("Nillability.XmlNillable")}
      ,element xs:simpleType
      {
       element  xs:restriction
       {
        attribute base {sql:column("TypeMap.XmlSchemaType")}
        ,element xs:maxLength
        {
         attribute value {sql:column("COLUMNS.CHARACTER_MAXIMUM_LENGTH")}
        }
       }
      }
     }
     ')
  when (COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null)
   and (COLUMNS.CHARACTER_MAXIMUM_LENGTH = -1)
   then cast(N'' as xml).query
     ('
     element  xs:element
     {
      attribute name {sql:column("COLUMNS.COLUMN_NAME")}
      ,attribute nillable {sql:column("Nillability.XmlNillable")}
      ,element xs:simpleType
      {
       element  xs:restriction
       {
        attribute base {sql:column("TypeMap.XmlSchemaType")}
        ,element xs:maxLength
        {
         attribute value {2147483647}
        }
       }
      }
     }
     ')
  when (COLUMNS.CHARACTER_MAXIMUM_LENGTH is null)
   and (COLUMNS.NUMERIC_PRECISION is not null)
   and (COLUMNS.NUMERIC_SCALE is null or COLUMNS.NUMERIC_SCALE = 0)
   then cast(N'' as xml).query
     ('
     element  xs:element
     {
      attribute name {sql:column("COLUMNS.COLUMN_NAME")}
      ,attribute nillable {sql:column("Nillability.XmlNillable")}
      ,element xs:simpleType
      {
       element  xs:restriction
       {
        attribute base {sql:column("TypeMap.XmlSchemaType")}
        ,element xs:totalDigits
        {
         attribute value {sql:column("COLUMNS.NUMERIC_PRECISION")}
        }
       }
      }
     }
     ')
  when (COLUMNS.CHARACTER_MAXIMUM_LENGTH is null)
   and (COLUMNS.NUMERIC_PRECISION is not null)
   and (COLUMNS.NUMERIC_SCALE is not null)
   and (COLUMNS.NUMERIC_SCALE != 0)
   then cast(N'' as xml).query
     ('
     element  xs:element
     {
      attribute name {sql:column("COLUMNS.COLUMN_NAME")}
      ,attribute nillable {sql:column("Nillability.XmlNillable")}
      ,element xs:simpleType
      {
       element  xs:restriction
       {
        attribute base {sql:column("TypeMap.XmlSchemaType")}
        ,element xs:totalDigits
        {
         attribute value {sql:column("COLUMNS.NUMERIC_PRECISION")}
        }
        ,element xs:fractionDigits
        {
         attribute value {sql:column("COLUMNS.NUMERIC_SCALE")}
        }
       }
      }
     }
     ')
  else cast(N'' as xml).query
    ('
    element  xs:element
    {
     attribute name {sql:column("COLUMNS.COLUMN_NAME")}
     ,attribute type {"xs:string"}
     ,attribute nillable {sql:column("Nillability.XmlNillable")}
    }
    ')
  end
 from INFORMATION_SCHEMA.COLUMNS
  inner join Nillability
    on Nillability.SqlNillable = COLUMNS.IS_NULLABLE
  left join TypeMap
    on TypeMap.SqlType = COLUMNS.DATA_TYPE
 where (COLUMNS.TABLE_CATALOG = TABLES.TABLE_CATALOG)
  and (COLUMNS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA)
  and (COLUMNS.TABLE_NAME = TABLES.TABLE_NAME)
 for xml path(''), root('Columns'), type
...

Table columns are translated to XML elements, each with appropriate XML Schema type extensions used to define the domain, which must be logically and functionally equivalent to the SQL Server domain used in each table column. Observe the case expression and see how the IsFixed column of the data type map is used, together with the actual SQL Server data type declarations, to translate SQL Server column domains to XML Schema domains.

Keys

...
select PrimaryKey.Name as [@name]
 ,'row' as [xs:selector/@xpath]
 ,(
 select PrimaryKey.[Columns]
 )
 from PrimaryKey
 where (PrimaryKey.[Catalog] = TABLES.TABLE_CATALOG)
  and (PrimaryKey.[Schema] = TABLES.TABLE_SCHEMA)
  and (PrimaryKey.[Table] = TABLES.TABLE_NAME)
 for xml path('xs:key'), root('PrimaryKey'), type
...
select UniqueConstraint.Name as [@name]
 ,'row' as [xs:selector/@xpath]
 ,(
 select UniqueConstraint.[Columns]
 )
 from UniqueConstraint
 where (UniqueConstraint.[Catalog] = TABLES.TABLE_CATALOG)
  and (UniqueConstraint.[Schema] = TABLES.TABLE_SCHEMA)
  and (UniqueConstraint.[Table] = TABLES.TABLE_NAME)
 for xml path('xs:unique'), root('UniqueConstraints'), type
...
select SelfReference.PrimaryKeyName as [@refer]
 ,SelfReference.ForeignKeyName as [@name]
 ,'row' as [xs:selector/@xpath]
 ,(
 select SelfReference.[ForeignColumns]
 )
 from SelfReference
 where (SelfReference.[ForeignCatalog] = TABLES.TABLE_CATALOG)
  and (SelfReference.[ForeignSchema] = TABLES.TABLE_SCHEMA)
  and (SelfReference.[ForeignTable] = TABLES.TABLE_NAME)
 for xml path('xs:keyref'), root('SelfReferences'), type
...

All three XML Schema key constraints follow the same principle: the selector defines the context for the key – the key is enforced inside the context of the node, designated by the XPath expression, and one or more fields define the nodes that constitute the key (again, via XPath expressions).

The XQuery Composition

...
.query
 ('
 element  xs:schema
 {
  element  xs:element
  {
   attribute name {concat(sql:column("TABLES.TABLE_SCHEMA")
      , ".", sql:column("TABLES.TABLE_NAME"))}
   ,element xs:complexType
   {
    element  xs:choice
    {
     attribute minOccurs {0}
     ,attribute maxOccurs {"unbounded"}
     ,element xs:element
     {
      attribute name {"row"}
      ,element xs:complexType
      {
       (: Table Columns :)
       element  xs:sequence {Table/Columns/xs:element}
      }
     }
    }
   }
   (: Primary Key :)
   ,Table/PrimaryKey/xs:key
   (: Unique Constraint(s) :)
   ,Table/UniqueConstraints/xs:unique
   (: Self-referencing Foreign Key(s) :)
   ,Table/SelfReferences/xs:keyref
  }
 }
 ')
...

The retrieval from INFORMATION_SCHEMA views in the example above is set-oriented – as many XML Schema definitions will be created as there are rows in the outer query (i.e. as there are user tables in the database) – all in a single result set. From the XML composition perspective, the retrieval operation is also nested – a table may contain multiple columns as well as a single constraint may be composed of multiple columns. Finally, all individual elements are placed into a single XML Schema definition.

Observe the comments inside the XQuery, enclosed in (: :) comment delimiters, to understand where and how individual T-SQL sub-queries are referenced to place the relevant XML fragments into the resulting XML document.

Ready?

When you're done reviewing the script, execute it. Observe the results, review the newly composed XML Schemas.

Homework

The best way to learn something is to try it out. :)

• Modify the original query to see the results returned by individual CTEs: the TypeMap, the Nillability, the PrimaryKey, the UniqueConstraint, the SelfReference.
• Modify the final query, and move individual XML columns, designated by the comments "Table Columns", "Primary Key", "Unique Constraint(s)", and "Self-reference Foreign Key(s)" to separate CTEs.

ML

SQL Server 2012 RTM

Earlier this month SQL Server 2012 RTM was announced, and the evaluation is now available for download:

• Microsoft SQL Server 2012 Evaluation.

The build number for the RTM is 11.0.2100.60.

Related downloads are also available:

• Books Online for SQL Server 2012 – product documentation is available online, and can also be installed locally. Books Online are not installed as part of the SQL Server installation; the Help Viewer needs to be configured appropriately for you to access SQL Server documentation;
• Microsoft SQL Server Data Tools – not simply a replacement for the SQL Server Business Intelligence Development Studio, this new – freely available – tool provides an improved database development experience, both to SQL Server as well as SQL Azure database developers;
• Microsoft SQL Server 2012 Feature Pack – additional components are available separately (all 34 of them);
• Adventure Works for SQL Server 2012 – sample databases are available on CodePlex. Read the installation instructions carefully; the deployment of data samples is not a trivial task, although it should not cause problems to a seasoned DBA.

Note to DQS users upgrading to RTM from RC0: before attempting to upgrade your Data Quality Services (DQS) installation, read the instructions available at TechNet:

• Upgrade DQS: SQL Server 2012 RC0 to SQL Server 2012 RTM.

May it serve you well!

ML

XPath ID for People Avoiding Pubs

I discussed the ID XPath function in a recent post. I composed the data samples used in that post from the data available in the pubs sample database. Since this particular database may not be a very popular commodity these days, I've also prepared a "pubs-free" alternative – using the same data as before, but with fewer obstacles.

The sample consists of two scripts showcasing the examples I've provided in my principal post – the retrieval of relational data from an XML document using special XML Schema data types. You can execute the scripts in your favorite testing database, regardless of whether the pubs database is present on your system or not. Of course, you should review the code before attempting to execute it, and follow the comments describing each individual execution step provided for you in the scripts.

• Publishers and Titles;
• Titles and Authors.

The examples above demonstrate the retrieval part of the operation, XML composition is demonstrated in the earlier post.

Enjoy!

ML

p.s. This may not be the right time of year to avoid pubs, though. ;)

SQL Server: The XPath ID Function

XML provides a simple and efficient way of storing relational data, especially for the purposes of transporting it from one RDBMS to another. Besides the "natural" technique, where the structure of the XML document is used to represent the relationships, XML Schema also provides three special data types that can be used to define the relationships inside an XML document. I've already discussed the former in my chapter of SQL Server MVP Deep Dives, so in this article we will examine the latter.

The xs:ID, xs:IDREF and xs:IDREFS XML Schema Data Types

The ID data type is a derived XML Schema data type:

- string

- normalizedString

- token

- Name

- NCName

- ID

The top-most type above is the string data type, a primitive XML Schema data type, and the types following it are derived from string by restriction – each derivate is more restrictive in respect to its properties and use than its base type.

While a valid string node can contain any character, a valid ID must begin with a letter or an underscore ("_") character, may contain any numeral and/or letter, but may only contain a restricted set of punctuation characters (the dot ".", the dash "-", and the underscore "_"), and it must not contain any blank characters (spaces, tabs, carriage-return or line-feed characters).¹

The purpose of the ID data type is to serve as a key – to uniquely identify each individual element of an XML document. Therefore, each ID value inside an individual XML document must be unique. This also serves a second purpose, namely to allow each uniquely identified element to be referenced by one or more related elements of the same document – via an IDREF or an IDREFS attribute.

...

- ID

- IDREF ... IDREFS

The IDREF data type is derived from the ID type – also by restriction: IDREF nodes can only contain valid ID values that exist inside the same XML document. For instance, in the following XML document, the address/@address_id attribute (typed as ID) represents a unique key of each address element, and the person/@address_id attribute (typed as IDREF) represents a reference to an address element. However, in this particular example only the first person element contains a valid reference, while the second one does not:

An address element with @address_id of "A3" does not exist in the above document.

The IDREFS data type is derived from the IDREF data type by list – it may contain a single ID reference or a space-delimited list of ID references. For instance, in the following example, the person/@address_id attribute (this time typed as IDREFS) contains multiple references:

NB: If the examples in this article aren't displayed correctly, and you're using Internet Explorer 9, please, use Compatibility View.

To illustrate the use of XML to store relational data by implementing these special XML Schema data types, I've created two XML documents from the data available in the pubs database, which is (or rather, used to be) a simplistic, yet robust data sample used by SQL Server documentation. The sample database is (still) available online.

For those of you who never worked with the pubs sample: it represents a database used in a library application used in the management of information about books, authors, publishers, etc. In this article I use two particular relationships from the pubs data model:

1. Each book is published by exactly one publisher, and each publisher can publish zero, one or more books;
2. Each book is written by one or more authors, and each author has written one or more books.²

Publishers and Titles

The relationship between the publishers and the books is shown in the following XML document:

The document contains a set of publishers (publisherCollection), each with a unique identifier stored in the @publisherId attribute (publisher/@publisherId), and a set of books (titleCollection), each with a valid reference to a publisher (title/@publisherId).

The following XML Schema governs the integrity of the "Publishers and Titles" XML document:

I prepared a script that you can use to create the corresponding XML Schema Collection in your copy of the pubs database. You will need to do so in order to try out the examples in this article. (NB: you can safely remove both XML Schema Collections presented in this article after you're done experimenting.)

I used the following T-SQL query to compose the first XML document, demonstrating the relationship between the Publishers and the Titles:

begin
 with xmlnamespaces
  (
  'http://www.w3.org/2001/XMLSchema' as xs
  ,'http://schemas.milambda.net/pubs' as bp
  )
 select cast((
  select (
   select [@bp:publisherId]
     = N'Pub_'
     + publishers.pub_id
    ,publishers.pub_name as [bp:name]
    ,publishers.city as [bp:city]
    ,publishers.[state] as [bp:state]
    ,publishers.country as [bp:country]
    from dbo.publishers
    where (exists (
      select *
       from dbo.titles
       where (titles.title_id like 'BU%')
        and (titles.pub_id = publishers.pub_id)
      ))
    for xml path('bp:publisher'), root('bp:publisherCollection'),
        elements xsinil, type
   )
   ,(
   select [@bp:titleId]
     = N'Titl_'
     + titles.title_id
    ,[@bp:publisherId]
     = N'Pub_'
     + titles.pub_id
    ,titles.title as [bp:title]
    ,titles.[type] as [bp:type]
    ,titles.price as [bp:price]
    from dbo.titles
    where (titles.title_id like 'BU%')
    for xml path('bp:title'), root('bp:titleCollection'),
        elements xsinil, type
   )
  for xml path(''), root('bp:pubs'), type) as xml(dbo.BookPublisher))
end

The query above produces a typed XML document. Proper typing is needed, not only to preserve data integrity, but also to provide the XML processor with information about the relationships that exist inside the XML document.

Titles and Authors

The relationship between the authors and the books is shown in the following XML document:

This document contains a set of authors (authorCollection), each with a unique identifier stored in the @authorId attribute (author/@authorId), and a set of books (titleCollection), each with a valid set of references to one or more authors (title/@authorIds).

The "Titles and Authors" XML document is also governed by an XML Schema:

You can use this script to create the corresponding XML Schema Collection in the pubs database.

This XML document was also composed using T-SQL, and is also typed accordingly:

begin
 with xmlnamespaces
  (
  'http://www.w3.org/2001/XMLSchema' as xs
  ,'http://schemas.milambda.net/pubs' as ba
  )
 select cast((
  select (
   select [@ba:authorId]
     = N'Auth_'
     + authors.au_id
    ,authors.au_lname as [ba:lastName]
    ,authors.au_fname as [ba:firstName]
    from dbo.authors
    where (exists (
      select *
       from dbo.titleauthor
        inner join dbo.titles
          on titles.title_id = titleauthor.title_id
       where (titles.title_id like 'BU%')
        and (titleauthor.au_id = authors.au_id)
      ))
    for xml path('ba:author'), root('ba:authorCollection'), type
   )
   ,(
   select [@ba:titleId]
     = N'Titl_'
     + titles.title_id
    ,[@ba:authorIds]
     = (
     select [authorId]
       = N'Auth_'
       + titleauthor.au_id
      from dbo.titleauthor
      where (titleauthor.title_id = titles.title_id)
      for xml path('titleAuthor'), type
     ).query('distinct-values(titleAuthor/authorId)').value('.', 'nvarchar(max)')
    ,titles.title as [ba:title]
    ,titles.[type] as [ba:type]
    ,titles.price as [ba:price]
    from dbo.titles
    where (titles.title_id like 'BU%')
    for xml path('ba:title'), root('ba:titleCollection'),
        elements xsinil, type
   )
  for xml path(''), root('ba:pubs'), type) as xml(dbo.BookAuthor))
end

Since more than one author could have collaborated on a single book, there could be multiple references from a Title to the Authors. SQL Server implements the fn:distinct-values XPath function that I've used in the query above to list multiple references, representing the many-to-many relationship between a Title and its Authors.

The function accepts a single argument, namely an XPath expression pointing to one or more nodes, and returns the atomic values from the resulting node set in form of a space-separated string, without duplicates, which is exactly what we need in our case – a set of references, to be placed in an IDREFS attribute.

The fn:id XPath Function

To query the XML documents composed in this article – for instance, to retrieve the Publisher who published a particular Book, or to retrieve a list of Authors who wrote it – we can use the fn:id XPath function, introduced in SQL Server 2005.

The current SQL Server implementation of this function accepts a single argument, namely an XPath expression pointing to an xs:IDREF (or xs:IDREFS) node containing a reference (or a list of references). The function returns a set of related XML nodes, containing the corresponding ID values.

1. To retrieve a list of publishers whose books cost less than $11 from the "Publishers and Titles" XML document:

   begin
    with xmlnamespaces
     (
     'http://www.w3.org/2001/XMLSchema' as xs
     ,'http://schemas.milambda.net/pubs' as bp
     )
    select Pubs.Publisher.query('bp:name').value
               ('.', 'varchar(40)') as PublisherName
     from @pubs.nodes
       ('
       id(data(/bp:pubs/bp:titleCollection/bp:title
           [bp:price < 11]/@bp:publisherId))
       ') Pubs (Publisher)
   end

   Result:

   PublisherName
   ----------------------------------------
   New Moon Books
   
   (1 row(s) affected)

2. To retrieve a list of authors whose books cost less than $12 from the "Titles and Authors" XML Document:

   begin
    with xmlnamespaces
     (
     'http://www.w3.org/2001/XMLSchema' as xs
     ,'http://schemas.milambda.net/pubs' as ba
     )
    select Pubs.Author.query('ba:lastName').value
               ('.', 'varchar(40)') as LastName
     ,Pubs.Author.query('ba:firstName').value
               ('.', 'varchar(20)') as FirstName
     from @pubs.nodes
       ('
       id(data(/ba:pubs/ba:titleCollection/ba:title
           [ba:price < 12]/@ba:authorIds))
       ') Pubs (Author)
   end

   Result:

   LastName                                 FirstName
   ---------------------------------------- --------------------
   Green                                    Marjorie
   O'Leary                                  Michael
   MacFeather                               Stearns
   
   (3 row(s) affected)

Can you think of another alternative for each of these queries? Is it more or less complex than the one with the fn:id function?

The fn:idref XPath function

The fn:idref XPath function should do exactly the opposite – for instance, return a list of Books published by a particular Publisher, or a list of Books written by a particular Author. I say should, because unfortunately the fn:idref function is not implemented in SQL Server.

To query a relationship from the perspective of the key, we have to resort to slightly more elaborate techniques. For example, using XQuery:

1. To retrieve a list of books published by a publisher from Berkeley:

   begin
    with xmlnamespaces
     (
     'http://www.w3.org/2001/XMLSchema' as xs
     ,'http://schemas.milambda.net/pubs' as bp
     )
    select Titles.Title.query('bp:title').value('.', 'varchar(80)') as Title
     from @pubs.nodes
       ('
       for $p in /bp:pubs/bp:publisherCollection/bp:publisher
           [bp:city = "Berkeley"]/@bp:publisherId
       return /bp:pubs/bp:titleCollection/bp:title[@bp:publisherId = $p]
       ') Titles (Title)
   end

   Result:

   Title
   --------------------------------------------------------------------------------
   The Busy Executive's Database Guide
   Cooking with Computers: Surreptitious Balance Sheets
   Straight Talk About Computers
   
   (3 row(s) affected)

2. To retrieve a list of books published by an author with the last name of Green:

   begin
    with xmlnamespaces
     (
     'http://www.w3.org/2001/XMLSchema' as xs
     ,'http://schemas.milambda.net/pubs' as ba
     )
    select Titles.Title.query('ba:title').value('.', 'varchar(80)') as Title
     from @pubs.nodes
       ('
       for $a in /ba:pubs/ba:authorCollection/ba:author
           [ba:lastName = "Green"]/@ba:authorId
       return /ba:pubs/ba:titleCollection/ba:title
           [contains(string(@ba:authorIds), $a)]
       ') Titles (Title)
   end

   Result:

   Title
   --------------------------------------------------------------------------------
   The Busy Executive's Database Guide
   You Can Combat Computer Stress!
   
   (2 row(s) affected)

Relational Data and XML

There you have it – yet another method of using XML as a means of transporting relational data. This time enforced in composition by dedicated XML Schema data types, and assisted in retrieval by the corresponding XPath function(s). Why wait until the data has reached its destination to verify whether relationships have been preserved accordingly?

Update: If the pubs database is not available in your environment, and if you're merely interested in the retrieval part of this exercise, you can find a simplified variation of the samples in another post.

ML

¹ You can find all the details in the XML Schema Datatypes W3C Recommendation.

² How would we call an author who hasn't written any books? ;)

SQL Server Integration Services, Execute SQL with Complex Parameters

If you're familiar with SQL Server Integration Services (SSIS) then you've probably, at one time or another, run into problems with the integration bit – however weird that may sound. I can understand the fact that SSIS is supposed to be generic and universal and platform independent, and therefore not favor a particular DBMS – not even the one that it's a part of. Nonetheless, for years now I have wished for SSIS to support SQL Server just a little better, and I'm still waiting... And as far as I know, so is pretty much everybody else I've talked to about this, ever since SSIS first came out.

In this post I discuss one particular issue with SQL Server Integration Services that has been the source of many headaches for me and I bet for a lot of you as well: complex parameters and the Execute SQL Task.

Rise above Primitive

Just what is a complex parameter? Well, a parameter of a complex data type, of course. For example:

• XML – it was introduced with SQL Server 2005, the same version that brought us SQL Server Integration Services. I've discussed XML on several occasions;
• User-defined CLR Types – also introduced with SQL Server 2005; and
• Table-valued Parameters – introduced with SQL Server 2008.

I. XML

Imagine a stored procedure with an XML parameter... Or better yet, take a look at these two:

• The first one returns the current date and time in an output XML parameter:
  

  create proc dbo.GetDate_asXml
   (
   @dateAsXml xml   = null  output
   )
  as
  begin
   with xmlnamespaces
    (
    'http://schemas.testing-ground.com' as tg
    )
   select @dateAsXml
     = (
     select getdate() as [@tg:date]
      for xml path('tg:element'), root('tg:entity'), type
     )
   ;
  end
  go

• The second one accepts an XML input parameter of the type returned by the first procedure, then extracts a date/time value from it, and returns it in a result set:
  

  create proc dbo.ExtractDate_asXml
   (
   @dateAsXml  xml
   )
  as
  begin
   with xmlnamespaces
    (
    'http://schemas.testing-ground.com' as tg
    )
   select @dateAsXml.query
     ('
     data(/tg:entity[1]/tg:element[1]/@tg:date)
     ').value
      (
      '.'
      ,'datetime'
      ) as ExtractedDateTime
   ;
  end
  go

How do we configure an SSIS Execute SQL Task to successfully execute both these procedures?

Data Source Providers and Data Types

The first choice we need to make is between the two data providers available to the task in question (we're connecting to SQL Server):

• OLE DB; or
• ADO.Net.

Next, we need to select the appropriate data types for:

• the SSIS Package variable(s); and
• the procedure's parameter(s).

Olé, DB!

According to the SSIS/SQL/OLE DB data type mapping, documented in the MSDN Library (unfortunately, not all of it in the same place, but that's another story), the SQL Server XML data type should map to the NVARCHAR OLE DB data type. Unfortunately, this is only half-true. None of the OLE DB data types, available to the Execute SQL Task in SQL Server Integration Services can be used for XML output parameters! To add insult to injury, the NVARCHAR data type can be used for XML data type input parameters.

This is the exception raised by the Execute SQL Task when trying to use the NVARCHAR data type for an XML output parameter:

Error: 0xC002F210 at %task name%, Execute SQL Task: Executing the query "%query 
text%" failed with the following error: "Implicit conversion from data type xml 
to nvarchar(max) is not allowed. Use the CONVERT function to run this query.". 
Possible failure reasons: Problems with the query, "ResultSet" property 
not set correctly, parameters not set correctly, or connection not established 
correctly.

Much Ado about .Net

The ADO.Net provider supports the XML data type, so there should be no problems here. Eventually. In fact, I've run into a different problem (explained later in this post), but was able to fix that in the end.

Variables

SSIS supports a subset of .Net data types for the SSIS Package Variables. XML is not supported explicitly, although either String or Object will do if the variable is to be used for Execute SQL Task's parameters – either using the OLE DB or the ADO.Net provider.

How Big is Your XML?

I've mentioned a problem earlier, haven't I? For output parameters of the XML data type, ADO.Net expects the parameter size to be set. The magic number that always seems to be accepted is 2147483647 (2 GB or 2^31 - 1 Bytes), otherwise the following exception will be raised by the Execute SQL Task (even if parameter size is left at -1, which is the default, and apparently does not stand for unlimited):

Error: 0xC002F210 at %task name%, Execute SQL Task: Executing the query "%query 
text%" failed with the following error: "String[0]: the Size property has an 
invalid size of 0.". Possible failure reasons: Problems with the query, 
"ResultSet" property not set correctly, parameters not set correctly, or 
connection not established correctly.

II. User-defined CLR Types

In this test I've used the Point User-defined CLR Type (CLR UDT) used as an example in Books Online.

According to documentation, OLE DB as well as ADO.Net generally do support CLR UDTs via their UDT or Udt data types, respectively. However, neither of these types is available to the Execute SQL Task in SQL Server Integration Services. Using the usual candidates, Object or String, fails as well.

On the other hand, every CLR UDT implements the ToString() and the Parse() methods, making it possible to use string representations of the UDT in the Execute SQL Task. This workaround is both: a lot of work and a long way around the problem. Why? First of all, they require a rewrite of the SQL query or the procedure, or the creation of a "wrapper" procedure that executes the actual procedure, and is executed from SSIS instead of the "real" one. Second, CLR UDTs usually implement additional accessors and operators – without them a complex type is incomplete, and its usability limited.

III. Table-valued Parameters

Introduced with SQL Server 2008, Table-valued Parameters (TVPs) provide a way of passing a set (a table) to a SQL Server module (procedure or function). Before TVPs the only way to pass a set of values to a SQL Server module, using Transact-SQL or another programming language, would be to pack the set up into a delimited string, to use XML, or to rely on other, usually significantly more elaborate means.¹

SSIS has seen its share of changes for SQL Server 2008, but... long story short: SQL Server Integration Services do not support Table-valued Parameters.

According to documentation (MSDN Library), TVPs are supported by OLE DB via its Object data type (I've never confirmed this, though), and by ADO.Net via its Structured data type (confirmed). However, none of these are available to the Execute SQL Task in SQL Server Integration Services.

Mental Aggregation

And here they are, all the conclusions, neatly packed in a table for your benefit:

	Provider	Variable	Output Parameter		Input Parameter
		Data Type	Data Type	Size	Data Type	Size
XML	OLE DB	String or Object	N/A		NVARCHAR	-1 or 2147483647
	ADO.Net		Xml	2147483647	Xml	-1 or 2147483647
CLR UDT	N/A
TVP	N/A

And the conclusion of all conclusions? XML via ADO.Net seems to be the only fully supported complex parameter in the SSIS Execute SQL Task.

I really hope this changes in the future...

ML

¹ Erland Sommarskog has written a few very useful articles dealing with the subject of exchanging data sets between modules.

Cannot open user default database? Login failed?

Assigning a default database to every server principal (login) is good practice, no doubt. Just think of the last time someone in your organization created a user object in master by mistake. Generally, the most appropriate database to set as the default for a user is the database they will most likely access when performing their work, and for most cases that would be a user database (rather than a system database).

The default database – set for each server principal – is the database used when a user connects to the SQL Server instance, unless another database is specified when the connection is initiated (e.g. through the application's connection string, in the Connect to... dialog in SQL Server Management Studio, etc.).

Databases are not forever

While system databases tend to be present at every instance, the same cannot be said for user databases. For whatever reason, sooner or later a database will be relocated to another server or abandoned altogether, in some cases even simply renamed.

For instance, let's imagine that we've created two databases: database D1 and database D2 on the same server instance, and we've made it a rule to assign D1 as the default database for every login we create. Later we could decide to move D1 to a new server. How would that affect the default database setting for the logins at the old server?

Dropping or detaching a database does not affect default database settings. In fact, users trying to connect to the old server might even be unpleasantly surprised by the following error message:

Cannot open user default database. Login failed.
Login failed for user '<login>'. (Microsoft SQL Server, Error: 4064)

The default database setting specifies which database the user will connect to by default. Since the user can (and should) always specify the database context when connecting, this default will only be used in the case when no database has been specified.

About SQL Server Error 4064 (DB_UFAIL_FATAL)

However, a non-existing database is not the only possible reason for this particular exception; there are more possibilities to consider:

• Does the database exist? We cover this later in this post;
• Is the database online? There are 6 states that a database can be in (more details in Books Online), so verify the state of the database:
  

  select sys.databases.name
   ,sys.databases.[state]
   ,sys.databases.state_desc
   from sys.databases

• Is access to the database restricted? There are 3 access modes that can be set for a database (more details in Books Online), so verify the access mode:
  

  select sys.databases.name
   ,sys.databases.user_access
   ,sys.databases.user_access_desc
   from sys.databases

• Does the user have the CONNECT permission on the database? By default every database principal, i.e. a user (created from a server principal, i.e. a login) is granted the CONNECT permission implicitly, but this permission can also be denied. I've discussed permissions and how to check them for a particular user in a previous post.

Exception 4064 is also documented in more detail at the Events and Errors Message Center, a service provided by Microsoft TechNet.

Let me in!

Once we've determined that the reason for the error is in fact in the default database settings referencing a missing database, we should change them appropriately for each individual login.

The next query returns a list of logins with a default database setting referencing a database that cannot be found on the current instance:

select sys.server_principals.name
 ,sys.server_principals.sid
 ,sys.server_principals.default_database_name
 from sys.server_principals
 where (sys.server_principals.[type] in ('S', 'U', 'G'))
  and (not exists (
   select *
    from sys.databases
    where (sys.databases.name = sys.server_principals.default_database_name)
   ))

The results of the query are restricted to only list server principals that are either a SQL login ("S"), a Windows login ("U"), or a Windows group ("G").

To allow the users to connect to the server without specifying a database (by relying on the default database setting) we need to assign an existing default database to each one of their logins – which is quite easy once we (a) know who they are, and (b) determine which database is the most appropriate to be used as default.

If you for some reason don't have time to think about which database should be the default for every login, or simply don't care which one it is (as long as it's not master, model or msdb), here's a little script that (using the query above) sets tempdb as the default database for each login that currently has their default database set incorrectly.

Why tempdb? It exists at every instance and is accessible to any login. Keep in mind though that this is a quick fix, not a best practice.

ML