The SQL configuration option 'ad hoc distributed queries 'does not exist.

Source: Internet
Author: User
Tags sql server books
Select * From OpenDataSource ('sqloledb', 'datasource = 192.168.1.201; initial catalog = dbname; user id = sa; Password = Sa'). dbname. DBO. tablename

The following error occurs when the preceding statement is executed:

MSG 18456, Level 14, state 1, line 1 user 'sa' Logon Failed. MSG 4060, level 11, state 1, line 1 cannot open the Database "bbage_gameinisde ". Logon Failed.

Because OpenDataSource is followed by the database name (dbname), initial catalog cannot be specified in the connection string.

The correct statement is as follows:

Select * From OpenDataSource ('sqloledb', 'Data source = 192.168.1.201; user id = sa; Password = Sa'). dbname. DBO. tablename

Note: The gray words above are all items that individual needs to configure.

If you run this statement, you may encounter the following problems:

SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because this component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".

Run the following statements:

Exec sp_configure ''ad hoc distributed queries ''' 1

Reconfigure

If you have not set the server configuration, the following error may occur:

The configuration option 'ad hoc distributed querys' does not exist or may be an advanced option.

In this case, you need to enable the Advanced Configuration:

Exec sp_configure 'show advanced option' 1

Reconfigure

The general operation can be performed in reverse order:

Exec sp_configure 'show advanced option' 1

Reconfigure

Exec sp_configure 'ad hoc distributed queries '1

Reconfigure

Note: The preceding reconfigure operation is used to dynamically update options. If this operation is not executed, the execution result of sp_configure is not maintained.

In addition, for sqlexpression 2005, You need to execute:

Exec sp_configure 'user instance enabled' 1

Reconfigure

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.