SQL server, SQLServerPedia Syndication

SQL error: server is not configured for DATA ACCESS.

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.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s