I have been stumped on this for this whole day now. I use MS SQL Server 2008. I have alinked server pointing to a DB2 database using an ODBC connection that has been running fine for years. Today I decided to change the name of the server and now whenever I run an openquery, run a job that connects to this linked server or when I try to see the tables of this linked server the query/job will run permanently and the tables forever say [Expanding...] but they never actually show. When I try to cancel a query that is stuck, it will never finish canceling. The only way I have found to actually stop queries is to log my account out and end my session forcefully (MS SQL server will tell me it cannot shut down but I can log my Windows account out get past it).
We already tried to recreate this exact linked server by scripting it and using a different linked server name but this gave the same result. I also tried recreating this linked server on another actual server and that worked fine. The server where the linked server is "stuck" on, works perfectly fine as long as I don't try to connect/communicate to these broken linked servers.
My question is basically, what in the world happened when I just changed the name of this server and does anyone have any suggestions on resolving this? I have pondered about a complete server restart, but since this concerns a production server this would be my absolute last resort. I however see no other options right now.
Solution was to do a complete server restart and to back up to an earlier date. We have done the exact same thing after that and everything worked fine, so it seemed like it was a one time critical anomaly.