I just wanted to pass along some information I just figured out accidentally. We utilize SQL Server at my work to house a rather large amount of data over about 4 SQL Servers, 3 were Virtual and 1 was an actual physical server. This weekend we moved the physical server to a virtual one and most everything just worked out of the box. When we tried to use some of the advanced features that required linked servers those features worked for some and not others, it seemed when we tried to use the newly made virtual server to a schema on itself it failed. We tried everything to make it work and since it was not working, we assumed we would need to change 100s of lines of code to remove the server part from the connection string when it was referencing the same server. We even tried creating a new linked server on this server to connect to itself, which of course failed due to it being the same server. All of the servers were SQL 2008s.
What did actually work was using a box that was VPN’d in which is running SQL 2008R2 to create a linked server on that same server, by starting SMSS on the VPN’d box and connecting to the server in question and going down to Server Objects then to Linked Servers and right clicking and adding linked server, and using the Server in question’s name in the server to connect to box and going through the security stuff and pressing Ok. Now when we want to use the linked box features they work as they always have when it was a separate physical box.
Hope that helps someone else out there that may face the same issue.
Later