how to diaplay date in MM/dd/yyyy format

how to display date in mm/dd/yy using select query ( i have dates which are in mm/dd/yyyyy format but when i run a query it displaying yyyy-mm-dd format).i want to display date in mm/dd/yyyy format so how to write select query for that

This 'should' work:

SELECT convert( varchar(10), MyColumn, 101 )

For example, using today's date:

SELECT convert( varchar(10), getdate(), 101 )

-
05/25/2007

|||what is this 101 , 102 in code|||

It the style number which indicates how the system should transform your data.

Here is more detail.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||thanks a lot MVPsql

How to determine when and if SQL Agent job will run again?

I need to determine programmatically if and when a SQL Agent job will run next. (a date and time) Is this possible and if so, any suggestions on how? I need to maintain a table of the next pending execution for each job so I can allow the user to cancel the next pending job, but I have to show them the date and time on which it will occur. Since I'm using most of the scheduling capabilities of SQL Agent, I really need to get the next execution time that SQL Agent thinks the job should run rather than trying to calculate when I think it should run based on all the various scheduling scenarios. I'm using SMO in SQL Server 20005.

Thanks

Guess I couldn't see the tree for the forest. Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John Peterson
You have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.
|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com.. .
>
|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com... [vbcol=seagreen]
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
up[vbcol=seagreen]
of
>
|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Data..._20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would[vbcol=seagreen]
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
wake[vbcol=seagreen]
> up
kind
> of
>
|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com.. .
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get clut
tered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will sh
ow the last
time a database was accessed? If not, is there a relatively simple SQL-base
d solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
potentially log access to specific databases. I suppose I could have a "mon
itor" wake up
every X seconds and read the distinct databases that were in use -- but that
seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
>potentially log access to specific databases. I suppose I could have a "mo
nitor" wake up
>every X seconds and read the distinct databases that were in use -- but tha
t seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
>|||Understood. How about reading database logs? Would *that* show any activit
y? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
up[vbcol=seagreen]
of[vbcol=seagreen]
>|||Well...I noodled on using DBCC LOG to try and see if any of that information
might be
germane, but I don't think so (everything is "reset" on a restart of SQL Ser
ver).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was la
st affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html[/ur
l]
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? Ho
w
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
wake[vbcol=seagreen]
> up
kind[vbcol=seagreen]
> of
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activi
ty? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >You have to do some server level settings for that.
> >
> >SQL Server can log event information for logon attempts and you can view it
> >by reviewing the errorlog. By turning on the
> >auditing level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click all/success etc(required
> >option).
> >
> >You must stop and restart the server for this setting to take effect.
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> Login attempts won't tell you if a database has been used or
>> not. It will only tell you about server logins, not database
>> access. You would need to monitor this with profiler
>> going forward. I don't think there is any direct way to get
>> historical usage if you haven't been monitoring for such.
>> I suppose you could always drop the databases and then see
>> who calls complaining to determine if the databases are
>> being used. And you could use whatever criteria in
>> determining if you actually backup the databases before
>> dropping them. That could make for an interesting work day.
>> -Sue
>> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >You have to do some server level settings for that.
>> >
>> >SQL Server can log event information for logon attempts and you can view it
>> >by reviewing the errorlog. By turning on the
>> >auditing level of SQL Server.
>> >
>> >follow these steps to enable auditing of all/successfull connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click all/success etc(required
>> >option).
>> >
>> >You must stop and restart the server for this setting to take effect.
>|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> >potentially log access to specific databases. I suppose I could have a "monitor" wake
up
> >every X seconds and read the distinct databases that were in use -- but that seems kind
of
> >heavy handed...
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> >> Login attempts won't tell you if a database has been used or
> >> not. It will only tell you about server logins, not database
> >> access. You would need to monitor this with profiler
> >> going forward. I don't think there is any direct way to get
> >> historical usage if you haven't been monitoring for such.
> >> I suppose you could always drop the databases and then see
> >> who calls complaining to determine if the databases are
> >> being used. And you could use whatever criteria in
> >> determining if you actually backup the databases before
> >> dropping them. That could make for an interesting work day.
> >>
> >> -Sue
> >>
> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >>
> >> >You have to do some server level settings for that.
> >> >
> >> >SQL Server can log event information for logon attempts and you can view it
> >> >by reviewing the errorlog. By turning on the
> >> >auditing level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of all/successfull connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click all/success etc(required
> >> >option).
> >> >
> >> >You must stop and restart the server for this setting to take effect.
> >>
> >
>|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> > Triggers on system tables isn't supported though and
> > sysprocesses is only materialized when used so there
> > wouldn't even be something to put a trigger on.
> >
> > -Sue
> >
> > On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> > >potentially log access to specific databases. I suppose I could have a "monitor"
wake
> up
> > >every X seconds and read the distinct databases that were in use -- but that seems
kind
> of
> > >heavy handed...
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> > >> Login attempts won't tell you if a database has been used or
> > >> not. It will only tell you about server logins, not database
> > >> access. You would need to monitor this with profiler
> > >> going forward. I don't think there is any direct way to get
> > >> historical usage if you haven't been monitoring for such.
> > >> I suppose you could always drop the databases and then see
> > >> who calls complaining to determine if the databases are
> > >> being used. And you could use whatever criteria in
> > >> determining if you actually backup the databases before
> > >> dropping them. That could make for an interesting work day.
> > >>
> > >> -Sue
> > >>
> > >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> > >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> > >>
> > >> >You have to do some server level settings for that.
> > >> >
> > >> >SQL Server can log event information for logon attempts and you can view it
> > >> >by reviewing the errorlog. By turning on the
> > >> >auditing level of SQL Server.
> > >> >
> > >> >follow these steps to enable auditing of all/successfull connections with
> > >> >Enterprise Manager in SQL Server:
> > >> >
> > >> >Expand a server group.
> > >> >Right-click a server, and then click Properties.
> > >> >On the Security tab, under Audit Level, click all/success etc(required
> > >> >option).
> > >> >
> > >> >You must stop and restart the server for this setting to take effect.
> > >>
> > >
> >
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
>> Triggers on system tables isn't supported though and
>> sysprocesses is only materialized when used so there
>> wouldn't even be something to put a trigger on.
>> -Sue
>> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>> >potentially log access to specific databases. I suppose I could have a "monitor" wake
>up
>> >every X seconds and read the distinct databases that were in use -- but that seems kind
>of
>> >heavy handed...
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> >> Login attempts won't tell you if a database has been used or
>> >> not. It will only tell you about server logins, not database
>> >> access. You would need to monitor this with profiler
>> >> going forward. I don't think there is any direct way to get
>> >> historical usage if you haven't been monitoring for such.
>> >> I suppose you could always drop the databases and then see
>> >> who calls complaining to determine if the databases are
>> >> being used. And you could use whatever criteria in
>> >> determining if you actually backup the databases before
>> >> dropping them. That could make for an interesting work day.
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >>
>> >> >You have to do some server level settings for that.
>> >> >
>> >> >SQL Server can log event information for logon attempts and you can view it
>> >> >by reviewing the errorlog. By turning on the
>> >> >auditing level of SQL Server.
>> >> >
>> >> >follow these steps to enable auditing of all/successfull connections with
>> >> >Enterprise Manager in SQL Server:
>> >> >
>> >> >Expand a server group.
>> >> >Right-click a server, and then click Properties.
>> >> >On the Security tab, under Audit Level, click all/success etc(required
>> >> >option).
>> >> >
>> >> >You must stop and restart the server for this setting to take effect.
>> >>
>> >
>sql

