Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: Sending HTTP from SQL

    I've been asked to get a SQL Server database to send data via URL variables to a 3rd party.

    so the DB will compose a URL for the client which will be their #URL#?ID=#DBValue#&Code=#DBValue#

    I can easily do this in a separate program but they want this to occur in SQL, which honestly i have no idea about any help?


    they are running SQL 2000, 2005 and 2008. the database is on a 2000 server but it's no real difficulty in linking it to one of the others if the later version have the functionality and the ealier ones don't
    Last edited by m.timoney; 09-17-09 at 06:29.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    What i have managed to find so far is (and i'm not sure this is the right method to use)

    Code:
    CREATE FUNCTION HTTP_Send
    (
    	@URL Varchar
    )
    RETURNS Varchar
    AS
    BEGIN
    	DECLARE @rc int
    	DECLARE @proc varchar
    	DECLARE @http varchar
    	DECLARE @URL varchar
    	DECLARE @post varchar
    
    
    	EXEC @rc = master.dbo.sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http output
    
    	SET	@proc = 'Open("POST" , "' + @URL + '", 0 )'
    	EXEC master.dbo.sp_OAMethod @http, @proc
    
    	SET @proc = 'SetRequestHeader("HTTP-Version","HTTP/1.1")'
    	EXEC master.dbo.sp_OAMethod @http,@proc
    
    	SET @proc = 'SetRequestHeader("Content-Length: 27")'
    	EXEC master.dbo.sp_OAMethod @http,@proc
    
    	SET @proc = 'SetRequestHeader("Content-Type","application/x-www-form-urlencoded")'
    	EXEC master.dbo.sp_OAMethod @http,@proc
    
    	SET @proc = 'Send()'
    	EXEC master.dbo.sp_OAMethod @http,@proc
    	RETURN ???
    
    END
    still trying to work out how to extract the reply, but it is some progress
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not much of an expert on this sort of thing either frankly but I have written some stuff in C# accessing REST APIs, deserialising the results and storing in SQL Server which I guess is pretty close. If you can "easily do this" in other languages and can link servers - any reason you aren't doing this in CLR managed code? Or even as an extended stored proc which should be ok and work in 2k through to 2k8?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by pootle flump
    any reason you aren't doing this in CLR managed code? Or even as an extended stored proc which should be ok and work in 2k through to 2k8?

    mainly because i'm a .net programmer that also knows SQL, i don't know SQL Server specifics very well. i have found the CLR UDF option but my manager doesn't want to use linking
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Has he\ she provided a good case why? Are they open to you presenting a counter argument? This is a real case of using a hammer to turn a screw IMHO. Any time I start thinking of using OLE automation in SQL Server I have to think "am I really doing the right thing?". SQL Server is a relational database management system, and it is best deployed to manage relational databases.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I thought you said in post #1 linking is ok - are you talking about linked SQL Servers both times? What if this were possible without linked servers?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by pootle flump
    Has he\ she provided a good case why? Are they open to you presenting a counter argument? This is a real case of using a hammer to turn a screw IMHO. Any time I start thinking of using OLE automation in SQL Server I have to think "am I really doing the right thing?". SQL Server is a relational database management system, and it is best deployed to manage relational databases.
    no reason given bar personal preference but if that is the best way to do it then the he will just have to lump it.

    i can understand that distributing systems so that parts of it are here and part there and another somewhere else is just asking for bit to be lost or redundant but needs must when the devil drives
    Last edited by m.timoney; 09-17-09 at 09:04.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •