Can I find change log information for a DTS

Hi,
Is it possible to find the date n time when a particular DTS was changed. All that I can see is thecreation date of a DTS :(
Will appreciate your help.Hi, try right-clicking the package and select Versions. it could give you more info on package history. mojza|||Thanks mojza :) That helps and though it says 'create date' I can use this information as the last modification date.

Many thanks once again.

Can I do this in one SQL statement

Let's say that I have a database that has customer name, invoice date, and
amount in it. If an entry exists in this database, then the company owes us
money. I am trying to put together a report that, for each company, details
how much is owed that is 30 days out from a given date, how much is owed
that is 60 days out, 90 days out, and greater than that.
The first statement would look a lot like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
(DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
The second statement looks like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
(DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
And so on. But, then I have to cut-and-paste this into Excel, and massage
it a bit to get
Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
Is there any way to get all these in one SQL statement so I don't have to
massage the data?
Thank you.
JoshuaTry,
SELECT
[name],
sold,
SUM(case when invdate >= dateadd(day, -30, convert(char(8), getdate(),
112)) then owed end) AS Amount30DaysDue,
SUM(case when invdate >= dateadd(day, -60, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -30, convert(char(8), getdate(), 112)) then
owed end) AS Amount60DaysDue,
SUM(case when invdate >= dateadd(day, -90, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -60, convert(char(8), getdate(), 112)) then
owed end) AS Amount90DaysDue,
FROM
InvOwed
GROUP BY name, sold
ORDER BY name
go
AMB
"Joshua Campbell" wrote:

> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
us
> money. I am trying to put together a report that, for each company, detai
ls
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>
>|||Try:
select name,sold,sum(case when datediff(d,invdate,'20040304') between 1 and
30 then owed else 0 end) [30day],
sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
else 0 end) [60day],
sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
else 0 end) [90day]
from InvOwed
group by name,sold
-oj
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
> us
> money. I am trying to put together a report that, for each company,
> details
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>|||I didn't know you could use case like that. Excellent. Thank you very
much!
"oj" <nospam_ojngo@.home.com> wrote in message
news:eoXDR5UOFHA.2468@.tk2msftngp13.phx.gbl...
> Try:
> select name,sold,sum(case when datediff(d,invdate,'20040304') between 1
and
> 30 then owed else 0 end) [30day],
> sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
> else 0 end) [60day],
> sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
> else 0 end) [90day]
> from InvOwed
> group by name,sold
> --
> -oj
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
and
owes
AND
massage
to
>

Can date ranges affect query plans?

Howdy. This seems strange to me, but maybe it is to be expected? I have two
queries that produce the same results.
When I use older date ranges(01/01/04 = 01/31/04), query 1 returns
consistently in 0-1 seconds, and query 2 returns in about 4 seconds.
But then when I use more recnt date ranges(01/01/06 - 01/31/06), I have to
cancel query 1 as it still hasnt completed after 10 minutes, while query 2
returns in about 10 seconds.
How can it be?
TIA, ChrisRWhat do the execution plans for the queries show?
SET SHOWPLAN_TEXT ON
--run your queries
Guess: Query1 will show a full table (or clustered index) scan for the date
range 1/1/06 - 1/31/06.
"ChrisR" wrote:

> Howdy. This seems strange to me, but maybe it is to be expected? I have tw
o
> queries that produce the same results.
> When I use older date ranges(01/01/04 = 01/31/04), query 1 returns
> consistently in 0-1 seconds, and query 2 returns in about 4 seconds.
> But then when I use more recnt date ranges(01/01/06 - 01/31/06), I have to
> cancel query 1 as it still hasnt completed after 10 minutes, while query 2
> returns in about 10 seconds.
> How can it be?
> TIA, ChrisR

Can Code be used to define parameters in a subscription?

I am trying to create an email subscription that runs every Monday for the previous week. To do this I was trying to set the start date parameter to dateadd("d", -7, today()) and the end date parameter to dateadd("d", -1, today()). However, every time I change it to anything but a static date value, the screen refreshes and changes the code back to the default date.

