Keeping The Audit Info Intact.

Apr 10, 2008

Hi all

I have a stored procedures with few DML stmts inside.

I need to log the success/failure of each DML statment into seperate audit table as follows

Begin
DML Stmt1
Log sucess/failure (Insert stmt to audit table)
DML Stm2
Log sucess/failure (Insert stmt to audit table)
end

when any of the above DML stmt fails, the entire transaction is getting rolled back including the audit information.

How can i keep the audit info intact when the transaction is rolled back.

Thanks.

View 1 Replies


ADVERTISEMENT

Purging A Database Data While Keeping The Structure Intact

Oct 15, 2006

Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis?   If I simply delete all the records the identities for the tables do not revert back to 1.

View 2 Replies View Related

What Info Should A SQL Server Audit Contain?

May 13, 2006

That is to say, from a management perspective? I've been asked to provide one, as I'm the first DBA there ever, but I'm not sure what depth of info they want. And, does anyoe have scripts that can perform the audit, or an actual example audit?

I'd imagine an audit should encompass the following:

Database names;
Database sizes;
Location and size of data/temp/log etc. files;
Hardware specs (cpu/memory/disk sizes);
Number of logins;
User applications.

I'm sure that's only the tip of the iceberg, I'd be grateful for your input!

Thanks in advance,


Jaybee.

View 4 Replies View Related

Truncate All Tables In DB With RI Intact

Jun 22, 2004

Anyone?

I tried the following code...thought I could trap the RI Error and move on...

It raises all the way out though....

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'myTable99' because it is being referenced by a FOREIGN KEY constraint.

I thought you could trap it this way...


CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO
CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
GO

INSERT INTO myTable99(Col1) SELECT 1
INSERT INTO myTable00(Col1, Col2) SELECT 1,2
GO

CREATE PROC myDynamicSQL99 @sql varchar(8000) AS EXEC(@sql)
GO

DECLARE @error int, @TABLE_NAME sysname, @sql varchar(8000), @x int, @rc int
SELECT @error = 0, @x = 0

DoItAgain:

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'myTable%'
ORDER BY TABLE_NAME DESC

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'TRUNCATE TABLE ' + @TABLE_NAME
SELECT @SQL
EXEC @rc = myDynamicSQL99 @SQL
SELECT @Error = @@Error
SELECT 'Error Code: ' + CONVERT(varchar(15),@Error) + ' @rc: '+ CONVERT(varchar(15),@rc)
IF @Error <> 0
SELECT @x = @x + 1
FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
END

CLOSE myCursor99
DEALLOCATE myCursor99

IF @x <> 0
BEGIN
SELECT @Error = 0
GOTO DoItAgain
END
GO

DROP PROC myDynamicSQL99
DROP TABLE myTable00
DROP TABLE myTable99
GO

View 14 Replies View Related

Database Audit Specification To Audit Select On Certain User And Table

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

Audit Logon / Audit Logoff Problem With SQL 2K

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

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

Keeping Job History ...

Aug 26, 2002

SQl7, sp3, NT4

How do I keep th job history of a job, say if I re-create the job?

We recreate the jobs often as part of a code move, but I'd like to retain the history of the previous jobs?

** sp_help_jobhistory -- only shows the jobs that exist, and not old jobs that no longer reside on the server.

Thanks,
AJ

View 1 Replies View Related

Keeping 2 DBs In 'synch'

Jan 10, 2005

Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.

Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.

What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!

View 5 Replies View Related

Keeping Last 10 Entries By ID

Sep 24, 2006

Hello

my table :
Report :
R_id (PK)
RName
RDate


i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name

i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)

how can i do it ?

thanks a lot for helping

View 1 Replies View Related

Keeping Decimal Value

May 23, 2008

Hi, I have the following code in a query:

SELECT [Issue date],DATEDIFF("dd",[Issue date],[Start date])/365 AS runningdays
FROM Database1..[Insurance Policies Working DB]
WHERE [Policy Number] LIKE '%1368529%'

The part 'DATEDIFF("dd",[Issue date],[Start date])' comes out as 364 if calculated on its own. However, then when it is divided by 365 it comes out as 0. How do I get it to show as a decimal instead of just rounding it down automatically? (Hope I've made sense)

Thanks in advance

View 3 Replies View Related

Keeping Tables Sorted

Nov 28, 2003

we have a simple table

Key, Name, Address, City, State, Zip ................ect

I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.

I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.

Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.

Appleby
Jones
Robertson

This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.

thanks in advance -arthur

View 3 Replies View Related

Keeping Track Of Table Changes ...

Nov 7, 2001

Hi

We need to keep track of all changes that are made to our tables.

The changes will be saved in a table that records:

- the table in which the change was made
- the name of the field that was changed
- the old data for the field
- the new data for the field etc..

I've seen a few examples that record the name of the table that was
modified but none that record done to the field level.

Can anybody give some guidance?

Thanks..

Wayne

View 1 Replies View Related

Keeping A Lock On Table Or Row

Feb 5, 2004

I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?

View 4 Replies View Related

Keeping Breakpoint And Debugging

Feb 27, 2007

hi, is there any possiblity to keep break point and run the step by step compilation as like we do in .net,if means please telll me how to do?

View 3 Replies View Related

Help With Keeping Track Of Payment

Nov 13, 2007

Hello All,

I have a problem concerning keeping track of a value within a query.
I have a table that tracks invoices recieved and payments made.
For each invoice number there may be multiple payments made against it.
I need something that will check and make sure that each invoice number has its payments equal to its received amount.

Any help would be greatly appreciated.
Thanks,

View 5 Replies View Related

Keeping Database As Small As Possible ?

Oct 16, 2006

Hello everyone,



I have a winform application with C# front end and sql express 05 backend.

In this database i have a table that holds manufacturer provided pricing and the manufacturers we work with update pricing constantly.

We have one table called "manufacturerpricing" which we are constantly inserting and deleting pricing records to/from to keep manufacturer pricing up to date. We may insert and delete as many as 2,000,000 records per month into this table.

This works perfectly fine and we have no problems here at all.

But with that being said, I am worried about the size of the database growing out of control due to temporary space etc. The database just keeps getting bigger and bigger.

How do I run some maintenance to keep the database size under control.

I would like to run this automatically from the C# front end so if ther is a stored proc I can call or an C# assembly I can reference that would be ideal.

Any help is greatly appreciated.

View 3 Replies View Related

Keeping State Between Calls To A UDF

Dec 14, 2006

Hi,

I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.

Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?

Thanks,

Alex

View 4 Replies View Related

Recomendations For Keeping Track Of Changes

May 8, 2007

Let's say I develop a 'version 1' of a program/database and ship it to some customers.

Then I continue work on version 2 with some databasechanges etc.

When version 2 is shipped to the customers, they need a way to upgrade their database

from version 1 to version 2. I guess this is easiest done with one/several sql-scripts.



What I want is your opinion about the best way to keep track of the changes between

version 1 and 2. Here are two ways with pros and cons I've thought of:



1 - Keep manual track of every change (index, changed columns, relations etc) and update a scriptfile with all changes.

Pros: Full control of what is happening

Cons: Pretty much extra work and risk of missing some changes.



2 - When version 2 is ready, run some third party tool to get a diff between the databases

Pros: Fast and easy

Cons: The DB-changes may need to be applied in a special order, with default values, etc etc and I'm not sure all tools handle this in a good way...?



So... what is your recomendations? I'm sure this must be a headace in every development project?



Regards Andreas

View 13 Replies View Related

Keeping Dirty Data

Jan 24, 2008

This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.

Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?

View 4 Replies View Related

Keeping An Accurate Count Of Events

May 16, 2006

I am newish to databases and would appreciate some
advise.  I think I have a solution to my
problem but it is going to take me a lot of time to get it running.  If there is a better way of doing it I would
like to know. 

I have a table :-





 â€śeventDatesâ€? with
columns (id, date, eventID,
eventCount)







The id auto increments as a Primary Key.
date holds the date of the
event.
EventID references another
table with info about the events
Up to 9 eventIDs can be added
for each date and I want eventCount to hold an integer (1 to 9) to allow me to “pivot�
the data to the table below




“results� with columns (date,
eventCount1, eventCount2 …..eventCount9) so each row will hold a date and non
to nine eventIDs occurring on that date.





Is there an easy way to keep eventCount accurate or do I
just have to write a lot of code?  I
will need to be able to remove events as well as add them.  I will use a mixture of stored procedures
and VB.Net I guess?





Many thanks for any advice.





Mike

View 4 Replies View Related

Keeping DTS Return Messages In A Log Table

Jul 2, 2001

Hi, I am trying to find a way to capture all the status (Start time, execution time, Status messages etc) from executing a DTS package in to a table I will create in a database, does anyone know, where those information being kept?
When I excute the DTS package manually, a window will come up and show the status of each step within the DTS package. I am hoping to capture these information and load it to my log table.

Thanks in advance.

View 1 Replies View Related

Keeping Entire Table In Memory

Aug 17, 1999

How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.

View 2 Replies View Related

Keeping Relationship Settings On Export

Sep 19, 2000

I'm exporting a large database. In enterprise manager the settings on
all of the PK/FK relationships are that
"Enable relationship for Insert AND Update" is UNchecked.
I need it this way, so I can delete and insert to the tables
without being hassled by THE MAN.

When I export the database, using DTS I export all the objects (EVERYTHING),
and all the data too. When I open the freshly copied database and get
properties on any relationship "Enable relationship for Insert AND Update"
is CHECKED! ARGH!

How do I keep this from happening? I'm so frustrated.
It is very time consuming to uncheck that darn box on hundreds
of relationships. Why doesn't it just stay the way it is set in
the original source DB ??

Is there a way to export a database and keep it EXACTLY the same?

If anyone can help me with this it would save me dozens of hours in work.
Thanks in advance.

Josh

View 10 Replies View Related

Log Trigger - Keeping Track Of Status Changes

Aug 28, 2015

I have a trigger that keeps track of status changes...

IF UPDATE(STATUS)
BEGIN
DECLARE @currentdate datetime
DECLARE @currentstatus integer
DECLARE @UserID integer
DECLARE @PermitID integer
DECLARE @Status integer

[Code] .....

It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table.

View 3 Replies View Related

Keeping A Production And Test Db Insync

May 14, 2004

I have just finished upsizing an Access database to SQL Server 2k. Now the SQL Server need to be run on a test basis to determine if i need to make more changes to the front-end (Access). The problem I am facing is how to keep the two databases in sync while I am testing. Any suggestions?

Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.

Let me know if anyone needs more info.

Thanks in advance.

View 1 Replies View Related

Keeping 5 Days Worth Of Backups

May 13, 2008

Hey all,

I'm trying to set up a backup schedule in SQL 2005 that will keep 5 .bak files within the folder I specify. I can get it to keep one backup only even though I specify that it is to keep 5 days worth of backups. Each backup happens at 12:00 am. I think the problem I may be having is I don't know how to get the maintenance plan to name the file mmddyyyy.bak or whatever it may need to determine that the file is 5 days old. Currently I am specifying a name of gmsqlbackup.bak. Any idea's that may help me out?

View 2 Replies View Related

Keeping Report Pages From Showing

Jun 18, 2008

I have a report that pulls a customer balance.
In crystal there was a way to have the page not show up if it met a certain criteria(say if the balance was 0 or negative).

I'd rather not filter them in SQL because it takes a few calculations to figure out what their balance is, and I already have SRS doing that calculation.
So is there a way to have a report page not print based on a certain criteria?

View 4 Replies View Related

Importing Tables Keeping Username

Feb 4, 2008

i'm importing tables from another sql server -
now the tables are coming is as
esther.tablename (my username on the other server)
instead of
dbo.tablename

how can i change this?

View 9 Replies View Related

Dev. Team - Keeping Three Tiers In Sync

Jul 23, 2005

This may not be a MSSQL-specific question, butI wanted to ask it here first, in case there'sa MSSQL and/or SourceSafe solution that will help.Our dev team is having some difficulty withkeeping the nightly builds in sync with thestored proc mods. I'm wondering if there aresome good case studies on how to avoid this"drift". Something like genning a new DB fromchecked-in SPs, etc. alongside each regular build,then always have a paired enterprise app/databaseduo that is tagged and added to a history.FWIW, we have a 3-tier .NET/C# app, andADO.NET is throwing exceptions every otherday.If the suggestion is to whip the DB guys, thatworks for me as well. ;-)Nah, there's much love there.Thanks in advance,~swooz

View 2 Replies View Related

SQL 2005: Keeping Track Of Database Changes

Jul 5, 2006

I'm actually taking Microsoft's 2779 and just finished a lab where wekept track of our changes to the database.However, I'm not happy with the scripts interface because it does nottell me the chronological order of my changes to the database.Could someone share with me their technique for keeping track ofdatabase changes?I'm actually thinking a set of tables would be best, because sometimesyou want to know what database object you made a change to and othertimes you want to know when you did something...

View 2 Replies View Related

SQL - Filter Out Duplicates But Keeping All Columns

Jul 20, 2005

Hi guys,I'm trying to compose a query that will select all columns from atable, but without any duplicates.E.g.table name is 'tblShipment'columns are:fldUnique(pk) | fldShipNo | fldDate | fldValue001 | 123 | 02/02/02 | 2000002 | 222 | 01/01/01 | 3000003 | 123 | 03/03/03 | 4000004 | 444 | 04/04/04 | 5000I want to be able to select all columns (4 columns) withoutduplicates, which should give me a recordset:fldUnique(pk) | fldShipNo | fldDate | fldValue001 | 123 | 02/02/02 | 2000002 | 222 | 01/01/01 | 3000004 | 444 | 04/04/04 | 5000** 003 is not selected becoz it's fldShipNo is identical to that of001.How should I go about querying that?I've tried to select it with DISTINCT, but i'm unable to use DISTINCTto select all columns. It is important that I have all columnsselected, especially the primary key (pk), becoz I need it forreference.I'm really having trouble trying to achieve this, so I hope someonecan help me out.Thanks,Shawn

View 3 Replies View Related

SQL Reporting Service Keeping Together Cells

Feb 21, 2008



Hello,

I currently have a report that has one table with rows that "can grow". When the report is generated, everything works accordingly, but if the row is too long, the row does not get cut in half, but rather, the whole row gets moved over to the next page. When this happens there ends up being a big white space from the last row to the end of the page, then the next row continues on on the next page. What I want is the row to be split if there is too much data instead of trying to keep the row in one peice. Is there an option to do this? I already have keep together unchecked.

Thanks.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved