Is it possible to query a server's DBCC SHOWFILESTATS from one server to another?

I am trying to write a single query that will go out a collect all the
DBCC SHOWFILESTATS. The trick is that the query has to attach to the
remote server, then run the DBCC SHOWFILESTATS, and pull that
information back.
e.g. use servername2.master DBCC SHOWFILESTATS
This obviouly does not work even with the linked server option. Is
there another way to do this? I know you can use a select statement and
a linked server connection to query remote databases.you could create a procedure like this then call the proc on the server
create proc prShowFileStats
as
set nocount on
create table #SHOWFILESTATS (FileID int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name varchar(100),
FileName varchar(100))
insert into #SHOWFILESTATS
exec ('DBCC SHOWFILESTATS')
select * from #SHOWFILESTATS
set nocount off
http://sqlservercode.blogspot.com/

Is it possible to move a database from 2005 to 2000?

Hello,

I have a database in 2005 that I'd like to copy and attach in 2000. Is this
possible? if so, how do I do it? I've tried doing a number of things from
just trying to attach to the db file to trying to export and import via
access and I can't seem to get anything to work.

Any help would be greatly appreciated.

Thanks!
RickRico (me@.you.com) writes:

Quote:

Originally Posted by

I have a database in 2005 that I'd like to copy and attach in 2000. Is
this possible? if so, how do I do it? I've tried doing a number of
things from just trying to attach to the db file to trying to export and
import via access and I can't seem to get anything to work.

You cannot attach a database from SQL 2005 on SQL 2000. When the developers
completed SQL 2000, they did not what know would be in the product five
years later.

To copy the schema, right-click the database in Mgmt Studio and select
Tasks->Generate Script. There is a setting to force SQL 2000 syntax,
you need to select that one. (I seem to recall that there was a bug
with this in SQL 2000 RTM, so make sure that you have SP1.)

If memory serves, create of foreign keys is at the end. In any case,
split the script so that FKs are in a script of their own. Run only
the script without the FKs. Beware that copying the schema can only be
completed successfully, if there is on use of new features in SQL 2005 in
the database.

To copy the data run this:

SELECT 'BCP db..' + name + ' out ' + name + '.bcp -T -S server2005 -n'
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy and paste result, run from a command-line window. Then change
the above to:

SELECT 'BCP db..' + name + ' in ' + name + '.bcp -T -S server2000 -n ' +
case when ident_current(name) is not null then ' -E' else '' END
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy, paste and run again.

Finally rnn the script with the FK:s.

Note the BCP stuff assumes that you have everything in the dbo schema,
as is the only thing that makes sense on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Is it not possible to access sql 2005 database, without attaching it first in management s

Hi All ...

I am not facing a problem while connecting to sql server 2005 databse, when i attach that first, in management studio, then I can access that from localhost, its fine at this point. but when i detach that database from management studio i am facing the follwing error while accessing it from localhost i.e.

System.Data.SqlClient.SqlException: Unable to open the physical file "D:\database\Hunzian.mdf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file D:\database\Hunzian.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The connection string to access that database is

undefined=@."Data

I will be happy to have your replies .

I am confused over " detach that database from management studio " - are just closing the connection for SQL Management Studio or are you detaching it.

Where is the database located w.r.t. to the localhost you refer to - is it on one of its drives or on a network share?

|||

Hi ... thanks for the reply ...

I am actually detaching the database from management studio, that database is actually situated at one of my local drive D:\Database\database.mdf. When I detach that database I am facing the above mentioned problem.

Regards

|||

Hi,

Is this error message thrown when you click detach? I think it might be generated when you connect to this database. Please check if you have granted proper permission on that folder to ASPNET account or Network Service account.

|||

Thanks,

When i detach the database from Management Studio, acctually i get this error while i try to connect to the database through localhost.

I am able to connect to the database when i attach database first from management studio then connect through localhost

What i want is:

I have detached the database from management studio, its fine uptill now, but i want to connect to that database from localhost without attaching it in management studio.

I hope you understood the scenario, the connection string works just fine while testing that in visual studio built in web server. I have already given permissions to ASPNET user.

Regards,