How do you call an external VBS script from within a T-SQL Job?
At the end of a job I want a VBS to fire off in the last step.
Not sure how to do this. Looked everywhere ... :)Typically, T-SQL (Execute SQL Task) and VBS (ActiveX Script Task) are
included in a DTS Package, which is then scheduled for execution using a
job.
http://msdn.microsoft.com/library/d... />
x_06er.asp
"Whispering Leaf" <WhisperingLeaf@.discussions.microsoft.com> wrote in
message news:63FFAF27-72B1-45B6-A326-5DCDFED2735A@.microsoft.com...
> How do you call an external VBS script from within a T-SQL Job?
> At the end of a job I want a VBS to fire off in the last step.
> Not sure how to do this. Looked everywhere ... :)
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
E-mail delivery
Hi All,
I need to programatically create a report, and deliver it.
I managed to create the report and a schedule.
When I call CreateSubcription, it fails
(InvalidDataSourceCredentialSettingException is thrown)
the message is:
"The current action cannot be completed because the user data source
credentials that are
required to execute this report are not stored in the report server
database."
Any ideas ?Make sure you are using stored credentials on the data source the report
uses. This is required for subscription processing.
--
Adrian M.
MCP
"Geek 007" <holmes@.programmer.net> wrote in message
news:uML1ADLTFHA.2304@.tk2msftngp13.phx.gbl...
> Hi All,
> I need to programatically create a report, and deliver it.
> I managed to create the report and a schedule.
> When I call CreateSubcription, it fails
> (InvalidDataSourceCredentialSettingException is thrown)
> the message is:
> "The current action cannot be completed because the user data source
> credentials that are
> required to execute this report are not stored in the report server
> database."
> Any ideas ?
>
>
>
I need to programatically create a report, and deliver it.
I managed to create the report and a schedule.
When I call CreateSubcription, it fails
(InvalidDataSourceCredentialSettingException is thrown)
the message is:
"The current action cannot be completed because the user data source
credentials that are
required to execute this report are not stored in the report server
database."
Any ideas ?Make sure you are using stored credentials on the data source the report
uses. This is required for subscription processing.
--
Adrian M.
MCP
"Geek 007" <holmes@.programmer.net> wrote in message
news:uML1ADLTFHA.2304@.tk2msftngp13.phx.gbl...
> Hi All,
> I need to programatically create a report, and deliver it.
> I managed to create the report and a schedule.
> When I call CreateSubcription, it fails
> (InvalidDataSourceCredentialSettingException is thrown)
> the message is:
> "The current action cannot be completed because the user data source
> credentials that are
> required to execute this report are not stored in the report server
> database."
> Any ideas ?
>
>
>
Friday, February 24, 2012
eliminate duplicate results
Hi,
Here is a brief description of a problem I'm currently experiencing:
We have a phone line that employees can call to report their status. There is no limit on how often they can call. Each time a call is completed a record is inserted into a table with the following info: the employees unique id, theire current status, and the location of an audio file should they have chosen to leave a message. When I query this table I'd like to only return the most recent call for each employee, disregarding all the rest.
So...
When I do a simple query like:
SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId
I get only one record, the most recent one, for each employee who has called. However as soon as I try to get another field I get one record for each different value in that field.
a second query:
SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId,
r.ircStatus
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId, r.ircStatus
This returns one record for each status value that each employee has.
I guess my question is how do I retrieve all of the fields in a record with the most recent date for each unique employee.
Thanks for the help.Read this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31147
And change MIN to MAX|||Thanks for the quick reply.
Which post in that thread am I looking at?|||Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect
Thanks Brett!
Here is a brief description of a problem I'm currently experiencing:
We have a phone line that employees can call to report their status. There is no limit on how often they can call. Each time a call is completed a record is inserted into a table with the following info: the employees unique id, theire current status, and the location of an audio file should they have chosen to leave a message. When I query this table I'd like to only return the most recent call for each employee, disregarding all the rest.
So...
When I do a simple query like:
SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId
I get only one record, the most recent one, for each employee who has called. However as soon as I try to get another field I get one record for each different value in that field.
a second query:
SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId,
r.ircStatus
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId, r.ircStatus
This returns one record for each status value that each employee has.
I guess my question is how do I retrieve all of the fields in a record with the most recent date for each unique employee.
Thanks for the help.Read this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31147
And change MIN to MAX|||Thanks for the quick reply.
Which post in that thread am I looking at?|||Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect
Thanks Brett!
Elegant alternative to table-valued function
I have a table, call it Liquidity, that is the result of joins of other
table-values functions, tables, and subqueries. Now I want to create a
table-valued function (or some parameterized query) called GetLiquidityInfo
to return a view in which each row contains all the columns from Liquidity
plus three additional columns, LockupDate, NoticeDate, and RedemptionDate.
Now, these three columns are calculated based on values from the columns
that come from Liquidity (Liquidity.*) plus an additional parameter that is
supplied from the user. I might use the results from GetLiquidityInfo to
generate a report, or I might need to use it as a source for further
queries.
My question is: what is the most elegant way to achieve GetLiquidityInfo? I
am hoping this can be answered without schema and code, which is
proprietary.
I had initially implemented each of the columns (LockupDate, NoticeDate,
RedemptionDate) as three scalar-valued functions. GetLockupDate produced
LockupDate, which was then supplied to GetRedemptionDate, which produced
RedemptionDate, which was then passed to GetNoticeDate to get NoticeDate.
In each of these function calls, additional parameters came from Liquidity.
Because each function returned a scalar value, I was able to create
GetLiquidityInfo as an inline table-valued function with several nested
queries. The innermost query got the columns from Liquidity; the next query
added the result of GetLockupDate as a column; the next query added the
result of GetRedemptionDate as a column; etc. The nested queries allowed me
to use the columns from the inner select statement as parameters to the
scalar-valued function in the outer select statement.
Now,
ly, the specifications have changed, and the three functions cannot
be chained together in this way. They are chained together like this now:
GetRedemptionDate --> GetLockupDate --> GetRedemptionDate --> GetNoticeDate
(That's right, GetRedemptionDate must be called twice.) And the logic of
the processing is more complex, so that nested queries don't work so well.
My first thought was to create a table valued function GetLiquidityDates to
return all three dates at once. I planned to JOIN Liquidity to
GetLiquidityDates(Liquidity.a, Liquidity.b, Liquidity.c, ...) but apparently
I cannot refer to columns from another table in the same join. I had hoped
SQL Server would process GetLiquidityDates as it does for a correlated
subquery, but it doesn't. I have considered using a cursor to evaluate
GetLiquidityDates for each row in Liquidity, and manually constucting the
result table, but I am not sure this is the best way to go.
Any suggestions?dustbort wrote:
> I have a table, call it Liquidity, that is the result of joins of other
> table-values functions, tables, and subqueries. Now I want to create a
> table-valued function (or some parameterized query) called GetLiquidityInf
o
> to return a view in which each row contains all the columns from Liquidity
> plus three additional columns, LockupDate, NoticeDate, and RedemptionDate.
> Now, these three columns are calculated based on values from the columns
> that come from Liquidity (Liquidity.*) plus an additional parameter that i
s
> supplied from the user. I might use the results from GetLiquidityInfo to
> generate a report, or I might need to use it as a source for further
> queries.
> My question is: what is the most elegant way to achieve GetLiquidityInfo?
I
> am hoping this can be answered without schema and code, which is
> proprietary.
> I had initially implemented each of the columns (LockupDate, NoticeDate,
> RedemptionDate) as three scalar-valued functions. GetLockupDate produced
> LockupDate, which was then supplied to GetRedemptionDate, which produced
> RedemptionDate, which was then passed to GetNoticeDate to get NoticeDate.
> In each of these function calls, additional parameters came from Liquidity
.
> Because each function returned a scalar value, I was able to create
> GetLiquidityInfo as an inline table-valued function with several nested
> queries. The innermost query got the columns from Liquidity; the next que
ry
> added the result of GetLockupDate as a column; the next query added the
> result of GetRedemptionDate as a column; etc. The nested queries allowed
me
> to use the columns from the inner select statement as parameters to the
> scalar-valued function in the outer select statement.
> Now,ly, the specifications have changed, and the three functions canno
t
> be chained together in this way. They are chained together like this now:
> GetRedemptionDate --> GetLockupDate --> GetRedemptionDate --> GetNoticeDat
e
> (That's right, GetRedemptionDate must be called twice.) And the logic of
> the processing is more complex, so that nested queries don't work so well.
> My first thought was to create a table valued function GetLiquidityDates t
o
> return all three dates at once. I planned to JOIN Liquidity to
> GetLiquidityDates(Liquidity.a, Liquidity.b, Liquidity.c, ...) but apparent
ly
> I cannot refer to columns from another table in the same join. I had hope
d
> SQL Server would process GetLiquidityDates as it does for a correlated
> subquery, but it doesn't. I have considered using a cursor to evaluate
> GetLiquidityDates for each row in Liquidity, and manually constucting the
> result table, but I am not sure this is the best way to go.
> Any suggestions?
Not without a proper repro. Your code may be proprietary but surely you
could create a hypothetical example.
It seems to me it ought to be possible to calculate these results
in-line in a query unless they are somehow recursive. Whether they are
recursive calculations or not is far from clear to me based on your
narrative.
Please also tell us what version of SQL Server you are using. In SQL
Server 2005 you may be able to make use of the CROSS APPLY operator.
David Portas
SQL Server MVP
--|||Here is the definition for the actual function that I refered to earlier as
GetLiquidityInfo. Below it is the definition for getLiquidityDates. My
logical table Liquidity roughly corresponds to @.activeTranches. (The
function is not inline because it actually runs faster when @.activetranches
is queried out first, because of the sources of the underlying data.
The error I get is "Line 140: Incorrect syntax near 'AT'" which is the
second argument of getLiquidityDates().
---
CREATE FUNCTION [dbo].[getLiquiditySchedule2]
(
@.asOfDate SMALLDATETIME
)
RETURNS @.liquiditySchedule TABLE
(
[valuationDate] SMALLDATETIME,
[valuationVersion] INT,
[value] FLOAT,
[rcgFundId] INT,
[rcgFundName] VARCHAR(100),
[mgrFundId] INT,
[managerName] VARCHAR(100),
[shareClass] VARCHAR(50),
[investmentDate] SMALLDATETIME,
[investmentAmount] FLOAT,
[lockupPeriodValue] INT,
[lockupPeriodUnit] VARCHAR(50),
[noticePeriodValue] INT,
[noticePeriodUnit] VARCHAR(50),
[redemptionFrequencyValue] INT,
[redemptionFrequencyUnit] VARCHAR(50),
[redemptionFrequencyBasisDate] SMALLDATETIME,
[lockupEndDate] SMALLDATETIME,
[latestNoticeDate] SMALLDATETIME,
[nextRedemptionDate] SMALLDATETIME,
[fractionRedeemed] FLOAT,
[fractionRemaining] FLOAT,
[totalInvestmentAmount] FLOAT,
[trancheInvestmentFraction] FLOAT,
[trancheValue] FLOAT
)
AS
BEGIN
DECLARE @.activeTranches TABLE
(
[rcgFundId] INT,
[mgrFundId] INT,
[shareClass] VARCHAR(50),
[investmentDate] SMALLDATETIME,
[investmentAmount] FLOAT,
[noticePeriodValue] INT,
[noticePeriodUnit] VARCHAR(50),
[redemptionFrequencyValue] INT,
[redemptionFrequencyUnit] VARCHAR(50),
[redemptionFrequencyBasisDate] SMALLDATETIME,
[lockupPeriodValue] INT,
[lockupPeriodUnit] VARCHAR(50),
[lockupEndFrequencyValue] INT,
[lockupEndFrequencyUnit] VARCHAR(50),
[lockupEndBasisDate] SMALLDATETIME,
[fractionRedeemed] FLOAT,
[fractionRemaining] FLOAT,
[valuationDate] SMALLDATETIME,
[valuationVersion] INT,
[value] FLOAT
)
INSERT INTO @.activeTranches
SELECT *
FROM getActiveTranches_optimized(@.asOfDate)
INSERT INTO @.liquiditySchedule
SELECT
[AT].[valuationDate],
[AT].[valuationVersion],
[AT].[value],
[AT].[rcgFundId],
[RFN].[rcgFundName],
[AT].[mgrFundId],
[MN].[managerName],
[AT].[shareClass],
[AT].[investmentDate],
[AT].[investmentAmount],
[AT].[lockupPeriodValue],
[AT].[lockupPeriodUnit],
[AT].[noticePeriodValue],
[AT].[noticePeriodUnit],
[AT].[redemptionFrequencyValue],
[AT].[redemptionFrequencyUnit],
[AT].[redemptionFrequencyBasisDate],
[LD].[lastLockupEndDate],
[LD].[nextNoticeDate],
[LD].[nextRedemptionDate],
[AT].[fractionRedeemed],
[AT].[fractionRemaining],
[AT_TOTAL].[totalInvestmentAmount],
[trancheInvestmentFraction] = [AT].[investmentAmount] /
[AT_TOTAL].[totalInvestmentAmount]
FROM
@.activeTranches [AT]
INNER JOIN RcgFundNames [RFN]
ON [AT].[rcgFundId] = [RFN].[rcgFundId]
INNER JOIN ManagerNames [MN]
ON [AT].[mgrFundId] = [MN].[managerId]
INNER JOIN (
SELECT
[rcgFundId],
[mgrFundId],
[shareClass],
[totalInvestmentAmount] = SUM([investmentAmount])
FROM
@.activeTranches
GROUP BY
[rcgFundId],
[mgrFundId],
[shareClass]
) [AT_TOTAL]
ON [AT].[rcgFundId] = [AT_TOTAL].[rcgFundId]
AND [AT].[mgrFundId] = [AT_TOTAL].[mgrFundId]
AND ISNULL([AT].[shareClass], '') = ISNULL([AT_TOTAL].[shareClass], '')
INNER JOIN
getLiquidityDates(
@.asOfDate,
[AT].[investmentDate],
[AT].[noticePeriodValue],
[AT].[noticePeriodUnit],
[AT].[redemptionFrequencyValue],
[AT].[redemptionFrequencyUnit],
[AT].[redemptionFrequencyBasisDate],
[AT].[lockupPeriodValue],
[AT].[lockupPeriodUnit],
[AT].[lockupEndFrequencyValue],
[AT].[lockupEndFrequencyUnit],
[AT].[lockupEndFrequencyBasisDate],
[AT].[rolloverFrequencyValue],
[AT].[rolloverFrequencyUnit],
[AT].[rolloverFrequencyBasisDate]) [LD]
RETURN
END
GO
----
CREATE FUNCTION [dbo].[getLiquidityDates]
(
@.asOfDate SMALLDATETIME,
@.investmentDate SMALLDATETIME,
@.noticePeriodValue INT,
@.noticePeriodUnit VARCHAR(50),
@.redemptionFrequencyValue INT,
@.redemptionFrequencyUnit VARCHAR(50),
@.redemptionFrequencyBasisDate SMALLDATETIME,
@.lockupPeriodValue INT,
@.lockupPeriodUnit VARCHAR(50),
@.lockupEndFrequencyValue INT,
@.lockupEndFrequencyUnit VARCHAR(50),
@.lockupEndFrequencyBasisDate SMALLDATETIME,
@.rolloverFrequencyValue INT,
@.rolloverFrequencyUnit VARCHAR(50),
@.rolloverFrequencyBasisDate SMALLDATETIME
)
RETURNS @.liquidityDates TABLE
(
[lastLockupEndDate] SMALLDATETIME,
[nextNoticeDate] SMALLDATETIME,
[nextRedemptionDate] SMALLDATETIME
)
AS
BEGIN
DECLARE @.nextRedemptionDate SMALLDATETIME
SET @.nextRedemptionDate = dbo.getNextRedemptionDate(
@.asOfDate,
@.noticePeriodValue,
@.noticePeriodUnit,
@.redemptionFrequencyBasisDate,
@.redemptionFrequencyValue,
@.redemptionFrequencyUnit)
DECLARE @.lastLockupEndDate SMALLDATETIME
SET @.lastLockupEndDate = dbo.getLastLockupEndDate(
@.nextRedemptionDate,
@.investmentDate,
@.lockupPeriodValue,
@.lockupPeriodUnit,
@.lockupEndFrequencyBasisDate,
@.lockupEndFrequencyValue,
@.lockupEndFrequencyUnit,
@.rolloverFrequencyBasisDate,
@.rolloverFrequencyValue,
@.rolloverFrequencyUnit)
IF (@.nextRedemptionDate < @.lastLockupEndDate)
BEGIN
-- The projected next redemption date actually occurs during a
-- lockup period.
-- Assume there is at least one real redemption period between
-- rolling lockup periods.
SET @.nextRedemptionDate = dbo.getNextRedemptionDate(
@.lastLockupEndDate,
NULL, -- Don't count notice period; it occurs during lockup.
NULL,
@.redemptionFrequencyBasisDate,
@.redemptionFrequencyValue,
@.redemptionFrequencyUnit)
-- This next redemption date is guaranteed to be after the
-- first one, so sufficient notice period is also guaranteed.
END
DECLARE @.nextNoticeDate SMALLDATETIME
SET @.nextNoticeDate = dbo.DateAddVar(
@.noticePeriodUnit,
-@.noticePeriodValue,
@.nextRedemptionDate)
INSERT INTO
@.liquidityDates
SELECT
@.lastLockupEndDate,
@.nextNoticeDate,
@.nextRedemptionDate
RETURN
END
GO
---
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1136398951.939350.98470@.g47g2000cwa.googlegroups.com...
> dustbort wrote:
> Not without a proper repro. Your code may be proprietary but surely you
> could create a hypothetical example.
> It seems to me it ought to be possible to calculate these results
> in-line in a query unless they are somehow recursive. Whether they are
> recursive calculations or not is far from clear to me based on your
> narrative.
> Please also tell us what version of SQL Server you are using. In SQL
> Server 2005 you may be able to make use of the CROSS APPLY operator.
> --
> David Portas
> SQL Server MVP
> --
>|||I forgot to say that I am using
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1136398951.939350.98470@.g47g2000cwa.googlegroups.com...
> dustbort wrote:
> Not without a proper repro. Your code may be proprietary but surely you
> could create a hypothetical example.
> It seems to me it ought to be possible to calculate these results
> in-line in a query unless they are somehow recursive. Whether they are
> recursive calculations or not is far from clear to me based on your
> narrative.
> Please also tell us what version of SQL Server you are using. In SQL
> Server 2005 you may be able to make use of the CROSS APPLY operator.
> --
> David Portas
> SQL Server MVP
> --
>|||On Wed, 4 Jan 2006 14:31:50 -0500, "dustbort" <dustbort at yahoo dot
com> wrote:
>Here is the definition for the actual function that I refered to earlier as
>GetLiquidityInfo. Below it is the definition for getLiquidityDates. My
>logical table Liquidity roughly corresponds to @.activeTranches. (The
>function is not inline because it actually runs faster when @.activetranches
>is queried out first, because of the sources of the underlying data.
>The error I get is "Line 140: Incorrect syntax near 'AT'" which is the
>second argument of getLiquidityDates().
(snip)
Hi dustbort,
The error is here:
> INNER JOIN
> getLiquidityDates(
> @.asOfDate,
> [AT].[investmentDate],
> [AT].[noticePeriodValue],
> [AT].[noticePeriodUnit],
> [AT].[redemptionFrequencyValue],
> [AT].[redemptionFrequencyUnit],
> [AT].[redemptionFrequencyBasisDate],
> [AT].[lockupPeriodValue],
> [AT].[lockupPeriodUnit],
> [AT].[lockupEndFrequencyValue],
> [AT].[lockupEndFrequencyUnit],
> [AT].[lockupEndFrequencyBasisDate],
> [AT].[rolloverFrequencyValue],
> [AT].[rolloverFrequencyUnit],
> [AT].[rolloverFrequencyBasisDate]) [LD]
What you try to do is not permitted in SQL Server 2000. If you join to a
table-valued UDF in a query, then the arguments of that UDF can't be
columns from the other tables in the query.
Logically (*), the steps taken to resolve a join to a table-valued UDF
are:
1. Materialize the UDF (i.e. execute it and store the results);
2. Check rows from UDF to rows from other table(s) in the JOIN and
evaluate ON condition to see which combinations will be retained.
The [AT].[ColumnName] arguments can only be replaced by a single value
after performing step 2, yet step 1 already requires single values.
(*) In reality, SQL Server will prefer more efficient paths of
execution, when available; this explanation is purely intended to
explain the logic of this limitation.
Note that SQL Server 2005 introduces the CROSS APPLY operator that will
permit you to do things like this.
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
Hugo Kornelis, SQL Server MVP
table-values functions, tables, and subqueries. Now I want to create a
table-valued function (or some parameterized query) called GetLiquidityInfo
to return a view in which each row contains all the columns from Liquidity
plus three additional columns, LockupDate, NoticeDate, and RedemptionDate.
Now, these three columns are calculated based on values from the columns
that come from Liquidity (Liquidity.*) plus an additional parameter that is
supplied from the user. I might use the results from GetLiquidityInfo to
generate a report, or I might need to use it as a source for further
queries.
My question is: what is the most elegant way to achieve GetLiquidityInfo? I
am hoping this can be answered without schema and code, which is
proprietary.
I had initially implemented each of the columns (LockupDate, NoticeDate,
RedemptionDate) as three scalar-valued functions. GetLockupDate produced
LockupDate, which was then supplied to GetRedemptionDate, which produced
RedemptionDate, which was then passed to GetNoticeDate to get NoticeDate.
In each of these function calls, additional parameters came from Liquidity.
Because each function returned a scalar value, I was able to create
GetLiquidityInfo as an inline table-valued function with several nested
queries. The innermost query got the columns from Liquidity; the next query
added the result of GetLockupDate as a column; the next query added the
result of GetRedemptionDate as a column; etc. The nested queries allowed me
to use the columns from the inner select statement as parameters to the
scalar-valued function in the outer select statement.
Now,
ly, the specifications have changed, and the three functions cannotbe chained together in this way. They are chained together like this now:
GetRedemptionDate --> GetLockupDate --> GetRedemptionDate --> GetNoticeDate
(That's right, GetRedemptionDate must be called twice.) And the logic of
the processing is more complex, so that nested queries don't work so well.
My first thought was to create a table valued function GetLiquidityDates to
return all three dates at once. I planned to JOIN Liquidity to
GetLiquidityDates(Liquidity.a, Liquidity.b, Liquidity.c, ...) but apparently
I cannot refer to columns from another table in the same join. I had hoped
SQL Server would process GetLiquidityDates as it does for a correlated
subquery, but it doesn't. I have considered using a cursor to evaluate
GetLiquidityDates for each row in Liquidity, and manually constucting the
result table, but I am not sure this is the best way to go.
Any suggestions?dustbort wrote:
> I have a table, call it Liquidity, that is the result of joins of other
> table-values functions, tables, and subqueries. Now I want to create a
> table-valued function (or some parameterized query) called GetLiquidityInf
o
> to return a view in which each row contains all the columns from Liquidity
> plus three additional columns, LockupDate, NoticeDate, and RedemptionDate.
> Now, these three columns are calculated based on values from the columns
> that come from Liquidity (Liquidity.*) plus an additional parameter that i
s
> supplied from the user. I might use the results from GetLiquidityInfo to
> generate a report, or I might need to use it as a source for further
> queries.
> My question is: what is the most elegant way to achieve GetLiquidityInfo?
I
> am hoping this can be answered without schema and code, which is
> proprietary.
> I had initially implemented each of the columns (LockupDate, NoticeDate,
> RedemptionDate) as three scalar-valued functions. GetLockupDate produced
> LockupDate, which was then supplied to GetRedemptionDate, which produced
> RedemptionDate, which was then passed to GetNoticeDate to get NoticeDate.
> In each of these function calls, additional parameters came from Liquidity
.
> Because each function returned a scalar value, I was able to create
> GetLiquidityInfo as an inline table-valued function with several nested
> queries. The innermost query got the columns from Liquidity; the next que
ry
> added the result of GetLockupDate as a column; the next query added the
> result of GetRedemptionDate as a column; etc. The nested queries allowed
me
> to use the columns from the inner select statement as parameters to the
> scalar-valued function in the outer select statement.
> Now,
ly, the specifications have changed, and the three functions cannot
> be chained together in this way. They are chained together like this now:
> GetRedemptionDate --> GetLockupDate --> GetRedemptionDate --> GetNoticeDat
e
> (That's right, GetRedemptionDate must be called twice.) And the logic of
> the processing is more complex, so that nested queries don't work so well.
> My first thought was to create a table valued function GetLiquidityDates t
o
> return all three dates at once. I planned to JOIN Liquidity to
> GetLiquidityDates(Liquidity.a, Liquidity.b, Liquidity.c, ...) but apparent
ly
> I cannot refer to columns from another table in the same join. I had hope
d
> SQL Server would process GetLiquidityDates as it does for a correlated
> subquery, but it doesn't. I have considered using a cursor to evaluate
> GetLiquidityDates for each row in Liquidity, and manually constucting the
> result table, but I am not sure this is the best way to go.
> Any suggestions?
Not without a proper repro. Your code may be proprietary but surely you
could create a hypothetical example.
It seems to me it ought to be possible to calculate these results
in-line in a query unless they are somehow recursive. Whether they are
recursive calculations or not is far from clear to me based on your
narrative.
Please also tell us what version of SQL Server you are using. In SQL
Server 2005 you may be able to make use of the CROSS APPLY operator.
David Portas
SQL Server MVP
--|||Here is the definition for the actual function that I refered to earlier as
GetLiquidityInfo. Below it is the definition for getLiquidityDates. My
logical table Liquidity roughly corresponds to @.activeTranches. (The
function is not inline because it actually runs faster when @.activetranches
is queried out first, because of the sources of the underlying data.
The error I get is "Line 140: Incorrect syntax near 'AT'" which is the
second argument of getLiquidityDates().
---
CREATE FUNCTION [dbo].[getLiquiditySchedule2]
(
@.asOfDate SMALLDATETIME
)
RETURNS @.liquiditySchedule TABLE
(
[valuationDate] SMALLDATETIME,
[valuationVersion] INT,
[value] FLOAT,
[rcgFundId] INT,
[rcgFundName] VARCHAR(100),
[mgrFundId] INT,
[managerName] VARCHAR(100),
[shareClass] VARCHAR(50),
[investmentDate] SMALLDATETIME,
[investmentAmount] FLOAT,
[lockupPeriodValue] INT,
[lockupPeriodUnit] VARCHAR(50),
[noticePeriodValue] INT,
[noticePeriodUnit] VARCHAR(50),
[redemptionFrequencyValue] INT,
[redemptionFrequencyUnit] VARCHAR(50),
[redemptionFrequencyBasisDate] SMALLDATETIME,
[lockupEndDate] SMALLDATETIME,
[latestNoticeDate] SMALLDATETIME,
[nextRedemptionDate] SMALLDATETIME,
[fractionRedeemed] FLOAT,
[fractionRemaining] FLOAT,
[totalInvestmentAmount] FLOAT,
[trancheInvestmentFraction] FLOAT,
[trancheValue] FLOAT
)
AS
BEGIN
DECLARE @.activeTranches TABLE
(
[rcgFundId] INT,
[mgrFundId] INT,
[shareClass] VARCHAR(50),
[investmentDate] SMALLDATETIME,
[investmentAmount] FLOAT,
[noticePeriodValue] INT,
[noticePeriodUnit] VARCHAR(50),
[redemptionFrequencyValue] INT,
[redemptionFrequencyUnit] VARCHAR(50),
[redemptionFrequencyBasisDate] SMALLDATETIME,
[lockupPeriodValue] INT,
[lockupPeriodUnit] VARCHAR(50),
[lockupEndFrequencyValue] INT,
[lockupEndFrequencyUnit] VARCHAR(50),
[lockupEndBasisDate] SMALLDATETIME,
[fractionRedeemed] FLOAT,
[fractionRemaining] FLOAT,
[valuationDate] SMALLDATETIME,
[valuationVersion] INT,
[value] FLOAT
)
INSERT INTO @.activeTranches
SELECT *
FROM getActiveTranches_optimized(@.asOfDate)
INSERT INTO @.liquiditySchedule
SELECT
[AT].[valuationDate],
[AT].[valuationVersion],
[AT].[value],
[AT].[rcgFundId],
[RFN].[rcgFundName],
[AT].[mgrFundId],
[MN].[managerName],
[AT].[shareClass],
[AT].[investmentDate],
[AT].[investmentAmount],
[AT].[lockupPeriodValue],
[AT].[lockupPeriodUnit],
[AT].[noticePeriodValue],
[AT].[noticePeriodUnit],
[AT].[redemptionFrequencyValue],
[AT].[redemptionFrequencyUnit],
[AT].[redemptionFrequencyBasisDate],
[LD].[lastLockupEndDate],
[LD].[nextNoticeDate],
[LD].[nextRedemptionDate],
[AT].[fractionRedeemed],
[AT].[fractionRemaining],
[AT_TOTAL].[totalInvestmentAmount],
[trancheInvestmentFraction] = [AT].[investmentAmount] /
[AT_TOTAL].[totalInvestmentAmount]
FROM
@.activeTranches [AT]
INNER JOIN RcgFundNames [RFN]
ON [AT].[rcgFundId] = [RFN].[rcgFundId]
INNER JOIN ManagerNames [MN]
ON [AT].[mgrFundId] = [MN].[managerId]
INNER JOIN (
SELECT
[rcgFundId],
[mgrFundId],
[shareClass],
[totalInvestmentAmount] = SUM([investmentAmount])
FROM
@.activeTranches
GROUP BY
[rcgFundId],
[mgrFundId],
[shareClass]
) [AT_TOTAL]
ON [AT].[rcgFundId] = [AT_TOTAL].[rcgFundId]
AND [AT].[mgrFundId] = [AT_TOTAL].[mgrFundId]
AND ISNULL([AT].[shareClass], '') = ISNULL([AT_TOTAL].[shareClass], '')
INNER JOIN
getLiquidityDates(
@.asOfDate,
[AT].[investmentDate],
[AT].[noticePeriodValue],
[AT].[noticePeriodUnit],
[AT].[redemptionFrequencyValue],
[AT].[redemptionFrequencyUnit],
[AT].[redemptionFrequencyBasisDate],
[AT].[lockupPeriodValue],
[AT].[lockupPeriodUnit],
[AT].[lockupEndFrequencyValue],
[AT].[lockupEndFrequencyUnit],
[AT].[lockupEndFrequencyBasisDate],
[AT].[rolloverFrequencyValue],
[AT].[rolloverFrequencyUnit],
[AT].[rolloverFrequencyBasisDate]) [LD]
RETURN
END
GO
----
CREATE FUNCTION [dbo].[getLiquidityDates]
(
@.asOfDate SMALLDATETIME,
@.investmentDate SMALLDATETIME,
@.noticePeriodValue INT,
@.noticePeriodUnit VARCHAR(50),
@.redemptionFrequencyValue INT,
@.redemptionFrequencyUnit VARCHAR(50),
@.redemptionFrequencyBasisDate SMALLDATETIME,
@.lockupPeriodValue INT,
@.lockupPeriodUnit VARCHAR(50),
@.lockupEndFrequencyValue INT,
@.lockupEndFrequencyUnit VARCHAR(50),
@.lockupEndFrequencyBasisDate SMALLDATETIME,
@.rolloverFrequencyValue INT,
@.rolloverFrequencyUnit VARCHAR(50),
@.rolloverFrequencyBasisDate SMALLDATETIME
)
RETURNS @.liquidityDates TABLE
(
[lastLockupEndDate] SMALLDATETIME,
[nextNoticeDate] SMALLDATETIME,
[nextRedemptionDate] SMALLDATETIME
)
AS
BEGIN
DECLARE @.nextRedemptionDate SMALLDATETIME
SET @.nextRedemptionDate = dbo.getNextRedemptionDate(
@.asOfDate,
@.noticePeriodValue,
@.noticePeriodUnit,
@.redemptionFrequencyBasisDate,
@.redemptionFrequencyValue,
@.redemptionFrequencyUnit)
DECLARE @.lastLockupEndDate SMALLDATETIME
SET @.lastLockupEndDate = dbo.getLastLockupEndDate(
@.nextRedemptionDate,
@.investmentDate,
@.lockupPeriodValue,
@.lockupPeriodUnit,
@.lockupEndFrequencyBasisDate,
@.lockupEndFrequencyValue,
@.lockupEndFrequencyUnit,
@.rolloverFrequencyBasisDate,
@.rolloverFrequencyValue,
@.rolloverFrequencyUnit)
IF (@.nextRedemptionDate < @.lastLockupEndDate)
BEGIN
-- The projected next redemption date actually occurs during a
-- lockup period.
-- Assume there is at least one real redemption period between
-- rolling lockup periods.
SET @.nextRedemptionDate = dbo.getNextRedemptionDate(
@.lastLockupEndDate,
NULL, -- Don't count notice period; it occurs during lockup.
NULL,
@.redemptionFrequencyBasisDate,
@.redemptionFrequencyValue,
@.redemptionFrequencyUnit)
-- This next redemption date is guaranteed to be after the
-- first one, so sufficient notice period is also guaranteed.
END
DECLARE @.nextNoticeDate SMALLDATETIME
SET @.nextNoticeDate = dbo.DateAddVar(
@.noticePeriodUnit,
-@.noticePeriodValue,
@.nextRedemptionDate)
INSERT INTO
@.liquidityDates
SELECT
@.lastLockupEndDate,
@.nextNoticeDate,
@.nextRedemptionDate
RETURN
END
GO
---
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1136398951.939350.98470@.g47g2000cwa.googlegroups.com...
> dustbort wrote:
> Not without a proper repro. Your code may be proprietary but surely you
> could create a hypothetical example.
> It seems to me it ought to be possible to calculate these results
> in-line in a query unless they are somehow recursive. Whether they are
> recursive calculations or not is far from clear to me based on your
> narrative.
> Please also tell us what version of SQL Server you are using. In SQL
> Server 2005 you may be able to make use of the CROSS APPLY operator.
> --
> David Portas
> SQL Server MVP
> --
>|||I forgot to say that I am using
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1136398951.939350.98470@.g47g2000cwa.googlegroups.com...
> dustbort wrote:
> Not without a proper repro. Your code may be proprietary but surely you
> could create a hypothetical example.
> It seems to me it ought to be possible to calculate these results
> in-line in a query unless they are somehow recursive. Whether they are
> recursive calculations or not is far from clear to me based on your
> narrative.
> Please also tell us what version of SQL Server you are using. In SQL
> Server 2005 you may be able to make use of the CROSS APPLY operator.
> --
> David Portas
> SQL Server MVP
> --
>|||On Wed, 4 Jan 2006 14:31:50 -0500, "dustbort" <dustbort at yahoo dot
com> wrote:
>Here is the definition for the actual function that I refered to earlier as
>GetLiquidityInfo. Below it is the definition for getLiquidityDates. My
>logical table Liquidity roughly corresponds to @.activeTranches. (The
>function is not inline because it actually runs faster when @.activetranches
>is queried out first, because of the sources of the underlying data.
>The error I get is "Line 140: Incorrect syntax near 'AT'" which is the
>second argument of getLiquidityDates().
(snip)
Hi dustbort,
The error is here:
> INNER JOIN
> getLiquidityDates(
> @.asOfDate,
> [AT].[investmentDate],
> [AT].[noticePeriodValue],
> [AT].[noticePeriodUnit],
> [AT].[redemptionFrequencyValue],
> [AT].[redemptionFrequencyUnit],
> [AT].[redemptionFrequencyBasisDate],
> [AT].[lockupPeriodValue],
> [AT].[lockupPeriodUnit],
> [AT].[lockupEndFrequencyValue],
> [AT].[lockupEndFrequencyUnit],
> [AT].[lockupEndFrequencyBasisDate],
> [AT].[rolloverFrequencyValue],
> [AT].[rolloverFrequencyUnit],
> [AT].[rolloverFrequencyBasisDate]) [LD]
What you try to do is not permitted in SQL Server 2000. If you join to a
table-valued UDF in a query, then the arguments of that UDF can't be
columns from the other tables in the query.
Logically (*), the steps taken to resolve a join to a table-valued UDF
are:
1. Materialize the UDF (i.e. execute it and store the results);
2. Check rows from UDF to rows from other table(s) in the JOIN and
evaluate ON condition to see which combinations will be retained.
The [AT].[ColumnName] arguments can only be replaced by a single value
after performing step 2, yet step 1 already requires single values.
(*) In reality, SQL Server will prefer more efficient paths of
execution, when available; this explanation is purely intended to
explain the logic of this limitation.
Note that SQL Server 2005 introduces the CROSS APPLY operator that will
permit you to do things like this.
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
Hugo Kornelis, SQL Server MVP
Friday, February 17, 2012
Effect of Shrinking a DB
Just wondering, other than the effect of making the database smaller, how does the shrink call affect actual performance? Should I expect an performance increase after it is done? Thanks.
~piroGenerally all shrink does it reduce the size of the database files, no real performance gain as far as I know. You have to ask your self why did they grow to that size in the first place, before shrinking them as it is a performance hit when they have to grow again.|||Thank you for replying.
So there really is no performance decrease when I shrink the database. The performance hit will come from db growth, which is normal. Do you know of any other commands or functions that are similar to Access's compact command?
~piro|||Not sure what Access's compact command does, but DBCC SHRINKDATABASE can move all data to contiguous pages and remove any remaining unused space. This has the potential of reducing I/O since you would need to read fewer pages to access all data.|||Good point, forgot about the moving of data pages.
~piroGenerally all shrink does it reduce the size of the database files, no real performance gain as far as I know. You have to ask your self why did they grow to that size in the first place, before shrinking them as it is a performance hit when they have to grow again.|||Thank you for replying.
So there really is no performance decrease when I shrink the database. The performance hit will come from db growth, which is normal. Do you know of any other commands or functions that are similar to Access's compact command?
~piro|||Not sure what Access's compact command does, but DBCC SHRINKDATABASE can move all data to contiguous pages and remove any remaining unused space. This has the potential of reducing I/O since you would need to read fewer pages to access all data.|||Good point, forgot about the moving of data pages.
Subscribe to:
Posts (Atom)