How to determine min. datetime in mdx

Say I have 2 columns, id and date as follow:

id date

1 11-Jan-2007

1 18-Jun-2007

2 21-Mar-2007

2 19-Sep-2007

How to write a mdx function to filter out on each id with the min date as follow:

id date

1 11-Jan-2007

2 21-Mar-2007

I would use ID as an dimension and this table as fact table - the date for a measure with the min aggregate function and then "select Dimension.ID on rows, measures.mindate on columns from cube"

HANNES

How to determine last "monthversary" date of an account?

I work on a project where advertisers on a classifieds Web site have the
possibility to place a certain number of ads per month.
The difficulty comes from the fact that a month period starts at each
"monthversary", meaning that if somebody created his account on June 15, I
would need to take into account the ads placed since the 15th of this
month...
I am simply trying to see how I can extract, in a very compact way, the last
"monthversary" date knowing the date the account was created and the current
date.
Right now I am using a method that works: I extract the day from the
creation date, prepend the current month and append the year, then:
1- if the "monthversary" is after today's date, go back one month
2- take care of the 30-day months issue and of of February as well (even
leap year)
3- take care of the year changes
And then I have a string that represents the last "monthversary" date, but I
am sure there is a simple, one-liner way to do this.
Thanks a lot!White,
Can you post the DDL and sample data? Sounds like the MAX function may work
here.
HTH
Jerry
"White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>I work on a project where advertisers on a classifieds Web site have the
>possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the
> last "monthversary" date knowing the date the account was created and the
> current date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
> I am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>|||lookup datedd and datepart in BOL
select dateadd(m,-1,getdate())
http://sqlservercode.blogspot.com/
"White Echo" wrote:

