hope someone can help.
I would like to centrally collect data from my remote servers - including MSDE and SSE to my central 'master' 2005 server.
I have created the linked servers fine to MSDE, checked the sp_configure option for remote access (it is set to 1) and all seems fine.
I can query tables on the remote servers and execute stored procedures using Select * from openquery(server, stored proc)
I cannot however seem to get it to work using 4 part naming
eg. exec server...sp_configure and every variant therein fails, whereas if i stick it in the openquery it works
could not execute procedure on remote server 'server' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.
Any help you can offer would be most appreciated