从 SQL Server 中的存储过程调用 URL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4798538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling a URL from a stored procedure in SQL Server?
提问by Spooks
Just wondering if its possible to call a URL from a stored procedure (eventually adding that procedure to a sql job) As the webpage refreshes my database, it would be excellent if I could automate this process.
只是想知道是否可以从存储过程调用 URL（最终将该过程添加到 sql 作业）当网页刷新我的数据库时，如果我可以自动化这个过程，那就太好了。
Edit:
编辑：
I want to be able to request a webpage from a store procedure. On the page load of the desired webpage there is a function that refreshes my database. I want it to refresh my database at 4 am every day. In order for me not to manually go onto the site at 4am (still sleeping) I need something else to do it for me. I thought sql jobs would be excellent, as I can set the time, and the job up. I don't know PowerShell all that well, and wanted to know if I could request a URL, or visit a url using a stored procedure or any other way.
我希望能够从存储过程请求网页。在所需网页的页面加载上，有一个功能可以刷新我的数据库。我希望它每天凌晨 4 点刷新我的数据库。为了让我不要在凌晨 4 点（还在睡觉）手动进入网站，我需要为我做其他事情。我认为 sql 工作会很棒，因为我可以设置时间并完成工作。我不太了解 PowerShell，想知道我是否可以请求 URL，或者使用存储过程或任何其他方式访问 url。
采纳答案by Bennor McCarthy
You can do it with Task Scheduler (part of Windows). Just create a scheduled task that opens Internet Explorer and browses to the page:
您可以使用任务计划程序（Windows 的一部分）来完成。只需创建一个计划任务，打开 Internet Explorer 并浏览到该页面：
"C:\Program Files\Internet Explorer\iexplore.exe" "http://yoursite.com/yourpage.aspx"
Or for 64-bit Windows:
或者对于 64 位 Windows：
"C:\Program Files (x86)\Internet Explorer\iexplore.exe" "http://yoursite.com/yourpage.aspx"
Alternatively, create a job using SQL Server Agent, and create a single step of type "Operating System (CmdExec)" with the above command.
或者，使用 SQL Server 代理创建作业，并使用上述命令创建类型为“操作系统 (CmdExec)”的单个步骤。
回答by Spooks
I'm pretty sure MS SQL doesn't allow you to do that directly.. obvious security issues. However, I think you can get around it by using xp_cmdshell to execute a vbscript file and in that file, create an xmlhttp request to a site.
我很确定 MS SQL 不允许你直接这样做......明显的安全问题。但是，我认为您可以通过使用 xp_cmdshell 执行 vbscript 文件并在该文件中创建对站点的 xmlhttp 请求来解决它。
xp_cmdshell command:
xp_cmdshell 命令：
EXEC master..xp_cmdshell 'c:\<file>.vbs',no_output
VBScript:
脚本：
call main()
sub main()
Dim xmlHTTP, url
Set xmlHTTP = WScript.CreateObject("Msxml2.XMLHTTP")
url = "<url>"
xmlHTTP.Open "GET", url, False
xmlHTTP.Send ""
end sub
EDIT
编辑
In response to a comment on how to do this asynchronously.
回应有关如何异步执行此操作的评论。
xp_cmdshell command:
xp_cmdshell 命令：
EXEC master..xp_cmdshell 'c:\caller.vbs',no_output
VBScript for caller:
调用者的 VBScript：
call main()
sub main()
Dim scmd
Set scmd = "c:\windows\system32\cscript.exe //nologo c:\<originalVBS>.vbs"
createobject("wscript.shell").run scmd,0,false
end sub
回答by user6168486
@newurl is url you want to hit @response is response you received
@newurl 是您要点击的网址 @response 是您收到的回复
EXEC Sp_oacreate 'MSXML2.XMLHTTP',@obj OUT;
EXEC Sp_oamethod @obj,'open',NULL,'get',@newurl,'false'
EXEC Sp_oamethod @obj,'send'
EXEC Sp_oamethod @obj,'responseText',@response OUTPUT
EXEC Sp_oadestroy @obj
