LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » CLR integration storedprocedure execution error

CLR integration storedprocedure execution error

Options

Previous Topic · Next Topic

raaz

Posted: Thursday, August 16, 2007 11:55:09 PM

Rank: Junior DBA

Joined: 5/17/2007
Posts: 59
Points: 189
Where do you live?: India

Hi

I created the an assembly and stored procedure using the following steps:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

alter database TEST set trustworthy on

CREATE ASSEMBLY ClrWebServices
FROM 'D:\Data\clr_4.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'D:\Data\clr_4.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE PROCEDURE InsertLocation(@city nvarchar(200),
@state nvarchar(200), @country nvarchar(200))
AS
EXTERNAL NAME ClrWebServices.StoredProcedures.clr_4
GO

After this when i am trying to execute the procedure InsertLocation

EXEC InsertLocation 'Sarasota','Florida','USA'

I am getting the following exception..

Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ClrWebServices.Test.TerraService.GetPlaceFacts(Place place)
at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country)
at StoredProcedures.clr_4(SqlString city, SqlString state, SqlString country)

I searched in the google and found one answer in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76222&SiteID=1 suggesting use

ALTER ASSEMBLY ClrWebServices WITH PERMISSION_SET=EXTERNAL_ACCESS

when i executed i got the exception

Msg 6213, Level 16, State 1, Line 1
ALTER ASSEMBLY failed because method "add_ConvertLonLatPtToNearestPlaceCompleted" on type "ClrWebServices.Test.TerraService" in external_access assembly "clr_4" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.

Can anybody help on this?

Thanks In Advance....

raaz

Posted: Wednesday, August 22, 2007 6:20:44 AM

Rank: Junior DBA

Joined: 5/17/2007
Posts: 59
Points: 189
Where do you live?: India

Hi,

I installed .NET framework SDK in the SQL SERVER machine where we have full admin rights and also run the commands for Trustworthy.

and followed the steps given in the community site given below…

http://www.codeplex.com/MSFTEngProdSamples/Wiki/View.aspx?title=SS2005%21Currency%20Web%20Service%20Sample&referringTitle=Home

at the final step it fails….

<sqlcmd -E -I -i test.sql >

executing the test.sql
[

USE AdventureWorks
GO

SELECT CurrencyCode, CurrencyRateDate, EndOfDayRate FROM dbo.CurrentCurrencyInformation(GETDATE());

]

I got the followin error....

Msg 6522, Level 16, State 1, Line 4
A .NET Framework error occurred during execution of user-defined routine or aggregate "CurrentCurrencyInformation":
System.InvalidOperationException: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<?xml version="1.0" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head>
<title>Server Unavailable
</title>
</head>
<body>
<h1>Server Application Unavailable </h1>


The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.


Administrator Note:
 An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

</body>
</html>

--.
System.InvalidOperationException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.Samples.SqlServer.Service.GetCurrentRates(DateTime asOfDate)
at Microsoft.Samples.SqlServer.CurrencyWebClient.GetCurrentCurrencyInformation(DateTime asOfDate) .

Any help on this ?

Thanks In Advance.....

Scott Whigham

Posted: Wednesday, August 22, 2007 9:57:01 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

Man, I have no clue... What website is it calling? Is that website down? That message - "Server Unavailable" - leads me to believe the website that is supposed to be responding to the web service call is down.

I don't have the source so I'm not sure I can help Frown

raaz

Posted: Thursday, August 23, 2007 5:59:34 AM

Rank: Junior DBA

Joined: 5/17/2007
Posts: 59
Points: 189
Where do you live?: India

Hi,

I was able to execute the webservice example installed with the SQL Server Samples by following the steps given in the following MicroSoft community link

http://www.codeplex.com/MSFTEngProdSamples/Wiki/View.aspx?title=SS2005!Currency%20Web%20Service%20Sample&referringTitle=Home

I tried creating my own example using Visual Studio and followed the steps as given in the link

