Users were having issues with connecting between their reports DB and their main DB. Since two different DBs were concerned, communication of a linked nature was required. Data access at the server level was enabled with the query:
sp_serveroption @server = ‘server_name\instace’,@optname =’Data Access’, @optvalue = ‘True’
Before I ran the query, I thought: what if this value was already set? If so, then the access issue would not have been due to the data access. How could I find this out? The command to use was:
sp_helpserver @server = ‘server_name\instance’ , @optname = ‘Data Access’
Running just sp_helpserver will show you all the current server options that are currently set. The good news is that data access was not set, and once it was set, the issue was resolved.
For viewing info, there are three levels of info that can be viewd: sp_helpserver, sp_helpdb, sp_helpfile, and sp_help. Sp_helpserver was explained above. Sp_helpdb provides info at the database level: DB file locations, size, status, etc. sp_helpfile will show detailed information about the physical DB files (data and logs). Sp_help will provide info about a database object. The three procedures will show all-info if no parameters are specified. If parameters are specified, you can have a more filtered set of information returned.