SQL CLR存储过程和Web服务 [英] SQL CLR Stored Procedure and Web Service

问题描述

我目前正在从事一项工作，需要从CLR存储过程中调用Web服务中的方法.

I am current working on a task in which I am needing to call a method in a web service from a CLR stored procedure.

一些背景:

基本上，我的任务需要大量的工作.如果严格用SQL完成，则大约需要30-45分钟才能完成处理.如果将相同的流程放入代码中，由于能够更有效地优化处理，因此我可以在几秒钟内完成该流程.唯一的问题是我必须将此过程设置为SQL Server中的自动化任务.

Basically, I have a task that requires ALOT of crunching. If done strictly in SQL, it takes somewhere around 30-45 mins to process. If I pull the same process into code, I can get it complete in seconds due to being able to optimize the processing so much more efficiently. The only problem is that I have to have this process set as an automated task in SQL Server.

按照这种方式，我将流程作为Web服务公开(我也将其用于其他服务)，并希望SQL CLR sproc能够使用该服务并执行代码.这使我可以执行自动化任务.

In that vein, I have exposed the process as a web service (I use it for other things as well) and want the SQL CLR sproc to consume the service and execute the code. This allows me to have my automated task.

问题:

我已经阅读了很多有关如何在CLR Sproc中使用Web服务的不同主题，并且已经有效地做到了.这是我遵循的示例.

I have read quite a few different topics regarding how to consume a web service in a CLR Sproc and have done so effectivly. Here is an example of what I have followed.

http: //blog.hoegaerden.be/2008/11/11/calling-a-web-service-from-sql-server-2005/

我可以使该示例正常运行.但是，每当将此过程与一个涉及数据库调用的Web Service方法配对时，都会得到以下异常(取决于是否包装try/catch):

I can get this example working without any issues. However, whenever I pair this process w/ a Web Service method that involves a database call, I get the following exceptions (depending upon whether or not I wrap in a try / catch):

消息10312，级别16，状态49，过程usp_CLRRunDirectSimulationAndWriteResults，第0行 .NET Framework执行被中止. UDP/UDF/UDT没有还原线程令牌.

Msg 10312, Level 16, State 49, Procedure usp_CLRRunDirectSimulationAndWriteResults, Line 0 .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

或

Msg 6522，第16级，状态1，过程MyStoredProc，第0行

Msg 6522, Level 16, State 1, Procedure MyStoredProc , Line 0

在执行用户定义的过程中发生了.NET Framework错误 常规或汇总的"MyStoredProc":

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyStoredProc':

System.Security.SecurityException:请求类型的许可 'System.Security.Permissions.EnvironmentPermission，mscorlib， 版本= 2.0.0.0，文化=中性，PublicKeyToken = b77a5c561934e089' 失败.

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

System.Security.SecurityException:

在System.Security.CodeAccessSecurityEngine.Check(对象需求， StackCrawlMark& stackMark，布尔值isPermSet)

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

在System.Security.CodeAccessPermission.Demand()

at System.Security.CodeAccessPermission.Demand()

在System.Net.CredentialCache.get_DefaultCredentials()

at System.Net.CredentialCache.get_DefaultCredentials()

在 System.Web.Services.Protocols.WebClientProtocol.set_UseDefaultCredentials(Boolean 值)

at System.Web.Services.Protocols.WebClientProtocol.set_UseDefaultCredentials(Boolean value)

在 MyStoredProc.localhost.MPWebService.set_UseDefaultCredentials(Boolean 值)

at MyStoredProc.localhost.MPWebService.set_UseDefaultCredentials(Boolean Value)

在MyStoredProclocalhost.MPWebService..ctor()

at MyStoredProclocalhost.MPWebService..ctor()

位于MyStoredProc.StoredProcedures.MyStoredProc(字符串FromPostCode， 字符串ToPostCode)

at MyStoredProc.StoredProcedures.MyStoredProc(String FromPostCode, String ToPostCode)

我确定这是一个许可问题，但是我不能，因为我一生都可以使用它.我曾尝试在CLR proc和其他一些东西中使用模拟.有什么建议?我想念什么?

I am sure this is a permission issue, but I can't, for the life of me get it working. I have attempted using impersonation in the CLR sproc and a few other things. Any suggestions? What am I missing?

推荐答案

我只是想弄清楚，但这就是我得到的:

I am just figuring this out, but this is what I got:

在SQL中将程序集创建为UNSAFE时，需要将权限集设置为

You need to set the permission_set when you create the assembly in SQL to UNSAFE.

CREATE ASSEMBLY [<assemblyname>] FROM '<path>/<assemblyname>.dll'
WITH PERMISSION_SET = UNSAFE
GO

如果您使用VS SQL数据库项目进行部署，也可以通过在数据库项目属性的数据库"选项卡上设置对UNSAFE的权限"来完成.

If you are using a VS SQL Database Project to do your deployment, this can also be done by setting the Permission to UNSAFE on the Database Tab of Properties for the Database Project.

这篇关于SQL CLR存储过程和Web服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！