Thursday, May 14, 2009

Calling external web service from with SQL Server 2005

I am trying to establish the way to call external web service from within sql server 2005, previously we used a javascript and use SQL Agent Job to execute the script. However, it seems that SQL 2005 has problem with it.

The external consultant proposed another way, which is to use OLE Automation, while we managed to make it work occasionally, it seems to be unreliable at all. The cause of this is still unclear, I will look back once I get more timee.

After all, we know that the native way to call external web service is to use CLR and create assembly, since I haven’t done this before, it will be quite interesting to figure out how it works. So far, I have managed to create the CLR assembly and made it working. No sure why the other two methods didn’t work, but this final approach works pretty well.


______________________________________________________________________

1 comment:

Wen's Technical Diary said...

I figured out the reason why the OLE automation method doesn't work was caused by a typo in the script I used. The string inside the passing paramaters need to be encoded due to the way the web service process it.