Communicate With WebService From MS SQL 2005 CLR Procedure, Quick Steps

-> Communicate with WebService from MS SQL 2005 CLR Procedure, Quick Steps

1- Create SQL project in V.S 2005.


2- Add new Trigger Class, right click on project select Add -> New Item.


3- Add Web Service reference.


4- Use impersonation technique to communicate with Service.


Example:


using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext context = id.Impersonate();


/////////////////////////////
proxy = new WebServiceProxy();

proxy.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

proxy.HelloWorld();

/////////////////////////////


context.Undo();
}


5- Set SQL Project Permission Level to External, change from the project properties Database Tab


"Permission Level Select the permission level from the drop-down list to specify a set of code

access permissions granted to the assembly when accessed by Microsoft SQL Server. The value can be

Safe, External, or Unsafe; these values correspond to the SQL Server permission sets SAFE,

EXTERNAL_ACCESS, and UNSAFE, respectively. Safe is the default.

This setting corresponds to the PERMISSION_SET argument for the SQL Server CREATE ASSEMBLY command.
" MSDN


6- "The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts

the database and the contents within it. By default, this setting is OFF, but can be set to ON by

using the ALTER DATABASE statement.

Note:
To set this option, you must be a member of the sysadmin fixed server role." MSDN


ALTER DATABASE [Database Name] SET TRUSTWORTHY ON
.



7- Build and Deploy assembly.

8- The web service reference generates XmlSerializers assembly, there are many ways to resolve
this issue, a quick way would be to add the XmlSerializers assembly to the database where the
main CLR assembly is deployed.


From "SQL Server Management Studio" select the target database, goto "Programmability" then ->
"Assemblies", there right click "New Assembly" and specify reference.





Hope this is helpful to others.
Regards
Rabeeh Abla