Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

http call embedded in stored proc Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 11:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
So i have a sql sript that i'm converting to a proc. Theres a chunk of sql that runs then the end user is supposed to goto an asp page that when opened has some inline sql that updates the db. Step one for me is to convert this to a proc. Once done i'll swing back and pull the sql out of the asp page and throw it into my proc accordingly.

Long story short is there a way to make an http call in sql stored proc? Was going to use bat file but i have to assume there's a better way.

i just need to embed http://www.helloWorld.asp in my stored proc

Make sence?
Post #927695
Posted Tuesday, May 25, 2010 11:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
I had to do this very recently (last few days), and I ended up creating the stored procedure as a CLR. I wasn't sure how I felt about it at first, but it ended up working really well. I created the CLR in Visual Studio and was able to go from conception to realization in only a few hours.

Here's the code for the CLR ion C# (I ended up making it as a UDF so I could call it for every line):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Text;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ufnFunction(string variable)
{
string str = "";
string cn = "http://server/default.aspx?variable=" + variable;
HttpWebRequest http = (HttpWebRequest)
WebRequest.Create(cn);
http.KeepAlive = false;

HttpWebResponse resp = (HttpWebResponse)http.GetResponse();
Stream receiveStream = resp.GetResponseStream();
Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
StreamReader readStream = new StreamReader(receiveStream, encode);

while (!readStream.EndOfStream)
{
str = str + readStream.ReadLine();
}

readStream.Close();
resp.Close();

return new SqlString(str);
}
};

Post #927706
Posted Tuesday, May 25, 2010 12:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
thanks for the solution. unfortunatly my mngr over heard me discussing your sol w/ a teamate and he hixed it on the spot. I appreciate the solution though.
Post #927714
Posted Tuesday, May 25, 2010 12:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
sorry. Nixed it on the spot.
Post #927715
Posted Tuesday, May 25, 2010 12:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
He nixed the solution or the entire project? Did he give a reason?

--J
Post #927720
Posted Tuesday, May 25, 2010 12:27 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
Nixed the solution. He's big on keeping things as simple as possable for easy debugging purposes. As i mentioned before we're monitoring serveral different apps accross 50 or so sql instances. So i understand where he's coming from. We switch the pager weekly between 5 of us so any backend dev work needs to be easily supported between all 5 of us. I'll prob end up createing a vbs that makes the http request and call vbs via .bat file in my proc.


I can't complain. We've finally moved all sql 2000 instances to 2005 or 2008. I'm really enjoying being away for 2000 thats for damn sure.
Post #927728
Posted Tuesday, May 25, 2010 12:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
I'd argue that calling a VBS from the command line is much less manageable than using CLR. Calling VBS from the command line introduces points of failure that don't exist in CLR, and it also introduces a possible security hole. Unless you already have the operating system extension enabled, I suggest you keep them disabled. CLR is much more secure.

The CLR doesn't have to be in C# either, it can be in VB. And, as you see, it's very short. Personally, I keep my CLRs under source control, and they are very easy to manage. Once deployed, I don't usually have problems with them at all...

It's ultimately up to you and your boss what you do, but personally I'd try to make the case for the CLR. Relying on the operating system seems sloppy, especially when the database provided the mechanism to do what you're doing, within the system, without compromising security.

--J
Post #927740
Posted Tuesday, May 25, 2010 12:40 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
Noted. Thanks J.
Post #927742
Posted Tuesday, May 25, 2010 1:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 12,887, Visits: 31,834
J i've got a question for you regarding deployment;
i created a new VS project named "MyCLRProjects, and added only your Partial Public class as an example;
it compiled, and i was able to run the command to add the assembly:
CREATE ASSEMBLY MyCLRProjects from 'C:\data\MyCLRProjects.dll' WITH PERMISSION_SET = SAFE

when i try to add the udf with the command below, i get this simple error:
Msg 6505, Level 16, State 2, Procedure fn_ReadHttpPage, Line 1
Could not find Type 'UserDefinedFunctions' in assembly 'MyCLRProjects'.



the code:
CREATE FUNCTION fn_ReadHttpPage(@pageToRead varchar(250))
RETURNS varchar(max)
EXTERNAL NAME MyCLRProjects.UserDefinedFunctions.ufnFunction


can you point out my obvious error? i'm sure my Root namespace is simply "MyCLRProjects"


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #927756
Posted Tuesday, May 25, 2010 1:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
That's definitely strange. Personally, I deployed mine from VS, but I've deployed CLRs manually before, and didn't have any such problem.

If I script out my function, it looks mostly identical to your SQL script:

CREATE FUNCTION [dbo].[ufnFunction](@variable [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerCLR].[UserDefinedFunctions].[ufnFunction]

So I'm not sure what's going on. Have you tried deploying from VS? Before this project, I had never tried deploying from VS before, but I tried it and it was pain-free. Might be worth a shot since the manual way is giving you trouble...

I'm not sure if this will make a difference, but the permission level will need to be External. I had to change that in the project properties before deploying.

--J
Post #927765
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse