Goldstar Lee on Sun, 13 Jan 2013 17:39:02
I have been using Visual Studio 2010 quite sometime, but this is the first time I try to use the Microsoft SQL Server 2005.
My SQL server was installed as part of the Visual Studio 2010 package.
(1) From Windows 7, START -> All Programs, I cannot find the "Microsoft SQL Server 2005". However, from Install/Uninstall Programs, I can see "Microsoft SQL Server 2005" (missing "Express") is there. But it does NOT have the size and Version information. Immediately underneath it, though, there is an entry of "Microsoft SQL Server 2005 Compact Edition [ENU]", complete with the date and version.
My question is : is the Microsoft SQL Server 2005 installed PROPERLY on my computer or not ? Why the "Express" is omitted ?
(2) When I tried to updater the SQL server 2005, with the Service Pack 4 or 3, (login as Administrator and "run" the downloaded file from the Command prompt), it failed with a message that the product was already there, and I should select the "Upgrade" to proceed. But there is No "Upgrade" to select from.
My Question is: I am not sure whether the SQL Server is installed properly on my computer (Question 1 above), but, should I Uninstall it first to avoid the problem in Question 2 ? Do I need to uninstall/Install just the SQL Server 2005, or the ENTIRE Visual Studio 2010 ?
Thanks very much,
irusul on Mon, 14 Jan 2013 15:27:03
- Click Start, and then click Control Panel.
- Open Add or Remove Programs.
- Select Microsoft SQL Server 2005
Even if "Express" is omitted you may have installed SQL Server Express.
You may already have the Service Pack installed, so no additional installation is required.
- Go to Start > All Programs > Microsoft SQL Server 2005 > SQL Server Configuration Manager.
- Start the SQLEXPRESS service.
- Open SQL Server Management Studio > connect using the connection string (local)\SQLEXPRESS
- Run this query: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
- Open this page and check your version based on your first column: Microsoft SQL Server 2012, 2008R2, 2008, 2005, 2000 and 7.0 Builds
Samuel Lester - MSFT on Mon, 14 Jan 2013 15:31:28
Hi, the reason there is no entry in your start menu is because VS 2010 installs only the SQL Server engine with no associated tools to manage the engine. The engine alone does not have a start menu item because it runs as a set of services. You can install the free version of SSMS Express from here (SQLManagementStudio_x86_ENU.exe), then connect to your instance in order to manage it. You can also run the discovery report from here to determine what SQL Server components are on your machine.
To upgrade your instance of SQL Server 2005 to SP4, use the link here.
Keep in mind that SQL Server 2012 has been released and is currently the newest version of the product. You can install SQL Server 2012 Express Edition from here and use that instance in place of your 2005 instance. If you go this route, install the version titled "SQLEXPRADV_x86_ENU.exe" so you also get the client tools.
Sam Lester (MSFT)
Goldstar Lee on Mon, 14 Jan 2013 18:35:22
Thanks so much for your answer. That is very helpful.
For the moment, I will stick to SQL2005, until I can make it work. In line with your suggestion, I have installed SQL Server Management Studio Express. As I proceeded, I encountered a few problems:
I was installing the Pubs sample databse. When I tried to Attach and Add the .MDF file, I got SQL Server 916 error. Realizing that I had a PERMISSION issue, I tried to grant permissions to the Logins, but it failed with SQL Server Error 4613.
My questions are:
(1) To resolve the above Error 4613, I need to grant myself permissions first. How do I do that ?
(2) Under my Server's Properties's Permissions, I have 1 Login, "BUILTIN\users", and 1 role, "public", Supposedly I need to grant permissions only to the "BUILTIN/users", not the "public" ?
(3) Under my Server\Security\Logins, there are 2 logins: "BUILTIN/users" and "sa". Under each login's Properties, there is a "Securables". Do I need to "Add" to it ? If yes then how ?
Thanks so much for your time and sharing your experise,
Goldstar Lee on Mon, 14 Jan 2013 18:57:38
Thanks so much for your answer. Sorry that I missed seeing your answer earlier.
As I proceed further, I have a few more questions (please see my reply to Samuel). My thanks in advance if you could answer those questions.
Samuel Lester - MSFT on Mon, 14 Jan 2013 21:19:55
How are you connecting to the instance in SSMS? Using Windows authentication (ex: domain account) or SQL Authentication? When you install SQL Server, you have the option to enable the sa account and provide a password. Do you remember if you set that during install? If not, you can still enable it from SSMS after install. Once that is set up, you can connect to the instance in SSMS using the sa account and this should eliminate the permissions issue since the sa account has full privileges. If you still hit permissions issue when restoring/attaching a DB using sa account, it is likely a Windows file permissions issue where the DB files are read-only or you don't have permission to access a particular folder on disk, network, etc.
Sam Lester (MSFT)
Goldstar Lee on Tue, 15 Jan 2013 00:16:35
Right on. Yes, I can connect to the instance of SQL Server 2005 Express using SSMS, thanks much. ( I am a new comer, so I want to make sure that by SSMS you meant SQL Server Management Studio)
However, 2 questions ensue:
(1) How do I enable and set up "sa" password after my SQL Server is installed ?
(2) When connecting to the Microsoft SQL Server (2005 Express), it does not give me a choice to enter the "User name" (it is grayed out: it uses the DEFAULT user name, which is my computer user name, but I cannot change it).
How do I choose the "sa" account then ?
(only "Server Name", "Authentication" are NOT grayed out. "User name", "Password" and "Server type" are ALL grayed out)
Thanks again for your great help !
Samuel Lester - MSFT on Tue, 15 Jan 2013 04:07:03
Almost there! Here's a nice blog entry with good screenshots showing how to enable the sa account:
Once you enable the sa account, click on the Authentication drop-down in the connection dialog and select SQL Server Authentication. The user name will be "sa" (no quotes) and the password will be whatever you've created when you enabled the account. If you're still hitting issues attaching/installing the pubs DB, we can sort those out as well.
Sam Lester (MSFT)
Goldstar Lee on Tue, 15 Jan 2013 16:24:58
Almost there but NOT yet. Here comes another hurdle:
When I switched to SQL Server and Windows Authentication Mode (Step 2) and Click OK (Step 3), it gave "Microsoft SQL Server: Error 229).
I tried to beat about the bush by myself but found myself still in the woods.
( I tried, e.g., executing queries: Use master; go; GRANT VIEW ANY DEFINITION TO myLogin; it failed compaining about syntax error near my login, which is my computer user name)
Thanks for supporting me to go thru this unchartered water of mine,
Goldstar Lee on Wed, 16 Jan 2013 02:01:46
It works now. The Pubs database is now running.
It was a silly mistake. I was religiously following the instructions from a web site for installing the Pubs and Northwind databases. However, it does not include an instruction that I should login as administrator when invoking SSMS. When I did just that, everything worked fine.
Thank you for your great help. Despite that the problem was somewhere else, I learned a lot about the inner working of the SQL Server thru your correspondence.