问题描述
我正在开发一个解决方案,我使用存储过程从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服务时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!