Operations on Tables and Views with User-Defined Types
You can use the SQL adapter to perform operations on tables or views that have columns of user-defined types (UDTs). You can use the standard table operations (Insert, Update, Delete, and Select) to read or write data into columns on UDT types. You can also execute stored procedures and functions on such tables. However, you need to perform certain tasks before you can use the adapter to operate on tables with UDT columns. Once you have performed these tasks, you can use the adapter to:
- Perform Insert, Delete, Update, and Select operations, as described in Performing Basic Insert, Update, Delete, and Select Operations by Using BizTalk Server.
- Execute stored procedures, as described in Executing Stored Procedures in SQL Server by Using BizTalk Server.
- Perform composite operations on tables with UDT columns, as described in Performing Composite Operations on SQL Server by Using BizTalk Server
- Poll tables with UDT columns, as described in Receiving Polling-based Data-changed Messages from SQL Server by Using BizTalk Server
- Perform other operations, as described in Developing BizTalk Applications.
You must perform the following tasks before you can use the adapter to perform operations on tables with UDT columns.
For SQL Server 2005
- While generating schema for operation using Visual Studio. Make sure the respective assemblies of the UDTs are available at the same location as the Visual Studio executable, devenv.exe. The executable is typically available at
<installation drive>:\Program Files\Microsoft Visual Studio <version>\Common7\IDE. - While performing the operation using BizTalk Server. Make sure the respective assemblies for the UDTs are available under the BizTalk Server installation location. For BizTalk Server 2006 R2, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2006. For BizTalk Server 2009, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2009.
- While performing the operation using Visual Studio. Make sure the respective assemblies for the UDTs are at the same location as the project executable file, which typically is under the project’s \bin\Debug folder.
For SQL Server 2008
- While generating schema for operation using Visual Studio
UDT Type Location of Assemblies UDTs shipped with SQL Server 2008, for example, Geography
- Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
- Make sure SqlServerSpatial.dll is available in the System32 folder.
You can install these DLLs on the computer by running the SQL Server 2008 setup and selecting Management Tools – Basic and Management Tools – Complete in the Feature Selection page of the wizard.
UDTs not shipped with SQL Server 2008 but defined by users
Make sure the respective assemblies for the UDTs are available at the same location as the Visual Studio executable, devenv.exe. The executable is typically available at
<installation drive>:\Program Files\Microsoft Visual Studio <version>\Common7\IDE. - Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
- While performing the operation using BizTalk Server
UDT Type Location of Assemblies UDTs shipped with SQL Server 2008, for example, Geography
- Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
- Make sure SqlServerSpatial.dll is available in the System32 folder.
You can install these DLLs on the computer by running the SQL Server 2008 setup and selecting Management Tools – Basic and Management Tools – Complete in the Feature Selection page of the wizard.
UDTs not shipped with SQL Server 2008 but defined by users
Make sure the respective assemblies for the UDTs are available under the BizTalk Server installation location. For BizTalk Server 2006 R2, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2006. For BizTalk Server 2009, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2009.
- Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
- While performing the operation using Visual Studio
UDT Type Location of Assemblies UDTs shipped with SQL Server 2008, for example, Geography
- Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
- Make sure SqlServerSpatial.dll is available in the System32 folder.
You can install these DLLs on the computer by running the SQL Server 2008 setup and selecting Management Tools – Basic and Management Tools – Complete in the Feature Selection page of the wizard.
UDTs not shipped with SQL Server 2008 but defined by users
Make sure the respective assemblies for the UDTs are available at the same location as the project executable file, which typically is under the project’s \bin\Debug folder.
- Make sure Microsoft.SqlServer.Types.dll is added to the GAC.
Once you have completed these tasks, you are all set to perform operations on tables with UDTs.
