I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
> I have a Sql server 2000 database which is already in production. The
client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application.
Does the client really care how it is STORED? You could easily create views
for all the relevant tables which perform UPPER() on varchar columns, and
this would be much easier than using triggers on all the tables to do the
convert on entry, IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)
|||You can do this in several places. Edit your client side code, so that it
converts all the input data to upper case, and then passes to the database.
Another option is to edit your stored procedures, so that they convert the
input data to upper case, before inserting/updating tables.
Yet another option is to have a trigger that converts the data to upper
case, but not really recommended, because of performance implications.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
|||thanks for your help Narayana .
I already did consider these 3 options but i am at a point where with the no
of the database table, stored procs and application, it would take me a long
time to do any kind of programming to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. Again please let me know if there
is any option or any other way other than the once you have metioned which
when i set would force all the data comming in to Upper case.
thanks, waiting for you reply
sameer
"Narayana Vyas Kondreddi" wrote:
> You can do this in several places. Edit your client side code, so that it
> converts all the input data to upper case, and then passes to the database.
> Another option is to edit your stored procedures, so that they convert the
> input data to upper case, before inserting/updating tables.
> Yet another option is to have a trigger that converts the data to upper
> case, but not really recommended, because of performance implications.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
> I have a Sql server 2000 database which is already in production. The client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application. Is there any
> system Database option in the database which when set to On would save all
> the data being stored to UPPER CASE.
> thanks
> Sameer
>
>
|||thanks for your help Aaron.
I already did consider this option but i am at a point where with the no of
the database table, stored procs and application, it would take me a long
time to do any kind of developement to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. again, please let me know if there
is any such switch in the database.
thanks
sameer
"Aaron [SQL Server MVP]" wrote:
> client
> Does the client really care how it is STORED? You could easily create views
> for all the relevant tables which perform UPPER() on varchar columns, and
> this would be much easier than using triggers on all the tables to do the
> convert on entry, IMHO.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||No, there are case-sensitive collations, but there is no global switch that
will force all data to upper case, sorry.
Tell the client if they really want this that there will be development time
involved.
http://www.aspfaq.com/
(Reverse address to reply.)
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:53744680-6679-4B86-A90B-51B80FEFA47F@.microsoft.com...
> thanks for your help Aaron.
> I already did consider this option but i am at a point where with the no
of
> the database table, stored procs and application, it would take me a long
> time to do any kind of developement to implement any of these 3 options.
And
> that is why i was curious if there is any Option in the database like the
> 'case-sesitive' Coalition which when set to ON would convert all the data
> being stored in the datase to Upper case. again, please let me know if
there[vbcol=seagreen]
> is any such switch in the database.
> thanks
> sameer
> "Aaron [SQL Server MVP]" wrote:
Upper[vbcol=seagreen]
views[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
Showing posts with label entered. Show all posts
Showing posts with label entered. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
How to make a table act like a queue
Currently, I have a table that I am using to queue up items to be processed later. The initial state that they are entered into this table is 'NEW', meaning that they are NOT in the queue. When the status is switched to 'READY', they are ready to be processed by outside application(s). When application(s) wish to grab the next available item in the queue, they call my stored procedure which does some checking and updates the status to 'PROCESSING'. This stored procedure works, but I think there is a better way of doing this that would allow for much fewer deadlocks.
Anyone care to comment?
here's the stored procedure (I came back to clean up some of the logic to make it simpler to see)
----------
CREATE PROCEDURE sp_GetNextDeliveryRequest AS
BEGIN
DECLARE @.IDint,
@.Done bit
SET ROWCOUNT 1
SET @.Done = 0
SET @.ID= NULL
WHILE @.Done = 0
BEGIN
SELECT @.ID= queue_id
FROM tbl_queue
WHERE ((status='READY')
ORDER BY status ASC, entered_date ASC
IF @.ID IS NOT NULL
BEGIN
UPDATE tbl_queue
SET status = 'PROCESSING',
WHERE request_id = @.ID
AND ((status='READY')
IF @.@.ROWCOUNT != 0
SET @.Done = 1
END
ELSE
BREAK
END
IF @.Done != 1
SELECT -1 AS 'QueueID'
ELSE
SELECT @.ID AS 'QueueID'
END
----------SELECT @.DeliveryID = request_id
FROM tbl_delivery_request
WHERE ((status='READY') OR (status IN ('PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5))
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC
Could return more than 1 id, so you'll get the last...
And yes, it sounds like a standard workflow function...
But it looks you're always taking the newest stuff. Why would things go in as New?|||Good point on returning more than 1. I did not realize it would take the last value as opposed to the first value. Thanks for that tip.
Things get entered as new because in our workflow process we needed to record how each delivery request would be handled (even though the item is not yet ready to be delivered). So, this table incorporates information for the delivery_request, as well as it's queue status. I'm in agreeance that this structure could be refactored for the better, but for now it is what it is.
So, is there a simpler (better) way of performing this function? Basically I'd like to get the next id without having another process get the same id.|||A couple of coding points:
Instead of using rowcount, use TOP 1 in your SELECT statement.
WHERE ((status='READY') OR (status IN ('PROCESSING', 'TRY AGAIN')))
is equivalent to the more concise:
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
Since @.Done is a bit value, you can just set it like this:
SET @.Done = @.@.ROWCOUNT
...as the @.@.ROWCOUNT int value will be implicitly translated to a bit value.
...so your procedure could be rewritten like this:
CREATE PROCEDURE sp_GetNextDeliveryRequest AS
BEGIN
DECLARE @.DeliveryID int, @.Done bit
SET @.Done = 0
SET @.DeliveryID = NULL
WHILE @.Done = 0
BEGIN
SET @.DeliveryID =
(SELECT TOP 1 request_id
FROM tbl_delivery_request
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC)
IF @.DeliveryID IS NOT NULL
BEGIN
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE request_id = @.DeliveryID
AND status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
SET @.Done = @.@.ROWCOUNT
END
ELSE BREAK
END
END
But the problem I think you are going to run into is that this procedure runs continuously until it succesfully finds a request_id. If that takes a few seconds or more, and another application initiates the same query, that is where I suspect your deadlocks are coming from.
I'd recommend that you rewrite the procedure to return null if there is no available request_id, and that your application be programmed to wait five or ten seconds before resubmitting.
blindman|||blindman,
Thanks for the tips. I'm making the modifications right now to see what kind of positive impacts they have. As for combining the status check, I have to keep that separate (notice the parenthesis) because of the additional date check for queued items in 'PROCESSING' or 'TRY AGAIN' status. Everything else seems to make perfect sense.
The one thing that I am stuck with though is a tradeoff of processing in my application versus processing in the stored procedure. If I choose to try only once to get the next available request_id, then I may (or may not) find one and be able to update the row before the next application tries. So, my call would effectively return null, meaning that there are no requests to handle. Now, I can choose to sleep for my current sleep time (60 seconds) and then repoll only to have the same situation happen (another application updates the record before I am able to update, causing the stored procedure to return null). So, the application (or thread) could end up spending a lot of time sleeping when there are requests to process. This was the reason why I choose to implement the looping at the stored procedure level, but this has its drawbacks (as evident by the deadlocks). I just wasn't sure if there was some better way of handling my situation without causing so many deadlocks. Like performing the update/lookup in 1 statement.|||Well...yeah...
Use set based operations, and get rid of
WHERE request_id = @.DeliveryID
In the update...
No?|||Add a column to your table called ProcessID and set its type to UniqueIdentifier. Then use this logic in your procedure:
------------
declare @.ProcessID UniqueIdentifier
set @.ProcessID = NewID()
UPDATE tbl_queue SET ProcessID = @.ProcessID, status = 'PROCESSING'
from tbl_queue
inner join (Select Top 1 request_id from tblequeue where(Your Criteria...)) NextRequest
on tbl_queue.request_id = NextRequest.request_id
Set @.ID = (select request_id from tbl_queue where ProcessID = @.ProcessID)
------------
Because the record is located and marked all in one statement, I think it will fix your deadlocks.
blindman
Brett; set based operations? What's your idea? Can you give more detail?|||What's the difference between:
SET @.DeliveryID =
(SELECT TOP 1 request_id
FROM tbl_delivery_request
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC)
IF @.DeliveryID IS NOT NULL
BEGIN
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE request_id = @.DeliveryID
AND status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
And
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
But I could be missing it...damn hangover...|||I think he needs to know and return the @.DeliverID value.
Go easy on the Margaritas. The weekends over Kaiser! :cool:
blindman|||Brett,
Blindman is correct. I needed to return the id of the next request to process. Blindman, thanks for the alternative. I've actually used that type of mechanism before (with some suttle differences). There is only one caveat to that, and that is to be careful that your spid is unique per call. That gets me in to some trouble here since I am pooling my connections to the database. So even though 2 threads will have 2 separate connections (and thus 2 separate spids), there could be more than 1 entry in the table with the spid=myspid.
Blindman, Brett, I really do appreciate the help. You're giving me a lot of ideas, and causing me to rethink why I've choosen this implementation to begin with! :)|||Your SPID is guaranteed to be unique if you use the NewID function.
100% Gar-run-teed, or yer munny back!
blindman|||I take that back. I belive I stepped right over that line. Well great solution. kudos.|||I still think its better development practice to return a null if there are no available request_ids, and then have the application resubmit five or ten seconds later.
I don't like the idea of procedures that could potentially run in endless loops.
blindman|||Do you really mean spid?
I'm soooo confused...|||It doesn't run an endless loop. It runs until a request is found and your process could update the record, or until no more requests are available in the queue (see the break statement). I do agree that it would be more advantageous to have what would be analagous to a synchronized block so that other processes requesting this stored procedure would simply wait until the current process exited the block.
That kind of logic would have to be performed at the application level, and for distributed computing issues (ability to run multiple applications to process this queue), does not fit my needs.|||No, I don't mean SPID. I mean GUID.
You should not need to care about whether your SPID is unique.
blindman
Anyone care to comment?
here's the stored procedure (I came back to clean up some of the logic to make it simpler to see)
----------
CREATE PROCEDURE sp_GetNextDeliveryRequest AS
BEGIN
DECLARE @.IDint,
@.Done bit
SET ROWCOUNT 1
SET @.Done = 0
SET @.ID= NULL
WHILE @.Done = 0
BEGIN
SELECT @.ID= queue_id
FROM tbl_queue
WHERE ((status='READY')
ORDER BY status ASC, entered_date ASC
IF @.ID IS NOT NULL
BEGIN
UPDATE tbl_queue
SET status = 'PROCESSING',
WHERE request_id = @.ID
AND ((status='READY')
IF @.@.ROWCOUNT != 0
SET @.Done = 1
END
ELSE
BREAK
END
IF @.Done != 1
SELECT -1 AS 'QueueID'
ELSE
SELECT @.ID AS 'QueueID'
END
----------SELECT @.DeliveryID = request_id
FROM tbl_delivery_request
WHERE ((status='READY') OR (status IN ('PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5))
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC
Could return more than 1 id, so you'll get the last...
And yes, it sounds like a standard workflow function...
But it looks you're always taking the newest stuff. Why would things go in as New?|||Good point on returning more than 1. I did not realize it would take the last value as opposed to the first value. Thanks for that tip.
Things get entered as new because in our workflow process we needed to record how each delivery request would be handled (even though the item is not yet ready to be delivered). So, this table incorporates information for the delivery_request, as well as it's queue status. I'm in agreeance that this structure could be refactored for the better, but for now it is what it is.
So, is there a simpler (better) way of performing this function? Basically I'd like to get the next id without having another process get the same id.|||A couple of coding points:
Instead of using rowcount, use TOP 1 in your SELECT statement.
WHERE ((status='READY') OR (status IN ('PROCESSING', 'TRY AGAIN')))
is equivalent to the more concise:
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
Since @.Done is a bit value, you can just set it like this:
SET @.Done = @.@.ROWCOUNT
...as the @.@.ROWCOUNT int value will be implicitly translated to a bit value.
...so your procedure could be rewritten like this:
CREATE PROCEDURE sp_GetNextDeliveryRequest AS
BEGIN
DECLARE @.DeliveryID int, @.Done bit
SET @.Done = 0
SET @.DeliveryID = NULL
WHILE @.Done = 0
BEGIN
SET @.DeliveryID =
(SELECT TOP 1 request_id
FROM tbl_delivery_request
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC)
IF @.DeliveryID IS NOT NULL
BEGIN
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE request_id = @.DeliveryID
AND status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
SET @.Done = @.@.ROWCOUNT
END
ELSE BREAK
END
END
But the problem I think you are going to run into is that this procedure runs continuously until it succesfully finds a request_id. If that takes a few seconds or more, and another application initiates the same query, that is where I suspect your deadlocks are coming from.
I'd recommend that you rewrite the procedure to return null if there is no available request_id, and that your application be programmed to wait five or ten seconds before resubmitting.
blindman|||blindman,
Thanks for the tips. I'm making the modifications right now to see what kind of positive impacts they have. As for combining the status check, I have to keep that separate (notice the parenthesis) because of the additional date check for queued items in 'PROCESSING' or 'TRY AGAIN' status. Everything else seems to make perfect sense.
The one thing that I am stuck with though is a tradeoff of processing in my application versus processing in the stored procedure. If I choose to try only once to get the next available request_id, then I may (or may not) find one and be able to update the row before the next application tries. So, my call would effectively return null, meaning that there are no requests to handle. Now, I can choose to sleep for my current sleep time (60 seconds) and then repoll only to have the same situation happen (another application updates the record before I am able to update, causing the stored procedure to return null). So, the application (or thread) could end up spending a lot of time sleeping when there are requests to process. This was the reason why I choose to implement the looping at the stored procedure level, but this has its drawbacks (as evident by the deadlocks). I just wasn't sure if there was some better way of handling my situation without causing so many deadlocks. Like performing the update/lookup in 1 statement.|||Well...yeah...
Use set based operations, and get rid of
WHERE request_id = @.DeliveryID
In the update...
No?|||Add a column to your table called ProcessID and set its type to UniqueIdentifier. Then use this logic in your procedure:
------------
declare @.ProcessID UniqueIdentifier
set @.ProcessID = NewID()
UPDATE tbl_queue SET ProcessID = @.ProcessID, status = 'PROCESSING'
from tbl_queue
inner join (Select Top 1 request_id from tblequeue where(Your Criteria...)) NextRequest
on tbl_queue.request_id = NextRequest.request_id
Set @.ID = (select request_id from tbl_queue where ProcessID = @.ProcessID)
------------
Because the record is located and marked all in one statement, I think it will fix your deadlocks.
blindman
Brett; set based operations? What's your idea? Can you give more detail?|||What's the difference between:
SET @.DeliveryID =
(SELECT TOP 1 request_id
FROM tbl_delivery_request
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
ORDER BY status ASC, entered_date ASC)
IF @.DeliveryID IS NOT NULL
BEGIN
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE request_id = @.DeliveryID
AND status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
And
UPDATE tbl_delivery_request
SET status = 'PROCESSING', process_start_date = getdate()
WHERE status IN ('READY', 'PROCESSING', 'TRY AGAIN')
AND DATEDIFF(minute, process_start_date, getdate()) > 5
AND delivered_date IS NULL
But I could be missing it...damn hangover...|||I think he needs to know and return the @.DeliverID value.
Go easy on the Margaritas. The weekends over Kaiser! :cool:
blindman|||Brett,
Blindman is correct. I needed to return the id of the next request to process. Blindman, thanks for the alternative. I've actually used that type of mechanism before (with some suttle differences). There is only one caveat to that, and that is to be careful that your spid is unique per call. That gets me in to some trouble here since I am pooling my connections to the database. So even though 2 threads will have 2 separate connections (and thus 2 separate spids), there could be more than 1 entry in the table with the spid=myspid.
Blindman, Brett, I really do appreciate the help. You're giving me a lot of ideas, and causing me to rethink why I've choosen this implementation to begin with! :)|||Your SPID is guaranteed to be unique if you use the NewID function.
100% Gar-run-teed, or yer munny back!
blindman|||I take that back. I belive I stepped right over that line. Well great solution. kudos.|||I still think its better development practice to return a null if there are no available request_ids, and then have the application resubmit five or ten seconds later.
I don't like the idea of procedures that could potentially run in endless loops.
blindman|||Do you really mean spid?
I'm soooo confused...|||It doesn't run an endless loop. It runs until a request is found and your process could update the record, or until no more requests are available in the queue (see the break statement). I do agree that it would be more advantageous to have what would be analagous to a synchronized block so that other processes requesting this stored procedure would simply wait until the current process exited the block.
That kind of logic would have to be performed at the application level, and for distributed computing issues (ability to run multiple applications to process this queue), does not fit my needs.|||No, I don't mean SPID. I mean GUID.
You should not need to care about whether your SPID is unique.
blindman
Wednesday, March 21, 2012
How to loop through in sql server 2000
I have a scenario where I have to keep track of users last three passwords,
if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."
Basically I can do : select top 3 passwords from MyTable order by createdDate desc
But how to do comparision ?
I was googling for if anything like array exist in sql server 2000?
Please suggest, any pointer......
Thanks
You can use the IN keyword to see if a scalar value is within a set of values.
Here's how I would do this. I'd have a stored procedure that accepts the @.NewPassword and @.UserID as input parameters and then returns either a 1 or a 0, and in .NET code I'd do an ExecuteScalar() and cast the result to a Boolean.
The T-SQL in the stored procedure would look something like:
IF @.NewPassword IN (SELECT TOP 3 Passwords FROM PasswordHistory WHERE UserID = @.UserID ORDER BY CreatedDate DESC)
SELECT 1
ELSE
SELECT 0
|||
Hi Scott,
Thanks for your reply, its surely something which i wanted , let me try it .
will let you know how it works out.
|||
It does work !!
Thanks a lot for your time.
|||
gridview:
It does work !!
Thanks a lot for your time.
Great! Happy Programming!
It does work !!
Thanks a lot for your time.
Great! Happy Programming!
Wednesday, March 7, 2012
How to know which tables is updated ?
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004You could use profiler to audit certain types of activity & subsequently see
what activity has occured through time.
You could use triggers &/or default values to maintain a list of who & when
records are added.
I generally add [Creator] & [CreationDate] fields with defaults of S
USER_SNAME() & GETDATE() respectively to any important tables...
--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004|||You can also purchase Log Explorer from Lumigent (www.lumigent.com)... It ca
n read a log file and tell you the changes that were made...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's communi
ty of SQL Server professionals.
www.sqlpass.org
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004You could use profiler to audit certain types of activity & subsequently see
what activity has occured through time.
You could use triggers &/or default values to maintain a list of who & when
records are added.
I generally add [Creator] & [CreationDate] fields with defaults of S
USER_SNAME() & GETDATE() respectively to any important tables...
--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004|||You can also purchase Log Explorer from Lumigent (www.lumigent.com)... It ca
n read a log file and tell you the changes that were made...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's communi
ty of SQL Server professionals.
www.sqlpass.org
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004
Friday, February 24, 2012
How to know the order of entries in a table
For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id ... ... ...
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...Hi
Please reread my first post in here:
http://www.dbforums.com/showthread.php?t=1620041
There is no order. If you need to know the order that data was inserted then you need to set something up yourself - use an identity column or a timestamp with a GETDATE() default value. Note that the second option will result in "ties" if you insert sets of data.|||Thanks for your help...
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id ... ... ...
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...Hi
Please reread my first post in here:
http://www.dbforums.com/showthread.php?t=1620041
There is no order. If you need to know the order that data was inserted then you need to set something up yourself - use an identity column or a timestamp with a GETDATE() default value. Note that the second option will result in "ties" if you insert sets of data.|||Thanks for your help...
Subscribe to:
Posts (Atom)