Export (0) Print
Expand All

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:

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 2010, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2010.

  • 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.

  • 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 2010, typically this is <installation drive>:\Program Files\Microsoft BizTalk Server 2010.

  • 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.

Once you have completed these tasks, you are all set to perform operations on tables with UDTs.

Show:
© 2014 Microsoft