> I work on a project where advertisers on a classifieds Web site have the
> possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the la
st
> "monthversary" date knowing the date the account was created and the curre
nt
> date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
I
> am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>
>|||Sorry, different computer if you see what I mean.
But the function is something that I could also use at this computer.
Simply put:
If I opened my account on June 15 2005, what function would help me to
determine what would be the day of the beginning of the current account
period, knowing that an accounting period starts at each "monthversary"?
(By the way I was surprised to see that the expression "monthversary" is out
there).
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> White,
> Can you post the DDL and sample data? Sounds like the MAX function may
> work here.
> HTH
> Jerry
> "White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
> news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>|||Nicolas,
Sometimes data works better than narratives. Could you post me some sample
data i.e, here is the date the account was opened, here is the monthversary,
and here is the date I want with x being the current date? Sample data -->
desired results?
Thanks
Jerry
"Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote
in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
> Sorry, different computer if you see what I mean.
> But the function is something that I could also use at this computer.
> Simply put:
> If I opened my account on June 15 2005, what function would help me to
> determine what would be the day of the beginning of the current account
> period, knowing that an accounting period starts at each "monthversary"?
> (By the way I was surprised to see that the expression "monthversary" is
> out there).
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||This only removes on month from Today's date... It does not help.
> lookup datedd and datepart in BOL
> select dateadd(m,-1,getdate())
> http://sqlservercode.blogspot.com/
> "White Echo" wrote:
>|||I thought I did this, I am sorry.
For instance:
Account created on June 15, 2005.
Today's date: October 21, 2005.
The result here would be: October 15, 2005
Account created on January 31, 2004.
Today's date: October 21, 2005.
The result here would be: September 30, 2005
If the account is created on June 15, 2005...
The first "month" started on July 15, 2004.
The second "month" on August 15, 2004
Etc...
But what I need to calculate is when the current "month" started for this
account.
The start day of a month is not the first day of a month, but rather what I
call the "monthversary": the same day number as that of the creation date.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elZhW9m1FHA.904@.tk2msftngp13.phx.gbl...
> Nicolas,
> Sometimes data works better than narratives. Could you post me some
> sample data i.e, here is the date the account was opened, here is the
> monthversary, and here is the date I want with x being the current date?
> Sample data --> desired results?
> Thanks
> Jerry
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
>|||On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
(snip)
>Right now I am using a method that works: I extract the day from the
>creation date, prepend the current month and append the year, then:
>1- if the "monthversary" is after today's date, go back one month
>2- take care of the 30-day months issue and of of February as well (even
>leap year)
>3- take care of the year changes
>And then I have a string that represents the last "monthversary" date, but
I
>am sure there is a simple, one-liner way to do this.
Hi White Echo,
Not sure if it's simpler, but you could write it in one line (though I
prefer slightly more formatting - read this emssage with a fixed font
for best effect), and it has the definite advantage that it can be used
in a query to process all rows at once (though my example uses only a
variable):
DECLARE @.StartDate datetime
SET @.StartDate = '20050615'
SELECT DATEADD(month,
DATEDIFF(month,
@.StartDate,
CURRENT_TIMESTAMP)
- CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
THEN 1
ELSE 0
END,
@.StartDate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much, my friend!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7mmil1t3gtgk6ktgcisgteocp120egtvlh@.
4ax.com...
> On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
> (snip)
> Hi White Echo,
> Not sure if it's simpler, but you could write it in one line (though I
> prefer slightly more formatting - read this emssage with a fixed font
> for best effect), and it has the definite advantage that it can be used
> in a query to process all rows at once (though my example uses only a
> variable):
> DECLARE @.StartDate datetime
> SET @.StartDate = '20050615'
> SELECT DATEADD(month,
> DATEDIFF(month,
> @.StartDate,
> CURRENT_TIMESTAMP)
> - CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
> THEN 1
> ELSE 0
> END,
> @.StartDate)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to determine date/time message was placed onto Queue?

I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.

Which time are you after. The time it was put on the initiator queue or the target queue?

You can add the time to the message when you send it.

|||

Ideally, both.

I was looking for something out-of-the-box... not something where I craft my own message. The messages I will be receiving are based on a pre-defined schema and I did not want to go back to the design table with adding new elements. You would think that the "queue" table would have an additional column for "created date/time" basically.

If anything else, I think that should be a consideration for a future enhancement in the next version of Broker.

|||

Please add a suggestion on connect.microsoft.com/sqlserver/feedback.

Make usre you say exactly which date you are after.

