problems executing a SELECT inside a TRAN against other computer

Hi
I have a problem executing a SELECT inside a TRAN against other computer

For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

The problem is that TABLE locks and it does not finish.

I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same

Please, someone could help me, thanks

Assanta:

A couple of things: (1) make sure that DTC is on and (2) maybe try using BEGIN DISTRIBUTED TRAN instead of BEGIN TRAN when you know you will be explicitly involving an external machine.

Dave

|||What do you mean when you say "this runs bad"? Does the statement take a long time to execute? Are you getting any errors? What is the exact behavior? Can you just isolate the case where it doesn't work and specify what you are doing? Are you running #4 from Computer2 and so on?|||

Hi

Thanks for your answers

MUGAMBO

Yes, I have the DTC STARTED

Yes, I've also try with de DISTRIBUTED and the problem is the same

UMACHANDAR

you have all the answers in my first mail

What do you mean when you say "this runs bad"? The problem is that TABLE locks and it does not finish.

Does the statement take a long time to execute? The problem is that TABLE locks and it does not finish.

Are you getting any errors? The problem is that TABLE locks and it does not finish, so the aren't ERRORS

What is the exact behavior? The problem is that TABLE locks and it does not finish.

Can you just isolate the case where it doesn't work and specify what you are doing?

4) IN THE SQL Query Analizer of the COMPUTER2
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

Are you running #4 from Computer2?

IN THE SQL Query Analizer of the COMPUTER2

BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

If I try a TRAN on other computer all is OK, so the problem is only on this two computers, there must have something diferent on the configuration, but I can't know what it is.

Any help please?

|||You are not changing data. There is no reason to run a transaction around a SELECT statement.|||

Hi Tom, thanks for the response

Yes, of course. But is for doing the statement more simple, what you use inside the TRAN is the same, always i use a TRAN it blocks, with a select inside, or with a insert, or......

|||

It is still not clear what you mean when you say the table locks. This can mean several things. One, the query you are trying is taking a long time or the query is getting blocked by other transactions on the server. Also, I was looking for the exact error message you are receiving from the server? Are you simply cancelling the command since it is taking a long time? Are you getting error# 7391? Did you look at sp_lock output for example to see if this is due to locks on the table due to other transactions in the server? Next, you need to check the waitstats using DBCC SQLPERF (see MSKB) to determine if the wait is related to MSDTC for example. Here the configuration of the MSDTC on computer1 needs to be checked (what is the coordinator, does it have network setting enabled, did you check the MSDTC statistics from the MSDTC console - this will show how long each distributed transaction is taking and if anything got committed or aborted). Lastly, your first query in your original post does not start a distributed transaction because the server part is a local server (you are using 4-part name on the same server - loopback) so you can't compare the two. Below is a KB article that has some steps for troubleshooting the MSDTC connection part:

http://support.microsoft.com/default.aspx/kb/306212

|||"Explicit" transactions, using BEGIN TRAN/COMMIT, are very special and should not be used "generically" for every command. They should only be used in the case where you have multiple update statements which depend on each other.

Bascially, what you are doing in the select statement is causing a table lock (for no reason). This is probably causing a deadlock situation. Look at your activity log and see if something is blocking.|||

Hi Umachandar, thanks for the reply

Nobody use this server, only me and i use for test, so there are no more transactions on the server

I mean with block that the table is "IX" during 30 minutes, then i cancel the command. Without the TRAN the select lasts 1 second

thanks for the article, but I continue with the same problem. also I have tried to Add Value TurnOffRpcSecurity (http://support.microsoft.com/default.aspx/kb/827805) although are in the same domain

Executing DBCC SQLPERF (waitstats) there are 77 types of names but noone about MSDTC, there is one lock, is the type "LCK_M_S" and has the values "Requests=1", "Wait time=78" and "Signal Wait Time=16"

Executing DBCC SQLPERF (SpinLockStats) "LOCK_HASH" with 2 Collisions and 132 Spins

More info I hadn't said until now:

The instalation is made from an image, after the instalation i changed the server and SQL name, now the SQL and the Server name is the same, and uniques in the domain, but maybe the image installation could have any problem with the name of any low level thing?

I've been looking for any soft to test the MSDTC communication between 2 machines, I've readed in a forum something about the "DTCPing.exe", I've executed the file and I thing the problem is this "RPC server is ready WARNING:the CID values for both test machines are the same while this problem won't stop DTCping test"

The solution like I read in a forum (http://cogitativemind.homeip.net/archive/2005/08/01/487.aspx) is "For enabling your MSDTC service, you go to command prompt type: msdtc -uninstall , you'll not get any feedback that this has completed so just wait for a bit, then type: msdtc -install, again wait for a bit. I then rebooted my server and everything came up roses"

but this didn't works ok, so I tried (http://forums.asp.net/thread/1192335.aspx)

Run MSDTC -uninstall

Go into the registry and remove the MSDTC keys in HKLM/Software/Microsoft/MSDTC and

HKLM/System/CurrentControlSet/Services/MSDTC

Reboot

Run MSDTC -install

An this yes, this makes my distributed transctions works ok!!!

|||Glad you got it working. You need to be careful using imaging software since there is lot of metadata used by various applications that can be incorrectly handled. And this can differ from different releases of Windows / Service Pack / Software too. For SQL Server, you said you changed the server and SQL name - so did you use sp_dropserver & sp_addserver with 'local' option. Apart from this one, there are other places in MSDB that can reference server names - like master servers in SQLAgent configuration and so on. And you should actually run SQL Server SETUP again to ensure that the machine change is handled correctly. I believe that SETUP detects the name change and performs some actions. Note that you still need to do the sp_dropserver/sp_addserver part since the @.@.SERVERNAME value is stored & obtained from the system catalog. Search in MSKB for articles that may help the imaging process or name change of machine.