December 1, 2007 at 4:16 am
My requirement is to find the Free Space of DB and send a mail to the user.
Im trying to use DTS package,
Is it possible to find the free space of Database using DTS Package?
Is there any MS SQL query available to find the free space of the Database?
Im looking for a single query to find the freespace instead of Stored Procedure.
In DTS package,
When i try to use a stored procedure, i cant able to create parameters. why?
What im trying to do in DTS package is,
I have a stored procedure, im trying to execute that. After i execute that the resultset should be passed as output parameter or global variable. I want to use that value in the activeX script.
Thanks,
MRK
December 1, 2007 at 11:46 am
The way to do this is call the stored procedure from the ActiveX script. Then you can get an output variable or result set.
http://www.thescripts.com/forum/thread498718.html
DTS doesn't do a great job of passing along information. If this is for just one database, you could set global variables to the result of the query. You can also turn the stored procedure into a query.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 4, 2007 at 10:27 pm
HI,
Im trying to execute the below procedure. But we are facing some techincal problem in this while implementing it into the DTS Package.
CREATE procedure DiskSpace_Size
AS
SET NOCOUNT ON
DECLARE @disksize Decimal(15,2)
SET NOCOUNT ON
BEGIN
CREATE TABLE #T1(DRVLETTER CHAR(1),DRVSPACE INT)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives;
select @DiskSize =(SELECT DRVSPACE FROM #T1 WHERE DRVLETTER='D');
Drop TABLE #T1
return @DiskSize
end
Execution:
------------
DECLARE @ReturnValue int
EXEC @ReturnValue = DiskSpace_Size
SELECT ReturnValue=@ReturnValue
While executing like this im getting the freespace. But when using DTS Package.
If i click on 'Parameters' then click on 'Create/edit Parameter' That value is not getting shown in the Returnvalue.
The Parameter "ReturnValue" is not getting the value.
How to resolve this?
It would be better if we find the freespace of DiskDrive in a single query.
So that it will work fine.
MRK.
December 5, 2007 at 2:15 am
Sometimes DTS can be a right pain in the a$$. :hehe:
What you can do to work around this is create the DiskSpace_size procedure with all of the lines involving the temporary table commented out.
CREATE procedure DiskSpace_Size
AS
SET NOCOUNT ON
DECLARE @disksize Decimal(15,2)
SET NOCOUNT ON
BEGIN
--CREATE TABLE #T1(DRVLETTER CHAR(1),DRVSPACE INT)
--INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives;
--select @DiskSize =(SELECT DRVSPACE FROM #T1 WHERE DRVLETTER='D');
--Drop TABLE #T1
return @DiskSize
end
GO
You can then create your ExecuteSQL Task in the DTS to execute the procedure:
DECLARE @rc INT
EXEC @rc = DiskSpace_size
SELECT @rc AS diskspace
Once you've created the code hit the parameters button and you should be able to assign 'diskpace' to a globalvariable.
Complete the ExecuteSQL task creation as necessary and then go back to the stored procedure, uncomment the lines and recreate the procedure.
... As I said, pain in the a$$! 😀
December 5, 2007 at 3:33 am
Hi,
Thank you very much.
The problem is with my output parameter.
i have resolved it.
Thanks for all your support.
MRK.
December 5, 2007 at 5:24 am
HI,
Im facing a trouble here.
For "D" drive i executed Procedure.
The out put parameter is Diskspace.
The Global variable is also Diskspace and the value 755 got stored in that.
And the output came as 755. (755 MB freespace)
This 755 is stored in Global variable. and the Package is working fine.
Then i tried to find freespace for "K" drive,
This time also the same 755 is coming.
It means the Global variable is not getting updated.
Why this global variable is not getting updated??
Can anybody help me to resolve this?
MRK.
December 5, 2007 at 5:44 am
How did you do this for the K drive? Did you recreate the procedure with a 'K' in the WHERE clause instead of a 'D'? Or something different?
You might also want to change your procedure for usability, making @drive CHAR(1) an input variable to the procedure and changing the WHERE clause to incorporate the new variable; this would make it more reusable.
December 5, 2007 at 6:31 am
Hi,
Yes, I recreated the procedure with a 'K' in the WHERE clause instead of a 'D'.
Whatever it may be it should return the correct value but only the previous value is getting assigned to the Global Variable..
The freespace of "K" drive is 9872 but still the global variable is not getting updated and the freespace of "C" drive is showing in the Global variable.
Why this global value is not getting updated?
MRK.
December 5, 2007 at 7:07 am
December 5, 2007 at 7:15 am
Hi,
Its working fine in SQL Analyser.
Showing the K Drive freespace properly.
K Drive is a proper drive only not another server.
But When i implement in DTS Package, the output globalvariable value is not getting updated.
Why the global variable is not getting updated based on the output?
MRK.
December 5, 2007 at 7:57 am
I've set up and ran a test on my dev machine here. When I've changed drive letters in the stored procedure, the correct value has been output to the global variable. The only condition under which this doesn't happen has been when the drive letter doesn't exist; the global variable value does not change from the previous drive run.
At the moment I can only suggest:
1. Checking that your SQL connection is to the correct server
2. Run the procedure for the C drive and then the D drive and check that the global variable value changes correctly at this point. Post back the results of these checks and while that's running I'll have another think. :crazy:
December 5, 2007 at 8:07 am
Hi,
When im Testing with "K" drive the value is coming and the global variable is getting updated and displayed the value 9722
Next when i test with "D" drive the global variable is not getting updated only the previous value is getting displayed as 9722 instead of 780.
I want to know why the global variable is not getting updated for every run?
MRK.
December 6, 2007 at 7:57 am
Hi,
Im using this procedure
ALTER procedure Size_DiskDrive
@driveletter char(1)
AS
DECLARE @flag int
DECLARE @dbsize Decimal(15,2),
@TBexist int
BEGIN
select @TBexist = count(*) from sysobjects where type = 'U' and name = 'Test1'
if (@TBexist = 0)
BEGIN
CREATE TABLE Test1(DRVLETTER CHAR(1),DRVSPACE INT)
INSERT INTO Test1 EXEC master.dbo.xp_fixeddrives;
select @DBSize =(SELECT DRVSPACE FROM Test1 WHERE DRVLETTER= @driveletter)
PRINT 'Success'
PRINT @TBexist
End
else
Begin
INSERT INTO Test1 EXEC master.dbo.xp_fixeddrives;
select @DBSize =(SELECT DRVSPACE FROM Test1 WHERE DRVLETTER= @driveletter)
PRINT 'Failure'
end
Truncate Table Test1
--Drop TABLE Test1
PRINT @DBSize
Return @DBSize
end
Im using this statements in Execute SQL Tasks to execute the above procedure.
DECLARE @rc decimal(15, 5)
EXECUTE @rc = Size_DiskDrive 'K'
SELECT @rc AS freespace
This automatically creates the parameter "freespace" i have created a global variable "freespace".
On executing first time im getting the correct value as "9222".
But If i change the input parameter as "D" instead of "K" like
EXECUTE @rc = Size_DiskDrive 'D'
im getting the same value "9222" again instead of "7332"
The new value is not getting updated in the global variable.
If i try this in SQL Analyzer its working fine for every execution. But in DTS the Golbal variable is not getting updated. why?
Can you pls help me to resolve this issue?
Thanks in advance,
MRK.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply