December 5, 2007 at 12:23 pm
Hi,
Im executing an stored procedure using DTS package.
That procedure returns a decimal value.
That retunvalue is passed as a Global Variable.
But that Global Variable is not getting updated for every execution of the DTS Package.
The Global Variable shows only the previous value and not the new value
Can any one help on this?
Thanks in advance,
MRK.
December 6, 2007 at 7:05 am
How do you execute the DTS package from stored procedure? You need to pass the parameter using /A to update the DTS package global variable.
December 6, 2007 at 7:27 am
HI,
Im using this code,
Create 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
This Command i have given in the Execute SQL
DECLARE @rc decimal(15, 5)
EXECUTE @rc = Size_DiskDrive 'K'
SELECT @rc AS freespace
In output parameter tab,
Parameter "freespace" automatically came and i have created an Global variable "freespace".
First time the value is coming fine as 9222.
But If i change the input parameter as "D" instead of "K" like
EXECUTE @rc = Size_DiskDrive 'D'
again im getting the same value again.
The new value is not getting updated in the global variable.
Can you pls help me to resolve this issue?
Thanks in advance,
MRK.
December 6, 2007 at 10:47 am
Sorry I misunderstood your question the first time. After I saw your code, then I understood but I was confused with your code. Is the drive letter the global variable ?
As far as I looked at your code, you put in EXECUTE @rc = Size_DiskDrive 'K', you hardcoded the variable.
If you want use global variable for the drive letter, you need to write ActiveX script to update the variable so it will looked liked 'EXECUTE @rc = Size_DiskDrive @driveletter' and Put DriveLetter in the Global Variable.
Here is the code of the ActiveX script. You have to go to the package property to find out the number of Execute SQL Task to change 'DTSTask_DTSExecuteSQLTask_7' to the correct name.
Function Main()
Dim oPkg
Dim oFS
Dim DriveLetter
Dim tsk, cus, sql
SET oPKG = DTSGlobalVariables.Parent
SET oFS = CreateObject("Scripting.FileSystemObject")
DriveLetter = TRIM(DTSGlobalVariables("DriveLetter").Value)
Main = DTSTaskExecResult_Failure
Set tsk =oPKG.Tasks("DTSTask_DTSExecuteSQLTask_7")
Set cus = tsk.CustomTask
sql = "EXEC Size_DiskDrive " & DriveLetter & "'"
cus.SQLStatement = sql
Main = DTSTaskExecResult_Success
SET oFS = nothing
SET oPKG = nothing
End Function
December 6, 2007 at 11:20 am
I read your question more carefully. If you want to get the result of stored procedure, you don't use global variable, you use output parameter of the execute SQL Task.
December 7, 2007 at 12:18 am
Hi,
Here im trying to find the freespace of the diskdrive.
Im passing the Drive letter "k" as input for my procedure like,
EXECUTE @rc = Size_DiskDrive 'K'
and returning the freepspace of the Drive "K" like,
return @DBSize
Getting the returning variable like,
EXECUTE @rc = Size_DiskDrive 'K'
Now my freespace value (9222) is stored in @rc and im displaying it using,
SELECT @rc AS freespace
The output is like,
freepace
--------
9222
A output parameter "freespace" is automatically getting listed in my Execute SQL when i click Parameters button in DTS.
I created a global variable called "freespace" and assigned that to the output parameter "freespace"
For the first time im getting the value properly as "7222" in the Global variable.
If i Change my input like
EXECUTE @rc = Size_DiskDrive 'D'
and test it in SQL Analyzer the value is coming properly as "8345".
But in DTS, If I give like this
EXECUTE @rc = Size_DiskDrive 'D'
Again im getting the same value as "9222" which is the freespace of "k".
The global variable is not getting updated for every execution.
This is my problem.
The global variable should get updated everytime based on my input drive letter and get proper freespace.
if you have any clarification. i will explain you more clearly.
Thanks,
MRK.
December 7, 2007 at 8:37 am
The global variable is an input variable not output variable. When you call the DTS package, you put in the global variable as input.
In your case, you want an output from your stored procedure, you cannot use global variable.
In the execute SQL task, there is input parameter and output parameter, you may use output parameter as I gave you the example of the web page to get the output variable.
If you insist to use global variable, you never get what you want.
Am I clear? Or you may ask other expert.:exclamationmark:
December 7, 2007 at 8:56 am
Hi,
Please clarify my doubt.
Here i explained my steps.
If you open Execute SQL Task, There is a button called "Parameters".
If you click on that it will open a pop-up window. In that two tabs will be there.
One is Input Parameter, Another is OutPut Parameter.
I Went to Output Parameter tab and there is a button called "Create Global Variable". Click on that.
It will open another popup window, In that i have mentioned the "Freespace" Global Variable.
click ok.
It comes back to the window again.
In that window, I have assigned this "Freespace" global variable to the Parameter "freespace".
and cliked ok.
Is this approach correct?
This approcah is working properly for another concept. there im using only a Sql statement and not a stored procedure.
only in executing stored procedure im facing this problem..
Can you help on this?
Thanks,
MRK
December 7, 2007 at 9:26 am
Hi,
I have looked into the URL you specified.
But it looks different.. May be its different version.
We are using SQL server 2000.
If possible pls provide A query to find the freespace of a Diskdrive. I need a single query and not an Stored Procedure.
This also solve my problem. I hope.
Thanks,
MRK
December 7, 2007 at 9:40 am
Loner,
What is wrong with using a global variable to hold the output of T-SQL executed in the Execute SQL task? I do it all of the time. Isn't that the whole point of the ExecuteSQL task's output parameter tab?
December 7, 2007 at 3:02 pm
Sorry I got the wrong web page. It was SQL Server 2005.
I think this web page will help you. One thing you need to define OUTPUT parameter in the procedure.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply