Solved

SQL Stored Procedure call Web Service

Posted on 2013-11-14
5
934 Views
Last Modified: 2013-11-20
Help pls guys :(

MY SP

CREATE proc [dbo].[spHTTPRequest] 
      @URI varchar(2000) = '',      
      @methodName varchar(50) = '', 
      @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

Open in new window


MY SELECTION

declare @xmlOut varchar(8000)
declare @RequestText as varchar(8000)

set @RequestText='
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:weat="http://ws.cdyne.com/WeatherWS/">
   <soap:Header/>
   <soap:Body>
      <weat:GetCityWeatherByZIP>        
      <weat:ZIP>85320</weat:ZIP>
	  </weat:GetCityWeatherByZIP>
   </soap:Body>
</soap:Envelope>'
exec spHTTPRequest 
'http://wsf.cdyne.com/WeatherWS/Weather.asmx', 
'POST', 
@RequestText,
'http://ws.cdyne.com/WeatherWS/WeatherSoap/GetCityWeatherByZIPRequest',
'',
'',
 @xmlOut out

select @xmlOut 

Open in new window


RESULT :(

The parameter is incorrect.
0
Comment
Question by:snnege
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39649552
Hi

Does the procedure execute from SQL ie SSMS or Query Analyzer?

Regards
  David
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39649580
find out which statement of the stored procedure results in "parameter incorrect". For that, insert print statements in the body of s.p., like this:


CREATE proc [dbo].[spHTTPRequest]
(...)

print 1
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
print 2
IF @hResult <> 0
BEGIN
print 3
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
print 4
      SELECT      hResult = convert(varbinary(4), @hResult),
(...)
print 5
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
print 6


and so forth. Watching what is printed before the error will allow you to narrow down to the statement that does not like the parameters. Then in the same way you can print the parameters that are passed.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39649592
Hi,

I often have a last parameter in many of my stored procedures @Debug tinyint = 0

And the put an if in front of the print statements as in if @Debug = 1

As it defaults to off, you don't even need to tell the app team that its there!

Regards
  David
0
 

Author Comment

by:snnege
ID: 39662308
I've requested that this question be deleted for the following reason:

wrong
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39662309
We showed you the usual steps all programmers in the world do in order to narrow down to the statement in error. What exactly was "wrong" ?
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now