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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-07-05 : 10:36:17
|
| Matt writes "I have looked through many sites for the answer to my question. I'm sure other people have the same problem but I can't find the answer.Question : I have an application which can access one of around 500 databases on a server (DBSRV02) to allow data capture. One of the databases became too large to fit onto the particular server so it was moved to another server (DBSRV06). The two servers were linked with full rights and MSDTC was running on both servers. The application needs to run through a connection on DBSRV02 so I dont want to open a seperate connection to DBSRV06 just for this one database.I am able to query the database fine but have problems when updating. In query analyser I ran the followingSET XACT_ABORT ONBEGIN DISTRIBUTED TRANSACTION UPDATE DBSRV06.dbPJ102A.dbo.tbl_Address SET cFlag = 'A' WHERE iURN = 23354COMMIT TRANSACTIONThere is no errors but the query runs for a long time. The table has a clustered unique index on iURN. If I display estimated execution plan, it shows a remote table scan!I think executing remote stored procedures may get round the problem but this will mean a change to an application used on 500 databases just for 1 remote data source. Please HelpMatt BryantSQL Database AdministratorBounty SCA Worldwide UK Ltd" |
|
|
|
|
|
Distributed Transaction Problem