本文介绍了从SQL Server使用Web服务时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在开发一个解决方案,我使用存储过程从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

推荐答案


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服务时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:43