Error when consuming web service from SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

Posted 2-Apr-17 22:53pm

Member 13017290

Updated 9-Apr-17 4:22am

Add a Solution

Comments

CHill60 3-Apr-17 6:32am

You mentioned that SQL Server and VS are installed on different servers. Do you have the DLL installed on the same server as SQL Server (as that is where the SP is running)?

Member 13017290 3-Apr-17 6:40am

Yes, the serializer dll is installed on the SQL Server:

CREATE ASSEMBLY [Database1.XmlSerializers]
AUTHORIZATION dbo
FROM '\..Path..\Database1.XmlSerializers.dll'
WITH PERMISSION_SET = UNSAFE

CHill60 3-Apr-17 6:47am

It was just a thought :)

RedDk 3-Apr-17 12:08pm

I don't know what could cause this because my work with creating CLR functions that are used by SQL Server is limited and usually by example posted here in CP. But that wouldn't prevent me from still going to places like CODEPLEX and looking up CLR projects that do exactly this sort of thing, inspecting the code, and duplicating the method down to it's teeth.

Member 13017290 4-Apr-17 7:44am

Thanks for the tip. I searched on the Codeplex site but unfortunately I didn't find anything that helped me there.

NightWizzard 3-Apr-17 13:02pm

Maybe the Windows account, you Webservice is running in has no permission to access the DLL?

Member 13017290 4-Apr-17 7:38am

No, the permission settings are correct so that is not the cause here.

Richard Deeming 4-Apr-17 11:06am

Are you absolutely certain that you've registered the XML serialization assemblies in SQL?

Using Webservices and Xml Serialization in CLR Integration[^]
XML Serialization from CLR Database Objects[^]
Getting XML Serialization and Webservices to work with SQL CLR[^]

Member 13017290 5-Apr-17 3:40am

Yes I have registered the serialization assembly (se my answer to the first comment in this article). I have also tried to set permission level to SAFE and EXTERNAL_ACCESS for the assembly when creating it in SQL Server but I still get the same error.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Badal Kumar · Answer 1 · 2017-04-09T04:22:00

Follow the steps:

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

C:\CRLProject\Database1.XmlSerializers.dll

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. 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. Execute below database command

ALTER DATABASE Database1 SET TRUSTWORTHY ON

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.