You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distribution queries against a da

admin2009-05-19  69

问题 You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distribution queries against a database that is stored on a SQL Server 2000 computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation.

You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead.
How should you configure SQL1?

选项 A、Use the ALTER DATABASE statement to change the collation of the databases on SQL1.
B、Add SQL2 as remote server.
C、Add SQL2 as a linked server. Select the Use Remote Collation check box, and do not specify a collation name.
D、Add SQL2 as a linked server. Select the Use Remote Collation check box, and specify a collation name. Repeat this process once for each database on SQL2.

答案C

解析 Explanation: We should set up SQL2 as a linked server. This would enable to issue distributed queries to it. We should also select Remote Collation option to use the collation on the remote server.

Note: SQL Server 2000 allows the creation of links to OLE DB data sources called linked servers. After a link has been created it is possible to reference rowsets from the OLE DB data sources as tables in Transact- SQL statements and pass commands to the OLE DB data sources and include the resulting rowsets as tables in Transact-SQL statements.

Each distributed query can reference multiple linked servers and can perform either update or read operations against each individual linked server. A single distributed query can perform read operations against some linked servers and update operations against other linked servers. The linked server options defined by using the sp_serveroption stored procedure control how SQL Server uses collations from linked servers. When the UseRemoteCollation option is set to TRUE, it specifies that the collation of remote columns is used for SQL Server data sources, and the collation specified in CollationName is used for data sources other than SQL Server.

When FALSE, distributed queries always use the default collation of the local server instance, and CollationName and the collation of remote columns are ignored. If the UseRemoteCollation option is set to TRUE, we can specify the name of the collation used by the remote data source in the CollationName option. The collation must, however, be supported by SQL Server. This option should be used when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations

Incorrect Answers:
A: The COLLATE clause of the ALTER DATABASE statement can be used to change the collation used by the database. However, this solution is inappropriate as the SQL2 server contains several databases, each of which uses a different collation. We would thus not be able to specify all the collations.

B: Remote servers are set up in pairs with both servers configured to recognize each other as remote servers. The configuration options for both servers should be set so that each instance of SQL Server allows remote users to execute procedure calls. However, support for remote servers is provided for backward compatibility only. Applications that must execute procedures against remote instances of SQL Server 2000 should use linked servers instead.

D: In this scenario we cannot specify the collation name as SQL2 contains several databases, each of which use a different collation.
转载请注明原文地址:https://kaotiyun.com/show/7whZ777K
0

最新回复(0)