Converting Audit Data To Create History
Apr 9, 2008
Hi
I have a table called my_history that has columns like this
column_name , old_value, new_value, key, date
bankaccount 30 50 1 01-Apr-2008
bankbalance 10 14 2 04-Apr-2008
and so on............
The history table is populated using triggers
The main table called my has a structure like
bankaccount bankbalance bankname name address key
50 50 xyz abc ford 1
30 14 abc xyz east 2
Now using this information can i reconstruct the records in my table before the update happened ?
I am finding it very difficult to do this is there a way to do this in t-sql ?
The problem is my_history table where the column_name keeps on varrying
regards
Hrishy
View 7 Replies
ADVERTISEMENT
Jul 20, 2005
HiI am looking to implement an audit/history table/tables but am lookingat doing this without the use of triggers.The reason for doing this is that the application is highlytransactional and speed in critical areas is important.I am worried that triggers would slow things down.I am more used to other database where by there is a utility to "dump"the contents of the transaction logs and use this for auditingpurposes. However SQL Server does not have this functionality (unlessthere is a sql server tool - 3rd party that I do not know about)Has anyone implemented something similar? Or used/using a 3rd partytool that will do this job.Effectively the clients would like to "look" at what happened - say 15minutes ago.thanksjohn
View 3 Replies
View Related
Jul 13, 2007
Hello. Taking a typical use having a history table, maintained from a base table via triggers... Trying to see how/if that can be done using the SQl 2005 Service Broker method, with messaging? The thought is that if we can do the History table updates ASYNC, the user will not wait more than setting up the Broker message queue. I saw this article about something similar, but it deals with LOGON triggers.
http://www.sqlservercentral.com/columnists/FVandeputte/sqlserver2005logontriggers.asp
I'd think you can't do Hisotyr type triggers, with a message, because wouldn't you need to write all teh INSERTED/UPDATED data somewhere anyways? and there could be multiple rows affected in any given insert/update/delete, so could you even pass that thru to a Broker?
Anyone know of any references to using Broker Services for sending INSERTED/UPDATED data along for Historical versioning?
Also, was curious about error handling, because say you update teh base table, and then a problem occurs, and the Hisotry table is not updated. I want them in sync. Where is the message data stored, and is it accesible even if teh server reboots before the data is RECEIVED from teh QUEUE?
Thanks, Bruce
View 7 Replies
View Related
May 12, 2015
We have a need to report on historical data when none exists in the database.
Create a tabular report rdl that is run on a regular schedule. This report run is saved as an Excel sheet that overwrites the previous run, which has the same name.
Report is designed to use two data sources--the database and and the previously run Excel sheet. The data is "merged" using the Lookup function thereby creating a new report that includes the history needed.
View 2 Replies
View Related
Apr 20, 2015
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
View 9 Replies
View Related
Aug 16, 2005
Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry Here is my trigger:Create TRIGGER tg_audit_task_order_awardees on xxxfor INSERT,UPDATE,DELETE as
INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'OLD', del.to_awardee, del.solicitation_id, del.contract_id, del.order_number, del.amount, del.show_public, getdate(), del.modified_user FROM deleted del
/* for a new record */
INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'NEW', ins.to_awardee, ins.solicitation_id, ins.contract_id, ins.order_number, ins.amount, ins.show_public, getdate(), ins.modified_user FROM inserted ins
View 1 Replies
View Related
Nov 2, 2004
I need to create a simple audit trigger for a table with 12 columns. I need to determine which row was changed. Is there a simple way to do that. The table structure is
ID Integer(4)
barcode(25)
epc_tag(24)
bc_stop_flag(1)
reject_flag(1)
complete_flag(1)
hold_flag(1)
pe_1_flag
pe_2_flag
pe_3_flag
pe_4_flag
pe_5_flag
View 3 Replies
View Related
Apr 11, 2014
Trying to create an audit that goes to the application log. With a certain criteria, select, update, delete etc.. where like 'example'-- some criteria..is there any reference out there that could give me some examples?
View 3 Replies
View Related
May 31, 2006
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables.
Is there a solution for the version 2000?
View 2 Replies
View Related
Mar 28, 2008
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
View 3 Replies
View Related
May 30, 2007
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS
DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''
DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)
DECLARE CurHistoryTable CURSOR FOR
-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0
BEGIN
-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
END
SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END
IF @AllowNulls = 'Y'
BEGIN
SET @SQLTable = @SQLTable + ' NULL'
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ' NOT NULL'
END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END
CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF
SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table
EXEC(@SQLTable)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
END
View 13 Replies
View Related
Dec 20, 2006
Hi,
To programmatically invoke a subscription we can use -
exec ReportServer..AddEvent 'TimedSubscription', '575F96C6-A1BD-49FD-9C2F-934FC9658780'
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
TIA,
Sam.
View 4 Replies
View Related
Aug 14, 2007
Hi,
Will someone please tell me how to create Report history(snapshots) in reporting service 2005.
Thanks and regards
Altaf Nizamuddin
View 1 Replies
View Related
Nov 1, 2014
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
View 1 Replies
View Related
Jan 18, 2006
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???
View 6 Replies
View Related
Jan 18, 2005
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,
View 6 Replies
View Related
Jul 15, 2015
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies
View Related
Mar 28, 2008
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
Code Snippet
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
View 6 Replies
View Related
Mar 12, 2008
Hi All,
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.
View 4 Replies
View Related
Oct 17, 2005
Here is the situation:Tables:AccountAccountDocumentAccountTestThen we have the following "Activity" table refencing "Account" tableabove:Table: ActivityColumns: ActivityID, AccountNo,...when "Activity" with accountno is created, we'd like to able to takesnapshop or copy all Account related data records from tables: account,accountdocument, accounttestand store it somewhere.This way even if account, accountdocument and accounttest tables changelater, then "Activity" accountno integrity in maintained.Do we need to create or replicate similar tables to store data foraudit?or Which other ways can this be achieved?
View 3 Replies
View Related
Mar 30, 2007
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
View 5 Replies
View Related
Mar 25, 2015
how to get products on which SalesPerson changed. Here is table with data.
View 9 Replies
View Related
Oct 3, 2007
We are using a SQL Server 2000 Replication.
I'm using the Merge Agent History Screen to retrive Informacion about
replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session?
Thanks
View 5 Replies
View Related
Mar 4, 2005
I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.
The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.
I figured I'd add the needed code to the stored procedure that's doing the update for the record.
When the stored procedure is called to do the update, the PK and parameters are sent.
The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:
Name, DateTime, Changes
FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204
How does the above approach sound?
Is there a better way you'd suggest?
Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?
Thanks,
Peter
View 3 Replies
View Related
Jun 27, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Aug 25, 2015
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]
[code]...
View 2 Replies
View Related
Jun 30, 2015
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?
View 5 Replies
View Related
Jul 24, 2015
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)
View 10 Replies
View Related
Aug 31, 2004
Hi There,
I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line:
Length = Convert.ToDouble( txtLength.Text )
I'm also using the following lines to prepare my stored procedure call:
arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5);
arParms[9].Value = record.Length;
My stored procedure has the following parameter definition:
@Length decimal(9,3)
My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error:
System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.
I don't know how to either make sql or C# to truncate the value or catch the exception to automatically assign 0 to the parameter.
Please Help.
Moshe
View 1 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
Aug 31, 2006
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View 3 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Sep 28, 2006
Hi,
I'm just wondering what's the best approach in Data Flow to convert the following input file format:
Date, Code1, Value1, Code2, Value2
1-Jan-2006, abc1, 20.00, xyz3, 35.00
2-Jan-2006, abc1, 30.00, xyz5, 6.30
into the following output format (to be loaded into a SQL DB):
Date, Code, Value
1-Jan-2006, abc1, 20.00
1-Jan-2006, xyz3, 35.00
2-Jan-2006, abc1, 30.00
2-Jan-2006, xyz5, 6.30
I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View 3 Replies
View Related
