An error has occurred while establishing a connection to the server. When connecting to SQ

I get the following error and have been trying to figure out why I keep getting it. Initially, I had placed my project under wwwroot folder and ran it under IIS and it gave this error. Then I moved it to my local C drive and same thing. I am sharing this project with two other co-workers and all our config files and code files are same...they don't get this error but I do. I checked that SQL Server Client Network Utility has TCP/IP and the 'Named Pipes' enabled. I thought maybe I have setting in the Visual Studio 2005 that I'm not aware of that's causing this problem...it can't be the server since my co-workers aren't having this error and can't be anything in the code since all of us are sharing this project through vss. I dont' think using different version of .net framework can create this error. I changed the version from 2.0 to use 1.1x and it gave same error... Any help would be greatly appreciated. Thanks in advance.

Server Error in '/RBOdev' Application.

---

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 settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: 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 settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): 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 settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +685966 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +109 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +383 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() +16 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) +195 System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) +95 System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() +105 System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) +497 System.Web.UI.Control.InitRecursive(Control namingContainer) +321 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

I actually figured out the problem. Under the <asp:WebpartManger> control, I had to specify Personalization-Enabled to "False"...I guess by default, if it is not set, it will be true...in that case, it'll look for a database in SQL Server 2005 which I"m not using...my database that I'm connecting to is in SQL Server 2000. However, the only thing that is still not clear to me is that my two other co-workers are sharing the same project with me using VSS. All their settings are the same as mine and clearly they had the WebpartManger and property for personalization was not set to false...but they did not get this error...why was I the only one getting it? Is there another location where the personalization property can be set to false? Thanks...

|||

Hi,

From your description, it seems that you are using WebParts feature in your website project, right?

Generally, when you are using WebParts in your application, it requires SQLExpress database, which is a built-in database in Microsoft Visual Studio 2005. Since webparts need personalization features , and all these personalization data would be stored in database.

Besides, in webparts there are several modes, including Browser,Design,Cateloge and etc. When you are in Browser mode, the personalization feature can be set in false status. But for other modes, it requires the personalization and database support.

Also, if you want to achieve personalization on the machine with a SQLServer 2000, you can use aspnet_regsql.exe tool to convert the ASPNETDB database in SQLExpress to your SQLServer database, and change the corresponding provider to achieve your personalization.

Thanks.

AMO Connection

I'm sorry for this newbie question, but I can't find any other help and I can't figure out how to solve this problem. Hope someone can help me:
I want to connect to a SSAS Server in order to detect the databases.
However, I don't know how to connect to the server in SSAS.
Whenever I connect with server.Connect(string), I get this error message:

---

Connection failed on Microsoft.SqlServer.Dts.Runtime.ConnectionManager with Object reference not set to an instance of an object.

---

Here is what my code looks like:

---

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices


Public Class ScriptMain

Dim connMgr As ConnectionManager = Dts.Connections("SSAS")
Dim server As Microsoft.AnalysisServices.Server
Dim db As Microsoft.AnalysisServices.Database
Dim connStr As String

connStr = Dts.Connections("SSAS").ConnectionString

Try
connMgr.AcquireConnection(Nothing)
MsgBox("connMgr is connected.")
server.Connect(connStr)
MsgBox("server is connected.")
Catch ex As Exception
Dts.Events.FireError(-1, "", "Connection failed on " + connMgr.ToString + " with " + ex.Message, "", 0)
End Try

For Each db In (server.Databases)
MsgBox(db.Name)
Next

End Sub

End Class

You forgot to initialize the server variable.

I think you want to do

server = connMgr.AcquireConnection(Nothing)

HTH,
Ovidiu

|||Thanks. Yes, this is what I want to do.
However,AcquireConnection(Nothing) returns an object which can not be casted to Microsoft.AnalysisServices.Server.

Error message on an explicit type cast:

---

Connection failed on Microsoft.SqlServer.Dts.Runtime.ConnectionManager with Unable to cast COM object of type 'System.__ComObject' to class type 'Microsoft.AnalysisServices.Server'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

