Late last week, I tried upgrading a server from MS SQL Server 2000 to MS SQL Server 2005 and as apt to occur (at times), the install did not go as smoothly as one would have hoped for. And, so I ended-up installing a fresh copy of MS SQL Server 2005.
The users came in this morning, and they could not get their precious Linked Server to work. I tried the same query on my side and it worked well.
The query is a simply, unfrightening little query that should probably work —
select top 10 * from linkedSrv…[Players#txt]
I checked the .Net and enabled “Ad Hoc Distributed Queries”.
exec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure
But, still the user complained that the query still did not work.
I checked my login and it still worked and so I thought may be it is a permission issue. But, which permission… And, so I created a simple, unprivileged account. And, then that one failed. And, so it appeared that I could work on my own, and hopefully get a solution and stop bothering the user per need for successive tests.
Searched the .Net a bit more and found one of those “solutions” that I used a couple of years back when dealing with a similar issue with MS Excel files. The “fix” is to determine the account that MS SQL Server is running under, determine the Temp directory for that account, and grant all users ability to access (read\write) to that “temp” folder.
Once I did that, things work. So hurray another one for the .Net — courtesy of MS – Common tips and tricks from a SQL Developer Support perspective
While some cases may start of complex, they sometimes turn out to be caused by something not that complex.
So I thought I would share some of the things that I came across and encourage you to keep trying new things.