I know I can set the defaults to the previous week in the report itself but I already have another subscription that runs the same report as MTD so the parameter defaults are set to that. The only other way I can think to accomplish this is to create a copy of the report with the only difference being parameter defaults (which I don't think is a good solution).

Any suggestions are welcome.

You said that you have the other report run for MTD. What do you mean by that?|||Sorry, Month To Date (MTD)|||Does anyone know if this is possible? I still have not found a solution.

can anyone tell me why the following sql does not work?

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id

(INNER JOIN category C on H.CategoryId = C.CategoryId)

order by date DESC

I'm not sure what you mean by "does not work" and you didn't post an error message.

However, it's possible you are using the reserved word Date in the order by clause.

Did you mean "order by PublishDate DESC"?

|||it saysIncorrect syntax near the keyword 'INNER'.|||

Try to remove the brace that encloses Inner Join and your statement will work fine

Hope my suggestion helps

|||

thanks

Can any one explain this query behavior

db_TBOdb_TBT
----------
------------------
TBOID | Date TBTID| TBOID| Date
--------------------------
rp01 | 01/08/2006 ap01 |rp01|02/08/2006
many rows ap02 | rp01 |05/08/2006
ap03 |rp03|04/08/2006

I want to find TBTTD field of table db_TBT
who have of db_TBO date db_TBT table date
and TBOID should be 'rp01' of both tables

when I give a query as it works as I needed

Select TBT.Date
from db_TBO TBO , db_TBT TBT
where TBT.TBOID = 'rp01'
and TBO.TBOID ='rp01'
and TBO.Date TBT.Date

My doubt is when I run the following query
" Select TBT.Date from db_TBO TBO , db_TBT TBT where TBT.TBOID = 'rp01'
"
it gives me more than 7 records

when I run the query using some change
" Select TBT.Date , TBT.TBID from db_TBO TBO , db_TBT TBT where
TBT.TBOID = 'rp01' "
it gives more rows than previous row

Can u give me any explations.

Thanks
PaiOn 23 Aug 2006 22:54:21 -0700, pai wrote:

Quote:

Originally Posted by

>db_TBOdb_TBT
>----------
>------------------
>TBOID | Date TBTID| TBOID| Date
>--------------------------
>rp01 | 01/08/2006 ap01 |rp01|02/08/2006
>many rows ap02 | rp01 |05/08/2006
> ap03 |rp03|04/08/2006
>
>I want to find TBTTD field of table db_TBT
>who have of db_TBO date db_TBT table date
>and TBOID should be 'rp01' of both tables
>

(snip)

Quote:

Originally Posted by

>
>My doubt is when I run the following query
>" Select TBT.Date from db_TBO TBO , db_TBT TBT where TBT.TBOID = 'rp01'
>"
>it gives me more than 7 records

Hi Pai,

Of course it does. The FROM clause lists two tables, comma-seperated.
That means that you'll get a cross join, also known as cartesian
product, between the two tables: each row from the first table will be
paired with each row from the second table. After that (*), the WHERE
clause removes rows based on TBT.TBOID. The result set willl include
only TBT-rows with TBOID equal to 'rp01' - but each of those rows will
still be paired against each of the rows in db_TBO.

Quote:

Originally Posted by

>when I run the query using some change
>" Select TBT.Date , TBT.TBID from db_TBO TBO , db_TBT TBT where
>TBT.TBOID = 'rp01' "
>it gives more rows than previous row

I don't understand this. The only difference between this query and the
previous query is the addition of one more column in the WHERE clause.
That shoould never affect the number of rows returned. Are you sure that
you didn't make a mistake when you copied the SQL into your message?

(snipped from above:)

Quote:

Originally Posted by

>when I give a query as it works as I needed
>
>Select TBT.Date
>from db_TBO TBO , db_TBT TBT
>where TBT.TBOID = 'rp01'
>and TBO.TBOID ='rp01'
>and TBO.Date TBT.Date

Indeed. This query also starts (*) with the cross join, but then retains
only rows with both TBT.TBOID and TBO.TBOOID equal to 'rp01'. That means
that you're left with each TBT-row for 'rp01' paired to each TBO-row for
'rp01'. These results are then further filtered by the date comparison.

I think your problems arise out of the use of the "old-style" FROM
clause. The newer style, with explicit joins, makes it much harder to
make this kind of mistakes since it forces you to explicitly write down
the join criteria:

SELECT some columns
FROM db_TBO AS TBO
INNER JOIN db_TBT AS TBT
ON TBO.TBOID = TBT.TBOID
WHERE TBO.Date TBT.Date;

(*) The order of evaluation described here is only a logical explanation
of the process. The query optimizer is free to (and definitely will, in
this case) change the order of evaluation, as long as the results remain
the same.

--
Hugo Kornelis, SQL Server MVP

Can "Date Modified" col be automatically updated w/o trigger for each table?

Hello,

I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?

Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.

Thanks!

Using triggers is the only way I can think out for this issue, as you want to record every modification to each table:)

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
Maury
I do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried..
.
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried
..
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will n
ot
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:
> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling the user defined function!

hai,

the problem is - I have created a userdefined function using SQL 2000

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)

return @.getfulldate
end

and normally we call this in the SQL statements as

select *, dbo.getfulldate('2006-05-03') from emp

This works fine and what I need was, I need to invoke the user-defined function like

select *, getfulldate('2006-05-03') from emp that is, without using "dbo".

If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.

So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.

anyone guide me, thanks!

Hi,

User defined functions can not be called with schema's reference. You must include schema "dbo." reference on each function calls.

|||

however we are able to call without the reference of dbo, functions that return table except scalar values...that is Why?...so there must be some thing to known as how to call without dbo reference. also visit

http://microsoft.apress.com/asptodayarchive/73823/sql-user-defined-functions

waiting for reply

|||

Hi anandh2007,

You are right. We can call a table-value functin without schema name while we must specify the schema name when calling a scalar value function.

This problem is by design (something relating the sql-server compiler),see the online book, I've bolderd the important part

Function Invocation

Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement.

Scalar-valued functions must be invoked by using at least the two-part name of the function.

For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. For more information, see Executing User-defined Functions (Database Engine).

Hope my suggestion helps