August 3, 2012 at 1:20 pm
Preface: Using MS Access 2010 DSN-Less to SQL Server table. SQL 2008r2 RTM on Virtual Server with only a C:\ drive. Log Free disk space to a single DB, single table. No-email notice.
There are some great examples on this site of how to get several drives and more. Just can't seem to get that code to work.
A SQL Server on a Virtual Machine only has a C:\ drive.
Running EXEC master..xp_fixeddrives returns an accurate GB Free for my SQL Server.
Just need a table in the production DB with the value of Exec master..xp_fixeddrives, the date-time appended.
Can put this into a job to run task daily.
1. insert free disk space, date time
DB Name = ProductionDB
Table Name = dbo.FreeHardDiskSpace
From an MSAccess 2010 point of view, a PassThrough Query can send T-SQL. However, many articles indicate that a different number for free space can be returned for depending on who is running it (e.g. sa vs local db user).
With a simple table, a trend chart can be created.
The primary use is to display a read-only free space on the users splash screen when they open the application.
I can start on this. However the experts here always present a better mousetrap and hints on how to avoid the pitfalls.
Best regards.
August 6, 2012 at 3:40 pm
Searched for a solution Monday. My post is all over the other web sites, with out an answer.
Here is my solution that works. Would not mind someone telling me there was a much easier, or better way to accomplish this.
Created a new Table :Sys_Info in my production Database.
ID_Sys_Info is an auto-increment, FreeDisk is int, Drive (2 char), and Date_time of datetime2 with (getdate()) as Default Value.
Created a new Job - Daily - Database Master, run as sa with command:
INSERT INTO [MyProductionDB].[dbo].[Sys_Info]
([Drive]
,[FreeDisk]
)
EXEC master.sys.xp_fixeddrives
GO
My MS Access 2010 has a local table with the list of SQL Server tables. It creates a DSN-Less connection to the tables - e.g. Sys_Info
There is a Spash Screen on the Access application - with a hidden link to a table named PERSIST - one record.
In Access, a new function will check the top ID_sys_info - and return how many Gigbytes are free. If the value is under 200 MB, a yellow warning will appear, under 100 MB a red warning with additional suggestions will apper for any user that logs on.
The purpose of keeping the data in a table is for the maintenance database charting.
Another Newbie's question
On the [Sys_Info] table, it would be nice to capturer @@Version and some other information in other columns.
The @@ functions could not be added into the Default values for a filed. One suggestion was to create a Trigger to update the field once the record is created.
Is there a way to create a default value for a field from a built in function with out creating a trigger?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply