2005 on 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

2005 on 2000

Author

Topic

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 11:11:06

this sql works on 2005. how do i get it to work on 2000?

create table #tmp_sfs2 (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
select @@version
insert into #tmp_sfs2 execute("DBCC showfilestats")

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-12-03 : 11:16:42

Usually, you start with the error message. Figure out what the error is telling, you, then devise a theory as to how to fix the problem. Test the theory, and if the theory is proven out, then you apply the fix.

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 11:20:00

i got this msg :

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DBCC showfilestats'.

is the insert with the results of an execute of dbcc compatible in 2000?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 11:28:27

how does resultset of DBCC showfilestats look in sql 2000? can you show sample?

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 11:46:43

the result set of dbcc showfilestats in 2000 is exactly like in 2005 :

1 1 16 16 P G:\appsdata\mssql_cs\dbfiles\MSSQL\DefaultData\d_dbdba002.mdf
3 2 1600 52 D001 G:\appsdata\mssql_cs\dbfiles\MSSQL\DefaultData\d_dbdba002_D001.ndf

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 12:08:52

It works in both. I donno what you are looking for. Use like this:

quote:
Originally posted by nandac

this sql works on 2005. how do i get it to work on 2000?

create table #tmp_sfs2 (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
select @@version
insert into #tmp_sfs2 execute('DBCC showfilestats')

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-12-03 : 13:07:01

Or set quoted_identifiers off.

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 13:10:31

god, dont tell me it is the quotes. but i can't use single quotes because my whole script looks like :

create table #tmp_sfs(
db_name varchar(255),
totalsize float,
sizeused float,
spaceleft float
)
EXEC sp_MSForeachdb 'USE ?
create table #tmp_sfs2 (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)

insert into #tmp_sfs2 execute("DBCC showfilestats")
insert into #tmp_sfs
select db_name(),convert(float, sum((totalextents) * 64.00)/1024),
convert(float, (sum(usedextents) * 64.00)/1024),
convert(float, ((sum(totalextents) * 64.00)/1024) - ((sum(usedextents) * 64.00)/1024))
from #tmp_sfs2'
select * from #tmp_sfs

it is a space usage report script which i need to execute in each database and insert into a temporary table to finally read it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:20:17

use two single quotes ('') instead of double quotes (")

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-12-03 : 13:55:09

Any particular reason you are not using the FILEPROPERTY function?


select name, fileproperty(name, 'SpaceUsed')/128 as "Size in MB"
from sysfiles

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 14:35:33

>Any particular reason you are not using the FILEPROPERTY function?

i need the (data) size of each database and the space which has been used up.

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 14:36:22

and i need them to work on both mssql 2000 and 2005.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 14:49:37

What is problem with this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115523

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 15:08:22

the problem is that i want specific information which i want to format and present as a report. a lot of unwanted information is in the output of sp_spaceused. so i can't use that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 15:14:34

Are you looking for this one?

http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2/Gathering-Space-Usage-Statistics.htm

nandac
Starting Member

27 Posts

Posted - 2008-12-03 : 15:25:40

>use two single quotes ('') instead of double quotes (")

this worked! thanks!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 16:01:59

Little formatting MVJ code:

Exec sp_MSforeachdb @command1 = ' use ?
Begin
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
end '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources