Can't See Database In SQL 2005 MSE

Discussion:

(too old to reply)

Neil

2008-12-29 11:14:15 UTC

I just upgraded from SQL 7 to SQL 2005 (Express) on my development machine,
and am having some trouble seeing my database. The database is functioning,
and I'm able to access the data through my front end (MS Access). Also, if I
open my copy of EM from SQL 7, I can see the database listed there, and can
open tables and so forth. But when I open Management Studio Express, all I
see listed are System Databases.

When I installed 2005 I selected the option to allow multiple instances of
SQL Server (thus, my server is "D10\SQLEXPRESS" instead of just the machine
name "D10"). I really don't need that, and I should have just gone with the
default instance. I'm wondering if that has anything to do with not being
able to see my database.

So my questions are:

1) How can see my database in MSE?

2) Is there any harm to using EM from SQL 7?

3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing named
instances of the server)?

Thanks so much!

Neil

Andrea Montanari

2008-12-29 11:43:29 UTC

Permalink

hi Neil,

Post by Neil
...
1) How can see my database in MSE?

I can think you did not upgrade your SQL Server 7.0 instance to SQLExpress
2005.. you just installed a named instance of it.. thus, your SQL Server 7.0
database still is on it.. you can back it up and restore it on the
SQLExpress instance..

Post by Neil
2) Is there any harm to using EM from SQL 7?

EM (both 7.0 and 2000) can not handle and can not connect to SQL Server
2005..

Post by Neil
3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing
named instances of the server)?

to see wether you are entitled to just upgrade your SQL Server 7.0 instance
to a SQLExpress 2005 one you have to check the upgrade matrix as proposed in
http://msdn.microsoft.com/en-us/library/ms143393(SQL.90).aspx ..
but you can (if you like) for sure uninstall SQL Server 7.0 and install a
fresh default instance of SQLExpress 2005, removing then the named instance
you already installed..

Post by Neil
When I installed 2005 I selected the option to allow multiple
instances of SQL Server (thus, my server is "D10\SQLEXPRESS" instead
of just the machine name "D10"). I really don't need that, and I
should have just gone with the default instance

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

Neil

2008-12-29 15:23:39 UTC

Permalink

Post by Andrea Montanari
hi Neil,

Post by Neil
...
1) How can see my database in MSE?

I can think you did not upgrade your SQL Server 7.0 instance to SQLExpress
2005.. you just installed a named instance of it.. thus, your SQL Server
7.0 database still is on it.. you can back it up and restore it on the
SQLExpress instance..

Post by Neil
2) Is there any harm to using EM from SQL 7?

EM (both 7.0 and 2000) can not handle and can not connect to SQL Server
2005..

Post by Neil
3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing
named instances of the server)?

to see wether you are entitled to just upgrade your SQL Server 7.0
instance to a SQLExpress 2005 one you have to check the upgrade matrix as
proposed in http://msdn.microsoft.com/en-us/library/ms143393(SQL.90).aspx
..
but you can (if you like) for sure uninstall SQL Server 7.0 and install a
fresh default instance of SQLExpress 2005, removing then the named
instance you already installed..

it's not in these terms.. you do not have to install the "multipli
instances option" as there is not such an option.. up to SQL Server 2000
you can, if you like, install many of them (usually up to 16 but this
limit depends on the edition and, of course, on your machine power).. so
you just installed a named instance and there's no problem with it..
regards and happy new year
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

Thanks, Andrea. I was a little unclear in my last message. When I said SQL
7, I meant SQL 7 MSDE. So the upgrade is to the same version.

I'm a little unclear of something. Are you saying that I currently have two
versions of SQL Server running -- my original SQL 7 MSDE and the new SQL
2005 Express, which is why I can see the database using SQL 7 EM, but not
SQL 2005 Express?

And, if so, then if I back up the database in SQL 7 and restore it in SQL
2005, then would I have two instances of the database running -- one in 7
and one in 2005?

I must be missing something here.

Thanks,

Neil

Neil

2008-12-29 15:44:02 UTC

Permalink

Post by Andrea Montanari
hi Neil,

Post by Neil
...
1) How can see my database in MSE?

I can think you did not upgrade your SQL Server 7.0 instance to SQLExpress
2005.. you just installed a named instance of it.. thus, your SQL Server
7.0 database still is on it.. you can back it up and restore it on the
SQLExpress instance..

Post by Neil
2) Is there any harm to using EM from SQL 7?

EM (both 7.0 and 2000) can not handle and can not connect to SQL Server
2005..

Post by Neil
3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing
named instances of the server)?

to see wether you are entitled to just upgrade your SQL Server 7.0
instance to a SQLExpress 2005 one you have to check the upgrade matrix as
proposed in http://msdn.microsoft.com/en-us/library/ms143393(SQL.90).aspx
..
but you can (if you like) for sure uninstall SQL Server 7.0 and install a
fresh default instance of SQLExpress 2005, removing then the named
instance you already installed..

OK, never mind the previous message. I think I got it. Because I did a named
instance of 2005, it didn't upgrade 7; just added another instance. And my
db was still running in 7. It can't run in both versions of SQL
simultaneously; but it can run in one or the other.

I attached the MDF to SQL 2005, and it's in there now; but SQL7 EM can't
access it. So that's good.

However, because my DSN is set to look for the data in D10, not
D10/SQLExpress, it fails. So I can either change the DSN, or remove the
named instance. So if I reinstall SQL 2005 with the default instance, it
should upgrade SQL 7, and I should be set, no?

Thanks,

Neil

Ekrem Önsoy

2008-12-29 16:15:26 UTC

Permalink

Why don't you just uninstall your old 7.0 instance and install a new SQL
Server 2005 Express Edition instance as a Default Instance and then detach
your database from your new 2005 Named instance and attach it to your new
2005 Default Instance?

Don't forget to configure TCP/IP or Named Pipes protocols so that your
network users is able to connect to your new SQL Server 2005 instance if you
have any.
--
Ekrem Önsoy

Post by Neil

Post by Andrea Montanari
hi Neil,

Post by Neil
...
1) How can see my database in MSE?

I can think you did not upgrade your SQL Server 7.0 instance to
SQLExpress 2005.. you just installed a named instance of it.. thus, your
SQL Server 7.0 database still is on it.. you can back it up and restore
it on the SQLExpress instance..

Post by Neil
2) Is there any harm to using EM from SQL 7?

EM (both 7.0 and 2000) can not handle and can not connect to SQL Server
2005..

Post by Neil
3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing
named instances of the server)?

to see wether you are entitled to just upgrade your SQL Server 7.0
instance to a SQLExpress 2005 one you have to check the upgrade matrix as
proposed in http://msdn.microsoft.com/en-us/library/ms143393(SQL.90).aspx
..
but you can (if you like) for sure uninstall SQL Server 7.0 and install a
fresh default instance of SQLExpress 2005, removing then the named
instance you already installed..

OK, never mind the previous message. I think I got it. Because I did a
named instance of 2005, it didn't upgrade 7; just added another instance.
And my db was still running in 7. It can't run in both versions of SQL
simultaneously; but it can run in one or the other.
I attached the MDF to SQL 2005, and it's in there now; but SQL7 EM can't
access it. So that's good.
However, because my DSN is set to look for the data in D10, not
D10/SQLExpress, it fails. So I can either change the DSN, or remove the
named instance. So if I reinstall SQL 2005 with the default instance, it
should upgrade SQL 7, and I should be set, no?
Thanks,
Neil

Neil

2009-01-01 16:52:07 UTC

Permalink

Yeah, I ended up having to uninstall SQL 7. The install program wouldn't
upgrade SQL 7 to a default instance, saying everything's already installed.
Once I uninstalled SQL 7, I was able to install the default instance.

Post by Ekrem Ãnsoy
Why don't you just uninstall your old 7.0 instance and install a new SQL
Server 2005 Express Edition instance as a Default Instance and then detach
your database from your new 2005 Named instance and attach it to your new
2005 Default Instance?
Don't forget to configure TCP/IP or Named Pipes protocols so that your
network users is able to connect to your new SQL Server 2005 instance if
you have any.
--
Ekrem Önsoy

Post by Neil

Post by Andrea Montanari
hi Neil,

Post by Neil
...
1) How can see my database in MSE?

I can think you did not upgrade your SQL Server 7.0 instance to
SQLExpress 2005.. you just installed a named instance of it.. thus, your
SQL Server 7.0 database still is on it.. you can back it up and restore
it on the SQLExpress instance..

Post by Neil
2) Is there any harm to using EM from SQL 7?

EM (both 7.0 and 2000) can not handle and can not connect to SQL Server
2005..

Post by Neil
3) Can I restore my setup to just use the default instance, instead of
allowing named instances (or is there a real advantage to allowing
named instances of the server)?

to see wether you are entitled to just upgrade your SQL Server 7.0
instance to a SQLExpress 2005 one you have to check the upgrade matrix
as proposed in
http://msdn.microsoft.com/en-us/library/ms143393(SQL.90).aspx ..
but you can (if you like) for sure uninstall SQL Server 7.0 and install
a fresh default instance of SQLExpress 2005, removing then the named
instance you already installed..

OK, never mind the previous message. I think I got it. Because I did a
named instance of 2005, it didn't upgrade 7; just added another instance.
And my db was still running in 7. It can't run in both versions of SQL
simultaneously; but it can run in one or the other.
I attached the MDF to SQL 2005, and it's in there now; but SQL7 EM can't
access it. So that's good.
However, because my DSN is set to look for the data in D10, not
D10/SQLExpress, it fails. So I can either change the DSN, or remove the
named instance. So if I reinstall SQL 2005 with the default instance, it
should upgrade SQL 7, and I should be set, no?
Thanks,
Neil

Fabio M. ROSSETTI

2009-01-01 10:25:00 UTC

Permalink

Post by Neil
1) How can see my database in MSE?

You can't.. If I remember correctly Query Analyzer still works, but no
MSE. Install Sql Server Management Studio Express (free download,
requires .NET 2.0), it roughly comprises the basic functionality of
MSE+QA. Also, use the Server Surface Area Configuration tool to enable
local AND remote connection via tcp/ip, if necessary.