We're off-topic a bit (not talking about Geocoding any more, but rather calling an arbitrary web service). That's OK...

When I run your code (with either URL) I am getting:

Error in spNS: HTTP result is: 401

So the code is working, but the server is returning an HTTP 401 code (unauthorized)

The problem is that the MSXML2.ServerXMLHttp supports providing username / password credentials via properties, but not embedded in the URL.

You can use an updated GetHTTP routine(see below) that accepts username and password. You can use it like this:

DECLARE @MyResults varchar(MAX)

DECLARE @HTTPStatus int

EXEC dbo.sputilGetHTTP

@URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true',

@User = 'username',

@Password = 'password',

@ResponseText = @MyResults OUTPUT,

@HTTPStatus = @HTTPStatus OUTPUT

SELECT

@HTTPStatus AS HTTPStatus,

@MyResults AS Response

Here is the code for the updated spGetHTTP procedure:

CREATE PROCEDURE [dbo].[sputilGetHTTP]

@URL varchar(MAX),

--URL to retrieve data from

@HTTPMethod varchar(40) = 'GET',

--can be either GET or POST

@ContentType varchar(80)= 'text/http',

--set to 'application/x-www-form-urlencoded' for POST, etc.

@DataToSend nvarchar(4000) = NULL,

--data to post, if @HTTPMethod = 'POST'

@HTTPStatus int = NULL OUTPUT,

--HTTP Status Code (200=OK, 404=Not Found, etc.)

@HTTPStatusText nvarchar(4000) = NULL OUTPUT,

@ResponseText nvarchar(MAX) = NULL OUTPUT,

--Full text returned by remote HTTP server (if @SuppressResponseText = 0)

@ErrorMsg varchar(MAX) = NULL OUTPUT,

--NULL unless an error message was encountered

@LastResultCode int = NULL OUTPUT,

--0 unless an error code was returned by MSXML2.ServerXMLHttp

@User varchar(512) = NULL,

--If provided, use this value for the HTTP authentication user name

@Password varchar(512) = NULL,

--If provided, use this value for the HTTP authentication password

@SuppressResponseText bit = 0,

--If 0, actual content is not returned from remote server (just status code)

@SuppressResultset bit = 1,

--If 0, result set is is not returned (just parameters)

@SilenceErrors bit = 0

--If 1, errors are not raised with RAISEERROR(), but caller can check @ErrorMsg.

--@ErrorMsg will be null if no error was raised.

--Written by David Rueter (drueter@assyst.com)

AS

BEGIN

--Retrieves data via HTTP

--http://msdn.microsoft.com/en-us/library/aa238861(v=sql.80).aspx

SET NOCOUNT ON

DECLARE @Debug bit

SET @Debug = 0

DECLARE @CRLF varchar(5)

SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @Obj int

DECLARE @PerformedInit bit

SET @PerformedInit = 0

DECLARE @ErrSource varchar(512)

DECLARE @ErrMsg varchar(512)

DECLARE @tvResponse TABLE (Response nvarchar(MAX))

SET @ErrorMsg = NULL

IF @Debug = 1 PRINT 'About to call sp_OACreate for MSXML2.ServerXMLHttp'

BEGIN TRY

EXEC @LastResultCode = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

ELSE BEGIN

SET @PerformedInit = 1

END

END TRY

BEGIN CATCH

SET @ErrorMsg = ERROR_MESSAGE()

END CATCH

BEGIN TRY

IF @LastResultCode = 0 BEGIN

IF @HTTPMethod = 'GET' BEGIN

IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (GET)'

EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false, @User, @Password

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

END

ELSE BEGIN

IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (POST)'

EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'POST', @URL, false, @User, @Password

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

IF @Debug = 1 PRINT 'About to call sp_OAMethod for setRequestHeader'

IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', @ContentType

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

END

END

IF @Debug = 1 PRINT 'About to call sp_OAMethod for send'

IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'send', NULL, @DataToSend

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'statusText', @HTTPStatusText OUT

IF @LastResultCode <> 0 BEGIN

EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT

END

IF (@LastResultCode = 0) AND (ISNULL(@SuppressResponseText, 0) = 0) BEGIN

INSERT INTO @tvResponse (Response)

EXEC @LastResultCode = sp_OAGetProperty @Obj, 'responseText' --, @Response OUT

--Note: sp_OAGetProperty (or any extended stored procedure parameter) does not support

--varchar(MAX), however returning as a resultset will return long results.

END

END TRY

BEGIN CATCH

SET @ErrorMsg = ERROR_MESSAGE()

END CATCH

DECLARE @DestroyResultCode int

EXEC @DestroyResultCode = sp_OADestroy @Obj

SELECT @ResponseText = Response FROM @tvResponse

SET @ErrorMsg =

NULLIF(RTRIM(

ISNULL(@ErrorMsg, '') +

ISNULL(' (' + @ErrMsg + ')', '') +

ISNULL(' [' + @ErrSource + ']', '')

), '')

IF @ErrorMsg IS NOT NULL BEGIN

SET @ErrorMsg = 'Error in sputilGetHTTP: ' + @ErrorMsg

IF @PerformedInit = 0 BEGIN

SET @ErrorMsg = @ErrorMsg + @CRLF +

'Remember that this stored procedure uses OLE. To work properly you may need to configure ' +

'your database to allow OLE, as follows: ' + @CRLF +

' EXEC sp_configure ''show advanced options'', 1;' + @CRLF +

' RECONFIGURE;' + @CRLF +

' EXEC sp_configure ''Ole Automation Procedures'', 1;' + @CRLF +

' RECONFIGURE;' + @CRLF +

'Also, your SQL user must have execute rights to the following stored procedures in master:' + @CRLF +

' sp_OACreate' + @CRLF +

' sp_OAGetProperty' + @CRLF +

' sp_OASetProperty' + @CRLF +

' sp_OAMethod' + @CRLF +

' sp_OAGetErrorInfo' + @CRLF +

' sp_OADestroy' + @CRLF +

'You can grant rights for each of these as follows:' + @CRLF +

' USE master' + @CRLF +

' GRANT EXEC ON sp_OACreate TO myuser' + @CRLF +

' GRANT EXEC etc. ...'

IF ISNULL(@SilenceErrors, 0) = 0 BEGIN

RAISERROR(@ErrorMsg, 16, 1)

END

END

END

IF ISNULL(@SuppressResultset, 0) = 0 BEGIN

SELECT

@URL AS URL,

@ResponseText AS ResponseText,

@HTTPStatus AS HTTPStatus,

@LastResultCode AS LastResultCode,

@ErrorMsg AS ErrorMsg

END

END