SQL server freezing when I save a linked view

I have two SQL 2000 Servers running on separate machines both running Windows Web Server 2000. I have linked server1 to server2 so that I can pull information from server2 into server1. I then created a view on server1 of a view on server2 so that I can more easily write queries against the server2 to get my required information.

For about a month this worked wonderfully but then server2 crashed. They had to completely rebuild server2 on a new machine with all the same specs but were unable to rebuild the database from which my view was pulling. The linked connection is still working since I can write a distributed query against server2 and get back correct information. They rebuilt the database I was pulling from under a different name so I went to my view and changed it to point to the new database. When I ran the SQL in the View Creation tool, it successfully returned the new records. However, when I tried to save the revised view, the entire server froze up and refused to respond.

We rebooted the machine and I tried again and had the same result. I then created a completely new view and it successfully pulled data from server2 but when I tried to save it, same result.

To see if the problem was with server1, I created a view of local data stored on server1 and saved it with no problems. I also saved a new table with no problems.

At this point, I've pretty much hit the wall. I can write a distributed query to pull info from server2 over the linked server connection, so I know it's working, but I don't have a clue as to why my machine freezes up every time I try to save a view of a view stored on server2. If I have to, I can change my program to work from the distributed query but I'd rather resolve the problem of my view.

Any advice or ideas for a direction worth investigating?

all i can think of is make sure the MS DTC (data transaction coordinator) service is running on both machines.

You're dead on the money Al. After I posted this thread I discovered that the rebuilt server had been built with Windows Server 2003 which has the distributed transaction coordinator turned off by default.

Since the linked server was still working, I went ahead and rewrote my scripts to work with distributed transactions rather than worrying about creating a view of their view.

Thanks

awesome. glad it worked. i never even knew what dtc was until i had a similiar problem.