I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:
empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.
Problem: When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said:
Command attempted:
Similar errors occur periodically, and the filename (C: empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.
I have a database replicated to another database in a remote server. We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated). Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this. I appreciate your help Thanks, Jeyam
Hi,I have an issue with my replication at the moment. I will try todescribe the scenario accurately.I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connectto the publisher to upload/download changes. I have a trigger set up onone table which updates another, here is an example of the trigger:"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotesFOR INSERT, UPDATE, DELETEASdeclare @projTotal as moneydeclare @projId as intdeclare @projcurrtype as intselect @projId = project_id from insertedselect @projcurrtype = proj_curr_type from qt_projects where project_id= @projId--Get project total from the sum of table [qt_quotes]select @projTotal = (selectsum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@projcurrtype))as quoteTotal from qt_quotes where project_id = @projId)--Update projects record with new project totalupdate qt_projectsset proj_act_totl = @projTotalwhere project_id = @projId"I feel my trigger maybe setup incorrectly in that replication thinks aninsert is occurring instead of an update. (Im quite new to triggers)What is happening is a conflict is occuring with the following message:"The row was inserted at Server.Publisher' but could not be inserted at'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGNKEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred indatabase 'Publisher', table 'qt_projects', column 'project_id'."What is also happening as a result of this conflict (I think) is therecord in question is getting deleted from the Publisher. This iscausing huge problems as it is proving quite difficult to get theserecords back in the system due to identity values.Can anyone guide me to what might be happeing here, is it the trigger?
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
What problems do trigger driven replication impose? I've got two SQL servers. When one is updated, triggers will automatically update the information on the production server. What problems can arise from this? Does it create problems when mass updates and inserts are being performed on tables with triggers? Thanks
I am testing a merge publication with a push subscription. The publication contains a single table. That table has an INSTEAD OF UPDATE trigger marked NOT FOR REPLICATION and an AFTER trigger that needs to go off during replication.
During Synchronization neither trigger goes off. If I drop the instead of trigger the after trigger will go off. What is going on?
Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.
Here is the problem:
When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:
The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).
Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.
We are getting this behavior on several SQL Server 2005's...
Additional notes on the environment:
-CLR Triggers written in VB, on most tables performing application functions
-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.
-Again this problem only exists on some tables in the database.
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record. The second trigger inserts record in to another database table and also updates a column of the table "Sample". The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser. Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
Hi: I have created the following trigger. alter trigger deletecategories on Product_Tbl_ProductDetailsfor delete as delete CategoryID from Product_Tbl_Catagory,Product_Tbl_ProductDetailswhere Product_Tbl_Catagory.CategoryID=Product_Tbl_ProductDetails.CategoryIDdelete SubcategoryID from Product_Tbl_SubCatagory,Product_Tbl_ProductDetailswhere Product_Tbl_SubCatagory.SubcategoryID=Product_Tbl_ProductDetails.SubcategoryID While deleting the record "delete from Product_Tbl_ProductDetails where CategoryID=1"I have encountered the below error Error:Msg 208, Level 16, State 1, Procedure deletecategories, Line 4Invalid object name 'CategoryID'. Can any one send me the correct query Thanks:Suresh
When I execute a query that updates multiple rows, the trigger issues an error. Does anyone see the problem ?
Listed below is the Error, Trigger, and Query.
Server: Msg 512, Level 16, State 1, Procedure TR_KYLAB_LOT_GRADE_01, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
CREATE TRIGGER TR_KYLAB_LOT_GRADE_01 ON KYLAB_LOT_GRADE FOR UPDATE NOT FOR REPLICATION AS Update KYLAB_LOT_GRADE SET updateUserName = user_name(), updateDateTime = getdate() WHERE KYLAB_LOT_GRADEID = ( SELECT INSERTED.KYLAB_LOT_GRADEID FROM INSERTED)
UPDATE KYLAB_LOT_GRADE SET OLD_ITEM = 'TEST' WHERE ITEM_TYPE_ID = 'FG' AND ITEM_NO = '2011312LV0' AND LOT_NO IN ('1000', '1001')
Hi everybody, My SQL 2000 refuses to recognize the INSTEAD OF trigger. I just pasted a query from the SQL Server Books Online article "Designing INSTEAD OF Triggers" into the Query Analyzer and was greeted with:
Server: Msg 170, Level 15, State 1, Procedure IO_Trig_INS_Employee, Line 2 Line 2: Incorrect syntax near 'INSTEAD'.
The following trigger is in a table called "PERSON". When I attempt to save it (not even testing it yet), I get the message "Incorrect syntaxt near 'GO'". Please advise as to what the error might be.
CREATE TRIGGER InsertHeadCount ON dbo.person AFTER INSERT AS INSERT INTO HEADCOUNT(person_id,activity_id,no_answer,not_coming,coming) SELECT i.person_id,a.activity_id,1,0,0 FROM INSERTED i CROSS JOIN ACTIVITY a GO
Background: I'm building an application that includes tables PERSON, ACTIVITY, and HEADCOUNT that look like this:
PERSON person_id (int) person_name (varchar(50)) ...etc
The intended functionality of the trigger is as follows: Suppose that:
a) The ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3. b) The HEADCOUNT table is already populated with 30 records.
TRIGGER OBJECTIVE: When a new record is added to the PERSON, say with person_id= 10, insert one record in the HEADCOUNT table FOR EACH activity in the ACTIVITY table for that person. Thus, if person #10 is added to the PERSON table, then the trigger would add the following records to the HEADCOUNT table:
I made a trigger for delete just like this."CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]FOR DELETEASDeclare @severity int,@IdNmbr nvarchar(10)Set @Severity = 0Declare NoId Cursor Local Static forSelect [Promo_Id] from deletedOpen NoIdWhile 1=1BeginFetch NoId Into@IdNmbrIf @@Fetch_Status <> 0BreakIf Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@IdNmbr)Set @Severity = @Severity+1ElseBeginDelete From MbPromo_hdr where [Promo_Id]=@IdNmbrEndEndClose NoIdDeallocate NoIdIf @Severity = 0CommitElseBeginRollBackPrint 'Data Cannot Be delete'EndGo"When i delete the record from Enterprise manager it give me an error"Another user has modified the content of this table or view. The databaserow you are modifying no longer exists in the database."Why? And it happen with all of the record at my table--Message posted via http://www.sqlmonster.com
I have a table:----------------------------------------------------CREATE TABLE CATEGORY (CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))GO----------------------------------------------------and i try to create this trigger, but i fail:----------------------------------------------------CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]FOR INSERTASDECLARE @TEMP_ID AS INT, @COUNTER AS INTUPDATE AI_CATEGORYSET @TEMP_ID = CATEGORY_ID, @COUNTER = 1IF PARENT_CATEGORY_ID IS NOT NULLBEGINWHILE@TEMP_ID IS NOT NULLBEGINUPDATE AI_CATEGORYSET @TEMP_ID = PARENT_CATEGORY_ID,@COUNTER = @COUNTER + 1ENDENDUPDATE CATEGORYSET DEPTH = @COUNTER----------------------------------------------------And i get the following Error using SQL Server 2000:Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7Invalid column name 'PARENT_CATEGORY_ID'.Any clue?Coosa
I have the following CREATE TRIGGER dbo.tgrCacheCustomers ON dbo.Customers FOR INSERT, UPDATE, DELETE AS EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers' and I get the following error that I dont understand:
Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.
The trigger does set my CommAudit field to 1 when imaSlsCls is changed, but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below. Thanks for any help, Tom
*** TRIGGER *** CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster FOR UPDATE AS IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted) BEGIN update tblItemMaster set commaudit = 1 from inserted, tblItemMaster where inserted.imaitnbr = tblItemMaster.imaitnbr END
*** UPDATE COMMAND *** UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1
*** ERROR *** Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
I have SQL Server 2000, and our web application is in WebObjects.
I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.
This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.
Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.
Here is the relevant portion of the trigger:
create trigger t_press_run_change on dbo.press_run_line_item for insert, update, delete as
updatepress_run_line_item setis_changed = 1 from deleted d join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id where(p.is_changed = 0 or p.is_changed is null) AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0)) OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0)) OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0)) OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0)) OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,'')) OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,'')) OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,'')) OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,'')) OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,'')) OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,'')) OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,'')) OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0)) OR(isNull(p.address_company,'')<>isNull(d.address_company,'')) OR(isNull(p.address1,'')<>IsNull(d.address1,'')) OR(isNull(p.address2,'')<>IsNull(d.address2,'')) OR(isNull(p.address_city,'')<>IsNull(d.address_city,'')) OR(isNull(p.address_state,'')<>IsNull(d.address_state,'')) OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,'')) OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,'')) OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))
Please can anyone help me code the trigger below. I have had errors pointing to "sp_configure 'SQL Mail XPs', 1;" and "RECONFIGURE", that this lines should be not where I have placed them. I have tried other combinations and have not worked at all! Is any other ways I can achieve this all?
Code Snippet
CREATE TRIGGER reminder ON Bug_Report FOR INSERT, UPDATE, DELETE AS sp_configure 'SQL Mail XPs', 1; EXEC master..xp_sendmail 'MaryM', 'Don't forget to print a bug report.' RECONFIGURE
hi....... i create a trigger after delete which work perfactly fine on one sqlserver2005 machine but not at another.......machine having sqlserver2005 this trigger effect 15 tables from which i am deleting data...when trigger fires..... now it cause following error when i fire ir on another machine error source: .net sql client data provider error message:maximun stored procedure,triggers,functions,or view nesting level exceeded (limit 32) plzzzz give da solution.....its really frustrating me.... thanks in advance
The many that I have spoken to all are clueless on this one. Thanks in advance for the right solution!
The insert trigger I created works fine (well, nearly fine), except that AFTER the first insert operation (ie second, third etc), it always produces the correct results BUT FOR THE PREVIOUS INSERTED ROW. It is as if there is a latency of one row in the temp table INSERTED.
I would greatly appreciate a 'why', and more importantly, a 'how to fix it' for this problem.
If you need to look at the code, a NOTEPAD file is attached.
Much appreciated
--start trigger-- create trigger UpdateAffiliateEarnings on Orders for insert, update as
--check existence of affiliateid, and for product type select @AffID=AffID, @ProductType=Source from Orders where AffID IS NOT NULL
--get relevant information if @AffID IS NOT NULL begin if @ProductType = 'FLOWER' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,FlowerOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end if @ProductType='PHONE' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,PhoneOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end
--update Affiliate account --get totals to update select @CurrentEarnings=AffTotalEarnings, @AffTotalPayments=AffTotalPayments from Affiliates where AffID=@AffID
--calculate new totals for affiliate account set @AffTotalEarnings=@CurrentEarnings+@Earnings set @AffOutstandingBalance=@AffTotalEarnings-@AffTotalPayments
--update affiliate account to new totals update Affiliates set AffTotalEarnings=@AffTotalEarnings, AffOutstandingBalance=@AffOutstandingBalance where AffID=@AffID
--roll back the transaction if there is an error if @@ERROR !=0 rollback tran end -- end of trigger --
Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:
sql Code:
Original - sql Code
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync ON smvcModImageManagerCategory FOR INSERT, DELETE AS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 );
-- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END
-- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; END GO
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO
However, when I execute a statement like:
sql Code:
Original - sql Code
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.
[code = "sql"]CREATE TRIGGER Trigger_20321 ON FACT_CUST_GRP_ICM_MO AFTER DELETE AS /* DELETE trigger on FACT_CUST_GRP_ICM_MO */ /* default body for Trigger_20321 */
[Code]...
Msg 102, Level 15, State 1, Procedure Trigger_20321, Line 21 Incorrect syntax near ')'.
I have the following trigger that updates a couple test fields to null when they are 1/1/1900, works great on inserts, and one line updates:
CREATE TRIGGER UpdateDate ON test AFTER INSERT, UPDATE AS UPDATE Test SET [CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END, [CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END where AutoID = (select AutoID from inserted)
However, when trying to do a multi line update statement, I get the following error:
Msg 512, Level 16, State 1, Procedure UpdateDate, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated
I need a trigger (well, I don't *need* one, but it would be optimal!)but I can't get it to work because it references ntext fields.Is there any alternative? I could write it in laborious code in theapplication, but I'd rather not!DDL for table and trigger below.TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblMyTable]GOCREATE TABLE [dbo].[tblMyTable] ([fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTableFOR INSERTASBEGINIF UPDATE(fldKBSubject)BEGINUPDATEtblMyTableSETfldSubject = i.fldKBSubjectFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDIF UPDATE (fldKBDescription)BEGINUPDATEtblMyTableSETfldDescription = i.fldKBDescriptionFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDEND
I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
I'm stumped on this trigger error and I'm hoping someone can help. Here's the background:
BACKGROUND Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error.
PROBLEM I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error:
SQLDBCode: 229Alerts error: SQLSTATE = 4200Microsoft OLE DB Provider for SQL ServerEXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'...
TROUBLESHOOTING I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do.
We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong)
This problem is driving me crazy so I thank you in advance for any suggestions!
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391
A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger': System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. Changed database context to 'pims'. OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ". System.Data.S (Source: MSSQLServer, Error number: 6549) Get help: http://help/6549
When creating a replication publisher in the live server enviroment, the repicaltion displays an error saying Could not obatin information about Windows NT group/user '........', error code 0x6e. (Microsoft SQL Server, Error: 15404)
When looking at the replication server, the error report says that the user is a System administrator on the Live Server but they do not exist on the replication server.