|||There would be a performance penalty for storing SENT and ENQUEUED times into the target queue for each message even if the user was not really interested in those statistics. Our current model is that any user-specific data (sent-timestamp, order-number, request-id, message-id, sender-user-name, etc) would be encapsulated into the message_body itself.

How to determine as to when an index was last rebuilt.

Hi,
Is it possible to determine the date/time as to when an index was last creat
ed / rebuilt.
ThanxHi,
SQL server will not store the index creation or modification dates.
Date and time the statistics were last updated can be viewd using the
command.
DBCC SHOW_STATISTICS ( table , index_name)
Thanks
Hari
MCDBA
"Ramesh" <Ramesh@.discussions.microsoft.com> wrote in message
news:6CBA7E49-EB22-4EA2-A185-134B94A726B9@.microsoft.com...
> Hi,
> Is it possible to determine the date/time as to when an index was last
created / rebuilt.
> Thanx

How to determine as to when an index was last rebuilt.

Hi,
Is it possible to determine the date/time as to when an index was last created / rebuilt.
Thanx
Hi,
SQL server will not store the index creation or modification dates.
Date and time the statistics were last updated can be viewd using the
command.
DBCC SHOW_STATISTICS ( table , index_name)
Thanks
Hari
MCDBA
"Ramesh" <Ramesh@.discussions.microsoft.com> wrote in message
news:6CBA7E49-EB22-4EA2-A185-134B94A726B9@.microsoft.com...
> Hi,
> Is it possible to determine the date/time as to when an index was last
created / rebuilt.
> Thanx

How to delete rows from a 15 million row table

Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

EdHi,
y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
Regards,
Ramya

Originally posted by KungFuJoe
Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

Ed|||I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.|||I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.

Ed

Originally posted by ramya
Hi,
y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
Regards,
Ramya|||Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

Ed

Originally posted by austrian_ead
I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.|||Originally posted by KungFuJoe
Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

Ed

I am sorry, I don't have it anymore, but the code was not complicated:

declare @.columnx integer
declare cur_test cursor for
select top 10000 0 as columnx from bigtable
for update
open cur_test
fetch cur_test into @.columnx
while @.@.fetch_status = 0
begin
delete from bigtable where current of cur_test
fetch cur_test into @.columnx
end
close cur_test
deallocate cur_test

it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.|||Thanks for your help :)

I'll give it a shot.

Ed

Originally posted by austrian_ead
I am sorry, I don't have it anymore, but the code was not complicated:

declare @.columnx integer
declare cur_test cursor for
select top 10000 0 as columnx from bigtable
for update
open cur_test
fetch cur_test into @.columnx
while @.@.fetch_status = 0
begin
delete from bigtable where current of cur_test
fetch cur_test into @.columnx
end
close cur_test
deallocate cur_test

it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.|||what's the secret there? bcp out the records you want to keep, alter table tbl nocheck constraint all, truncate, bcp in!|||Yeah, but he says the table is used 24/7 and it can't be down at all. It's heavy use is probably why the deletes are taking so long.

Maybe he should try turning off loging, deleting his records, and then turning it back on again and starting a new backup cycle.

Otherwise, I think his best bet is to keep deleteing them in small batches, and as separate transactions.

I don't see what he gets out of using a cursor for this.|||Originally posted by KungFuJoe
I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.
Ed

What is that there is no maint window?

Also, is there an index on the column?

Have you checked out sp_lock? what processes are going on?

Is this a web based app?

And you say 3 months is a 1/3 of the data...that's a lot of data AND I'm sure it's going to continue to grow...

You seriously need to understand the growth of this monster and plan an archival strategy...what type of data btw is ancient history after that period of time...don't you track a status or something...or is like phone records?

what does your app do?|||Actually, my understanding is that the database needs to be 24/7, not the table. Besides, the poster already mentioned making several attempts to delete from this table, during which it WAS inaccessible for anything other than SELECT with READ_UNCOMMITTED isolation level.|||Originally posted by KungFuJoe
I can't risk bringing this table down for any amount of time...|||I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on . I need something that will work while the database is still up.

Ed|||Well if it isn't the SQL Server hardcore !!!

How you dudes doing ?

Here is a novel way of removing records from a HUGE table.

You could turn Select Into / Bulk Copy on

Perform a select into non-logged operation using a where clause to filter the records into another table. If you have an index on the column with the where clause on it then should be fast.

reindex the new table.

Perfom a two sp_renames of the two tables.

If you are prepared to have you db down for the time it takes perform the two sp_renames then you are jammin'!

Got to love those non - logged op's.|||Here we go again...and how this better? You forgot about recompiles that you'll have to do...got enough time to spare?|||Sorry mate,

