ASP Programming Question (build a calendar and connect with SQL Se

Hi anyone,
I wish to build an ASP calendar table and when I click on the calendar
specific date, it will automatically add the date into the SQL Server table.
The table must be in Year, month and date
--
regards seowhuaHi
Please visit at http://www.aspfaq.com/show.asp?id=2519 written by Aaron
"seowhua" <seowhua@.discussions.microsoft.com> wrote in message
news:3552695A-4AF2-4AE9-9AA6-8723A1B33230@.microsoft.com...
> Hi anyone,
> I wish to build an ASP calendar table and when I click on the calendar
> specific date, it will automatically add the date into the SQL Server
> table.
> The table must be in Year, month and date
> --
> regards seowhua

ASP Coding build Calendar

Hi anyone,
my question is I wish to use ASP coding to develop a calendar which allow my
user select specific date and enter the date into the SQL database
--
regards seowhuaAnd which part do you need help with?... the popup calendar, or inserting
into db, or capturing the value in ASP and passing to DAL? Please be more
specific.
"seowhua" <seowhua@.discussions.microsoft.com> wrote in message
news:95CBD251-CEF2-4A04-83F6-BB4C97D7F1D3@.microsoft.com...
> Hi anyone,
> my question is I wish to use ASP coding to develop a calendar which allow
> my
> user select specific date and enter the date into the SQL database
> --
> regards seowhua|||Hi
You may want to look at this article
http://www.aspfaq.com/show.asp?id=2519
"seowhua" <seowhua@.discussions.microsoft.com> wrote in message
news:95CBD251-CEF2-4A04-83F6-BB4C97D7F1D3@.microsoft.com...
> Hi anyone,
> my question is I wish to use ASP coding to develop a calendar which allow
> my
> user select specific date and enter the date into the SQL database
> --
> regards seowhua

ASP Calender...

Hi,
Can I know how can I build the calender. Normally a calender image will
shown besides the date text box. When we click on it,a calender will pop up
and if we select a value from it, the date will be inserted in the
corresponding text area.
TQ
--
regards seowhuaI think you might be in the wrong newsgroups. This is the SQL group :)
Now, if you want a table to store a calendar :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"seowhua" <seowhua@.discussions.microsoft.com> wrote in message
news:51073539-0E05-4FB8-B932-CDCFB7444B31@.microsoft.com...
> Hi,
> Can I know how can I build the calender. Normally a calender image will
> shown besides the date text box. When we click on it,a calender will pop
> up
> and if we select a value from it, the date will be inserted in the
> corresponding text area.
> TQ
> --
> regards seowhua

Ascending Date Column Statistics "dbcc traceon( 2389 )"

I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>

AS2005. Strange MDX behaviour.

Hi, MDX gurus,

I have a unexplainable problem with pretty easy MDX.

Following MDX queries

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

provide the same result as expected.

This query

//
select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

returns as expected one member

but this query

select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

retuns nothing. This is strange, isn't it?
Can anybody explain it?

Thanks in advance,

Vladimir Chtepa

The puzzle for me is not why your fourth query doesn't return anything - I don't think it should - but why the third query does return July 2004. In your third query you're filtering the members on [Date].[Calendar].[Month] and for each one checking the currentmember on [Date].[Date] - but the currentmember should be the All Member on [Date].[Date] in all cases, as the following queries show:

with member measures.test as [Date].[Date].CurrentMember.membervalue

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

and

with member measures.test as [Date].[Date].CurrentMember.membervalue = CDate("11/07/2004")

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Very strange...

Chris

|||

I don't know why 3-d query returns "expected" result.

It will be great If anybody from developer team could explain it.

Arrays in SQL server

I am creating an application for booking events. Each event has several dates and each date has a fixed amount of available seats.

Currently in the events table I have a field for the number of available seats and a field for the string of dates that are later parsed into a listbox control.

The problem with my current setup is each date is sharing the same number of available seats so if date Event A is decemented 10 so will Event B.

I need some way to associate each date with it's own number of available seats. What is the best way to do this? An array? If so how do I store an array in the DB?

Thanks, Justin.::I need some way to associate each date with it's own number of available seats. What is
::the best way to do this? An array?

By NO means.

The best way is to apply proper standard procedures for REALATIONAL databases. There is something called "normalization", which, btw., is explained in EVERY beginner book about databases.

Som ,y advice is: your best way is to shell out some money and read a ookabout how a database works. Something simple, like "SQL for Dummies".

