I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?
It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
sql
Showing posts with label blah. Show all posts
Showing posts with label blah. Show all posts
Wednesday, March 28, 2012
Query works in 2000 but not 2005
I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
Query works in 2000 but not 2005
I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete an
d
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete an
d
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
Saturday, February 25, 2012
Query the last access data/time
Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB
|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>
|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB
|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>
|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
Query the last access data/time
Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||This is a multi-part message in MIME format.
--040107090904080005040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>
>
--040107090904080005040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Or alternately, you could create a profiler trace (with
sp_trace_create and the other trace procs) and set it to run when SQL
server starts (with sp_procoption). Then you could query the output of
that trace (if you wanted to query it with T-SQL you'd import the trace
output file (open it in Profiler and then SaveAs... a Trace Table...)
into a table and query that table). This is a little kludgey and
fairly expensive (the running profiler trace that is) in terms of
resources on the SQL box but it would work as long as you set up your
trace appropriately (it would take a bit of fine tuning).<br>
<br>
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.<br>
<br>
HTH.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<em><font face="Tahoma" size="2"> mallesons</font><font face="Tahoma"> </font><font
face="Tahoma" size="2">stephen</font><font face="Tahoma"> </font><font
face="Tahoma" size="2"> jaques</font></em><font face="Tahoma"><br>
</font><font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a></font></span> </p>
</div>
<br>
<br>
Hari Prasad wrote:
<blockquote cite="mid%23LLkY7teFHA.900@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:jbngspam@.yahoo.com"><jbngspam@.yahoo.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:42bf9544$0$18637$14726298@.news.sunsite.dk">news:42bf9544$0$18637$14726298@.news.sunsite.dk</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040107090904080005040400--|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>>Hi,
>>SQL Server will not store these information. But for new object creation you
>>can see the CRDATE column in sysobjects table.
>>But for Insert/update and delete you need to write trigger to populate a
>>audit table. Later you could use the audit table.
>>Thanks
>>Hari
>>SQL Server MVP
>>"John B" <jbngspam@.yahoo.com> wrote in message
>>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>>
>>
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||This is a multi-part message in MIME format.
--040107090904080005040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>
>
--040107090904080005040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Or alternately, you could create a profiler trace (with
sp_trace_create and the other trace procs) and set it to run when SQL
server starts (with sp_procoption). Then you could query the output of
that trace (if you wanted to query it with T-SQL you'd import the trace
output file (open it in Profiler and then SaveAs... a Trace Table...)
into a table and query that table). This is a little kludgey and
fairly expensive (the running profiler trace that is) in terms of
resources on the SQL box but it would work as long as you set up your
trace appropriately (it would take a bit of fine tuning).<br>
<br>
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.<br>
<br>
HTH.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<em><font face="Tahoma" size="2"> mallesons</font><font face="Tahoma"> </font><font
face="Tahoma" size="2">stephen</font><font face="Tahoma"> </font><font
face="Tahoma" size="2"> jaques</font></em><font face="Tahoma"><br>
</font><font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a></font></span> </p>
</div>
<br>
<br>
Hari Prasad wrote:
<blockquote cite="mid%23LLkY7teFHA.900@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:jbngspam@.yahoo.com"><jbngspam@.yahoo.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:42bf9544$0$18637$14726298@.news.sunsite.dk">news:42bf9544$0$18637$14726298@.news.sunsite.dk</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040107090904080005040400--|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>>Hi,
>>SQL Server will not store these information. But for new object creation you
>>can see the CRDATE column in sysobjects table.
>>But for Insert/update and delete you need to write trigger to populate a
>>audit table. Later you could use the audit table.
>>Thanks
>>Hari
>>SQL Server MVP
>>"John B" <jbngspam@.yahoo.com> wrote in message
>>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>>
>>
Query the last access data/time
Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation yo
u
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation yo
u
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>
>|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
[vbcol=seagreen]
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>
Subscribe to:
Posts (Atom)