Received an pm asking for more details on the programming portion of this, as CLR can be a bit scary the first time around.
hopefully, this can get someone started, but there's a lot of tutorials out there; I'm better with specific questions rather than overall processes.
1. I believe you'll need to set your database to TRUSTWORTHY.
so assuming you create a brand new database named "Test" so you can be sure this works, you need to run this command:
ALTER DATABASE Test SET TRUSTWORTHY ON
2. it's a lot easier to modify something that already exists, than it is to start fresh.
So I slapped together a Visual Studio Database project real quick.
grab this zip file containing a Visual Studio 2010 and open it in in visual studio.CLRWeb.zip
3. go to the project properties and change the database connection shown below:
4. Use the Build>>Build CLRWeb command as shown in the screenshot above.
5. Use the Build>>Deploy CLRWeb command as shown in the screenshot above.
If the build fails, Control+alt+O takes you to the output window....review the error.
6. Once deployed, use the same test method I pasted before to test it in TSQL
declare @url varchar(1000)
declare @int int
SET @int = 59
SET @url='http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=' + CONVERT(varchar,@int)
declare @results varchar(max)
SELECT @results = dbo.CLR_WebQuery(@url)
If it works, you can now start customizing it by changing the project name or whatever else you need to do, but it would be good to go as is.
you might want to consider using a certificate instead of setting Trustworthy, but that should get you started.
if you were going to use it as is, you would repeat the same steps above so you point at the final target database.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!