I never said I had this in a stored proc.

Used to work in a large datawarehouse and found this ad hoc method was useful when I wanted to avoid using a delete.

Since this was the original question - I thought it might be an alternate option if he could handle downtime for the time of 2 sp_rename's

Cheers|||Don't apologize...unless the poster comes back with more info, everything is speculative...|||I'll second Aldo2003's tip to reduce the affect of downtime and to get the optimum performance one should arrange a downtime window to accomplish the task easily and fastly.

It would be ideal if downtime can be agreed by business and will be faster when no other process is accessing the database.

When you're getting something you should be ready to sacrifice something...:)|||So, you're all saying that "SELECT * INTO..." is faster than TRUNCATE?

Anyone for a test?|||No, they aren't trying to optimize the total process time, just the table down-time. So they aren't counting the SELECT because it doesn't lock up the table.

I think they are assuming that no new data will be added to the table between the SELECT INTO NEWTABLE and the RENAME, and I bet that this is not the case.|||Actually, SELECT will placed a shared lock on the table, unless you change the isolation level. But the point is that BCPing only records that you need paired with truncating the table is faster than any other solution, and they don't seem to see it that way. I, in turn, don't see anything else as an alternative. Downtime is a downtime is a downtime. It's just a matter of how much down, and ... what's the time? It's that time, when I go a have my mid-day smoke break off campus!!!|||You have to leave the campus?

This from a state where drinking and driving where basically legal up to a short while ago?|||Well, we got this mayor Garza, all fitness-oriented (I can still probably kick his butt without getting off the bar stool), and our glorious and victorious CEO decided to come up with a "we're doing you good" initiative by declaring it on the local news. Reality (we have our ways [needs to be pronounced with a heavy Russian accent]) shows that the company got a 10% insurance reduction for making it a non-smoking campus. So, smoking, non-smoking, or smoke up someone's a$$? That's the dilemma ;)|||Have they banned smoking in bars?

Did you know, NYC (The new Rome) has banned smoking in all bars throughout the entire city...

NEW YORK F____'N CITY!

I mean give me a break...

You can buy crack on the corner but no cig...|||Technically, I believe that crack has been illegal in NYC for quite a while...|||Originally posted by Brett Kaiser
This from a state where drinking and driving where basically legal up to a short while ago? What, they've closed the drive up windows on the liquor stores in Texas ? Say it isn't so!

-PatP|||Originally posted by blindman
Technically, I believe that crack has been illegal in NYC for quite a while... Ok, so what's your point here ?

-PatP|||Originally posted by Brett Kaiser
Have they banned smoking in bars?

Did you know, NYC (The new Rome) has banned smoking in all bars throughout the entire city...

NEW YORK F____'N CITY!

I mean give me a break...

You can buy crack on the corner but no cig...

<RANT>
Not just New York City...since last summer it's the whole state...thank god we built a shack behind our building to shelter us from this wonderful weather...to make matters worse NYS also added a line to the tax forms this year to make us pay the maximum amount of tax possible..they want us to estimate goods we purchased outside our county, on reservations (where we get our cigs), and online...*&$&^ politicians...

</RANT>|||Starting tomorrow, going to the gym

How about banning McDonalds, what's up with this no supersizing $%&#?|||Originally posted by rdjabarov

Starting tomorrow, going to the gym

How about banning McDonalds, what's up with this no supersizing $%&#?

..its all about cutting the budgets...actually i think they will move supersizing overseas (not to bring that subject up AGAIN)...|||that's a good one, here they went through 3 indians, with the same outcome. and how do you like an answer to a questions "what's a trigger?"

"it's when you move your mouse, because that's what oracle objects is all about"!!!

How to delete row 169441?

MSSQL database table DATE field has corruption. The error message says it is at row 169441. I cannot finish an export, browse to the record or delete it. I got the bad record below by exporting to a text file and this was the last record before it stopped.

This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"

This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"Can you select that row with this where clause?

where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||If there is no "key" on this table you could try:

Backup your database

Set RowCount 169440 -- Process only first nnnn rows.
Select * Into #TMP_Table from YourTable
Set RowCount 169441 -- Process only first nnnn rows.
Delete From YourTable -- Should delete all rows copied plus the bad one.
Set RowCount 0 -- Process all rows
Insert Into @.TMP_Table Select * From YourTable -- Copy the rest

This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!

OR

You could try some of the undocumented DBCC commands to force the data.

Good luck.|||This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"

This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"
You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?

We can't diagnose the problem like this.
Please take the time to post correct data with a complete and careful description if you want correct answers.