Results 1 to 12 of 12

Thread: Help with script

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Question 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?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try set getdate() as default value for column interval in table FreeSpaceData.

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    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

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  5. #5
    Join Date
    Feb 2004
    Location
    Massachusetts
    Posts
    32
    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

  6. #6
    Join Date
    Feb 2004
    Posts
    6
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Massachusetts
    Posts
    32
    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

  8. #8
    Join Date
    Feb 2004
    Posts
    6
    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.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    If you are looking for Disk Monitoring, try this

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

  10. #10
    Join Date
    Feb 2004
    Posts
    6
    Excellent! Many thanks.

  11. #11
    Join Date
    Feb 2004
    Location
    Massachusetts
    Posts
    32
    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.

  12. #12
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Thanks.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •