Help with script

CREATE TABLE #FreeSpace
(
Drive char(1),
MB_Free int
)

INSERT INTO #FreeSpace EXEC master..xp_fixeddrives

CREATE TABLE FreeSpaceData
(
interval datetime not null,
Drive char(1),
MB_Free int
)

INSERT into FreespaceData values (getdate())
INSERT into FreespaceData select * from #freespace

I am trying to insert a row of date, drive and diskspace into FREESPACEDATA table but there's a syntax error.

Any ideas ho w to get around this?

Try set getdate() as default value for column interval in table FreeSpaceData.

Thanks for the suggestion RMIAO. I have amended my code to the following:-

DROP default default_date
go
CREATE DEFAULT default_date AS getdate()
GO

DROP TABLE FreeSpaceData
GO
CREATE TABLE FreeSpaceData
(
Drive char(1),
MB_Free int
,interval datetime not null ,
)
select * from freespacedata

DROP TABLE #FreeSpace
GO
CREATE TABLE #FreeSpace
(
Drive char(1),
MB_Free int
)

INSERT INTO #FreeSpace EXEC master..xp_fixeddrives

EXEC sp_bindefault 'default_date', 'freespacedata.interval'

insert into freespacedata default values

/*************************************/
This will insert the current date into the interval column but how do I insert the 2 records in #FreeSpace table into table FreeSpaceData?

Current result
Drive MB_Free interval
----- ---------- --------
NULL NULL 2004-02-18

My desired result is:-

Drive MB_Free interval
----- ------- ------------
C 14534 2004-02-18
D 8547 2004-02-18

CREATE DEFAULT default_date AS getdate()
GO
CREATE TABLE FreeSpaceData(Drive char(1), MB_Free int,interval datetime not null )
Go
--truncate table FreeSpaceData
EXEC sp_bindefault 'default_date', 'freespacedata.interval'

DROP TABLE #FreeSpace
GO
CREATE TABLE #FreeSpace(Drive char(1), MB_Free int)
GO
INSERT INTO #FreeSpace EXEC master..xp_fixeddrives
go

insert into freespacedata (Drive,MB_Free) select * from #FreeSpace

select * from freespacedata

My take on this is very simple. Declare Deafult "Getdate()" on the Interval column once and then just use this one line statement to accomplish what you are looking for.

INSERT FreespaceData (DRIVE, MB_FREE) EXECUTE MASTER..xp_fixeddrives

select * from freespacedata

There is no need for the temp table #FreeSpace.

Good Luck

Thanks MAK; your solution worked perfectly!!!

skasarla, I tried the solution you propose before I post the problem. The problem was xp_fixeddrives return 2 values. As the disk space will change constantly, I put those values in a temp table.

I wanted a stored procedure which will report over time the disk usage of my application; that's why I need the date.

I appreciate your feedback but, If you read my suggestion carefully you will realize that using temp table is redundant. Once you have the default "getdate()" on the column "interval" then you can directly insert the values into the table freespacedata.

The difference between MAK's suggestion and mine is on the need for Temp Table. The choice is yours but the steps with temp table is unnecessary. By the way, the results are same with both approcahes.

Here is how table looks with my script.

select * from freespacedata

C 11566 2004-02-18 13:01:30.453
E 12749 2004-02-18 13:01:30.453
C 11567 2004-02-19 10:55:27.340
E 12749 2004-02-19 10:55:27.340
C 11567 2004-02-19 10:59:51.973
E 12356 2004-02-19 10:59:51.973

Good Luck

Thanks for your comments skasarla. I have amended my script as you suggested (without the temp table) and I can get it to work now.

The problem was originally I had this:-
INSERT INTO FreeSpaceData EXEC master..xp_fixeddrives
GO

instead of this:- (DOH!)
INSERT INTO FreeSpaceData (drive,mb_free) EXEC master..xp_fixeddrives
GO -- i.e the column names


/**************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'FreeSpaceData') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table FreeSpaceData
GO

CREATE TABLE FreeSpaceData (
Drive char (1) ,
MB_Free int NULL ,
interval datetime default getdate() not null
)
GO

INSERT INTO FreeSpaceData (drive,mb_free) EXEC master..xp_fixeddrives
GO

select * from freespacedata

/**************************************/

Many thanks to all of you for your help/suggestions. It's much appreciated.:cool:

If you are looking for Disk Monitoring, try this

http://www.databasejournal.com/featu...0894_3296731_2

Excellent! Many thanks.

MAK, that is a good article. We were caught up in his question about the query but for disk space minitoring your article should help.

Thanks.

:D