I manually registered the local hello world web service that was used in the code and was able to run it from iis.(http://localhost/LocalService/Service.asmx)

and my Code is as follows.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using TestProject.LocalService;
using System.Xml.Serialization;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProcedure()
{
// Put your code here
Service S = new Service();
S.UseDefaultCredentials = true;
String str = S.HelloWorld();

using (SqlConnection cn = new SqlConnection("context connection=true" Wink

)
{
string query = "INSERT INTO dbo.TestTab(xxx) VALUES ('" + str + "')";

using (SqlCommand insertCommand = new SqlCommand(query, cn) )
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}
};

AT SQL Server End:

Able to create the assemblies with external access permissions and

use master
go
SELECT replace(physical_name, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'D:\Program Files\TestSolution\')
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASYMMETRIC KEY ExternalTest_Key FROM EXECUTABLE FILE = 'D:\Program Files\TestSolution\TestProject\bin\Debug\TestProject.dll';
CREATE LOGIN ExternalTest_Login FROM ASYMMETRIC KEY ExternalTest_Key
GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalTest_Login
GO

use TEST
Go

CREATE ASSEMBLY TestPrjService
FROM 'D:\Program Files\TestSolution\TestProject\bin\Debug\TestProject.dll'
WITH permission_set = external_access;

CREATE ASSEMBLY TestPrjServiceSerializers
FROM 'D:\Program Files\TestSolution\TestProject\bin\Debug\TestProject.XmlSerializers.dll'
WITH permission_set = external_access;
GO

CREATE PROCEDURE callTestPrjService
AS
EXTERNAL NAME TestPrjService.StoredProcedures.TestProcedure
GO

EXECUTE callTestPrjService

at the final execution got the following error

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

A .NET Framework error occurred during execution of user-defined routine or aggregate "callTestPrjService":

System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

System.Net.Sockets.SocketException:

at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)

at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)

System.Net.WebException:

at System.Net.HttpWebRequest.GetRequestStream()

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at TestProject.LocalService.Service.HelloWorld()

at StoredProcedures.TestProcedure()

.

Any Idea why this error came?

I am in the assumption that it could be because the code I developed using visual studio which was not available in the machine where SQL Server was installed.

Moved the code to the Server where SQL Server and .NET Frame work 2.0 were installed and followed the steps mentioned in the above link.

Is my assumption Correct? Any help about this?

raaz

Posted: Friday, August 24, 2007 6:26:21 AM

Rank: Junior DBA

Joined: 5/17/2007
Posts: 59
Points: 189
Where do you live?: India

Hi,

I was able to resolve the issue and got it executed.

The issue was the it needs the ConnectionString to be added to web.config and Strongname key to be added to assembly.cs {This is important to create the assemblies , with external_access} and in the reference.cs the url should point to the correct path of the web service { here the mistake was the url is pointing to the port number of the server where the .NET code was build, so i chaged it to local}

and after that compile, create the SQL Assembly as well as the sp and done the required job by executing the sp.

How ever while using the exeternal web service for example(http://www.terraserver-usa.com/TerraService2.asmx) followed the same steps and at the final execution by getting the same error mentioned earlier.....

Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ClrProject_Ext.TerraService.TerraService.GetPlaceFacts(Place place)
at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country)
at StoredProcedures.ClrCallExtWebService(SqlString city, SqlString state, SqlString country)

Do i need to open any firewall for this external site to be opened?

Any Help On this?

Thanks In Advance...

.

Scott Whigham

Posted: Tuesday, August 28, 2007 3:47:40 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

Why not try a different CLR assembly (such as the one at the bottom of this page: http://msdn2.microsoft.com/en-us/library/bb293147.aspx) and diagnose the problem that way? If you can successfully use another web service then there's a problem with the terraserver site. If not, then it's your code/firewall/config.

I really don't think it's your firewall though

raaz

Posted: Wednesday, August 29, 2007 8:59:13 AM

Rank: Junior DBA

Joined: 5/17/2007
Posts: 59
Points: 189
Where do you live?: India

Hi Scott,

I tried the with Other web service also (CurrencyConverter) and ended with same error at final execution.

Any how i will go through the link that you have given and give another try.. :-)

ThankQ

Scott Whigham

Posted: Wednesday, August 29, 2007 9:12:54 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

Well, you know as well as I do I'm sure that the best way to really understand something is to not understand it at all, break it, fix it, break it again and again, and then keep figuring out how to fix it Smile

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.