You will end up with multiple tables, and an event being on multiple occations will have multiple entries, one for every date.

This:

::and a field for the string of dates that are later parsed into a listbox control.

is something that you better make sure your boss does not see. I would terminate everyone doing something like this immediately for gross incompetence in 99.99% of the cases. The relational model is VERY clear with how things like this should be modelled, and has been so for the last 30-sth years. Your model is denormalized and it is sure you run into trouble.

Really, get a beginner book about databases.|||As Thona states - Normalization is the key.
when you build a denormalized database, your asking for trouble.

With limited knowledge of what your trying to accomplish, I would envision a structure more like this...

tblEvent
--EventId (autonumber)
--EventDescription

tblEventInstance
--EventInstanceId (autonumber)
--EventId (integer)
--EventDate (date/time)
--EventSeats (integer)

tblEventSignUp
--EventInstanceId (integer)
--Person

etc...

anytime you're packing multiple pieces of data into a single column you gotta ask yourself "why?"

Aritmetic Overflor on READING a date value

I have a date in my SQL Server Database of 3/12/2204. This is, of course, 200 years after today and is not a correct value.

However, when my repeater is trying to display the records in that table, I get the following error:

"Arithmetic overflow error converting expression to data type datetime."

Here is the stack trace:

[SqlException: Arithmetic overflow error converting expression to data type datetime.]
System.Data.SqlClient.SqlDataReader.Read() +157
System.Data.Common.DbEnumerator.MoveNext() +44
System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +504
System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +48
System.Web.UI.WebControls.Repeater.DataBind() +23

Now, none if this is in my own code, and so it is difficult to put any decent error-trapping. This is all in the built-in ASP.NET Repeater and SqlDataReader classes.

Why on Earth would there be an arithmetic overflow here? Note that the original table this is coming from has the date stored in the SqlDbType.DateTime format! In other words, why is there any conversion needed?

Perhaps it is converting the data from SqlDbType to DbType, but even then, what is so difficult about dealing with the year 2204? According to BOL, the DateTime data type should support up to the year 9999!

Note that when I access the table through Enterprise Manager and change the date, the error disappears. I tried a number of over-and-under tests, and I get this error on any date after 3/19/2151!

Does ASP.NET not support dates after that? I doubt it, but does anyone have an explanation for this?

Thanks,
JKalmost sounds like internally it's storing it as an unsigned integer (like milliseconds since a certain time), although I agree this seems buggy. this could be SQL Server data provider specific. are you sure you aren't using a smalldatetime, that's only valid up to 2079. don't think SQL Server would even let you enter 2204 in a smalldatetime column though.

wonder if this explains it (from BOL)

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

just thinking out loud...

cs|||Thanks,

I had originally thought that the smalldatetime might have been part of it when I first got this overflow error a few weeks ago.

What I did was change all of my table columns from smalldatetime to datetime, just to be on the safe side.

The problem seemed to disappear at that time, but then came back today, when I was able to test it a bit more thoroughly.

The real oddity is that SQL Server itself permits the value in the database. It reads, writes and displays the date fine with no error.

But when the SqlDataReader class pulls the next record in the data set containing that information, it runs into this arithmetic overflow.

Any other ideas?
JK

Arithmetic Overflow converting date error with UK dates but not with US dates.

