Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Update in Linked Server TimeOut

Author  Topic  abartra Starting Member 1 Post Posted - 2005-02-11 : 16:54:21 I have two databases: General in SQL Server 7 and SRM in SQL Server 2000, the DBA have created a linked server to SRM. When I insert a row in the table Customers in General (SQL Server 7), the trigger associated to this table updates the data for this customer in my Customers table in SRM (SQL Server 2000). I have the indexes at 98% in my Customers table in SRM. (I have reindex the table because it taked up to 4 minutes to complete the update!). Now the insert operation with the trigger associated takes 40 seconds up to 1 minute to conclude, this generates a timeout (sometimes) when I insert a customer from my application (ASP .Net) This is the trigger, please take a look at it and tell me if it can be optimized: CREATE TRIGGER ti_Direccion ON dbo.Customers FOR UPDATE AS BEGIN DECLARE @MsgError varchar(255) --replicate Direccion in SRM Set XACT_ABORT ON UPDATE [etf8800\SQL2000].SRM.dbo.Customers SET Numero_direccion = I.Numero_direccion, Tipo_via_domicilio = I.Tipo_via_domicilio, Via_domicilio = I.Via_domicilio, Edificio_domicilio = I.Edificio_domicilio, Departamento_domicilio = I.Departamento_domicilio, Manzana_domicilio = I.Manzana_domicilio, Lote_domicilio = I.Lote_domicilio, Numero_domicilio = I.Numero_domicilio, Interior_domicilio = I.Interior_domicilio, Tipo_agrupamiento_domicilio = I.Tipo_agrupamiento_domicilio, Agrupamiento_domicilio = I.Agrupamiento_domicilio, Referencia_domicilio = I.Referencia_domicilio, Codigo_Postal = I.Codigo_postal, Codigo_departamento = I.Codigo_departamento, Codigo_provincia = I.Codigo_provincia, Codigo_distrito = I.Codigo_distrito, Codigo_Pais = I.Codigo_Pais, Direccion_linea_1 = I.Direccion_linea_1, Direccion_linea_2 = I.Direccion_linea_2, Direccion_linea_3 = I.Direccion_linea_3 FROM [etf8800\SQL2000].SRM.dbo.Customers C INNER JOIN Inserted I ON C.Id_Customer=I.Id_Customer Set XACT_ABORT OFF IF @@ERROR <> 0 BEGIN SELECT @MsgError = 'Error in the replication' RAISERROR( @MsgError, 16, 1 ) ROLLBACK RETURN END END I've tried creating a table with the same structure in General to see what happens and the insert operation plus trigger execution flies, it takes only a few seconds, so I believe it has to do with the linked server. Is there anything else I could do to make it faster? Thanks a lot