Pckg variable not getting set

I have created a simple package to zip a file. I am determining the file to be zipped dynamically at runtime using the Script task. I have a package variable defined as ReadWrite in the script task; I am attempting to store the dynamically determined file name to this package variable.

The code I am using to assign the value to the variable is:

Dim LatestBackupName As String

Dim ZipName As String

Dts.Variables("FileName").Value = ZipName & " " & LatestBackupName

I have set a breakpoint in the code to step into each statement. I am able to see that the "FileName" variable is being assigned the appropriate value. However, once the Script task completes, the value assigned to FileName is not stored in the variable. It is as if it gets the value during the script task and then loses it upon completion.

Am I going about setting the variable value wrong?

Any guidance is appreciated.

Nevermind. An answer to an unrelated post helped me figure this out. The variable was getting set - it just does not show it the way DTS would have. I set DelayValidation to TRUE to resolve the error I was receiving that I thought indicated the variable was not set.

Passing the same variable into a few SQL queries & join them

I have a table that has these fields:
AgentID, Address, RegNo, CnNo, IssueDt, SubmitDt, NoOfDays

Example of data is:

AgentID Address RegNo CnNo IssueDt SubmitDt NoOfDays
1 No 5, Jln Abc KL123 11111 4/12/03 4/15/03 3
1 No 5, Jln Abc KL123 12345 5/10/03 5/20/03 10
1 No 5, Jln Abc KL123 13000 6/13/03 6/13/03 0
1 No 5, Jln Abc KL123 15123 8/15/03 8/16/03 1

NoOfDays actually is DateDiff("d", IssueDt, SubmitDt)

I have to build a report for each Quarter that contains the following:

Agent: 1
Address: No 5, Jln Abc
RegNo: KL123
Total of CN: 3
Total of CN(with Days>0): 2
Days (Min): 3
Days (Max) : 10

This is based on the 2nd quarter (from April to June).

What I did is, I created 3 views.
1)A view that gets Count(CnNo) GROUP BY all the fields HAVING DatePart("quarter", IssueDt) = varQuarter
2)A view that gets Count(CnNo) GROUP BY all the fields HAVING DatePart("quarter", IssueDt) = varQuarter AND NoOfDays > 0
3)A view that gets Min(NoOfDays), Max(NoOfDays) GROUP BY .....HAVING DatePart("quarter", IssueDt) = varQuarter

Right now, I hard code the varQuarter as 2.
After creating all these views, I joined them together to become a view & put all the info in the crystal report to produce the report.

My problem is to make the report dynamic.
I don't know how to pass the Quarter variable into these views.Take the sql for each of the views, place them into a stored procedure, and do the appropriate joins. Replace all your hardcoded varQuarter with @.VarQuarter and define @.VarQuarter as an input parameter for the stored procedure.

Then call it like this:

EXEC BigReportQuery @.VarQuarter=3

The definition of BigReportQuery would start like this:

CREATE PROCEDURE [dbo].[BigReportQuery]
@.VarQuarter int
AS
SELECT x,y,z FROM SomeTable INNER JOIN ... HAVING DatePart("quarter",IssueDt)=@.VarQuarter ... etc.
GO

Alternatively, just combine calls to each of the existing views but from within the stored procedure.

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az alábbiakat írta a következõ
hírüzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>