Solved

SQL Server(SQLExpress) to something like SQL Server(SQL Server 2005) ?

Posted on 2006-07-10
15
347 Views
Last Modified: 2007-11-27
HI there!

When I try to use the Database Tuning Advisor from SQL Server 2005 I get the message unavailable for SQL Express and it seems to me that my (local) server is configured to SQLExpress even though I don´t remember configuring it at all (perhaps when I installed VStudio this was made by itself...) and even though I have installed the SQL Server 2005 Enterprise Edition.

Where should I make changes and where do I look for the proper connection string related to that, please?!

Thanks,
fskilnik.

Obs.: when I look at "Computer Management", node "SQL Server Configuration Manager" , node "SQL Server COnfiguration Services", it shows "SQLServer(SQLEXpress)" running... that´s why it seems to me that although SQL Server 2005 is installed, the Management Studio seems to related to the SQLExpress version. This is just a newbie guess.
0
Comment
Question by:fskilnik
  • 10
  • 5
15 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 17080736
Configuration Manager is showing the services available. Since you are not using Express why don't you stop the service and then see if that is still an issue? Did you install a local copy of SQL?
0
 

Author Comment

by:fskilnik
ID: 17082919

Hello ptjcb. Thanks for your reply.

>> Did you install a local copy of SQL?

I guess so!  Well, I see the Start > All Programs > Microsoft SQL Server 2005 > etc (here there are 7 sub-folders, Management Studio is only one of them) , therefore I have never suspected there was something missing during the installation... ?!

>> why don't you stop the service and then see if that is still an issue

Great idea (good test) !  I did it, but now I could not connect (as expected)... Then I simply deleted the  "ComputerName\SQLExpress" Database Engine presented at the "Registered Servers" (Management Studio) and tried to configure a new "Server Registration". I could not, please help me to do that (I left "Windows Authentication" only, I guess this was not the issue but I really don´t know!)

The error message when I tried to create the SQL Server Registration was exactly the following:

-----------------------------------------------------------------
Testing the registered server failed. Verify the server name, login credentials and database, and then click Test again.

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 does not allow remote connections

(provider: SQL Network Interfaces, error: 26_ Error locating server/Instance specified)(Microsoft SQL Server)
-----------------------------------------------------------------

Perhaps what could be a hint to you is the following: at "SQL Server Configuration Manager" I could stop the SQL Server (SQLExpress) Service but I COULD NOT start the second one, the "SQL Server Browser" Service, although it is mentioned there, with state "Stopped" and Start Mode "Manual".

Another (perhaps) hint:  at the "Registered Servers" (Management Studio) , Database Engine right-clicked and "Update Local Server Registration", I get EXACTLY the SQLEXpress previous connection that I deleted before!

Please keep helping me, ptjcb !

Thanks a lot,
fskilnik

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17083143
Ok...when you installed Visual Studio it decides to install an express whether you need it or not.

You have installed SQL 2005 which should have asked you to install a default or named instance (I think it is fourth or fifth form in the installation). That is what I meant by a local copy of SQL 2005. That is the instance that we want to use, not express.

If you open the Control Panel:Administrative Tools: Services - do you see a local instance and SQL express? Are they both started? If the local instance is not started then you will not be able to register it.

Windows authentication is fine as long as you do not have anyone logging onto the server who is not in Windows.

Browser is used to listen to ports where named instances listen. At a command line type "NET START SQLBROWSER"

What is the operating system? Do you have a firewall?

0
 

Author Comment

by:fskilnik
ID: 17083716

Hi, ptjcb !  Thanks again, let´s see:

>> You have installed SQL 2005 which should have asked you to install a default or named instance (I think it is fourth or fifth form in the installation).

I really don´t remember if I asked it...   :(

>> do you see a local instance and SQL express? Are they both started?

All the "related" Services with their configurations/states are the following:
 
01.  Server  --  Started  --  Automatic -- Local System
02.  SQL Server (SQLEXPRESS)  --   Started***  --  Automatic  -- Network Service
03.  SQL Server Active Directory Helper  --  <blank>  -- Disabled  -- Network Service
04.  SQL Server Browser  --  Started  --  Manual  --  Local System

*** The SQLEXPRESS was <blank>  because I stopped it before, then I tried (now) to restart it and then it appeared Started as expected (sure...) ...

>> Windows authentication is fine as long as you do not have anyone logging onto the server who is not in Windows.

Great. Not a problem, then.

>>  At a command line type "NET START SQLBROWSER"

I didn´t do it because in 04. above it´s "Started". If I should do this anyway, please let me know.

>> What is the operating system? Do you have a firewall?

Windows XP Profession and yes, AVG-Antivirus with Firewall (version 7.1)

Thanks for the help. Please continue!  :)    (I have already increased to 500 points and you may consider them yours, for sure!)


0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17084353
Ok...the 01 SQL Server is the one you want to use, not .02 express

It (the 01) is started. Good. Now you cannot see it when you look at registered servers? It should have your workstation name (whatever that is). If not, then you should be able to register it by using adding '.'  (without the quote marks, just the dot) or [local]. That is the one sql should treat as local, not express.

You are right - you do not need to restart the browser.

Be sure that ports 1433 are open in the firewall (that's what SQL expects).

From your original:
When I try to use the Database Tuning Advisor from SQL Server 2005 I get the message unavailable for SQL Express and it seems to me that my (local) server is configured to SQLExpress even though I don´t remember configuring it at all (perhaps when I installed VStudio this was made by itself...) and even though I have installed the SQL Server 2005 Enterprise Edition.

It seems as if you are trying to run the DTA on express, not your instance. After you register your instance (the instance that was installed with the Enterprise installation) I would stop the express. You do not need/use it.

tried to configure a new "Server Registration". I could not...error - SQL does not allow remote connections
SQL Server Surface Area Configuration  - Surface Area Configuration for Services and Connections. That should open a new window. Select remote connections and allow local and remote connections





0
 

Author Comment

by:fskilnik
ID: 17084969

You are great, ptjcb, but I still have bad news...

>> Now you cannot see it [SQLBrowser] when you look at registered servers?

No. Much more on this below.

>> It should have your workstation name (whatever that is).

From the "SQL Server 2005 Surface Area Configuration" , I have exactly the following nodes:

(-) <white "computer" picture>  SQLEXPRESS
INSIDE THIS "folder"      
 (-)  <yellow "file tank" picture>  Database Engine                  
               Service        <------   I put Startup type "Disabled" here              

     
               Remote Connections    <------  I put "Local and remote" and "TCP & Pipes"

(-) <yellow "file tank" with a "glass-increasing size-lens" picture>  SQL Server Browser
 Service   <----------  I put "Automatic"  here

What I guess is strange is the fact that this 2nd "folder"  (The SQL Server Browser" one has no "Remote Connections" for instance...


>> then you should be able to register it by using adding '.'  (without the quote marks, just the dot) or [local].

I tried both, no way!  :(  

The "New Server Registration" is with <blank> in the "Server Name"  (good) but when I try the . (dot) or [local] it gets an error message as you said at the last lines of your post...


>> Be sure that ports 1433 are open in the firewall (that's what SQL expects).

I disactivated the AVG firewall (the only one I use) , just to avoid any trouble related to that...

>> It seems as if you are trying to run the DTA on express, not your instance. After you register your instance (the instance that was installed with the Enterprise installation) I would stop the express. You do not need/use it.

Now I understand the problem, no doubt.  But strange thing, it seems that something forbids me to put the SQL Server to be the Regular/Default  Registration Server!!

When I see the dropdown list at the "Server Name" (that is initially blank) , I have exactly:

<blank>   Initial State
ComputerName\SQLEXPRESS          (awful!)  even when I disabled this option at the Surface Configuration!!!!
sqlserverName  (A external SQL Server instance, at my web service provider)  this one works ok (since ever)
<Browse for more>

When I click the last one, I go to the "Browse for Servers" Pop-up and in it, at the "Local Servers" folder, I see only "Database Engine" and, you guess... ONLY THE ComputerName\SQLEXPRESS little monster!!!!!!!!!!!

I hope I could "drive you" even deeper in my computer configurations.  Please don´t loose hope and give me more suggestions, ok?!

Thanks A LOT,
Fábio.

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17085212
Now you cannot see it [SQLBrowser] when you look at registered servers? No - you will never see SQL Browser in registered servers. It is a service to listen to other sql servers. What I meant was...ok, here is your list from earlier:

All the "related" Services with their configurations/states are the following:
 
01.  Server  --  Started  --  Automatic -- Local System
02.  SQL Server (SQLEXPRESS)  --   Started***  --  Automatic  -- Network Service
03.  SQL Server Active Directory Helper  --  <blank>  -- Disabled  -- Network Service
04.  SQL Server Browser  --  Started  --  Manual  --  Local System

01. Server (is this SQL Server? or some other kind of server?). The 01 server if it is SQL Server should be visible.

Forget about sql browser. It is running and doing its thing. Sorry for the confusion.

SQL Server 2005 Surface Area Configuration - a new tool from Microsoft. This is a link that describes it: http://msdn2.microsoft.com/en-us/library/ms173748.aspx

It depends on the 01. Server - what is that?


0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:fskilnik
ID: 17085313

>> It is a service to listen to other sql servers.

Ok! I am the one who must apologyse! I didn´t realize I was considering  01. and  04. as "equal" , surely they are not also because one is referred as Network SERVICE and the other Local SYSTEM...

>> 01. Server (is this SQL Server? or some other kind of server?).

Good question!  Its "description" (At the "Services"  >  "Services(Local)"  place says NOTHING on this matter!!  :(  
See below:
----
Supports file, print, and named-pipe sharing over the network for this computer. If this service is stopped, these functions will be unavailable. If this service is disabled, any services that explicitly depend on it will fail to start.
----

but clicking twice in it I could see some folders. In the "General" one, at "Path to executable", it is written exactly:

----
C:\WINDOWS\system32\svchost.exe -k netsvcs
----

>> The 01 server if it is SQL Server should be visible.

Then it´s not, unfortunately.  Terrible Thing!!!!

Could you guess what the Server is?  



0
 

Author Comment

by:fskilnik
ID: 17085329

OPS!

At exactly the same place, I found the description and "Path to executable" related to the other one:  

04. SQL Server Browser

They are

"Provides SQL Server connection information to client computers."

AND

"c:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

Isn´t this one we were looking for?
0
 

Author Comment

by:fskilnik
ID: 17085372

Well, I guess not, right?!  I mean, you said

"Forget about sql browser. It is running and doing its thing. Sorry for the confusion."

I apologyse for my confusion...

0
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 17085439
No problem - new tools. New ways to make them work.

Ok - our issue is that you have installed SQL Server 2005 Enterprise Ed on this XP box but you do not have a local instance (from everything that you have given me I would say it was skipped during installation, but that is just a guess).

How difficult would it be to re-install Enterprise?
0
 

Author Comment

by:fskilnik
ID: 17085495
No problem at all, ptjcb.

I am really sorry to take your time like that!  If you think this is the wiser thing to do, I will do it NOW  :)   !!

If so, please just tell me if the usual way is ok, I mean:

01. Desinstall by Control Panel > Add/Remove Programs

02. Re-start computer

03. Install SQL Server 2005 at the Default place

That´s it?
0
 

Author Comment

by:fskilnik
ID: 17086051
Well, I will wait your confirmation till tomorrow (please) , otherwise I will proceed as mentioned above.

Anyway, you more than deserve the points, of course.

Thank you very much, ptjcb, and sorry for all the trouble. I would never imagine the problem was installation-related, by the way, therefore your expertise advice on this matter was/is as good as any other thing you could have found.

See you in my next difficulties, I hope!  ;)

Best regards,
Fábio.

P.S.: I will come back for the feedback after I re-install the program.  :)
0
 

Author Comment

by:fskilnik
ID: 17090049
I´m back, ptjcb, just to mention I opened another EE question related to the unistall stuff.

The link is below, just in case you have time and/or is in the mood to help me in this other matter.

Thanks a lot, anyway.


http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21916489.html
0
 

Author Comment

by:fskilnik
ID: 17101952
Feedback (for ptjcb and interested readers) :  

ptjcb was right!  I re-installed SQL Server 2005, following ptjcb´s instructions (click last link above) and now I see (and use satisfactory) the Server connection, not (only) the SQLExpress one.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now