How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks on
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. Is
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.
This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.
|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:

> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>
>

How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks on
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. Is
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:
> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
> >I know you can use xp_fixeddrives to find the free space left in the disks
> >on
> > the sql server box.
> >
> > I trying to write a procedure where I can set a threshold in the DB for
> > each
> > disks and once we reach it send me an email. Now I have the Disk Size as
> > hard coded, the problem with this is that if we changes disks or use this
> > procedure on another box running SQL Server, it not going to be accurate.
> > Is
> > there anyway to find total disk size using some XP's like xp_fixeddrives.
> > Thanks.
>
>|||My team had the same problem, except that in SQL 2005 we didn't want to allow
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure on
a regular basis.
"yodarules" wrote:
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
> > sp_diskspace
> > http://www.sqldbatips.com/displaycode.asp?ID=4
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> > news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
> > >I know you can use xp_fixeddrives to find the free space left in the disks
> > >on
> > > the sql server box.
> > >
> > > I trying to write a procedure where I can set a threshold in the DB for
> > > each
> > > disks and once we reach it send me an email. Now I have the Disk Size as
> > > hard coded, the problem with this is that if we changes disks or use this
> > > procedure on another box running SQL Server, it not going to be accurate.
> > > Is
> > > there anyway to find total disk size using some XP's like xp_fixeddrives.
> > > Thanks.
> >
> >
> >

How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks o
n
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. I
s
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:

> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>
>|||My team had the same problem, except that in SQL 2005 we didn't want to allo
w
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure o
n
a regular basis.
"yodarules" wrote:
[vbcol=seagreen]
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
>|||My team had the same problem, except that in SQL 2005 we didn't want to allo
w
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure o
n
a regular basis.
"yodarules" wrote:
[vbcol=seagreen]
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
>

How do I do "If statement" in SSIS?

I need to get value from one table and if it is (for example) string - TodayDate, I need to change it to "Today Date" with a space in it and use it later in my Data Flow.

So I would need something similar to

If value = "TodayDate" Then

value - "Today Date"

Else

.....

How do I do that?

Thanks.

Check on the Derived Column transform in SSIS dataflow task.

|||

To add to Wenyang's answer...you will need to use the Conditonal Operator within the Derived Column transform.

? : (Conditional) (SSIS)

(http://msdn2.microsoft.com/en-us/library/ms141680.aspx)

Wenyang, I don't thinkyour answer is complete. Do you really think it warrants being marked as an answer? Hope you don't mind me asking.

Regards

Jamie

|||

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

|||

Vita wrote:

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

MonitorType == "SA" ? @.[MyVariable] : @.[SomeOtherVariable]

-Jamie

|||

Thanks.

|||Please don't forget to mark posts as answered.|||

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

|||

I believe you can continue to add additional tests in the Derived Column task.

Rob

|||

Vita wrote:

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

Yes. You need to nest the Conditonal Operator.

MonitorType == "SA" ? @.var1 : (MonitorType == "BA" ? @.var2 : (MonitorType == "DA" ? @.var3 : (MonitorType == "DS" ? @.var4 : @.SomeDefaultVariable)))

-Jamie

please don't forget to mark as answered.

|||Thanks again. I appreciate it.