Click here to Skip to main content
15,902,189 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi, Id like to send the following string to the web using a MSSQL stored procedure, as I would have pasted the string in the web browser itself. What do I need to have in place and what needs to be switched on in SQL2008 R2?

http://api.thirdparty.com/http/sendmsg?user=xxxxxx&password=xxxxx&api_id=xxxxxx&to=081234567&text=Sending+http+from+sql

Pierre
Posted
Updated 23-Jan-12 4:50am
v3

1) Create this procedure or modify your SP accordingly

SQL
-- ==================================
-- Alter Stored Procedure Template
-- ==================================
ALTER PROCEDURE GetURL
 (@Username Nvarchar(50),@url NVARCHAR(MAX)= NULL OUTPUT)
AS
    SELECT @url=
    'http://api.thirdparty.com/http/sendmsg?user=' + username + '&password=' + password + '&api_id=' + api_id + '&to=081234567&text=Sending+http+from+sql'
    FROM Userinfo WHERE username=@Username
    select @url url
GO


2) Update your code to add this

C#
SqlDataReader adm = null;
            string outurl;
            //http://www.codeproject.com/Answers/315162/how-to-create-gridview-which-has-column-and-row/?cmt=207083#cmt2_315162
            SqlConnection myConnection = new SqlConnection("Data Source=MDT765;Initial Catalog=TST;User Id=sa;Password=sa@123");
            myConnection.Open();
           SqlCommand cmd = new SqlCommand("GetURL", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
              new SqlParameter("@username", SqlDbType.NVarChar,50));
            cmd.Parameters["@username"].Value = "user1";


            SqlParameter url = new SqlParameter("@url", SqlDbType.NVarChar);
            // Type=null;
            url.Direction = ParameterDirection.InputOutput;
            url.Value = null;
            url.Size = 255;
            cmd.Parameters.Add(url);

            adm = cmd.ExecuteReader();
            if (adm.HasRows)
            {
                while (adm.Read())
                {
                    outurl = adm["url"].ToString();
                    Response.Redirect(outurl);

                }

            }
 
Share this answer
 
Comments
RDBurmon 27-Jan-12 2:33am    
Thanks pierrecor
Thanks for the reply; it helped a lot. I had to modify the code a bit to work from Windows app. Herewith the final code in c#. The string that is sent is buildt in SQL as described above. I use Clickatell.com as service providor.

//Needs proxy authentication
WebProxy proxy = new WebProxy(@"10.35.0.22:8080");
proxy.Credentials = new NetworkCredential("username", "password", "domain");
WebRequest.DefaultWebProxy = proxy;

//*** This does not work with IE - get garbage in response - will figure out //*** later

//WebProxy proxy = new WebProxy();
//proxy.Credentials = CredentialCache.DefaultCredentials;
//WebRequest.DefaultWebProxy = proxy;

SqlDataReader adm = null;
string outurl;
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=xxxxx;User Id=xxxxx;Password=xxxxx");
myConnection.Open();
SqlCommand cmd = new SqlCommand("GetURL", myConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 50));
cmd.Parameters["@username"].Value = "xxxx";


SqlParameter url = new SqlParameter("@url", SqlDbType.NVarChar);
// Type=null;
url.Direction = ParameterDirection.InputOutput;
url.Value = null;
url.Size = 255;
cmd.Parameters.Add(url);

adm = cmd.ExecuteReader();
if (adm.HasRows)
{
	while (adm.Read())
	{
		outurl = adm["url"].ToString();

		//changed for winforms app
		WebRequest request = WebRequest.Create(outurl);
		WebResponse response = request.GetResponse();
		MessageBox.Show(response.ResponseUri.ToString());
	}
}
 
Share this answer
 
Comments
RDBurmon 27-Jan-12 2:33am    
I am glad to read you had solved your problem .
Before the creation we nedd to Grant permision to following system object under desired sqlserver user.

sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo
sp_OADestroy
sp_OAStop
sp_OACreate
sp_OAGetProperty

Sqlserver Stored Procedure

Create procedure [dbo].[HTTP_DB_ALERT]( @sUrl varchar(2000), @response varchar(8000)
out)
As
Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)


exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto
eh end

exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end

if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end

exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900