RMS 2.X SQL Server blocked access - Ad Hoc Distributed Queries

(0) Share

Report

Posted on by Dan902

RMS 2.X MS SQL EXpress 2012 (SP4) simple setup Main DB and separate Cash Register

During an network connectivity issue my Cash register went in offline mode. When the connectivity to the main DB server came back on. I got the following error on the cash register.

SQL Server blocked access to STATEMENT 'OpenRowSet/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system Administrator can enable the use of "Ad Hoc Distributed Queries' buy using sp_configure....

How do I fixed this issue? Do I enable Ad Hoc Distributed Queries on both database Servers (the main DB server and the Cash Registers offline) or only the cash registers? Any security implications I should be aware of when doing this?

Thanks

Danny

All responses (1)

Answers (0)

Dan902 1 on at

Like (0)

Report

RE: RMS 2.X SQL Server blocked access - Ad Hoc Distributed Queries

So I did the the following:

execute sp_configure 'ad hoc distributed queries', 1

reconfigure with override

On both my cash register computer and DB server, the transaction get uploaded to the DB server properly when I regain connectivity (back in ON-LINE mode) and when I go a z-report to close the batch, it appears as if the "off-line database is getting synchronize.

But a new item that had been added while online does not appear to be in the off-line system when i use Store Operation in offline mode. When in do a DBCheck on the offline database I get error 91: Object variable or With block variable not set?

Any help would be appreciated.

Thanks

Danny