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


Top 5 Contributors of the Month
Lee Hnetinka
mithun
Post New Web Links

Data from the server subscription cannot trigger table trigger

Posted By:      Posted Date: November 16, 2010    Points: 0   Category :Sql Server
 

Publisher  Server A  (Job  table) no trigger

Subscription Server B (Job table) has trigger

Data from the Server A subscription cannot trigger Server B Job table trigger




View Complete Post


More Related Resource Links

In a trigger can up insert data into a table located on a linked server?

  

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'

Auto udating data in multiple table through trigger.

  

table a(

sno IDENTITY int(1),

name varchar(5),

roll_no numeric(5)

sum more columns

)

table b(

sno IDENTITY int(1),

name varchar(5),

sum more columns

)

table c(

sno IDENTITY int(1),

roll_no numeric(5)

)

 

Could any one kindly tell me how can i do it.

whenever data is entered in table a the values of name and roll_no to be automatically filled in table b and table c with use of trigger.


trigger on insert is failing for data insert in a table

  

hello experts,

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 [46]] 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'.".

any solution


mark it as answer if it answered your question :)

how to get id value from the row inserted in a table with thr trigger.

  

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?


Thanks.


Under The Table: Spatial Data Support In SQL Server 2008

  

New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009


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

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

  
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work.  Be sure and create the 'Audit' table first though. The following code write audit entries to a Table called'Audit' with columns 'ActionType'    //varchar'TableName'    //varchar'PK'    //varchar'FieldName'    //varchar'OldValue'    //varchar'NewValue'    //varchar'ChangeDateTime'    //datetime'ChangeBy'    //varchar using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public partial class Triggers{//A Generic Trigger for Insert, Update and Delete Actions on any Table[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger(){SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Contextstring TName; //Where we store the Altered Table's Namestring User; //Where we will store the Database UsernameDataRow iRow; //DataRow to hold the inserted valuesDataRow dRow; //DataRow to how the deleted/overwritten valuesDataRow aRow; //Audit DataRow to build our Audit entry withstring PKString; //Will temporarily store the Primary Key Column Names and Values hereusing (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{conn.Open();//Open the Connection//Build the AuditAdapter an

Trigger that deletes another table's row

  
Hi, I'm new to making triggers. I have the following trigger wich should delete a row in the Activity_Log table when I delete a row in the Poll_Comments table.... I have an error in my where clause - because it don't delete the specific row.... My trigger:ALTER TRIGGER [dbo].[deletePollCommentsTrigger] ON [dbo].[Poll_Comments] FOR DELETE AS BEGIN DECLARE @intRowCount int SELECT @intRowCount = @@RowCount IF @intRowCount > 0 BEGIN DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activityDateCreated IN (SELECT commentsCreateDate FROM deleted) AND activitySectionPId = 8 END END If I change the statement to: DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activitySectionPId = 8 It correctly deletes all rows with that specific questionId and where activitySectionPId = 8... But I want also to have a where clause on, that ask for commentsCreateDate = activityDateCreated - so I only delete one row and not all rows... How can I do that? Kind regards,simsen :-) 

Data Driven Subscription problem - sub runs slowly, has 40 errors out of 300, but report server trac

  
I have two report servers and a single separate SQL Server 2008 server hosting the data source database and the report server catalogues. There is a data driven subscription that runs off a sproc that returns 300 rows with email addresses for this report to be emailed to. The report itself contains around 30 subreports each with its own sproc to retrieve data. As the report is executing there are long pauses between the times when emails are actually sent out, and the subscription status will finally end with something like "Done: 300 processed of 300 total; 40 errors." I am not sure how to go about troubleshooting this problem. Details: I kicked it off at (say) 2:00 PM. Within 3 minutes in the report server execution log I saw 300 entries. There were exactly 300 people who should receive reports, so the master report should have executed 300 times and sent out 300 emails. So this looked good in the execution log. But when I checked report manager's status page for the subscription it was listed as processing 50 out of 300... Then all activity stopped, about 15 minutes some more reports started emailing out, and now I see there are around 590 rows in the execution log and maybe 97 emails sent out... Then finally I get a grand total of 846 rows in the execution log and a grand total of 260 of 300 emails sent out with 40 errors so 260+40=300 exactly. The time betw

