In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.

Step 1
Create a stored procedure in your SQL Server.
  CREATE proc [dbo].[spHTTPRequest]    
        @URI varchar(2000) = 'http://localhost:55253/',         
        @methodName varchar(50) = 'Get',    
        @requestBody varchar(8000) = '',    
        @SoapAction varchar(255),    
        @UserName nvarchar(100), -- Domain\UserName or UserName    
        @Password nvarchar(100),    
        @responseText varchar(8000) output   
  as   
  SET NOCOUNT ON   
  IF    @methodName = ''   
  BEGIN   
        select FailPoint = 'Method Name must be set'   
        return   
  END   
  set   @responseText = 'FAILED'   
  DECLARE @objectID int   
  DECLARE @hResult int   
  DECLARE @source varchar(255), @desc varchar(255)    
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
                    source = @source,    
                    description = @desc,    
                    FailPoint = 'Create failed',    
                    MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- open the destination URI with Specified method    
  EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Open failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set request headers    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set soap action    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @len int   
  set @len = len(@requestBody)    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  /*   
  -- if you have headers in a table called RequestHeader you can go through them with this   
  DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)   
  DECLARE RequestHeader CURSOR  
  LOCAL FAST_FORWARD   
  FOR  
        SELECT      HeaderKey, HeaderValue   
        FROM RequestHeaders   
        WHERE       Method = @methodName   
  OPEN RequestHeader   
  FETCH NEXT FROM RequestHeader   
  INTO @HeaderKey, @HeaderValue   
  WHILE @@FETCH_STATUS = 0   
  BEGIN  
        --select @HeaderKey, @HeaderValue, @methodName   
        EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue   
        IF @hResult <> 0   
        BEGIN  
              EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT  
              SELECT      hResult = convert(varbinary(4), @hResult),   
                    source = @source,   
                    description = @desc,   
                    FailPoint = 'SetRequestHeader failed',   
                    MedthodName = @methodName   
              goto destroy   
              return  
        END  
        FETCH NEXT FROM RequestHeader   
        INTO @HeaderKey, @HeaderValue   
  END  
  CLOSE RequestHeader   
  DEALLOCATE RequestHeader   
  */    
  -- send the request    
  EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody    
  IF    @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Send failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @statusText varchar(1000), @status varchar(1000)    
  -- Get status text    
  exec sp_OAGetProperty @objectID, 'StatusText', @statusText out   
  exec sp_OAGetProperty @objectID, 'Status', @status out   
  select @status, @statusText, @methodName    
  -- Get response text    
  exec sp_OAGetProperty @objectID, 'responseText', @responseText out   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'ResponseText failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  destroy:    
        exec sp_OADestroy @objectID    
  SET NOCOUNT OFF   
      
  GO   

The Stored Procedure takes the following parameters.
  @URI: the URI of the web service
  @MethodName: this would be ‘GET’ or ‘POST’
  @RequestBody: this is the SOAP xml that you want to send
  @SoapAction: this the operation that you want to call on your service
  @UserName: NT UserName if your web service requires authentication
  @Password: the password if using NT Authentication on the web service
  @ResponseText: this is an out parameter that contains the response from the web service

Step 2
Make the setting in SQL for it.
  Use master 
  sp_configure 'show advanced options', 1  
   
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'Ole Automation Procedures', 1  
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'show advanced options', 1  
  GO  
  RECONFIGURE; 

Step 3
Call the stored procedure (Here is a sample call to my service).
  declare @xmlOut varchar(8000) 
  Declare @RequestText as varchar(8000); 
  set @RequestText= 
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/"> 
     <soapenv:Header/> 
     <soapenv:Body> 
        <tem:CreateOrder> 
           <!--Optional:--> 
           <tem:OrderRequest> 
              <tem:OrderId>200</tem:OrderId> 
              <!--Optional:--> 
              <tem:OrderName>something</tem:OrderName> 
           </tem:OrderRequest> 
        </tem:CreateOrder> 
     </soapenv:Body> 
  </soapenv:Envelope>' 
  exec spHTTPRequest 
  'http://localhost/testwebservices/helloworldservice.asmx', 
  'POST', 
  @RequestText, 
  'http://tempuri.org/CreateOrderForMe',   -- this is your SOAPAction: 
  '', '', @xmlOut out 
  select @xmlOut  

Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.

Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.

Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit

We ran into an issue with a customer this week. This error was flooding the error log. After a little digging, I found it had to do with transactional replication (also applies to Change Data Capture) they had set up which included LOB data.

Per MSDN,
The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536 bytes.

In the error above you can see it plainly states that the column’s LOB data nvarchar(max), in this case, was 65754 bytes which was over the max default size of 65536. Which ironically is 64k.   64*1024 = 65536 (if you didn’t know). Adjusting the max text repl size for this server solved our issue. Below you can see the ways to change this value. For us changing it to the max value of 2147483647 bytes which is 2 GB was the way to go. If you don’t know the max value you can also set it to -1 which means no limit, the limit will be based on datatype limits. Previously, the limit was 2GB.

Script
GO  
EXEC sp_configure 'show advanced options', 1;   
RECONFIGURE ;   
GO  
EXEC sp_configure 'max text repl size',2147483647;   
GO  
RECONFIGURE;   
GO 

Using GUI
At the Server Level right click and go to Properties.
Click on Advanced. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

What is a Key Lookup?
One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to fulfill the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap. It uses a row id instead of a primary key to do the lookup.

As you can see these can be very expensive and can result in substantial performance hits in both I/O and CPU. Imagine a query that runs thousands of times per minute that includes one or more key look ups. This can result in tremendous overhead which is generated by these extra reads and it effects the overall engine performance.

Let’s look at an example.
    SELECT [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID], 
    [UnitPrice],[ModifiedDate]   
    FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]   
    Where [ModifiedDate]> 2014/01/01  and [ProductID]=772
 

The cost of the key lookup operator is 99% of the query. You can see it did an Index Seek to the IX_SalesOrderDetail_ProductID which is very effective, but that index did not have all the columns needed to satisfy the query. The optimizer then went out to the clustered index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to retrieve the additional columns it needed. You can see what it got by hovering over the key lookup in the query plan window.

The good thing about Key and RID look ups is that they are super easy to fix. With a little modification to the non-clustered Index IX_SalesOrderDetail_ProductID we can change to query plan from an Index Seek and a Key Lookup to a very small index seek. All we have to do is recreate that index and add the Output List fields as Included columns on that index.
    CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]  
    ON [Sales].[SalesOrderDetail]([ProductID] ASC) 
    INCLUDE ([CarrierTrackingNumber],[UnitPrice], [ModifiedDate], [OrderQty]) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
    ON [PRIMARY] 

And as you can see, we now have an Index Seek only and a more efficient plan.

Key Lookups can cause performance headaches, especially for queries that run many times a day. Do yourself and your environment a favor and start hunting these down and get them fixed.