Exceptionally dumb SQL Server question
-
Our many applications have always run with SQL Server 2000, which I have installed on my system, referencing a few databases. Now I want to test if the proper working of our programs will be affected by using SQL Server 2005 instead. I haven't yet installed SQL Server 2005. 1) When I install SQL Server 2005 (or for that matter, if I have multiple instances of SQL Server 2000 installed for some silly reason), I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached? 2) If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database? I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server: Provider=SQLOLEDB;Data Source=;Persist Security Info=True;User ID=;Initial Catalog=;Password= Thanks for the help. I'd rather know this before I install SQL Server 2005. -- modified at 10:55 Wednesday 5th April, 2006 Hmmm. Those smiley's you see are code project misinterpreting my angle brackets. I don't actually put smileys in my connection string :laugh:
-
Our many applications have always run with SQL Server 2000, which I have installed on my system, referencing a few databases. Now I want to test if the proper working of our programs will be affected by using SQL Server 2005 instead. I haven't yet installed SQL Server 2005. 1) When I install SQL Server 2005 (or for that matter, if I have multiple instances of SQL Server 2000 installed for some silly reason), I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached? 2) If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database? I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server: Provider=SQLOLEDB;Data Source=;Persist Security Info=True;User ID=;Initial Catalog=;Password= Thanks for the help. I'd rather know this before I install SQL Server 2005. -- modified at 10:55 Wednesday 5th April, 2006 Hmmm. Those smiley's you see are code project misinterpreting my angle brackets. I don't actually put smileys in my connection string :laugh:
BambooMoon wrote:
I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached?
Yes. You will need to upgrade a SQL Server 2000 database to work with SQL Server 2005. However, it is possible to manage (mostly) a SQL Server 2000 instance from the SQL Server 2005 client tools. The back end database is still SQL Server 2000.
BambooMoon wrote:
If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database?
The connection string identifies the instance.
BambooMoon wrote:
I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server
Then you have likely always connected to the default instance (it has no name)
Data Source=<machine_name>for the default instance ORData Source=<machine_name>\<instance_name>for specificly named instance.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
BambooMoon wrote:
I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached?
Yes. You will need to upgrade a SQL Server 2000 database to work with SQL Server 2005. However, it is possible to manage (mostly) a SQL Server 2000 instance from the SQL Server 2005 client tools. The back end database is still SQL Server 2000.
BambooMoon wrote:
If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database?
The connection string identifies the instance.
BambooMoon wrote:
I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server
Then you have likely always connected to the default instance (it has no name)
Data Source=<machine_name>for the default instance ORData Source=<machine_name>\<instance_name>for specificly named instance.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Thank you, Colin, that is exactly what I needed to know. Since I wanted a default instance of SQL Server 2005, I tried installing it and specifying that. It claimed that it would update my existing software (SQL Server 2000) and become the default instance, but it lied. Instead, it failed to install at all (or to rollback the installation). So after then uninstalling all versions of SQL Server from my machine, I could finally install SQL Server 2005 properly and it works without problems ...... except that I find that other machines, running SQL Server 2000, cannot go to Enterprise Manager and create a new server registration that connects them to my machine (even though I can create new server registrations in my SQL Server 2005 that connect me to their machines). Is it not possible for SQL Server 2000 installations to connect through Enterprise Manager to SQL Server 2005 installations? Or perhaps it is some security issue. The computer I'm using has had mysterious security problems since I got it. For example, mine is the only computer in the office to which nobody can map a shared drive, and nobody understands why not. -- modified at 14:27 Wednesday 5th April, 2006
-
Thank you, Colin, that is exactly what I needed to know. Since I wanted a default instance of SQL Server 2005, I tried installing it and specifying that. It claimed that it would update my existing software (SQL Server 2000) and become the default instance, but it lied. Instead, it failed to install at all (or to rollback the installation). So after then uninstalling all versions of SQL Server from my machine, I could finally install SQL Server 2005 properly and it works without problems ...... except that I find that other machines, running SQL Server 2000, cannot go to Enterprise Manager and create a new server registration that connects them to my machine (even though I can create new server registrations in my SQL Server 2005 that connect me to their machines). Is it not possible for SQL Server 2000 installations to connect through Enterprise Manager to SQL Server 2005 installations? Or perhaps it is some security issue. The computer I'm using has had mysterious security problems since I got it. For example, mine is the only computer in the office to which nobody can map a shared drive, and nobody understands why not. -- modified at 14:27 Wednesday 5th April, 2006
BambooMoon wrote:
Is it not possible for SQL Server 2000 installations to connect through Enterprise Manager to SQL Server 2005 installations?
Enterprise manager works through an intimate knowledge of how SQL Server works internally. It cannot make any assumptions about how SQL Server 2005 works and will therefore not connect to it.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog