Remoting/Connection pooling issue?

Remoting/Connection pooling issue?

Old forum URL: forums.lhotka.net/forums/t/4063.aspx


rlriggs posted on Wednesday, December 19, 2007

I'm stumped and hoping someone may be able to give me a clue how to resolve this.

Have a Windows app using remoting.   The SQL 2005 resides on the same box as IIS that hosts the remoting objects.    Application works fine on my machine, works most of the time using remoting.  

In the remote environment, I can log on, I can perform the majority of the business object calls and successfully read and save to database.   However, in two places in my code I get the following error:

'An error has occurred while establishing a connection to the server.   When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settins SQL Server does not allow remote connections.'   Seems like this is pointing to a connection pooling problem??

This is odd, because I'm connecting and doing everything under the same connection string.    The places in my code where this pops up is in an 'unassign method' I use to remove a child object from a parent - similar to Rocky's AssignProjectResources/unassignProjectResources methods.   One code sample (again this works locally, but will not work in the remoting environment)

Public Sub RemoveCourseCategory(ByVal courseID As Integer, ByVal courseCategoryID As Integer)

'method to remove a course category

Using cn As New SqlConnection(HelperFunctions.GetConnectionString())

cn.Open()

Using cm As SqlCommand = cn.CreateCommand

With cm

.Connection = cn

.CommandType = CommandType.StoredProcedure

.CommandText = "boDelCategoryCourseEdit"

cm.Parameters.AddWithValue("@CourseID", courseID)

cm.Parameters.AddWithValue("@CourseCategoryID", courseCategoryID)

.ExecuteNonQuery()

End With

End Using

End Using

End Sub

I'm missing something apparently very simple, but it's tough to debug in he remote environment and it works fine locally.   Any words of wisdom?

 

 

RockfordLhotka replied on Wednesday, December 19, 2007

Is it possible that this method is somehow being called outside a DataPortal_XYZ method? In other words, it could be running on the client instead of the server?

Commuterman replied on Thursday, December 20, 2007

Could the following KB article help?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Additionally, in SQL Server Management Studio, right click SQL server instance, properties, connections and check settings of the "Allow remote connections to this server".

If you have remoting working for other business objects and onward calls to SQL server then the above checks probably won't resolve anything.

As you are no doubt aware there are many articles on the internet that suggest reasons for remote connections to SQL server not working, so hopefully one of those will have your solution.  Failing that I can only suggest simplifying your code so that all it is doing is trying to perform the simplest hop from your remote business object to SQL server and then building up from there.

JoeFallon1 replied on Thursday, December 20, 2007

I second Rocky's question.

Where is the call to that Sub being made from?

Unless you are in a DataPortal method it will only work locally.

Joe

 

rlriggs replied on Thursday, December 20, 2007

Good news/Bad news - Rocky, as usual, was dead on in that I was making the call outside the data portal (kind of the whole point of the framework, eh?) - that's the good news.   The bad news is I'm such a dork for not seeing this.    I was also very consistent and duplicated the error in about 12 different places, but now it should be easy to fix.

Again, thank you for all the great work and all your prompt replies - happy holidays to all.

 

ajj3085 replied on Thursday, December 20, 2007

Don't feel bad, it's easy to end up doing such things.  I'm sure everyone here has that happen to them, and it can be difficult tracking down where you let data slip out where it shouldn't be.

Andy

rlriggs replied on Thursday, December 20, 2007

More good news - true epiphany and realizing why Rocky created Command objects.

Copyright (c) Marimer LLC