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?
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.
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
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.
Copyright (c) Marimer LLC