Create SQL CLR Project in VB

Create SQL CLR Project in VB:

File -> New Project
SQL Server
OK
At Project properties select SQLCLR Tab
Change Lanugage to VB
Add new items
SQL CLR VB -> SQL CLR VB User Defined Functions

Cheers!
Sanjay

Error The top XML element references distinct types

Error The top XML element references distinct types

Full error message:

C:\SQLServer\UPSProduction\UPSProduction\UPSProduction\EXEC: Error:  : The top XML element ‘CodeDescriptionType’ from namespace ” references distinct types UPSProxy.CodeDescriptionType and UPSProxy.CodeDescriptionType1. Use XML attributes to specify another XML name or namespace for the element or types.

Code Extract for Class CodeDescriptionType
[System.Xml.Serialization.
XmlTypeAttribute(Namespace=“http://www.ups.com/XMLSchema/XOLTWS/Rate/v1.1”)]
public partial class CodeDescriptionType {

Code Extract for Class CodeDescriptionType1
[System.Xml.Serialization.
XmlTypeAttribute(TypeName=“CodeDescriptionType”, Namespace=“http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0”)]
public partial class CodeDescriptionType1 {

Resolution:
Code Extract for Class CodeDescriptionType
[System.Xml.Serialization.
XmlTypeAttribute(TypeName=“CodeDescriptionType”,Namespace=“http://www.ups.com/XMLSchema/XOLTWS/Rate/v1.1”)]
public partial class CodeDescriptionType {

Code Extract for Class CodeDescriptionType1
[System.Xml.Serialization.
XmlTypeAttribute(TypeName=“CodeDescriptionType1”, Namespace=“http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0”)]
public partial class CodeDescriptionType1 {

Cheers!

Where is Visual Studio 2012 SQL CLR Project Templates ?

Where is Visual Studio 2012 SQL CLR Project Templates ?

  1. Start Visual Studio 2012 and new SQL Server Project.

    SQL CLR Project

    SQL CLR Project

  2. Click ok and add new item to the project.  SQL CLR project templates are listed.
    SQL CLR Project Add New Item

    SQL CLR Project Add New Item

    SQL CLR Templates

    SQL CLR Templates

  3.  SQL CLR is VB Templates and C# SQL CLR is the C# templates.

Cheers!

 

 

 

Mapping CLR Parameter Data

Mapping CLR Parameter Data:

Mapping CLR Parameter Data type between SQL Server and .NET Framework quick reference.  See more here.

SQL Server data type Type (in System.Data.SqlTypes or Microsoft.SqlServer.Types) CLR data type (.NET Framework)
bigint SqlInt64 Int64, Nullable<Int64>
binary SqlBytes, SqlBinary Byte[]
bit SqlBoolean Boolean, Nullable<Boolean>
char None None
cursor None None
date SqlDateTime DateTime, Nullable<DateTime>
datetime SqlDateTime DateTime, Nullable<DateTime>
datetime2 None DateTime, Nullable<DateTime>
DATETIMEOFFSET None DateTimeOffset, Nullable<DateTimeOffset>
decimal SqlDecimal Decimal, Nullable<Decimal>
float SqlDouble Double, Nullable<Double>
geography SqlGeography

SqlGeography                   is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack.

None
geometry SqlGeometry

SqlGeometry                   is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack.

None
hierarchyid SqlHierarchyId

SqlHierarchyId                   is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack.

None
image None None
int SqlInt32 Int32, Nullable<Int32>
money SqlMoney Decimal, Nullable<Decimal>
nchar SqlChars, SqlString String, Char[]
ntext None None
numeric SqlDecimal Decimal, Nullable<Decimal>
nvarchar SqlChars, SqlString

SQLChars                   is a better match for data transfer and access, and SQLString is a better match for performing String operations.

String, Char[]
nvarchar(1), nchar(1) SqlChars, SqlString Char, String, Char[], Nullable<char>
real SqlSingle                   (the range of SqlSingle, however, is larger than real) Single, Nullable<Single>
rowversion None Byte[]
smallint SqlInt16 Int16, Nullable<Int16>
smallmoney SqlMoney Decimal, Nullable<Decimal>
sql_variant None Object
table None None
text None None
time None TimeSpan, Nullable<TimeSpan>
timestamp None None
tinyint SqlByte Byte, Nullable<Byte>
uniqueidentifier SqlGuid Guid, Nullable<Guid>
User-defined type(UDT) None The same class that is bound to the user-defined type in the same assembly or a dependent assembly.
varbinary SqlBytes, SqlBinary Byte[]
varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[], Nullable<byte>
varchar None None
xml SqlXml None

Create UPS Web Services Proxy for Rating Ground Freight Web Services

Create UPS Web Services Proxy for Rating Ground Freight Web Services

Summary

UPS Web services allow customers to use UPS API  to ascertain the freight charges.  The SDK can be downloaded from UPS Web site.  Developers would need to register to access the SDK. How to create UPS Web Services Proxy for Rating Ground Freight Web Services is described in this article.

  1. Download the Rating Package from UPS website.

    UPS Developer Kit

    UPS Developer Kit Download page

  2. Download the rating package, which is compress zip folder.  Locate the folder Rates_Pkg_Gnd.zip\RatingGROUNDFREIGHTWebService\SCHEMA-WSDLs

    Rating WSDL

    UPS Rating WSDL files

  3. Next in order to generate the proxy web services script, we will need  IIS web services to host and serve these files. The easies way to host and serve the files would be install IIS Express, which get automatically installed when Microsoft Web Matrix is installed.  Microsoft Web Matrix is a free tool available for download from Microsoft.
  4. Start Web Matrix

    Webmatrix

    Webmatrix 3

  5. Create  a new empty site

    web matrix empty site

    web matrix empty site

  6. Skip on creating a azure web site at next screen.  Rename the site folder to UPS.

    rename empty site folder

    rename empty site folder

  7. Add the UPS WSDL Files extracted at  (2) above to the web site folder created at (6) above.  Right click the web site folder and add existing
    Add Existing

    Add Existing

    Wsdl files

    Wsdl files

  8. Note the site URL which is listed at the Site option.

    web matrix web site

    web matrix web site

  9. Start the visual studio command prompt.  Change directory (cd ) to a directory where you have read and write permission.
    vs 2012 developer command prompt

    vs 2012 developer command prompt

    cd c:\sqlserver\upsfreight
    wsdl /o:UPS.cs /n:FreightRate http://localhost:28909/FreightRate.wsdl

    UPS.cs –> C# web proxy script file for UPS Rating web services
    FreightRate –> Name space

  10. Generate the Webservices Proxy file

    web services proxy

    web services proxy

Notes:

Edit the UPS WSDL file to select communicating between test and production environment.

 

 

Creating CLR stored procedure to call external web services

Requirement :

The requirement was to get Sales Pad Application compute freight for sales orders by communicating with UPS web services.

Summary Implementation:

  1. Sales pad allows to run C# scripts when the save button is clicked.
  2. Implement a CLR Stored procedure at the SQL Server to compute freight charges for any given Microsoft Dynamics GP sales order  # provided as input parameter.
  3. Call the CLR stored procedure from Sales Pad pre save script to update the sales order with the freight charges returned by the  CLR stored procedure.

Applications Used:

  1. Microsoft Visual Studio 2012 & SDK Tools
  2. Microsoft SQL Sever 2012
  3. Microsoft Dynamic’s GP 2010.
  4. Sales Pad

 Steps for Creating CLR stored procedure to call external web services – Part 1

    1. Generate UPS Web Proxy C# scripts.
      1. Create CLR Stored Procedure C# script.
      2. Create the Microsoft Visual Studio 2012 Project.

        Visual Studio 2012 SQL Server CLR Project

        Visual Studio 2012 SQL Server CLR Project

      3. Add C#  scripts created above as existing items to the project. – See Part 2  – Creating Web Proxy UPS Freight Rate Inquiry
        Part 3  – Creating C# SQL Store procedure scripts

        UPS Web Proxy and CLR Stored Procedure added to project

        UPS Web Proxy and CLR Stored Procedure added to project

      4. Add project references.

        CLR PROJECT REFERENCES

        CLR PROJECT REFERENCES

      5. Active SQL Server Objects Explorer

        CLR Project Sql Server Objects

        CLR Project Sql Server Objects

      6. Create database “UPSFreight” which will host the CLR Stored procedure.  The dataset can be created from SQL Server Management Studio or from Visual Studio.

        Create Database from Visusal Studio

        Create Database from Visual Studio

      7. Create the database project at the UPSFreight visual studio project solution.  For this right click on the UPSFreight database at the visual studio SQL object explorer and select create project.
        Create visual studio database project

        Create visual studio database project

        Visual Studio Import Data base

        Visual Studio Import Data base

      8. Add the database reference to the UPSFreight Project.  For this right click the reference folder from solution explorer, select add database reference and select the database project created above.
        visual studio solution explorer with UPSFreight project and the data base project

        visual studio solution explorer with UPSFreight project and the data base project

        Add Database Reference

        Add Database Reference

        Add Database Reference select DB project

        Add Database Reference select DB project

      9. The database reference should be listed at the UPSFreight project references folder at the solution explorer.

        DB project at solution explorer

        DB project at solution explorer

      10. Create UPSFreight SQL User account.  This is the account which will be granted with rights to run the external assembly.  This account should not be assigned any other server roles.

        Create SQL Login

        Create SQL Login at SQL Server Management Studio.

      11. Grant DB Owner rights to UPSFreight user for the UPSFreight database.
        USE UPSFreight
        go
        EXEC Sp_changedbowner  ‘UPSFreight’
      12. Grant Unsafe Assembly access to UPSFreight SQL User
        USE master
        go
        GRANT UNSAFE ASSEMBLY  TO UPSFreight
      13. Turn on the trustworthy property of the database.  This has to be done at two places.  The first is turning on the property at SQL server Management Studio.
        USE UPSFreight
        go
        ALTER DATABASE UPSFreight
        SET trustworthy ON
      14. Next set the Visual Studio Project Database Properties. Right Click the project properties and click on database setting.

        Project  properties

        Project properties

      15.  The trustworthy setting can be found at database setting miscellaneous tab.

        Trustworthy property setting

        Trustworthy property setting

      16. Set the assembly ownership to dbo at visual studio project properties SQLCLR section.Note at  database we have already set the db owner to be UPSFreight and granted the unsafe assembly access.
      17. Set the permission level to UNSAFE.

        UNSAFE permission

        Owner = dbo, and permission = UNSAFE

      18. Next sign the assembly. Click on signing and create a new key.
        key

        key

        key snk

        key snk

      19. Once the CLR Library get created we need to instruct visual studio to serialize the created library.  This can be done by adding post build instruction.  Key points to notice here is first, search for sgen.exe should provide path.  Second if there are space at the path to the key file, the path should be escaped by “\”.   The post build instruction to serialize the assembly would some thing like this:”C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sgen.exe”/compiler:”\”/keyfile:$(ProjectDir)Key.snk”\” /force “$(TargetPath)”

        Post Build Instruction

        Post Build Instruction

      20. Update the connection at the debug section.

        debug db connection

        Setup debug DB connection

      21. Enable CLR at the SQL Server.
        sp_configure ‘show advanced options’, 1;
        go
        RECONFIGURE;
        go
        Sp_configure ‘clr enabled’, 1;
        go
        RECONFIGURE;
        go
      22. Build the project to generate the libraries.  On successful build, the library files can be found at the bin folder.

        Project Libraries

        Project Libraries

      23. Next the assemblies needs to be deployed to at the SQL Server. Where as it is possible to deploy the assembly from SQL server, only the CLR library gets deployed.  The post build serialization created is not deployed.  Therefore manually deploy the assemblies.Use  UPSFreight
        go
        CREATE assembly upsfreight FROM ‘C:\path_to_assembly\UPSFreight.dll’ WITH
        permission_set = unsafe;
        go
        CREATE assembly [UPSFreight.XmlSerializers] FROM
        ‘C:\path_to_assembly\UPSFreight.XmlSerializers.dll’ WITH permission_set = unsafe
        ;
        go
        CREATE PROCEDURE Getratesp
        AS
        EXTERNAL name [UPSFreight].[StoredProcedures].[GetRateSp];
        go
      24. Finally the CLR is created!

        CLR Created

        CLR Created

      25. Test
        Hurray

        Hurray!!!

        Cheers!