How to get the return/execution value of a package from a parent?

Hi there,

I'm trying to get the return value of a package. I see there is a ForcedExecutionValue property which I set using an expression (variable). What I'm executing are 2 packages, Package1 contains an Execute Package Task that calls Package 2. Package 2 contains a Script Task that sets the value of variable Max. I want to get the value of Max in Package 1 then how can I do this?

My first approach is toset the return value of Package 2 = Max and then I thought I could retrieve this value from Package 1 but I'm not able to do that yet.

Any thoughts?

Thanks for any help!The way I would approach this is to use a Script Task (surprise, surprise) to load and execute the package instead of the Execute Package Task. Your script has the ability to read the child packages variables after it has executed, thus allowing child variables to be passed back to the parent.
http://blogs.msdn.com/jamesk/archive/2005/12/21/506463.aspx

Alternatively, you can also have the child package set the parent's variable directly.
http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx|||Hi JayH,

Yes, I did the first approach and it worked fine. I'm loading the package from a Script Task and getting the Executables.Count and store this value in a local variable.

Thanks for the suggestion!.

Ricardo

How to get Stored Procedure output ?

I have a variable @.NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @.NetPay. How can I do That?

Set @.NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

Create Procedure dbo.spGetNetPay (
 @.NetPay Money OUTPUT
) AS
SET @.NetPay = (Select Top 1 NetPay From SomeTable)
GO

|||Post your code for spGetNetPay to enable determine the best solution for you.|||I use Kay Lee's solution. My spGetNetPay is huge, having more than 200 factors to determine the net pay. However, adding an output parameter is an easy job for me:)

I am still interested in if it is possible to code "user defined function". It has its beauty such that you can add it in select statment column list. But it is totally new for me. I even never seen sample code...

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:

> I am using SQL Server 2000 and in SP or Function, my problem is as follows
:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of co
de
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:
> I am using SQL Server 2000 and in SP or Function, my problem is as follows:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of code
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:uem$6elfGHA.5088@.TK2MSFTN
GP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> glsD:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Image (and text/ntext) data types cannot be declared as local variables.
>> These are mostly intended to be transferred to/from application code
>> directly.
>> What do you plan to do with the image value in the proc? You can use
>> SUBSTRING to assign 'chunks' of the image value to a local varbinary
>> variable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "angi" <angi@.news.microsoft.com> wrote in message
>> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>>
>

how to get error message

Hello!

I am using T-SQL (SQL Server 2000). I would like to get error message

(for example stored in some string variable) when error appeares. I

found only system variable @.@.error which holds the error number of the

specific error. But I didn't find any way to get a message which is

written in SQL Qery analyzer in the Message window beneath the query

window.

Is it possible to get that message somehow? I would like to store it in my error table...

Thanks a lot,

Ziga

Hi

You can query the sysmessages table in the Master database for the error number that you receive, this will give you the description of the error. Books Online contains a description of the sysmessages table schema should you want to know what each field is, look under sysmessages.

HTH

|||Thanks a lot!

But anyway I would like to get the same message as is reported in sql

server (with object names - attribute name). Systables contains

attribute description with description of the messages with

placeholders. I don't know how to put some real object names on the

place of placeholders... Actually I can't put them, because sql server

should somehow (cause I don't know on which attribute it breaks).|||

Hi

I don't believe there is a way of finding out the exact same message as displayed by SQL Server when an error occurs (although I could be wrong). If your procedure does multiple things then you could use the @.@.Error to determine if an error has occurred at each step of the procedure and then you would have at least more knowledge of where the error occurred. Add to that, the error number returned, you should then be able to log sufficient error information.

HTH

|||Unfortunately, there is no way to obtain the system error message in TSQL. SQL Server 2005 has new exception handling features that provide you this capability. So best is to log server generated error messages on the client-side and capture only the error numbers on the server.