View Complete Post
I have a reports table with column report id and and report version. I created a trigger on this table to execute an application to do some processing each time a row is inserted in reports table. I need to pass the new report id execute this application from the trigger. How do I get the report id from the row that was inserted?
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
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.
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
) ON [PRIMARY]
Following are the index definitions
CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPU
EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'
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!
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
Need suggestion on Mutiple table insert and update in DAL I have two datatable and its own table adapter and both are realated . What is the best practice to update or insert both table . . Any ideas
I have two datatable and its own table adapter and both are realated . What is the best practice to update or insert both table . . Any ideas
If I create a data tabe in C# like so:
datatable t = new DataTable;
DataColum C1 = new DataColum;//ID column
//the same with column C2
Then I fill up the table with some data.
Then the goal is to either insert this data or update data in an SQLtable in SQL. If the data is inserted, the ID column in t would be empty, if updated, it would not be. So in the insertion case, the sql query needs to make sure that unique IDs are assigned during insertion, in the update case, it just has to look for the right IDs.
How to go about this?
Asking because I am only familiar with updating and inserting single lines into a table. One could loop in C# over the datatable t of course and insert or update line by line, but this would probably be slow. Is there a way to write an SQL query like this somehow:
Insert Into SQLTable Values (@t)
And Update like this:
Update SQLTable SET ... Where C1 = @t.C1
Or something like that.
I know that SQL server can create automatic update and insert queries and that you can then type things in C# like
But, this has proven to be super slow on our server, for unknown reasons. I have asked on this forum and got some answers from experts, but was not able to srot out the problem.
How can we write
I read many post about Intergrating Fckeditor to ASP.Net MVC, and I have done.
Now, I have tbl_Articles table, it contain some articles... How to Insert - Update - Create articles from TextArea of FCKeditor (ASP.Net MVC)
Please give some way for me. Thanks.
here is some question for you and i'm sure you guys will have awsome solution
i have a table with companyid and tranid as a primary key. there is a insert trigger on it and the trigger insert property is instead of insert, means data is manually inserted thru trigger.
in trigger, i check if companyid and tran id exists in the table or not, if yes then i add a record in another table (history table/duplicate table) and if not found then i add record in this table.
this stuff work perfectly if i work in ssms and it is all good but when i work thru ssis, it gives me following error and it clearly showing that it is because of pk
[Insert Data in EFT ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Mail_Queue'. Cannot insert duplicate key in object 'dbo.EMail_Queue'.".
Somebody is making updates to a table that shouldn't be updated! I've been asked to track which user, the updated column name(s), and the before and after value(s). It doesn't matter how many or which field(s) had the value updated. If
it was updated, we need to track it.
The straightforward method seems to be using a DML update trigger like the example shown at
http://msdn.microsoft.com/en-us/library/ms187326.aspx, but there's over 30 columns in the table that need to be watched. From the example at that page, it appears I would need a "IF UPDATE(my_col_name)"
statement for each and every column we'd like to watch. Correct?
How would we ID the user that made the change? I assume there's a variable available within an update trigger that holds that value.
My main question ends there, but here's a little more background that may affect any advice:
1. We can't track changes in a table. Instead, they have to be written to the SQL Log, which I've found can be accomplished using "xp_logevent".
2. A separate log entry for every field updated in a row is not a requirement. One log entry with a row showing the old values and another row showing the new values would suff
How to archieve the field template that can update or insert multiple field value in database table,please?