SQL Stored Procedure call Web Service

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.
snnegeAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
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
 
David ToddSenior DBACommented:
Hi

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

Regards
  David
0
 
David ToddSenior DBACommented:
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
 
snnegeAuthor Commented:
I've requested that this question be deleted for the following reason:

wrong
0
 
Vadim RappCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.