Calling a Web Services from a SQL Stored Procedure

In this article i will explain how to call a Web Service from a Store Procedure with parameters.

For this article i will use Google Maps API Web Services for web service call.

The Store Procedure for calling Web Service is

The basic idea is to construct the Url, then use the MSXML2.XMLHTTP object to make the request and get the response. From there, we then cast the response into Xml, and pull out the values of interest via xquery.

Something that is also very important when using the sp_OAMethod Stored Procedure this awaiting the POST or GET method, default almost always send POST, but if we sent this method, we can not display the XML reading generated by our Web Service, so we must use the GET method.
 
Now when we execute the store procedure, it need a parameter @Location then it will call the Google Maps API Web Services and return an XML.

Since this Store Procedure relies on OLE Automation, you will need to enable this on your SQL box:

On the SQL Server box go to…

Start-> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
Click “Surface Area Configuration for Features”
Click the DB -> Database Engine
Select OLE Automation & Check the checkbox Enable OLE Automation…
Close the tool and then the sproc should work.

Creating SharePoint Website or Subsite Programmatically

Sometime we require creating a sub site programmatically. In this example, I will create a SharePoint website by using .Net Console Application.

 Open Microsoft Visual Studio here i am using Microsoft Visual Studio 2012 and create new console Application project

Click File -> New -> Project -> Console Application

Enter name of project "SP_New_WebSite". and select the targeted framework to .NET Framework 3.5.

Make sure that target platform is 64bit to check this click project ->SP_New_WebSite Properties ->build 

Now add the SharePoint 2010 reference. In Solution Explorer Right click on References  -> Add Reference then click Extensions and Select Microsoft.SharePoint and click OK

Now Use the following Source Code to Create New Website or sub site Programmatically:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace SP_New_WebSite
{
    class Program
    {
        static void Main(string[] args)
        {
           createNewWebSite ();
           Console.ReadKey();
        }

       /*

        * Title Create New web Site:

        * Author:   Axeex

        */

        private static void createNewWebSite()

        {

            SPSite site = null;

            try

            {

                using(site = new SPSite("http://dev.sharepoint.local/sites/local"))

                {

                    using(SPWeb web = site.AllWebs.Add("axeexBlog","Axeex Blog","This Blog is created by using c# code",1033,"BLOG#0",true,false))

                    {

                        Console.WriteLine("New Website {0}",web.Url);

                    }

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine("Error ---> "+ex.ToString());

            }

            finally

            {

                site.Dispose();

            }

        }
    }
}
 

Name of the site templates

E.g. Use “STS#0” for Team site or “BLOG#0” for Blog site.

Available site templates	Site Template Names
Team Site	STS#0
Blank Site	STS#1
Document Workspace	STS#2
Blog	BLOG#0
Group Work Site	SGS#0
Visio Process Repository	VISPRUS#0
Basic Meeting Workspace	MPS#0
Blank Meeting Workspace	MPS#1
Decision Meeting Workspace	MPS#2
Social Meeting Workspace	MPS#3
Multipage Meeting Workspace	MPS#4
Assets Web Database	ACCSRV#1
Charitable Contributions Web Database	ACCSRV#3
Contacts Web Database	ACCSRV#41
Issues Web Database	ACCSRV#6
Projects Web Database	ACCSRV#5
Document Center	BDR#0
Records Center	OFFILE#1
Business Intelligence Center	BICenterSite#0
My Site Host	SPSMSITEHOST#0
Personalization Site	SPSMSITE#0
Enterprise Search Center	SRCHCEN#0
Basic Search Center	SRCHCENTERLITE#0
FAST Search Center	SRCHCENTERFAST#0
Enterprise Wiki	ENTERWIKI#0
Publishing Portal	BLANKINTERNETCONTAINER#0
Publishing Site	CMSPUBLISHING#0
Publishing Site With Workflow	BLANKINTERNET#2