Trigger not firing on second table.

  
Im wondering if someone can help me. I have created 3 simple tables. The 1st table has a trigger that inserts a record into the 2nd table. The second table has a trigger that inserts into the 3rd table. When I insert a row into the 1st table the 1st trigger on this table fires but the trigger on the 2nd table does not fire.  In the triggers i have two print statements which should print out text when the trigger is firing but I never see the 2nd trigger firing. Stepping through with the debugger in SQL 2008 I am also not able to F11 into the 2nd insert statement. Im left scratching my head on this and was wondering if anyone could help here. Thanks in advance. I have included a sampel script to replicate the porblem.     CREATE TABLE [dbo].[TriggerTest1](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRAINT [PK_TriggerTest1] PRIMARY KEY CLUSTERED (     [Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[TriggerTest2](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRA

After insert trigger on the same table.

  
Hi! i have the following table. CREATE TABLE [dbo].[EICANALESRES]( [IdContrato] [decimal](10, 0) NOT NULL, [EiCanalId] [decimal](10, 0) NOT NULL, [EiFechServ] [datetime] NOT NULL, [EiFechMaq] [datetime] NOT NULL, [EiFechCorr] [datetime] NOT NULL, [EiValorFis] [decimal](25, 4) NOT NULL, [EiValorNum] [decimal](25, 4) NOT NULL, [EiValorCalc] [decimal](25, 4) NOT NULL, [EiEst] [smallint] NOT NULL, PRIMARY KEY CLUSTERED ( [IdContrato] ASC, [EiCanalId] ASC, [EiFechServ] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato. Without deleting the original record. i have this so far, but it erases the original record.   alter TRIGGER [dbo].[datosdemo] ON [dbo].[EICANALESRES] AFTER INSERT AS BEGIN SET NOCOUNT ON; delete from eicanalesres where idcontrato = '13' and eicanalid = '91' and eivalorfis = '0' update dbo.eicanalesres SET idcontrato = '13' FROM INSERTED WHERE eicanalesres.idcontrato = inserted.idcontrato AND eicanalesres.eicanalid = inserted.eicanalid and eicanalesres.eifechserv = inserted.eifechserv and inserted.idcontrato = '02' and inserted.eicanalid = '91'

SQL Server 2008, what is the difference between (for/after) definition for trigger?

  
Hello. The difference is not clear for me. can someone explain it more carefully pls? as said: FOR | AFTER AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified. So what is the difference between for insert and after insert?

How to create/trigger a server-only script

  
Background:  I have a site that sends a good bit of emails, some one at a time, and some larger groups of admin emails.  I'm running on a shared hosting service.  The mailserver is horribly slow, so any script that sends a single email can take as much as 15 seconds to execute (and I can't seem to set Response.BufferOutput  to false, so the whole page has to wait the entire duration).  On the bulk emailings, it can take minutes for that to run.I want to create a "mail queue" of my own... I want any script that sends an email to, instead, write a row to a db table containing the necessary email info, which will let the script get on with its business quickly.  Then, I want to create a script that runs on the server only, and have it check the table for emails, send them, and remove them from the table.I know I can have the "mailsender" script run as a scheduled task, and it would thereby run on the server only.  But I would prefer to have the initial script somehow "trigger" the mailsender script.So, QUESTION:  Is there a way in asp.net to create a script that will execute solely on the server, and call that script from another page, so that the calling page doesn't wait for the server script to execute?Thanks!

How to submit data to a SQL Server database table from a custom page ?

  
Using SPD, I have created a custom page. It has a form with ASP.NET controls textbox, radio buttons, dropdown, submit button etc., Also, I created a Database connection (to the SQLServer database table) under Database Connections. But not sure how to connect this form to the database connection so that when form data is submitted, the data will be inserted into the table.

how can insert only new record into another table through trigger

  
I have on master table in sql in which every time new data is inserted.i just want to copy the new inserted record into another table.How can i do this. below is my trigger but how can it identify only new records should be inserted into second table.please help me and modify it. CREATE TRIGGER CopyRecordToPortInTable ON SmsTable AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into Portintable(smsno,smstext,flag) select (sms,smstext,0) where condition END

How can we insert csv file data into Sql server table based on some condition ?

  

Hello All experts there,

 

I need to insert some csv file data into sql server 2008. but there are multiple tables created in sql server and I need to check some conditions before data insertion and based on the condition value, sql script should insert the data in to specific table. in other words I need to insert data from one csv file to different tables based on some condition.

is it possible? if yes then can anyone please give me an idea on how can we do that?

 

Thanks in advance

Regards

Nipendra Garg


How to write a trigger when given a condition in SQL SERVER 2005?

  

Hi Guys,
I am using SQL SERVER 2005.

I know how to write a trigger for a basic insert or update or delete. But here in my case I have to use the below condition and create a trigger.


I havent used any trigger till now in my job. So I am facing a slight problem in understanding and writing the code.....
I got the following requirement to create a new trigger for the following scenario :

The user (john) should be notified when the following happens :

Any transaction entered to account 100 which is not a ABN,CCH,or TTW
Any transaction entered to account 189 which is not a KHD

Description of fields :
100 and 189 are values for AccountCode Column in Account Table
ABN,CCH,TTW and KHD are values for CompanyTypeCode Column in Company Table.
I need to create new trigger on DLT_Data table

Categories: 
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