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
I need one trigger for Auditing purpose.Currently we are using SQL Server 2005. I have gone thru some articles on DDL trigggers but iam not able to get good info.
Frns, Need a trigger which can track all ddl's on all tables in the current database in an audit table. It should include dropping tables,altering tables ... I should'nt allow both these activities. The action performed must be tracked in the following table.
create table audit_ddl ( login_user varchar(20), login_time datetime, table_name varchar(50), action_performed varchar(200) -- Entire ALTER/DROP SQL stmt the user issued issued must be tracked here. )
There are two table table A and table B , if something is updtaed in table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....
Here is the Update trigger i wrote :
CREATE TRIGGER [updbacklog] ON [testbacklog] FOR UPDATE AS Update test1backlog Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber , test1backlog.SalesOrderNumber = inserted.SalesOrderNumber , test1backlog.CustPONumber = inserted.CustPONumber , test1backlog.Status = inserted.Status , test1backlog.Comments = inserted.Comments , test1backlog.TargetCompletionDate = inserted.TargetCompletionDate , test1backlog.ActualCompletionDate = inserted.ActualCompletionDate , test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired , test1backlog.WorkOrderType = inserted.WorkOrderType , test1backlog.CustomerName = inserted.CustomerName , test1backlog.Attn = inserted.Attn , test1backlog.CustAddr1 = inserted.CustAddr1 , test1backlog.CustAddr2 = inserted.CustAddr2 , test1backlog.CustAddr3 = inserted.CustAddr3 , test1backlog.City = inserted.City , test1backlog.State = inserted.State , test1backlog.Postal = inserted.Postal , test1backlog.Customer = inserted.Customer , test1backlog.Address = inserted.Address , test1backlog.ShipDate = inserted.ShipDate , test1backlog.Carrier = inserted.Carrier , test1backlog.Waybill = inserted.Waybill , test1backlog.CanBeShipped = inserted.CanBeShipped , test1backlog.PlannerCode = inserted.PlannerCode , test1backlog.rowguid = inserted.rowguid from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber and test1backlog.CustPONumber = inserted.CustPONumber and test1backlog.Status = inserted.Status and test1backlog.Comments = inserted.Comments and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired and test1backlog.WorkOrderType = inserted.WorkOrderType and test1backlog.CustomerName = inserted.CustomerName and test1backlog.Attn = inserted.Attn and test1backlog.CustAddr1 = inserted.CustAddr1 and test1backlog.CustAddr2 = inserted.CustAddr2 and test1backlog.CustAddr3 = inserted.CustAddr3 and test1backlog.City = inserted.City and test1backlog.State = inserted.State and test1backlog.Postal = inserted.Postal and test1backlog.Customer = inserted.Customer and test1backlog.Address = inserted.Address and test1backlog.ShipDate = inserted.ShipDate and test1backlog.Carrier = inserted.Carrier and test1backlog.Waybill = inserted.Waybill and test1backlog.CanBeShipped = inserted.CanBeShipped and test1backlog.PlannerCode = inserted.PlannerCode and test1backlog.rowguid = inserted.rowguid
I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:
In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?
I try this into de Trigger Code ....
DECLARE @FECHA_1_ANT AS datetime DECLARE @FECHA_2_ANT AS datetime
DECLARE @FECHA_1_INS AS datetime DECLARE @FECHA_2_INS AS datetime
Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID
But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....
Hi, I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA. Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2. Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers. Thank you!
Hi, I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA. Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2. Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers. Thank you! I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.
Recently I am facing a problem as, I have a trigger on table1 in after insert event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
Hi, I am configuring replication and i started using wizard . i opened publishing and distributor wizard and click on next it is saying that "Sql manager could not complete wizard because @@sername is null and use sp_add server name to set @@ servername. This is productionbox. Any body help me how to fix the problem and what is that meaning osf error. Thanks
Hi Guys, We are using transactional replication, I want to add a filter on the rows. Can Any one explain me how to add it. To do this we should stop replication and then add the filter ???.
Hi Guys, I am having around 1000 tables, in which around 700 tables are not having primary keys on the tables, The tables which are not having primary key, i want to set up an snap shot replication, Tables with primary keys with transactional replication. can i have both transational as well as snap shot set up on the same server????
I have setup the snap shot replication on one table.It says the initial snap shot not yet available.What does it mean?How to activate the replication? Thanks.
Hello there, I need some urgent help on the Replication .....SQL 6.5, sce 5a, windows NT4.0
A Publication has been installed between 2 server (SQL 6.5, sce pack 5a).
The task Repl_Subs_Distribution fails with the following message: "The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 80"
1) with a Push Subscription, the error "no subscriber has been defined" came up, so I also did a pull subscritption for the remote server = could it be the cause of the job id not matching?
2) although I found a document on a "sp_Mskill_job" replication stored procedure, it is not present in my DB and I cannot use it. Is there a way to create it?
Thanks for your precious help. Best regards, Rachel
I was advised to Compair max job_id in MSsubscriber_status on distribution and MSlast_job_info on subscription and If not match, to modify the id in MSlast_job_info.
There is no distribution server in this Replication....any other idea or possibility??
I wanted information on Transactional Push replication between SQL Servers . I have one table on SQL Server 6.5 and there are certain text in the table with single quotes . When i replicate this table to SQL Server 7.0 the text appears in double quotes on SQL Server 7.0 .
Can anyone help regarding this problem ? Has it something to do with version problem .? Where can i get more information on this issue ?
Many thanks . An early response will be highly appreciated .
Thank you so much for your prompt response . Is it possible to change the settings of the Quoted Identifiers ? How can we do it in SQL Server 6.5 and SQL Server 7.0 ? Do you have any info. on the same . ?
I have been asked to take over the maintenance on dB servers that I have not originally 'set up'. The request came as a result of the database log files growing at such a rapid pace its taking up hard drive space. I ran sp_dboption and noticed that the log files are NOT set up to truncate on checkpoint. As in SQL 2000 I wanted to determine if the dB was set up in 'simple' or 'full' recovery mode. It appears they are set up in 'full' recovery mode (which we can modify, since this is not a transactional dB and we rarely if ever have to restore from the transaction log). Hence my question,
Is there anything I need to worry about (regarding replication)if I change the dboption to truncate on checkpoint and set dB in simple recovery mode?
And after I backup the transaction log and try to shrink it will that have any adverse affects? I feel like this is all I need to do but replication is new ground for me, any information regarding it would be greatly appreciated. Also, any advice on how to maintain the transaction logs would be helpful.
hi, I want to setup transactional replication(PULL) between 2 servers . can anyone guide me with the steps involved while performaing a Pull replication from server1 to server2. Any help appreciated.
I need some urgent help. I'm managing merge replication between 12 servers. Today I had to make some changes in the database design, so I removed replication, altered the tables in question (on all servers)and created a new publication. I choose the option that all subscribers have the data and the schema, but when I start the replication I get almost immediately an error "Invalid column name rowguidcol". But there is no column in my database with such a name. Further investigation brings up another message "xp_execresultset: unable to execute result set".
Can anyone please help me. The database should be ready by tomorrow.
Hi, Every one I have added new column in my Publication - Article Table , Same column I have added in my Subscriber Table , I am doing Transaction replication , but now its giving me error Insert Error - Column Name or Supplied Value doesn't Match, If any one has any idea please suggest me Thanks Nirmal
We are using Ms SQLSERVER 6.5 on NT 4.0 service pack 3 I have joined in as a new DBA and don't know much about MS-SQLSERVER & it's replication. What I have observed out here is that one of the databases was being replicated betnn Tokyo and Kawasaki and suddenly after 22nd Feb stop replicatiing without giving any errors. I have checked the max job_id in MSsubscriber_status on distribution database as well as max_job_id in MSlast_job_info on subscription db.They seem to match.I have also tried to drop and recreate the unique clustered index on the subscribing database.But nothing seems to work.
A Publication has been installed between 2 server (SQL 6.5, sce pack 5a).
The task Repl_Subs_Distribution fails with the following message: "The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 80"
1) with a Push Subscription, the error "no subscriber has been defined" came up, so I also did a pull subscritption for the remote server = could it be the cause of the job id not matching?
2) although I found a document on a "sp_Mskill_job" replication stored procedure, it is not present in my DB and I cannot use it. Is there a way to create it?
Thanks for your precious help. Best regards, Rachel
Hi, all.. I by mistake Overwrites distribution DB. Server1 and Server2 replicates each other. Applications are using Server1, and Server2 is back up purpose. When server1 fails application redirected SErver2, using same data since there is replication. Server2 had distribution DB.
Now there is no distribution DB.. it's messed up... I want to restore replication between Server1 and Server2. I tried to make replication from Server1, I got errors..
By Accident One table got truncated on the subscriber which is one of the table in Published articles. Now all the Update inserts and delete are failing . and other tables are not being replicated either. what could be done to fix this . Publisher gets a lots of updates every hour how ever because of this problem all the tables are not getting any transactins replicated. Please help ASAP
I am trying to do transactional replication from 2000 to 2005 . But i am getting error" agent hasn't been registered .Run sql server setup program' while creating snapshot. It is production server. How do i make it work.I am doing pull subscription from 2005 server
I'm running merge replication with several databases. So far everything worked fine, but now I tried to insert some new records and I get the message:
Server: Msg 7139, Level 16, State 1, Line 1 Length of text, ntext, or image data (73728) to be replicated exceeds configured maximum 65536. The statement has been terminated.
Can anybody tell me how if I can change this max. value and how ? Thanks a lot Mike B.
Hi, I changed the sa password today on my subscriber databases, and now my transactional replication fails, as it cannot connect to the subscribers. I updated the publisher's linked server logins, but it still fails. Help please! Thanks, Jason
