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

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