Sql-server – How to use a stored procedure in SQL Server 2014 to call a external XML source

sql serverxml

I am attempting to retrieve currency exchange rates from http://www.floatrates.com/daily/usd.xml using a stored procedure in SQL Server 2014. The result is in XML. So I created the following stored procedure:

--DROP PROCEDURE [dbo].[proc_CallWebService]
CREATE PROCEDURE [dbo].[proc_CallWebService]
AS
    DECLARE @obj INT
    DECLARE @sURL VARCHAR(200)
    DECLARE @response varchar(max)

    SET @sURL = 'http://www.floatrates.com/daily/usd.xml'

    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'GET', @sURL, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

    SELECT @response [response]
    EXEC sys.sp_OADestroy @obj
RETURN

and when I run this stored procedure, I get NULL in return instead of the XML.

What do I need to change in my stored procedure to get an XML response from that site mentioned above?

Best Answer

EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

sometimes doesn't return the output parameter. Particularly if the response XML is longer than your @response variable.

Try:

declare @result table(data text)

INSERT into @result (Data) exec sp_OAGetProperty @obj, 'responseText'

select * from @result

Related Solutions

Sql-server – Is it possible to get SQL Server 2008 to download a file from a URL

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely.

but the size of the XML can be quite variable (esp given that a binary would pass it to the stored proc using L2S) and Iv'e already overflowed the input to the SP a couple of times because there's been too much data

Use the techniques from DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC to stream the HTTP response into the database.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – Is it possible to get SQL Server 2008 to download a file from a URL

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question