从SQL Server使用Web服务时出错 [英] Error when consuming web service from SQL server

问题描述

我正在开发一个解决方案，我使用存储过程从SQL Server调用Web服务。

我在Visual Studio 2015中编写CLR代码C＃

SQL Server是带有.NET Framework 3.5.1的2008版本（SQL Server和Visual Studio安装在不同的服务器上）



如果我打电话给我的Visual Studio解决方案中的C＃程序的Web服务工作正常但是当我从存储过程调用时，我收到此错误消息：

无法加载文件或程序集\ .. （路径）.. \Database1.XmlSerializers.dll'或其中一个依赖项。系统找不到指定的文件。

StackTrace：at System.Reflection.AssemblyName.nGetFileInformation（String s）

在System.Reflection.AssemblyName.GetAssemblyName（String assemblyFile）

at StoredProcedures.SqlStoredProcedure1（String land）



我尝试过：



在我的数据库中ase项目我发布构建序列化程序集（C：\Program Files（x86）\Microsoft SDKs \ Windows \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ br />


在reference.cs文件中，我添加了[System.Xml.Serialization.XmlSerializerAssemblyAttribute]以及序列化程序集的路径。



我暂时禁用了CLR Exception设置，以防止抛出异常来破坏执行。



我验证了序列化程序集的依赖关系并找到了三个程序集。我已将这三个程序集放在与序列化程序集相同的目录中。



在测试期间，我在数据库项目中使用权限级别UNSAFE。



我尝试使用添加服务引用和添加Web引用来生成Web服务的代理



I非常感谢帮助解决这个问题。



问候

Christer

I am developing a solution where I'm calling a web service from SQL Server using a stored procedure.
I'm writing the CLR code in Visual Studio 2015 C#
The SQL Server is version 2008 with .NET Framework 3.5.1 (SQL Server and Visual studio are installed on different servers)

If I make the call to the web service from a C# program in my Visual Studio solution it works fine but when I make the call from the stored procedure I get this error message:
"Could not load file or assembly '\..(path)..\Database1.XmlSerializers.dll' or one of its dependencies. The system cannot find the file specified."
StackTrace: at System.Reflection.AssemblyName.nGetFileInformation(String s)
at System.Reflection.AssemblyName.GetAssemblyName(String assemblyFile)
at StoredProcedures.SqlStoredProcedure1(String land)

What I have tried:

In my database project I Post build the serializer assembly ("C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\sgen.exe" /force "$(TargetPath)")

In the reference.cs file I added [System.Xml.Serialization.XmlSerializerAssemblyAttribute] with the path to the serializer assembly.

I have temporarily disabled CLR Exception settings to prevent exceptions thrown to break the execution.

I verified the serializer assembly dependencies and found three assemblies. I have placed these three assemblies in the same catalog as the serializer assembly.

During testing I use Permission level UNSAFE in the database project.

I have tried both using 'Add Service Reference' and 'Add Web Reference' to generate the proxy to the Web Service

I would appreciate help to solve this problem.

Regards
Christer

推荐答案

（TargetPath））



在reference.cs文件中，我添加了[System.Xml.Serialization.XmlSerializerAssemblyAttribute]以及序列化程序集的路径。



我暂时禁用了CLR异常设置，以防止抛出异常来中断执行。



我验证了序列化程序集依赖项并找到三个程序集。我已将这三个程序集放在与序列化程序集程序相同的目录中。



D测试我在数据库项目中使用权限级别UNSAFE。



我尝试使用添加服务引用和添加Web引用来生成代理网络服务



我很感激帮助解决这个问题。



问候

Christer

(TargetPath)")

In the reference.cs file I added [System.Xml.Serialization.XmlSerializerAssemblyAttribute] with the path to the serializer assembly.

I have temporarily disabled CLR Exception settings to prevent exceptions thrown to break the execution.

I verified the serializer assembly dependencies and found three assemblies. I have placed these three assemblies in the same catalog as the serializer assembly.

During testing I use Permission level UNSAFE in the database project.

I have tried both using 'Add Service Reference' and 'Add Web Reference' to generate the proxy to the Web Service

I would appreciate help to solve this problem.

Regards
Christer

按照以下步骤操作：



1.将DLL放在特定位置的SQL服务器上，例如：

Follow the steps:

1. Place the DLL on SQL server at specific location e.g:

C:\CRLProject\Database1.XmlSerializers.dll

2.执行以下SQL查询以安装程序集

2. Execute below SQL query to install the assembly

 EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MySqlCLRProject')
BEGIN
	DROP ASSEMBLY MySQLCLRProject
	ALTER ASSEMBLY MySQLCLRProject
	FROM 'C:\CRLProject\Database1.XmlSerializers.dll'
	WITH PERMISSION_SET = UNSAFE ;
END
ELSE
BEGIN
	CREATE ASSEMBLY MySQLCLRProject
	FROM 'C:\CRLProject\Database1.XmlSerializers.dll'
	WITH PERMISSION_SET = UNSAFE;
END

3.使用存储过程作为提到下面

3. Use stored procedure as mentioned below

T ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[dsp_SampleCLRSP]
@dataList [nvarchar](max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MySQLCLRProject].[StoredProcedures].[dsp_CLRGetSampleList]
GO

4.执行以下数据库命令

4. Execute below database command

ALTER DATABASE Database1 SET TRUSTWORTHY ON

5.执行以下命令

5. Execute below command

CLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::Database1 TO [<big>somedvuser</big>]' 

SELECT @Command = REPLACE(REPLACE(@Command 
            , 'Database1', SD.Name)
            , 'DomainName\<big>yoursqluserid</big>', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

PRINT @Command
EXEC(@Command)

如果您仍然面临此问题，请告诉我们。

Please let us know if you still facing the issue.

这篇关于从SQL Server使用Web服务时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！