Call a webservice from TSQL Stored Procedure using MSXMLI am working on a n integration project that required me to call a webservice through a stored procedure in the SQL database.After proving my concept using the CLR integration features in Sql Server, I learned that the production database was actually running on Sql Server 2.There went my proof of concept.So now I have to resort to doing web service calls the old way, making post requests using MSXML.This is what my stored procedure looks like.CREATE proc dbo. sp.HTTPRequest. URI varchar2.Name varchar5. 0.Body varchar8. 00.Soap. Action varchar2.User. Name nvarchar1.DomainUser. Name or User.Name. Password nvarchar1.Text varchar8. 00.Call Update Stored Procedure Calling Another StoredI am working on a n integration project that required me to call a webservice through a stored procedure in the SQL database.After proving my concept using the CLR.Alter or Modify a Stored Procedure.Below figure displays the syntax for alter a stored procedure.As you can see below to modify a stored procedure ALTER keyword is.I am writing a simple web application to call a stored procedure and retrieve some data.Its a very simple application, which interacts with clients database.We. SET NOCOUNT ON.IF method. Name. select Fail.Point Method Name must be set.Text FAILED. DECLARE object.ID int. DECLARE h.Result int. DECLARE source varchar2.EXEC h. Result spOACreate MSXML2.MKkJJhIVrQ/VQM-3u6CoSI/AAAAAAAABf4/oXARMClYZjg/s1600/With%2BStored%2BProcedures.PNG' alt='Call Update Stored Procedure Calling Stored' title='Call Update Stored Procedure Calling Stored' />Server.XMLHTTP, object. ID OUT.IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Create failed.Medthod. Name method.Name. goto destroy.URI with Specified method.EXEC h. Result spOAMethod object.ID, open, null, method.Name, URI, false, User.Name, Password. IF h.Result lt 0. EXEC spOAGet.Error. Info object.ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Open failed.Medthod. Name method.Name. goto destroy.EXEC h. Result spOAMethod object.ID, set. Request.Header, null, Content Type, textxml charsetUTF 8.IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Set. Request.Header failed. Medthod.Name method. Name.EXEC h. Result spOAMethod object.ID, set. Request.Header, null, SOAPAction, Soap.Action. IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Set. Request.Header failed. Medthod.Name method. Name.Body. EXEC h. Result spOAMethod object.ID, set. Request.Header, null, Content Length, len.IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Set. Request.Header failed. Medthod.Name method. Name.Request. Header you can go through them with this.DECLARE Header. Key varchar5.Header. Value varchar5.DECLARE Request. Header CURSOR.LOCAL FASTFORWARD.SELECT Header. Key, Header.Value. FROM Request.Headers. WHERE Method method.Name. OPEN Request.Header. FETCH NEXT FROM Request.Header. INTO Header.Key, Header. Value.WHILE FETCHSTATUS 0.Header. Key, Header.Value, method. Name.EXEC h. Result spOAMethod object.ID, set. Request.Header, null, Header.Key, Header. Value.IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Set. Request.Header failed. Medthod.Name method. Name.FETCH NEXT FROM Request.Header. INTO Header.Key, Header. Value.CLOSE Request. Header.DEALLOCATE Request.Header. send the request.EXEC h. Result spOAMethod object.ID, send, null, request.Body. IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Send failed.Medthod. Name method.Name. goto destroy.Text varchar1. 00.Get status text. exec spOAGet.Property object. ID, Status.Text, status. Text out.OAGet. Property object.ID, Status, status out.Text, method. Name.Get response text.OAGet. Property object.ID, response. Text, response.Text out. IF h. Result lt 0.EXEC spOAGet. Error.Info object. ID, source OUT, desc OUT.SELECT h. Result convertvarbinary4, h.Result. source source.Fail. Point Response.Text failed. Medthod.Name method. Name.OADestroy object.ID. SET NOCOUNT OFF.GOThe stored procedure takes the following parameters URI the URI of the web service Method.Name this would be GET or POST Request.Body this is your SOAP xml that you want to sendSoap.Action this the operation that you want to call on your serviceUser.Name NT User. Name if your web service requires authenticationPassword the password if using NT Authentication on the web serviceResponse.Text this is an out parameter that contains the response from the web service.Here is a sample call to my service declare xml.Out varchar8. 00.Declare Request. Text as varchar8.Request. Text. lt soapenv Envelope xmlns soapenvhttp schemas.Header. lt soapenv Body.Create. Order. Optional.Order. Request. Order.Id 2. 00lt tem Order.Id. lt Optional. Order.Name somethinglt tem Order.Name. lt tem Order.Request. lt tem Create. Force Group Policy To Update Remotely Anywhere . Order. lt soapenv Body.Envelope. exec sp.HTTPRequest. http localhosttestwebserviceshelloworldservice.Create. Order. For.Me., xml. Out out.Out The stored procedure runs and selects the response from my service call which in my case is lt Envelope xmlns soaphttp schemas.XMLSchema instance xmlns xsdhttp www.XMLSchema. lt soap Body.Create. Order. For.Me. Response xmlnshttp tempuri.Create. Order. For.Me. Result. lt Message Order Created for melt Message.Create. Order. For.Me. Result. lt Create.Order. For. Me. Response.Body. lt soap Envelope Note that the stored procedure automatically adds the appropriate http headers for Content Type, Soap.Action and Content Length.If you need to pass additional http headers with your request, refer the section in the center of the stored procedure which is commented out.Basically you need a table in your database called Request.Headers with 3 columns Header.Key, Header. Value, Method.Populate this table with the keys and values for the additional headers.The Method column will contain GET or POST depending on what method you are using.Another thing to note here is that I tried the above calls with MSXML2.XMLHTTP and Microsoft.XMLHTTP but did not succeed with either.I kept getting an error from msxml.The parameter is incorrect.I believe this to be a bug with msxml that requires the request body text to be in a specific format that is not available through a sql data type and hence cannot be used via TSQL.THese will hoever work fine when performing a simple http request non SOAP or a SOAP request that does not need a request body.SOAP requests from within TSQL the only component that worked for me was MSXML2.Server. XMLHTTP. There are some third party components that I came across, some paid, but MSXML2.Server. XMLHTTP worked just fine for me.The next step is to actually take the SOAP response that I recieved and extract the appropriate data from it that will be another post.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |