InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:15:44 GMT20http://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxReceived 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:[code]ALTER DATABASE Test SET TRUSTWORTHY ON[/code]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.[b][url=http://www.stormrage.com/SQLStuff/CLRWeb.zip]CLRWeb.zip [/url][/b]3. go to the project properties and change the database connection shown below:[img]http://www.stormrage.com/SQLStuff/clr_change_database.png[/img]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[code]Use Test;GOdeclare @url varchar(1000)declare @int intSET @int = 59SET @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)print @results[/code]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.Thu, 08 Nov 2012 12:08:20 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxoh yeah!!![img]http://www.stormrage.com/SQLStuff/TSQL_Hammer.png[/img]Thu, 08 Nov 2012 09:23:27 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxLOL Lowell you really pulled out all the stops with your T-SQL hammer on this one!!! Talk about brute force. I don't remember the link to the hammer on your site but this conjured up memories of that one. :-DThu, 08 Nov 2012 08:18:07 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxI'd do exactly what Sean is recommending...I'd have a service slam the web site and store the results in a table, all outside of TSQL.Now, having said that, just to be stubborn, i got out my "gotta do it in TSQL" hat, and created a CLR to return a web page, or in this case a web request.It works via TSQL, and takes about 6 seconds or so to get the results...so it's not all that quick, but we are talking about the web, here.the call:[code]declare @url varchar(1000)declare @int intSET @int = 42SET @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)print @results/*OK#0 appels effectifs / #42 / #0*/[/code]and the CLR definition:[code] 'requires 'Imports System.Net <Microsoft.SqlServer.Server.SqlFunction()> Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' varchar(8000) = SqlString, SqlChars=varchar(max) Dim request As WebRequest = HttpWebRequest.Create(URL) Using response As WebResponse = request.GetResponse() Using dataStream As Stream = response.GetResponseStream() Using reader As New StreamReader(dataStream) Dim responseFromServer As String = reader.ReadToEnd() Return New SqlChars(New SqlString(responseFromServer)) End Using End Using End Using request = Nothing End Function[/code]Thu, 08 Nov 2012 08:08:26 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxI would not do this in sql. This sounds like you need a simple program to do this. If I had this assignment I would generate a .net windows service for this.Thu, 08 Nov 2012 07:42:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxI may be overcomplicating things, and there may be a much more simple way to do this, but for the first part, you're looking at creating an agent job that fires up a select, using the table output as a variable;i.e. @URL is your URL path@nocall = your selected record resultset @nocall = select <field> from <table> where <whichever criteria you're using to choose the number>set @URL = 'http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall='@nocallYou can then use a cmdexec command to fire up Internet Explorer with the URL:(Create a text document with the following entry:@start "" /b "C:\Program Files\Internet Explorer\iexplore.exe" %*Rename the file AutoURL.bat)You could then call this from the command line;*exec AutoURL www.google.com for exampleYou would have to pass the variable output to the command (i.e. AutoURL @URL)Personally, I would consider using a VB Script for the whole job, rather than anything SQL offers on its own.The second part may be more difficult, as it will depend on the structure of the website and what you're actually able to read from it.Thu, 08 Nov 2012 04:20:11 GMTMcSQLhttp://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspxHello ,Can somebody please assist me, i need to call this URL http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=XXXX where XXXX is a number which we obtain from a table by a simple select Query.create schedule Job which execute every 1 minute and also to save the reponse we obtain from the URL to a database. Any idea ?ThanksWed, 07 Nov 2012 20:55:58 GMTmooneer3112