---

So, how can I use the connection with Microsoft.AnalysisServices.Server?
|||

I'm not much of a VB guy:)

maybe it should be something like:

server = connection.AcquireConnection(Nothing) As Server

HTH,
Ovidiu

|||I'm no VB guy, too :-)

What should that statement do? (btw: it does not work)
If it should be a cast: the problem is not the cast syntax, it is that I cannot downcast the returned object, since there is some incompatibility with the COM hierarchy.

Microsoft.AnalysisServices.Server has a connect Method on its own, however, it only takes a string as argument and it fails when calling it in SSIS.

Does anybody know how to use the ConnectionManager connection in Microsoft.AnalysisServices?
|||I found the error. Some VB mistake
Server.Connnect works well ...

Thanks for your support.

Am I locking it up...

Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.

But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @.@.error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.

Tomwhat do you see when you do sp_who2?

Any blocking?

Do you call any other sprocs?

Do you execute any extended procs?|||Nope I do not see any blocking. No, but will and no. Any ideas?

I did find this. By the way I am using your template.

Server: Msg 2732, Level 16, State 1, Procedure CreateParContracts, Line 879
Error number 0 is invalid. The number must be from 13000 through 2147483647
This happens when I reach the sp_error lable at the end of stored proc. Here is the code

SP_Error:
If @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
If (@.Error_Type = 50002)
BEGIN
Rollback TRAN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END
RAISERROR @.Error_Type @.Error_Message

the line 879 seems to be pointing to if statement "If @.Error_Type = 50001"|||Can you attach the sproc so we can look at it?|||Ok I believe I have attached the file. Actually I did not orginally produce this script. It was just a set of t-sql code being run each time they need to load these. I am trying to automate the process. Couple of things.

1. The attachment is not the one I could get to compile. I kept getting a Server: Msg 133, Level 15, State 1, Procedure CreateParContracts, Line 862
A GOTO statement references the label 'SP_Error' but the label has not been declared.

??

2. If I remove the sp_exit and just put it the end of the stored procedure then I have the snippet of code that I just posted. So I don't know. Looks ok to me but I have something wrong. :eek:

Thanks Brett

Tom|||I'm looking...

first very bad thing is the creation of a permanent table...what if the sproc is run at the same time...change that to use a temporary table...and even though the temp table goes away at the end of the batch, it's always a good idea to drop it on the way out...

I'm still looking|||Well, I agree but i didn't think it would hurt anything leaving it in since it would get run once a month and by one person. But, if this were to be used by more than one user than yes it has to use a temp table. As matter of fact I think it would better to just rewrite the whole thing. Since there are a lot of things that bother me about this script.|||Yeah..there are around 25 separate steps...

And where did you get that code? Error location btw is a debugging tool I use when I want to know which statement failed..I updated them..

Try this attachment|||It was just a script file that was being run with 4 other script files to process this data. The one you looked at was the first one that got ran. There are four others. This is all processed into files that get loaded to a AS400. As stated earlier I decided to try and create a sp for each script file. Then run all 5 from a containter. This may not work as they are. But I really don't have the time to rewrite them. I wonder if it will hang now. I will try it out. Thanks a lot for your help. I will let know if I get it working.

Thanks
Tom|||Good Luck...You might want to consider breaking it up.

BUT Where did you get that code?

That's definitely part of my template and definetly my error handling...

My Blog Entry (http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx)

Now I gotta make sure it runs|||I was afraid of this but now it just keeps running and running and running. It might have something to do with some code trying to delete or change data that as yet to be committed from being inserted before. Would that make sense? If I move the Begin Tran down near the end it runs fine.

Tom|||Yes, I got the error handleing from your template. The rest is a mixture of what was there and what I normally put in. Perhaps I am not understanding question. :confused:|||Doesn't make sense.....if you didn't have any at all, it is implicitly 1 transaction anyway for that spid...and would rollback with an error that was severe enough...

Run the sproc with the BEGIN TRAN at the top...

And while it's just sitting there...run sp_who2 to see what's up...

You might want to start profiler as well (On a client)