 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / CLR / November 2009CLR 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.
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: 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. Following is the hardware specifications: 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. --------------------------------------------------------------------------------------------------------------------------------------------------------------- > 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 [quoted text clipped - 57 lines] > > Shamshad Ali. > 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 [quoted text clipped - 9 lines] > 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.  SignatureErland Sommarskog, SQL Server MVP, esquel@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 Erland, 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. >> 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 [quoted text clipped - 16 lines] > The error message says that the code cannot make contact with the other > SQL Server. That is usually due to some network problem. > 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 [quoted text clipped - 3 lines] > mentioned earlier. I suspect that the network card of VM Machine has some > limits ? what you think? So your function makes a loopback connection to the same instance? Any particular reason you don't use the context connection instead? SignatureErland Sommarskog, SQL Server MVP, esquel@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 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. >> 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 [quoted text clipped - 6 lines] > So your function makes a loopback connection to the same instance? Any > particular reason you don't use the context connection instead? > 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. SignatureErland Sommarskog, SQL Server MVP, esquel@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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.