Then we have the following "Activity" table refencing "Account" table
above:
Table: Activity
Columns: ActivityID, AccountNo,...
when "Activity" with accountno is created, we'd like to able to take
snapshop or copy all Account related data records from tables: account,
accountdocument, accounttest
and store it somewhere.
This way even if account, accountdocument and accounttest tables change
later, then "Activity" accountno integrity in maintained.
Do we need to create or replicate similar tables to store data for
audit?
I have a small webapplication, sql server 2000. The users can only update the data in the system. However my client needs a report that display changes. The changes are that needs to be monitored are only change of order status, change of delivery date and when a user splits an order.
What is the best practice to keep track of changes? A mirror table for each table with changes?
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
Hi, i'm wondering which is the best way to search data in a SQL Server. I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query. I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices? Thanks for your help.
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
I'm just wondering if it is bad programming practice to have many data sources on one page. I need a "Matrix" type of page that grabs data from many tables and so far I have 5 data sources. Is this common or is there something I should be doing to make it more efficient?
Hi all, I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:
AuditTable that have the following fields: AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate
then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?
Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great. Thanks,
I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables.
Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows:
Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database.For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up? Is my current way of working good practice?
Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?
I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.
Will anyone gives me advices for that? Really need help.
We are working on converting to SQL 2005 database. During the conversion we are having to rewrite a lot of code and doing a lot of intital testing and development on development data. This is causing our transaction logs to really big. I have created a maint plan that runs nightly that does a back up of database and tran log but throughtout the day the tran logs are getting really big and eating up a ton of disk space. Does anyone have suggestions on what sort of maint plan I can setup to run on my developement data where as at this point I am not concened about being able to roll back the database just keep is small as possible and "healthly"
Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).
I dont want to place any trace or triggers, is there any in-built option.
Hi,I want my application to audit any data changes (update, insert,delete) made by the users. Rather than have an audit table mirroringeach user table, I'd prefer to have a generic structure which can loganything. This is what I've come up with:TABLE: audit_record*audit_record_id (uniqueidentifier, auto-assign, PK) - uniqueidenfiier of the audit recordtable_name (varchar) - name of the table where the action (insert/update/delete) was madepk_value (varchar) - primary key of the changed record. If the PKitself has changed, this will store the old value.user_id (varchar) - user who changed the recorddate (datetime) - date/time at which the change was madeaction (int) - 0, 1 or 2 (insert, update, delete)TABLE: audit_column*audit_record_id (uniqueidentifier, composite PK) - FK tocdb_audit_record table*column_name (varchar, composite PK) - name of the column with changeddatanew_value (text?) - value after the changeSo every column which changes has its new value logged individually inthe audit_column table. However, I'm not sure what data type thenew_value column should have. The obvious answer (to me) is text, asthat can handle any necessary data type with the appropriateconversion (we don't store any binary data). However, this table isgoing to grow to millions of records and I'm not sure what theperformance implications of a text column will be, particularly giventhat the actual data stored in it will almost always be tiny.Any thoughts/recommendations/criticism would be greatly appreciated.ThanksAlex
Hallo I need some hints because I would like to set up my service broker in the proper way.
First: I'm going to setup service Broker between 2 databases on the same sql server instance.
My Goal: I insert the data on table1 in the DB1.On table1 there is a trigger that begin conversation and send the message to the service on the DB2. On the receiving queue there is an Activation that take the xml message, shred it and save the content on th table 2 on the DB2. Actually this SP is my main concern because the shred and insert involves also several checks so it could take "a while".
Volume of data: it seems that there are 100-200 daily insert in the table1 on the DB1 and it is possible that some of them arrives at the "same time".
Very quickly: On the DB1 the trigger:
Notice that I reuse always the same conversation and the send queue as RETENTION = OFF
begin transaction;
begin
set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceiveService');
if @dialog_handle is null
BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SendService]
TO SERVICE 'ReceiveService'
ON CONTRACT [MainContract]
WITH ENCRYPTION = OFF;
END
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE Message ('uyiuy')
commit;
END
On the DB2 ReceiveQueue is defined as:
CREATE QUEUE [dbo].[ReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceivedTrade] , MAX_QUEUE_READERS = 100 , EXECUTE AS N'dbo' ) ON [PRIMARY]
But most important is the Activation's SP and this is the main code:
while (1 = 1)
begin
begin transaction
-- Receive the next available message from the queue
WAITFOR (
RECEIVE top(1) @message_type=message_type_id,
@message_body=message_body,
@dialog = conversation_handle FROM ReceivedQueue
) if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
SET @ErrorSave = @@ERROR ;
IF (@ErrorSave <> 0)
BEGIN
ROLLBACK TRANSACTION ;
SET @ErrorDesc = N'An error has occurred.' ;
END CONVERSATION @dialog
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;
INSERT INTO [dbo].[tblLog] VALUES(@ErrorDesc,NULL)
I have to say that everything works fine but I don't think that it is completly performant. I read several blogs/forums and now I'm confused on the best way to implement service broker messaging: These are my questions:
1) I read that is the DB's are in the same instance It can improve performances the send the message directly on the Queue2. Can be useful in my case? How to implement it? 2)In my case it is necessarly that I send a message back to the initiator? 3) In my case the conversation never end. I don't think that this is correct but in case of the data push who has to END the conversation? The target? the initiator? never end to avoid overhead (I read that big overhead is caused by the BEGIN and END DIALOG)?
3) where (initiator or Target) and how to handle the service broker error? In my case my applications are SPs and I need to inform the developer or DBA that something went wrong during the processing ( conversation or shredding stored procedure).
4) In my case is should be a good idea to think about how to solve the possibility of the "poison messages"
for sure I will find out other questions... Any hint, link is appreciated!
I am using SQL Server 2000 Enterprise Edition fully patched. Database is in Simple Recovery mode.
I need to change a column's data type from "int(4)" to "smallint(2)". I know for sure that there will be no data (precision) lost, because I know the possible values that this column could have.
My problem is that the table I am dealing with has 600,000,000 records in it. I dropped all indexes before I tried to alter the table. But still it is taking forever and filling up my 280GB disk with transaction log file.
I know that in Oracle, if I want I can turn off logging and do these kind of modifications relatively faster.
I was wondering if there is a way of disabling logging before running this alter command.
What is the best practice to handle a situation of this sort?
Here is my idea but I am looking for the best practice.
Each record can have 3 possibilites.
I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.
writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file
SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default) order by event_time desc GO --DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]
1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process.
2. Information like WHO and WHEN printed the report and WHAT data was viewed?
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
I'm having problems using the update wizard to move data from 6.5 sql server (on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and passes me over to Dr Watson) when login fails for the 6.5 machine.
I am sure I have the right pasword (I've tried variations as well) and have updated the hosts file so that the machine is known by it's name (I think one of the FAQ answers suggested that)....
What other possibilities are there for moving the data ? I looked at bcp, but that seems a rather long winded route (the data contains time stamps so I suppose there will be a problem reading them in on the 7.0 side) ...
what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000.
Hi all, I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates. Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table. My Ms access table Name Id jas 100 meena 101 viji 102
My Ms sql table Name Id jas 100 meena 101 viji 102
After 1 min, say 2 records are added to my Ms access table like, Name Id jas 100 meena 101 viji 102 bhuvana 103 pinky 104 Now i want to insert only the latest records from Ms access to Ms sql like, Name Id jas 100 meena 101 viji 102 bhuvana 103 pinky 104 how to do this? thanx in advance. Jasmeeta.
I am wondering the best way to go about a task I have been assigned. We have two similar websites but each is located on a different network. One network is secure so it cannot be accessed on the normal WWW. The secure network will contain the master database. I need to write a program or do something with SQL server to retrieve all records from the WWW site and get them onto the secure database. I also in the future will need to update records from the WWW site if they have been updated. What is the easiest way to move data from one network to the other when I cannot connect to both databases simultaneously? Thanks, Matt
I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data. What methods are available to do this swap of data between databases? I was thinking of doing something like this: Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db. This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.
Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:
1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.
I am trying to move a data and log backup file from my C drive to another drive to free my C drive. Does anybody know the easiest way to do it? Thanks.
This is (probably) a simple question, but I'm new to SQL Server scripting.
What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.
Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.
Ideally, we want this to be a script that is run daily.
Does anyone have any ideas or examples that we could use.