Hi there,
My problem is that I have a DateTo dataset that receives a DateFrom
parameter. The default value of the DateFrom param is taken from a
DateDefault parameter value.
However, when I run the DateTo dataset, entering 27/03/2007, I get an
arithmetic overflow conversion error. If I enter US date 03/27/2007,
it works.
I am not sure why this happens because the dates use ISO (112) or ANSI
(102) formatting - I have tried with both of these.
I think it's to do with the conversion of dates, but I have not been
able to get a working version that does not use US dates.
DateFrom:
with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc
DateDefault:
with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte
DateTo:
with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
replace with '03/27/2007' succeeds
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc
I have attached a really simple RDL file that shows the problem. To
use it, simply change the datasource to any DB that has the standard
SSIS sysdtslog90 table in. This is automatically generated on the DB
when you turn on logging in SSIS to log to sql server.
Thanks in advance for any help!
RDL FILE
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="MyLoggingDB">
<DataSourceReference>MyLoggingDB</DataSourceReference>
<rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<ReportParameters>
<ReportParameter Name="DateFrom">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DateDefault</DataSetName>
<ValueField>DateValue</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Date From</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DateFrom</DataSetName>
<ValueField>DateValue</ValueField>
<LabelField>DateLabel</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<Height>5cm</Height>
</Body>
<rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DateFrom">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateDefault">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateTo">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc</CommandText>
<QueryParameters>
<QueryParameter Name="@.DateFrom">
<Value>=Parameters!DateFrom.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>To answer my own question and save others wasting time.
I have solved this issue now, by converting all the dates to use ISO
standard 20070327 format and pass this around to the parameters
instead of the full date.
Thanks
On 27 Mar, 17:54, "weelin" <wee...@.gmail.com> wrote:
> Hi there,
> My problem is that I have a DateTo dataset that receives a DateFrom
> parameter. The default value of the DateFrom param is taken from a
> DateDefault parameter value.
> However, when I run the DateTo dataset, entering 27/03/2007, I get an
> arithmetic overflow conversion error. If I enter US date 03/27/2007,
> it works.
> I am not sure why this happens because the dates use ISO (112) or ANSI
> (102) formatting - I have tried with both of these.
> I think it's to do with the conversion of dates, but I have not been
> able to get a working version that does not use US dates.
> DateFrom:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc
> DateDefault:
> with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte
> DateTo:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
> replace with '03/27/2007' succeeds
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc
> I have attached a really simple RDL file that shows the problem. To
> use it, simply change the datasource to any DB that has the standard
> SSIS sysdtslog90 table in. This is automatically generated on the DB
> when you turn on logging in SSIS to log to sql server.
> Thanks in advance for any help!
> RDL FILE
> <?xml version="1.0" encoding="utf-8"?>
> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
> 2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
> SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="MyLoggingDB">
> <DataSourceReference>MyLoggingDB</DataSourceReference>
> <rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
> rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>2.5cm</BottomMargin>
> <RightMargin>2.5cm</RightMargin>
> <PageWidth>21cm</PageWidth>
> <ReportParameters>
> <ReportParameter Name="DateFrom">
> <DataType>String</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DateDefault</DataSetName>
> <ValueField>DateValue</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>Date From</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>DateFrom</DataSetName>
> <ValueField>DateValue</ValueField>
> <LabelField>DateLabel</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>21cm</InteractiveWidth>
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ColumnSpacing>1cm</ColumnSpacing>
> <Height>5cm</Height>
> </Body>
> <rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
> <LeftMargin>2.5cm</LeftMargin>
> <DataSets>
> <DataSet Name="DateFrom">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateDefault">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateTo">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.DateFrom">
> <Value>=Parameters!DateFrom.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>16cm</Width>
> <InteractiveHeight>29.7cm</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>2.5cm</TopMargin>
> <PageHeight>29.7cm</PageHeight>
> </Report>

are you sql god?

Hi all,

I need the time that a stored procedure was created/altered.

In sysobjects table, creation date of the stored procedure is logged. But if the stored procedure is altered this column is not updated.

1. Is last alter date of a stored procedure loged somewhere?

2. If not, can I restrict users to "alter procedure" but not "drop/create procedure" so that they will have to drop and re-create procedures so sysobjects.crdate will always be updated.

I hope I will get a positive answer.

Ata

Besides the create_date, there is also a modify_date in the objects catalog. That is probably what you are looking for.

Thanks
Laurentiu

|||

If this is SQL Server 2005, you should query the DMV... sys.objects

This is taken from the Books-on-line (the sys.objects DMV.)

Column name	Data type	Description
name	sysname	Object name.
object_id	int	Object identification number. Is unique within a database.
schema_id	int	ID of the schema that the object is contained in. For all schema-scoped system objects that ship with SQL Server 2005, this value will always be IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA').
principal_id	int	ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint
parent_object_id	int	ID of the object to which this object belongs. 0 = Not a child object.
type	char(2)	Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint P = SQL stored procedure PC = Assembly (CLR) stored procedure FN = SQL scalar function FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function R = Rule (old-style, stand-alone) RF = Replication-filter-procedure SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TR = SQL DML trigger IF = SQL inlined table-valued function TF = SQL table-valued-function U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure IT = Internal table
type_desc	nvarchar(60)	Description of the object type. AGGREGATE_FUNCTION CHECK_CONSTRAINT DEFAULT_CONSTRAINT FOREIGN_KEY_CONSTRAINT PRIMARY_KEY_CONSTRAINT SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE SQL_SCALAR_FUNCTION CLR_SCALAR_FUNCTION CLR_TABLE_VALUED_FUNCTION RULE REPLICATION_FILTER_PROCEDURE SYNONYM SERVICE_QUEUE CLR_TRIGGER SQL_TRIGGER SQL_INLINE_TABLE_VALUED_FUNCTION SQL_TABLE_VALUED_FUNCTION USER_TABLE UNIQUE_CONSTRAINT VIEW EXTENDED_STORED_PROCEDURE INTERNAL_TABLE
create_date	datetime	Date the object was created.
modify_date	datetime	Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shipped	bit	Object is created by an internal SQL Server component.
is_published	bit	Object is published.
is_schema_published	bit	Only the schema of the object is published.

