.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Help required regarding audit (Update/INSERT/DELETE) a table in Database.

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server

Hi all


Help required regarding audit (Update/INSERT/DELETE) a table in Database.



I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.


Now I want find that who is doing this


View Complete Post

More Related Resource Links

select ,insert,update and delete is not getting recorded in the database audit spec.



I am using SQLSERVER 2008 Standard Edition and i m trying to implement database audit for few database. Following is the script that i used:


SERVER AUDIT [audit_mirror]






SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

How to archieve the field template that can update or insert multiple field value in database table,



How to archieve the field template that can update or insert multiple field value in database table,please? 

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

reading xml data and insert into database table

hi friends, .very urgent for me so only i send following script i create for read xml data and to import to table. i got result for reading xml and rows are added to table but the values are showing null pls clarify this problem. thanks alter procedure pizza_sales ( @xml xml ) as begin INSERT INTO pizza_xml (LOC_CODE, CUST_CODE, PRODUCT_ID, INV_TYPE, INV_NO, INV_DT, INV_QTY_IN_INV_UOM, INV_RATE_IN_BS_CURR, INV_VU_IN_INV_CURR, -- TOTAL_DISC_IN_BS_CURR, NET_INV_VU_IN_INV_CURR, NET_INV_VU_IN_BS_CURR, TOTAL_TXES_IN_BS_CURR ) select table1.column1.value('@LOC_CODE','nvarchar(75)'), table1.column1.value('@CUST_CODE','nvarchar(75)'), table1.column1.value('@PROD_CODE','nvarchar(75)'), table1.column1.value('@INV_TYPE','nvarchar(50)'), table1.column1.value('@INV_NO','nvarchar(100)'), table1.column1.value('@INV_DT','datetime'), table1.column1.value('@INV_QTY_IN_INV_UOM','numeric(28,8)'), table1.column1.value('@INV_RATE_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@INV_VU_IN_INV_CURR','numeric(28,8)'), --table1.column1.value('@TOTAL_DISC_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_INV_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@TOTAL_TXES_IN_BS_CURR','numeric(28,8)') from @xml.nodes('pizza/pizzaxml')as table1(column1) end declare @id

Allow or deny roles to update, insert and delete records.

Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

MS Linq To SQL Samples - Insert/Update/Delete

Hi, I'm stumbling along trying my hand at Linq to SQL and have come across this code below that was provided by MS.http://msdn.microsoft.com/en-us/vbasic/bb737928.aspxThe problem is there is no ".add" method for Categories to chose in the statement db2.Categories.add(newCategory)I've seen similar videos here on ASP .NET that uses code very much like the example below, but I am unable to run this example connected to a Northwind SQL database using Web Developer 2010 Express. This is the One-to-Many example listed under Linq To SQL Samples - Insert/Update/Delete. Thanks for any help! Shawn Dim db2 = New DataClassesDataContext Dim ds = New DataLoadOptions() ds.LoadWith(Of Category)(Function(p) p.Products) db2.LoadOptions = ds Dim newCategory = New Category With {.CategoryName = "Widgets", _ .Description = "Widgets are the customer-facing analogues " & _ "to sprockets and cogs.", _ .Picture = Nothing} Dim newProduct = New Product With {.ProductName = "Blue Widget", _ .UnitPrice = 34.56#, _ .Category = newCategory, _

SQL Checking the table before insert or update

I need your help with a SQL Stored Procedure PLEASE!!!GOAL: I need to check TableA for the ItemID if the ItemID Is Not found Insert a new record. If the ItemID Is found Update existing record.***********************Below is Incorrect***********************Please HELP!!!!IF @ItemID is not null          Set NewRecord =  Select ItemID FROM ContentItems Where EXISTS  (select ItemID from ContentForm_Fields where ItemID=@ItemID)IF NewRecord is not null   Update ContentForm_FieldsELSE Insert ContentForm_Fields

Update, delete, insert

I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases. How can I make the second database update, delete and insert?  

Update, Delete, Insert Options

I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases.

How do I update a normalised database table


Hi, I am a hobbyist learning SQL and .asp and am using Visual Web Developer Express 2008 to produce a website for a sports club. 

I have created normalised data tables to hold look up information (such as venues, players, teams etc) and want to dynamically update the 'matches' data table (my transactions) using webpages instead of running SQL queries.

What I want to do is select a name from a drop down list and then post the relevant ID associated with that name to my 'matches' table. At the moment I can only link to the value in the drop down list, i.e. the name which is incompatible with the validation which expects a primary key integer.

I'm sure this problem is a fairly common one but I haven't found the right answer on any of the tutorials or blogs, all of them assume that the data is de-normalised or describe things in more technical ways than I can yet understand. If anyone can point me to a 'how to article' or tell me what I am doing wrong, I would be grateful.

Trigger without UPdate,INSERT and DELETE


I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.




Why deadlock occurs using INSERT/UPDATE in a table with 2 sessions



I am working on a problem in my odbc application, which i have isolated to the MS SQL Server query analyzer.

The problem is related to the deadlock faced by one of the session, when 2 transactions each running from 2 sessions are trying to INSERT a record from their respective session with the duplicate values for the columns icol1 and cCol2 and then one of the session UPDATE the value of column icol1 in the previously inserted record. After the completion of the test, I see 1 record created in the table from one of the session, but other session becomes deadlock victim.

Following is the table structure:

CREATE TABLE [sgarg].[test_buf](

[icol1] [int] NULL DEFAULT ((0)),
[ccol2] [varchar](30) NULL,
[ccol3] [varchar](30) NULL,
[RECID] [bigint] NULL

Following are the index definitions

CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
 [icol1] ASC,
 [ccol2] ASC

CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]

Save/Update/Delete Sql Server data base table using Sharepoint Designer


Can some one help me to achieve below task

Save/Update/Delete Sql Server data base table using Sharepoint Designer.

Insert, Update, Delete with ajax


I'm kind of new to ajax. I normally just use  the canlendar and some popup stuffs.

i'm wondering what is the practice to doing Insert, Update, and Delete on  GridView/FormView with ajax.

Is UpdatePanel the best thing i can use it with Insert, Update, and Delete?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend