DBCC results into tables

Question

DBCC results into tables

David Paskiet

SSCrazy

Points: 2949
More actions
May 11, 2005 at 9:07 am

#88398

A show stopper for me here.
If you run the script below, it will cause an error. However, if you run the select portion after the error, it will have accomplished the desired result. Unfortunately, the table has to be manually dropped after the proc is run. Any ideas why????? If you add the GO statement in between each line it works. However, that blows up the rest of the script that this piece is embedded in.
*********************************************
CREATE TABLE #db_file_information( fileid integer, theFileGroup integer, Total_Extents integer, Used_Extents integer, db varchar(30),file_Path_name )
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- add two columns to the temp table
alter table #db_file_information add dude as ((Total_Extents-Used_Extents)/(Total_extents*1.0))
select * from #db_file_information
drop table #db_file_information
************************************************
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

What is the error? It could be that SQL Server is trying to resolve the temp table in the query evaluation phase (before the create table actually executes) and since it doesn't exist, it it erroring out.

Try creating a 'real' temp table (something like TMP_db_file_information) and using that instead. You can always drop it at the end. Or you could try table variables (I haven't used table variables too often, so I don't have any advice regarding them).

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 2

Also, when I tried to execute the script, I got:

Server: Msg 173, Level 15, State 1, Line 1

The definition for column 'file_Path_name' must include a data type.

Try something like:

CREATE TABLE #db_file_information( fileid integer, theFileGroup integer, Total_Extents integer, Used_Extents integer, db varchar(30),file_Path_name varchar(200))

I finally tried actually running the whole thing (should have done that the first time around). Try the following:

CREATE TABLE #db_file_information( fileid integer, theFileGroup integer, Total_Extents integer,

Used_Extents integer, db varchar(30),file_Path_name varchar(200))

GO

-- Get the size of the datafiles

insert into #db_file_information exec('DBCC showfilestats')

GO

-- add two columns to the temp table

alter table #db_file_information add dude as ((Total_Extents-Used_Extents)/(Total_extents*1.0))

GO

select * from #db_file_information

GO

drop table #db_file_information

Go

David Paskiet SSCrazy Points: 2949 More actions · Answer 3

Sorry, I cut out the data type, it shold be varchar(300). Did I mention I am using SLQ Server 2000?

The actual error is:

Server: Msg 213, Level 16, State 5, Line 13

Insert Error: Column name or number of supplied values does not match table definition.

This error is what happens when the alter statement executes I assume. I am wondering if it is altering the table before the first statement does. If this is the case, how can it be fixed?!?

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

David Paskiet SSCrazy Points: 2949 More actions · Answer 4

Thanks Chris but as I inferred, using the GO keyword is not an option. The rest of this script is dependent upon variables and results as it progresses.

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 5

David,

I guess it is "Alter table" that caused you pain.

Try the following:

CREATE TABLE #db_file_information(

fileid integer

, theFileGroup integer

, Total_Extents integer

, Used_Extents integer

, db varchar(30)

, file_Path_name varchar(300)

, dude as (Total_Extents-Used_Extents)/(Total_extents*1.0))

-- Get the size of the datafiles

insert into #db_file_information ( fileid

, theFileGroup

, Total_Extents

, Used_Extents

, db

, file_Path_name )

exec('DBCC showfilestats')

-- add two columns to the temp table

select * from #db_file_information

drop table #db_file_information

HTH,

Jeffrey

David Paskiet SSCrazy Points: 2949 More actions · Answer 6

YOu are absolutely correct. The problem happens when I try to run the alter in the script. I am guessing that it has something to do with the order in which the script tries to execute.

I worked around the problem like this:

-- Get the size of the datafiles and place them into the table created in teh original post.

insert into #db_file_information exec('DBCC showfilestats')

-- place the data into another table that can be modified with the correct fields and values

select *,cast(((Total_Extents-Used_Extents)/(Total_extents*1.0))*100 as decimal(15,2)) as percent_free

into #db_file_information_final

from #db_file_information

BY just selecting one table into another, I managed the result set I wanted. THis is not the resolution I was hoping to use but for now it works. I still would like to make the original script work. So please keep sending me any ideas you might have.

Thanx................David

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 7

David, to solve ur problem, see below

CREATE TABLE #db_file_information(

fileid integer

, theFileGroup integer

, Total_Extents integer

, Used_Extents integer

, db varchar(30)

, file_Path_name varchar(300) )

-- Get the size of the datafiles

insert into #db_file_information

( fileid

, theFileGroup

, Total_Extents

, Used_Extents

, db

, file_Path_name )

exec('DBCC showfilestats')

-- add two columns to the temp table

alter table #db_file_information add dude as ((Total_Extents-Used_Extents)/(Total_extents*1.0))

select * from #db_file_information

drop table #db_file_information

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 8

Along the same idea as previous posts...

Declare @DB VarChar(256)

Set @DB='TempDB'

If Object_Id('TempDB.dbo.#Tmp') is not Null Drop Table #Tmp

Create Table #Tmp

(

FId Int,[FGroup] Int,TotalExtents Int,

UsedExtents Int,FName nVarChar(2000),FPath nVarChar(2000)

)

Insert #Tmp (FId,FGroup,TotalExtents,UsedExtents,FName,FPath)

Exec('Use '+@DB+' DBCC ShowFileStats')

Alter Table #Tmp

Add ExtentsUsedPct as UsedExtents*100.0/TotalExtents

Select * from #Tmp

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 9

Peter, if you use Exec('Use '+@DB+' DBCC ShowFileStats'), DBCC ShowFileStats will only show stats for TempDB instead of the current database, which may not be what David wants.

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 10

Hello Jeff,

I do this intentionally so I can programatically set the Database when I run the script. If this exact piece of code is used he should set @DB to the database in question.

I pretty much hate to hard-code things that could reasonably be varied

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.