are you sql god?

Hi all,

I need the time that a stored procedure was created/altered.

In sysobjects table, creation date of the stored procedure is logged. But if the stored procedure is altered this column is not updated.

1. Is last alter date of a stored procedure loged somewhere?

2. If not, can I restrict users to "alter procedure" but not "drop/create procedure" so that they will have to drop and re-create procedures so sysobjects.crdate will always be updated.

I hope I will get a positive answer.

Ata

Besides the create_date, there is also a modify_date in the objects catalog. That is probably what you are looking for.

Thanks
Laurentiu

|||

If this is SQL Server 2005, you should query the DMV... sys.objects

This is taken from the Books-on-line (the sys.objects DMV.)

Column name	Data type	Description
name	sysname	Object name.
object_id	int	Object identification number. Is unique within a database.
schema_id	int	ID of the schema that the object is contained in. For all schema-scoped system objects that ship with SQL Server 2005, this value will always be IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA').
principal_id	int	ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint
parent_object_id	int	ID of the object to which this object belongs. 0 = Not a child object.
type	char(2)	Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint P = SQL stored procedure PC = Assembly (CLR) stored procedure FN = SQL scalar function FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function R = Rule (old-style, stand-alone) RF = Replication-filter-procedure SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TR = SQL DML trigger IF = SQL inlined table-valued function TF = SQL table-valued-function U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure IT = Internal table
type_desc	nvarchar(60)	Description of the object type. AGGREGATE_FUNCTION CHECK_CONSTRAINT DEFAULT_CONSTRAINT FOREIGN_KEY_CONSTRAINT PRIMARY_KEY_CONSTRAINT SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE SQL_SCALAR_FUNCTION CLR_SCALAR_FUNCTION CLR_TABLE_VALUED_FUNCTION RULE REPLICATION_FILTER_PROCEDURE SYNONYM SERVICE_QUEUE CLR_TRIGGER SQL_TRIGGER SQL_INLINE_TABLE_VALUED_FUNCTION SQL_TABLE_VALUED_FUNCTION USER_TABLE UNIQUE_CONSTRAINT VIEW EXTENDED_STORED_PROCEDURE INTERNAL_TABLE
create_date	datetime	Date the object was created.
modify_date	datetime	Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shipped	bit	Object is created by an internal SQL Server component.
is_published	bit	Object is published.
is_schema_published	bit	Only the schema of the object is published.

Are there any built in aritmetic capabilities on the Date and Time type?

Simply put, I have a 'Date and Time', (06/03/2006 11:40:00), passed to the SelectCommand via the QueryString. I would like to gain data that is between the supplied time and 1 hour prior.
Is there a simple way to take 1 hr off the 'Date and Time" value or is it necessary to build code that parses the string then adjusts it?

The 'Time' and 'TimeStamp' entities below are both of type 'Date and Time' and all values are gained from the same sql database. I.e. the time used as the basis for selecting the hr period is from the same database as the one where the hour period will be selected from.

Any help would be great.

SelectCommand="SELECT [Timestamp], [Volume] FROM [out8$] WHERE (([CustomerLvl1] = @.CustomerLvl1) AND ([Timestamp] = @.Timestamp))">

<SelectParameters>

<asp:QueryStringParameterDefaultValue="TNT Express"Name="CustomerLvl1"QueryStringField="Cust"

Type="String"/>

<asp:QueryStringParameterDefaultValue="20/02/2006 22:20:00"Name="Timestamp"QueryStringField="Time"

Type="DateTime"/>

Perhaps you could do something like this:

SELECT [Timestamp], [Volume] FROM [out8$] WHERE
[CustomerLvl1] = @.CustomerLvl1) AND [Timestamp] >= @.Timestamp ANDDATEDIFF(hh, [TimeStamp],getdate()) =1

|||For future reference, you can view all the Date and Time functions onMSDN here.