How to manage all the SqlNK versions at the same time?

Hi everyone,

Nowadays, we've got four sql versions running around: 6.5, 7.0, 2000 and 2005.

If you try attach from Enterprise Manager 2005 servers it doesn't allow you because of 2005 uses SMO instead of DMO.

If you try attach from Management Studio 6.5 or 7.0 servers it doesn't allow you too.

We'd like to have from the same place a tool for all of them. Is it possible?

Thanks in advance,

I have client tools installed for SQL 6.5, 2000 & 2005 in order to manage our environment that is a mixture of SQL 6.5 to 2005 versions, I have no problem in having 3 of them and using at same time.

Could you please explain what you mean by 'if you try to attach....'.

|||

Hi Staya,

I meant, when you do this action: "New Sql Server Registration" from your client.

Yeah, I know, I can open three clients in my own workstation without problems.

I'm talking about that you can't attach from your Management Studio (2005) 7.0 or 6.5 clients.

Beyond of this, from Enterprise Manager (2000) you can attach any Sql Server 2005 because of sql2k uses DMO library instead of SMO (which is native for 2005).

Ok, you could do it from Query Analyzer.

|||I can connect to any of SQL 2k instances at my end using SSMS and check what is the service pack of those SQL instances that are in version 2000. Even the query analyzer in SQL 2005 should be able to connect to SQL 2000 instnaces.|||

Yeah, don't worry.

Idea was that we might have available an unique tool for see all of them.

My idea is maybe something crooked.

|||I believe SSMS is good one to go, but due to the limitations on backward compatibility you might not get what you want. So in this case you need to have 2 or 3 versions of SQL tools to continue the work. I'm sure there is a opportunity for a third party company to develop such tool.

HOW TO LOGIN IN TO SQL EXPRESS AND HOW TO ATTACH DATABASE PLZZ ITS URGENT

Hi

i am developing an installer for our produce where there is a need to install sqlexpress and then i have to attach the database to that but i am unable to understand how to do that , i any one can help in this issue plss

To install SQL Express you need to include the installer and then pass command line parameters to it. This link will help http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp

Now to attach a database, you need to create the database, then detach it, now you have an .mdf and an .ldf file. You need to include that in the installer, then once SQl Express is installed on the customer machine you need to re-attach it, you can do this by using the sp_detach, called through sqlcmd on or a sqlclient sqlcommand object.

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris