CLR function works with Physical Servers but in VM it initially works then through error network-related or instance-specific error occurred while establishing a connection to SQL Server.
Shamshad Ali
already developed in C# so we are not going to re-write the same code using
stored procedure. But due to some business requirement, we are calling this
function in a stored procedure from SQL Server (2005). The CLR function
while initializes it read connectionstring values from a hard coded
C:\myCon.ini file which resides on SQL Server (2005).
This function is called thousand times to iterate records using a cursor in
following SP (usp_RecoverTracking) and it works very well on Physical server
machines but on VM Server machines it starts normally and do its iterate
with few thousand records and then start throwing following error:
Msg 6522, Level 16, State 1, Procedure usp_RecoverTracking, Line 81
A .NET Framework error occurred during execution of user-defined routine or
aggregate "Extract2":
System.Data.SqlClient.SqlException: A network-related or instance-specific
error occurred while establishing a connection to SQL Server. The server was
not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider:
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.Creat...
Please note that if I call same CLR individually after this error it again
works without any problem but when it traverse thousands of rows it returns
gives error above after sometime.
Please let me know if there is any issue with VM machines hardware? how do I
find where is the problem?
Shamshad Ali.
Shamshad Ali
VM Server Configuration (initially runs for a while and then start Failing
and throwing the error):
=======================
VMWare Virtual Platform
Intel Xeon CPU
E7340 @ 2.4 GHz (4 Processors of x86 Family Model 15 Stepping 8 Genuine
Intel ~2394 Mhz)
2.39 GHz, 15.3 GB of RAM
Physical Address Extension
Physical Server Configuration (Successfully run):
=======================
Power Edge 2850
Intel Xeon CPU 3.60 GHz (4 Processors of x86 Family 15 Model 4 Stepping 10
Genuine Intel ~3591 Mhz)
3.59 GHz, 6.00 GB of RAM
Physical Address Extension
Does it is because of hardware difference? Please help !!!
Shamshad Ali.
---------------------------------------------------------------------------------------------------------------------------------------------------------------
"Shamshad Ali" <shamsh...@softech.us> wrote in message
news:3B70A7CB-BCE2-4E25...@microsoft.com...
Erland Sommarskog
> We developed a CLR function that do some work with database, as the
> logic is already developed in C# so we are not going to re-write the
> same code using stored procedure. But due to some business requirement,
> we are calling this function in a stored procedure from SQL Server
> (2005). The CLR function while initializes it read connectionstring
> values from a hard coded C:\myCon.ini file which resides on SQL Server
> (2005).
>
> This function is called thousand times to iterate records using a cursor
> in following SP (usp_RecoverTracking) and it works very well on Physical
> server machines but on VM Server machines it starts normally and do its
> iterate with few thousand records and then start throwing following
> error:
Apparently your CLR function connects to some SQL Server instance. Where is
this instance? On the same machine? Somewhere else in the network?
The error message says that the code cannot make contact with the other
SQL Server. That is usually due to some network problem.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Shamshad Ali
Thanks for your reply. The CLR Function is on SQL Server and called from
same box. There is no any other activity that require network to perform any
other activity from any other machine. The function is itself called from
same box.
Initially it start work for few minutes then it starts throwing the error
mentioned earlier. I suspect that the network card of VM Machine has some
limits ? what you think?
Shamshad Ali.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CCCF1AC...@127.0.0.1...
Erland Sommarskog
> Thanks for your reply. The CLR Function is on SQL Server and called from
> same box. There is no any other activity that require network to perform
> any other activity from any other machine. The function is itself called
> from same box.
> Initially it start work for few minutes then it starts throwing the error
> mentioned earlier. I suspect that the network card of VM Machine has some
> limits ? what you think?
particular reason you don't use the context connection instead?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Shamshad Ali
Web application (hosted on another box) individually for each session. So
the main idea is to use same code. The SQL CLR function is a wrapper to that
C# method.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CCD5AB8...@127.0.0.1...
Erland Sommarskog
> The reason is the core method of this function is also called from
> ASP.NET Web application (hosted on another box) individually for each
> session. So the main idea is to use same code. The SQL CLR function is a
> wrapper to that C# method.
I would strongly recommend that you rewrite the function, so that it
uses the context connection when you are running the function from
within SQL Server. This will have several advantages:
1) You don't need to create the assembly with EXTERNAL_ACCESS.
2) You get better performance without using a loopback.
3) You strange error message will vanish into thin air.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online: