Error locating server/instance specified

I am trying to connect to the AdventureWorks database while using Visual Basic 2005 Express. I am currently using the SQL Server 2005 trial version. Here is the error message:

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)

I found some threads in the archives when I researched this problem but couldn't relate the answers directly to my situation. Prior to installing SQL Server 2005 Trial version, I used the SQL Server 2005 Express edition on this machine and did not have any problems connecting to databases. I removed the express edition prior to installing the trial version.

In the Visual Basic "Add Connection" dialog box, I leave the default radio button checked ("Use Windows Authentication") as I have always done.

Any help in getting this resolved will be appreciated.

Use the Surface Area Configuration Tool to see if the server is able to accept connections.

In addition when you installed did you give the instance a name or did you leave it as the default?

|||

Hi Euan:

I installed the program using the default settings (which seemed to work fine for the Express version that I had used previously).

Following your suggestion, I opened up the Surface Area Configuration Tool and set all the items to "automatic" thinking that would allow the maximum access to the databases. Since I am only using it on one machine for evaluation, I am not worried about security at this point. I just want to get it to work with Visual Basic.

When I tried to connect to AdventureWorks in Visual Basic 2005 Express, I got the same error message as before.

Any thoughts on how to proceed?

|||

Is the SQL Server on the same machine as the VB code or on a different machine?

Can you please go into the Windows Services Control GUI from Control Panel and check the list of installed services, please list all the ones that start with mssql and say what their service status is.

What are you using as a connection string to the database(this might be in the property of a control so search the project for server=)

|||

VB and SQL Server are on the same machine.

I am using XP Home Edition. Here is what I found under Administrative Tools\Services:

SQL Server (MSSQLSERVER)Started

SQL Server AgentStarted

SQL Server Analysis Services (MSSQLSERVER)Started

SQL Server FullText Search (MSSQLSERVER)Started

I am attempting to connect via the menu - "Tools\Connect to Database" which brings up the "Add Connection" Wizard. There is to place to enter a connection string.

If I add a control such as the DataGridView and try to link it to a data source, I end up back at the "Add Connection" Wizard without being able to enter a connection string.

|||Can you please try conecting via sqlcmd and the command line please?|||

Thanks for your help Euan:

As a total Newbie, I am not familiar with using sqlcmd or the command prompt.After some reading in the MSDN books online I did the following:

I clicked “Start”

I clicked “Run”

I typed in “sqlcmd” and clicked “OK”

I typed in “select @.@.servername go” and clicked “Enter”

The program returned “Office” (1 row affected)

I assume that I connected to SQL Server 2005 using sqlcmd since “Office” is the server name I connect to with SQL Server Management Studio.

|||So when you try and connect from VS what connection string are you using?|||

As stated previously, I have been attempting to connect via the menu - "Tools\Connect to Database" which brings up the "Add Connection" Wizard. There is to place to enter a connection string. I have used the "Add Connection" Wizard previously with SQL 2005 Express to connect to several databases without any problems.

I have also tried via Database Explorer and Data Sources which have both eventually taken me to the same "Add Connection" Wizard resulting in the same error message.

I just tried something different that may help resolve this issue.

I tried adding the AdventureWorks database via Solution Explorer, right clicking the project and then add existing resource. I browsed to and clicked the AdventureWorks db and got the following error message:

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251

I clicked OK to exit the message box and Solution Explorer then added AdventureWorks database to the project. But when I tried to connect as described above, I got the orignal error message about not locating the server.

Questions:

1. If I am using Visual Basic 2005 Express, do I have to have SQL Server Express 2005 installed to connect to an .mdf database?

2. If so, can SQL Server trial version and SQl Server Express 2005 be installed and run properly on the same machine?

3. Or is VB 2005E simply remembering that it had used SQL Server Express 2005 previously to connect to databases and is insisting on using it again instead of the SQL Server trial version?

|||The same exact thing happened with me. I am currently using visual basic 2005 express edition and SQL 2000. Everything seems to be working fine, but everytime i try to open a database using vb, it takes me to the exact same screen (where it asks me for the file path) and whether i want it to be a windows authentication or sql server. I tried both, and they give me the same error as well. Please let me know if someone knows how to solve this, my email address is shahkevat@.hotmail.com.|||Kevat, you are using a feature called user instances that is only available in SQL Server Express 2005, not SQL 2000. either connect to the database as a service (ie not a file) or install SQL Express|||

Moonshadow,

There are 2 ways of conencting to SQL Server I seem to remember one is file and one is database, I am guessing you are choosing file? Can you try using database please?

|||I had a problem with not being able to view my local server thru' Visual Studio 2005. Per the information on this thread, i saw the windows services under control panel -> Administrative tools on my windows XP professional box and turned on the service called "SQL Server Browser" which broadcasts the service from ur machine. (If i understand it correctly). After, that it was smooth sailing and i was able to add a new connection to my Visual Studio 2005 professional edition. I am using SQL Server Express Edition. I hope this helps. By default, the mentioned service was not turned on. I have now set it to automatic On everytime my machine boots up..Good luck..Thank u all for discussing ur problem and sharing the valuable information.|||

Euan: I have tried connecting every way that I can find, including:

1. Via the menu - "Tools\Connect to Database" which brings up the "Add Connection" Wizard.

2. Via Database Explorer, right click on Data Connections, and select "Add Connection"

3. Via Solution Explorer, right click on the project, and select "Add existing item" and go to the folder with the database file.

All these eventually end up with the same error message cited above.

If there is another way of connecting direclty to the database (i.e., without a file), please tell me the steps and I will be happy to try it.

I still am wondering about two of the questions I asked above:

1. If I am using Visual Basic 2005 Express, do I have to have SQL Server Express 2005 installed to connect to an .mdf database? I have two other machines with VB Express and SQL Server Express. No problems with either one and no problems with this machine when it had both express versions installed. It is only since I upgraded to the full SQL Server 2005 trial version that I am having problems with this machine when using VB Express. It seems that I have run into this compatibility question before with regard to user instances, etc.

2. If so, can SQL Server trial version and SQl Server Express 2005 be installed and run properly on the same machine? Then maybe VB Express could connect via SQL Server Express.

I have ordered the full version trial of Visual Studio (which I hope contains VB 2005) and I am hoping that it will be more compatible with the trial version of SQL Server.

Thanks for your patience in solving this.

|||

1/ Yes you need SQLExpress to connect to .mdfs, absed on your answer above where you list the services which are running, you have a default instance of regular sql server not of express right now so I suspect the file open thing is not going to work, you need to connect to it as a regular database, I don't have a copy of VS Express installed but there should be an option to connect via a database file(might just be called file) or database, you do not want the file option. You should be prompted for a server name in the correct connection type, just enter "."

2/ They can indeed be installed on the same machine but the trial version should just work for you.