I am stumped on this one. Let me explain what I am trying to do:
From a client PC which has a MSDE instance running with a database already defined, I am accessing my ASP.NET Web application which has a back-end SQL Server database. My Web app creates a linked server definition to the MSDE database by calling a a stored procedure which executes sp_addlinkedserver. My intent is to eventually be able to transfer selected data from the 'master' db down to the MSDE db.
The linked server definition appears on Enterprise Manager, but attempts to run queries against it from the ASP.NET page return a 'SQL Server does not exist or access denied' message.
I added a loopback to my ASP.NET query so that, if it fails the first time, it just tries it again. This did not work, no matter how many times I re-try it.
BUT, if I do a query against the definition directly from the SQL Server Query Analyzer, the first time it times out but the second time it works. Once it works, if I go back to the ASP.NET page and run the query against the linked server again, now it works as well!
Can someone explain what is going on? Is there anyway from my ASP.NET page to fire off the same function as if I were selecting the linked server 'refresh' option from Enterprise Manager or running multiple queries against the linked server from the Query Analyzer?
Thanks for your help!