I need to know the last time each table on my database was accessed or
modified. Is that possible? I'm usign SQL Server 2000.Not unless you have been running a trace for a while.
Andrew J. Kelly SQL MVP
"Huacuz" <Huacuz@.discussions.microsoft.com> wrote in message
news:C640BB82-4F63-4B41-9600-BAEED7396F57@.microsoft.com...
> I need to know the last time each table on my database was accessed or
> modified. Is that possible? I'm usign SQL Server 2000.|||Hi
If you want an off the shelf product you could look at AuditDB from Lumigent
http://www.lumigent.com/Products/auditdb_sql.html
John
"Huacuz" wrote:
> I need to know the last time each table on my database was accessed or
> modified. Is that possible? I'm usign SQL Server 2000.|||What is your definition of 'accessed'; perhaps any time any process selects
a row for any reason?
"Huacuz" <Huacuz@.discussions.microsoft.com> wrote in message
news:C640BB82-4F63-4B41-9600-BAEED7396F57@.microsoft.com...
> I need to know the last time each table on my database was accessed or
> modified. Is that possible? I'm usign SQL Server 2000.|||> I need to know the last time each table on my database was accessed or
> modified. Is that possible?
Not without using Profiler or 3rd party auditing tools, no.
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Wednesday, March 21, 2012
Last Table Access
How do I determine who last accessed a SQL 2000 table?See this quite related article.sql
Monday, March 19, 2012
Last Inserted row
I am trying to access the row which was just inserted into a table. Will the following statements be equivalent of each other?
STATEMENT 1:
SELECT JobNumber
from tblCustServiceHistoryHdr
where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/
STATEMENT 2:
SELECT JobNumber
from inserted
where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/Would you like to know the last row inserted by your session, or by all sessions ?
About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.
Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.|||Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.
But for now I will only need to know the inserted row for my session.|||How about introducing a new column to your table which will hold the time stamp for new rows inserted?|||I thought the DateModified column acted like a timestamp...u know everytime there r updates to that particular row the DateModified column is altered using getdate().|||Why not put an Identity Seed field on your table ?
Then do the insert through a stored procedure and pass back the identity field value.
By the way, @.@.IDENTITY returns the last generated value for the newly inserted row.|||What do you want to know the inserted row for ?
Might help us with a solution|||Originally posted by gayamantra
Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.
But for now I will only need to know the inserted row for my session.
Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.
STATEMENT 1:
SELECT JobNumber
from tblCustServiceHistoryHdr
where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/
STATEMENT 2:
SELECT JobNumber
from inserted
where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/Would you like to know the last row inserted by your session, or by all sessions ?
About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.
Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.|||Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.
But for now I will only need to know the inserted row for my session.|||How about introducing a new column to your table which will hold the time stamp for new rows inserted?|||I thought the DateModified column acted like a timestamp...u know everytime there r updates to that particular row the DateModified column is altered using getdate().|||Why not put an Identity Seed field on your table ?
Then do the insert through a stored procedure and pass back the identity field value.
By the way, @.@.IDENTITY returns the last generated value for the newly inserted row.|||What do you want to know the inserted row for ?
Might help us with a solution|||Originally posted by gayamantra
Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.
But for now I will only need to know the inserted row for my session.
Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.
Monday, March 12, 2012
Last Function in SQL Server ?
Hi there,
--Access Query that Doesn't work in SQL
SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
FROM Employees
--Access Query that work with SQL if we have an EmployeeID
SELECT TOP 1 FirstName,LastName
FROM Employees
ORDER BY EmployeeID DESC
--SQL only if we don't have an EmployeeID
Declare @.FirstName varchar(20)
Declare @.LastName varchar(20)
SELECT @.FirstName = FirstName, @.LastName = LastName
from Employees
SELECT @.FirstName, @.LastName
--Test1 to make sure that we looking for last record
Select * FROM Employees
What is the equivalent Last access function in T-SQL
Thanks
Oded Dror
Email: odeddror@.cox.netA table in SQL has no inherent logical order so there is no "first" or
"last" row. How do you want to define which row should be the last?
I believe that if you don't specify ORDER BY then Access just picks the
last record based on insertion order. However, SQL Server doesn't
preserve the information about insertion order unless you explicitly
create a column or columns to record that information.
For example, if you have a modification_date in your table you could
take the latest date:
SELECT first_name, last_name
FROM Employees
WHERE modification_date =
(SELECT MAX(modification_date)
FROM Employees)
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||First or Last concept can be applied just to an ordered set.
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid desc
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname, lastname
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname desc, lastname desc
AMB
"Oded Dror" wrote:
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
>|||The concept of "last" does not exist in SQL nor SQL Server as SQL thinks in
terms of sets. Last must derived from the data on which you are querying usi
ng
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order i
n
which it was entered. No such guarantee exists in most database products lik
e
SQL Server or Oracle.
Thomas
"Oded Dror" <odeddror@.cox.net> wrote in message
news:%23gtUM9sYFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
--Access Query that Doesn't work in SQL
SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
FROM Employees
--Access Query that work with SQL if we have an EmployeeID
SELECT TOP 1 FirstName,LastName
FROM Employees
ORDER BY EmployeeID DESC
--SQL only if we don't have an EmployeeID
Declare @.FirstName varchar(20)
Declare @.LastName varchar(20)
SELECT @.FirstName = FirstName, @.LastName = LastName
from Employees
SELECT @.FirstName, @.LastName
--Test1 to make sure that we looking for last record
Select * FROM Employees
What is the equivalent Last access function in T-SQL
Thanks
Oded Dror
Email: odeddror@.cox.netA table in SQL has no inherent logical order so there is no "first" or
"last" row. How do you want to define which row should be the last?
I believe that if you don't specify ORDER BY then Access just picks the
last record based on insertion order. However, SQL Server doesn't
preserve the information about insertion order unless you explicitly
create a column or columns to record that information.
For example, if you have a modification_date in your table you could
take the latest date:
SELECT first_name, last_name
FROM Employees
WHERE modification_date =
(SELECT MAX(modification_date)
FROM Employees)
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||First or Last concept can be applied just to an ordered set.
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid desc
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname, lastname
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname desc, lastname desc
AMB
"Oded Dror" wrote:
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
>|||The concept of "last" does not exist in SQL nor SQL Server as SQL thinks in
terms of sets. Last must derived from the data on which you are querying usi
ng
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order i
n
which it was entered. No such guarantee exists in most database products lik
e
SQL Server or Oracle.
Thomas
"Oded Dror" <odeddror@.cox.net> wrote in message
news:%23gtUM9sYFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
Last Function
Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature?
Example:
Item_Code Time
123456 11:40
123456 11:41
123456 11:42
By grouping on item code and using the last function on the Time column, the third record would be returned.
Item_Code Time
123456 11:42
All, help is appreciated.Last is not as reliable as you may think, and SQL Server doesn't have it. Try Max instead.|||well, i really cannot use max because it will give me a max each cloumn. For example:
Item Date Time
12345 7/10/06 11:21
12345 7/10/06 11:45
12345 7/12/06 09:45
If i group on item and use max for date and time i would get
Item Date Time
12345 7/12/06 11:45
I basically need to find a way o get the last record of a set.
Maybe there is a better way to approach this.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.
I think that would probbaly be my best bet. Thanks for all your help.|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:
http://support.microsoft.com/kb/208190/en-us|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:
http://support.microsoft.com/kb/208190/en-us
Sweet, I really appreciate all the help and notes.
Example:
Item_Code Time
123456 11:40
123456 11:41
123456 11:42
By grouping on item code and using the last function on the Time column, the third record would be returned.
Item_Code Time
123456 11:42
All, help is appreciated.Last is not as reliable as you may think, and SQL Server doesn't have it. Try Max instead.|||well, i really cannot use max because it will give me a max each cloumn. For example:
Item Date Time
12345 7/10/06 11:21
12345 7/10/06 11:45
12345 7/12/06 09:45
If i group on item and use max for date and time i would get
Item Date Time
12345 7/12/06 11:45
I basically need to find a way o get the last record of a set.
Maybe there is a better way to approach this.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.
I think that would probbaly be my best bet. Thanks for all your help.|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:
http://support.microsoft.com/kb/208190/en-us|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:
http://support.microsoft.com/kb/208190/en-us
Sweet, I really appreciate all the help and notes.
Last edit time?
How can i mark a row with its last edit time?
With access it was obvious, in sqlserver?Hi
With access it was obvious, in sqlserver?Hi
You can add a datetime column that you update using the getdate() function.
If you only want to version-stamp the row then you can use a rowversion
column.
John
<pinkoPallino@.pinkopallonia.it> wrote in message
news:426b078b.748264697@.news.tin.it...
> How can i mark a row with its last edit time?
> With access it was obvious, in sqlserver?
Last date of access on a DB
Hi!
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed o
n
the server?
Any help will be appreciated.
FRivasFrivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed o
n
the server?
Any help will be appreciated.
FRivasFrivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
Last date of access on a DB
Hi!
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed on
the server?
Any help will be appreciated.
FRivas
Frivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com
|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed on
the server?
Any help will be appreciated.
FRivas
Frivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com
|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
Last date of access on a DB
Hi!
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed on
the server?
Any help will be appreciated.
FRivasFrivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed on
the server?
Any help will be appreciated.
FRivasFrivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>
Last Access to the Database
Is there a tool for SQL Server 2000 that will tell me the last time all the
databases were accessed on the server? I'm trying to detemin which databases
are not used anymore or not very often.
Thanks,
David
======================================
David McCarter
www.vsdntips.com
VSDN Tips & Tricks .NET Coding Standards available at:
www.cafepress.com/vsdntips.20412485
dotNetDave (dotNetDave@.discussions.microsoft.com) writes:
> Is there a tool for SQL Server 2000 that will tell me the last time all
> the databases were accessed on the server? I'm trying to detemin which
> databases are not used anymore or not very often.
No, there is no such feature.
If you are an evil man, you use set the databases offline, and see how
many people scream.
If you are more the conflict-avert sort of person, you can set the
suspected databases to autoclose. If memory serves, you can then trace
an access by looking at the dates for the MDF files. But there is a
catch here: if anyone browse the list in EM and is not sysadmin, EM
will have to open the DB to see if that person has access. Or it could
be due to any other random visit.
Slightly more reliable is to run a trace, but you may need to run it
for three montns or so.
Personally, I think being evil is a good qualification for a DBA. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
databases were accessed on the server? I'm trying to detemin which databases
are not used anymore or not very often.
Thanks,
David
======================================
David McCarter
www.vsdntips.com
VSDN Tips & Tricks .NET Coding Standards available at:
www.cafepress.com/vsdntips.20412485
dotNetDave (dotNetDave@.discussions.microsoft.com) writes:
> Is there a tool for SQL Server 2000 that will tell me the last time all
> the databases were accessed on the server? I'm trying to detemin which
> databases are not used anymore or not very often.
No, there is no such feature.
If you are an evil man, you use set the databases offline, and see how
many people scream.
If you are more the conflict-avert sort of person, you can set the
suspected databases to autoclose. If memory serves, you can then trace
an access by looking at the dates for the MDF files. But there is a
catch here: if anyone browse the list in EM and is not sysadmin, EM
will have to open the DB to see if that person has access. Or it could
be due to any other random visit.
Slightly more reliable is to run a trace, but you may need to run it
for three montns or so.
Personally, I think being evil is a good qualification for a DBA. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Friday, March 9, 2012
Last & First SQL aggregate functions
I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?
Below is the Access SQL statement:
SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));
Use MIN and MAX
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||In SQL Server 2005, you can do something like this: select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex from ( select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex, rank() over (partition by tblZoneNameString.TagIndex order by ?) as rk from tblZoneNameString ) as T where rk = 1 Where I've written ? you will need to put whatever column or columns answer the question "last in order of what?". Perhaps this is something like someDateTime DESC. Steve Kass Drew University JimNolandCBI@.discussions.microsoft.com wrote:
> I'm trying to migrate an app. from Access to SQL Server, and find that
> Transact-SQL does not support LAST/FIRST functions. Is there any
> alternative to these?
>
> Below is the Access SQL statement:
>
> SELECT Last(tblZoneNameString.Val) AS strZoneName,
> tblZoneNameString.TagIndex
> FROM tblZoneNameString
> GROUP BY tblZoneNameString.TagIndex
> HAVING (((tblZoneNameString.TagIndex)>0));
>
>
Wednesday, March 7, 2012
Large table/slow query/ can performance be improved?
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?
Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?
Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.
If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.
|||Hi,
These are some tips from my side.
1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.
Finally, can you elaborate/post what exactly u r trying to do and
achive.
Amit
"Robert" <stop.spam@.whitehouse.gov> wrote in message news:<HLFKqK.MqD@.news.boeing.com>...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform more
> of the work on the server there by minimizing the amount of extraneous table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?|||As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.
1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)
2) In Access, make a new query, but don't choose any tables.
3) Choose Query/SQL Specific>Pass-Through
4) Paste the SQL Statement in there
5) Choose View/Properties
6) Set the Connect string (and ODBC Timeout!) appropriately
Run the query.
I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!
The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!
You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)
Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.
If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through...this is the best way to optimize performance.
CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!
I'd be interested in seeing the SQL Statement of the 6-hr query.
Monday, February 20, 2012
Large move from Access to SQL Server 200
Hi,
My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.
The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.
For example, the following code is taken from Access :
SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;
But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?
ThanksGROUP BY Description?
Another Classic Access example...
LOOK OUT! RUN AWAY TRAIN...ummmm developers...
Do you have a lot of nested Access queries as well?
Have you tried just linked the SQL Server tables?
Have you given any thought to a re-write?|||Actually we can't link to the Access db. Everything has to be stored in the SQL database.|||I meant the old access app, with no tables, linked to the sql server database...
are the table and column names still all the same?
If you link it, it should all run...
might be very slow though...
but at least you'll have all your reports...|||Without and more infomation, it looks like the Access upgrade thingy (whatever they call it now) has morphed soem of your text fields to TEXT or NTEXT. If you want to be able to join on these fields, they need to be CHAR, VARCHAR (or NCHAR, NVACHAR).
Your first step after running that thing is to go through the whole database, field by field, and see what has happened to your data in the translation.
Almost as easy to do it manually.
-bpd|||I ended up doing all of the queries as views and getting rid of the one text field, then on the final query, joined the text field, thus by-passing the Group By clause for that field. Thanks for all the help though.
My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.
The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.
For example, the following code is taken from Access :
SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;
But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?
ThanksGROUP BY Description?
Another Classic Access example...
LOOK OUT! RUN AWAY TRAIN...ummmm developers...
Do you have a lot of nested Access queries as well?
Have you tried just linked the SQL Server tables?
Have you given any thought to a re-write?|||Actually we can't link to the Access db. Everything has to be stored in the SQL database.|||I meant the old access app, with no tables, linked to the sql server database...
are the table and column names still all the same?
If you link it, it should all run...
might be very slow though...
but at least you'll have all your reports...|||Without and more infomation, it looks like the Access upgrade thingy (whatever they call it now) has morphed soem of your text fields to TEXT or NTEXT. If you want to be able to join on these fields, they need to be CHAR, VARCHAR (or NCHAR, NVACHAR).
Your first step after running that thing is to go through the whole database, field by field, and see what has happened to your data in the translation.
Almost as easy to do it manually.
-bpd|||I ended up doing all of the queries as views and getting rid of the one text field, then on the final query, joined the text field, thus by-passing the Group By clause for that field. Thanks for all the help though.
Subscribe to:
Posts (Atom)