Set - 4

Question 1 :

In Reporting Services 2005, how is Report Builder typically deployed to end users?

Answer :

One click deployment launched from a menu on the Report Manager home page
One click deployment is fast and easy, users click the menu on the Report Manager page. It is not a web application, but rather a .Net Winform application.

Question 2 :

Native Web Services require what type of endpoint in SQL Server 2005?

Answer :

HTTP endpoints
Native XML Web Services in SQL Server 2005 require HTTP endpoints to communicate with clients.

Question 3 :

If you have a replication scenario with one publisher, one distributor and one subscriber and the transaction isolation level is not set to serializable, what happens when a transaction fails on the publisher?

Answer :

It is still sent to the distributor and executed on the subscriber.
A transaction that fails in the publication database is still sent to the distributor and subscriber. YUou can trap the error, but you would need to trap it on the subscriber as well.

Question 4 :

When creating a full-text index on a BLOB column (image, binary or varbinary) where a file is stored, what happens if an incorrect file prefix is stored in the type column? (For instance: if a word document is stored in the image or a varbinary(MAX) column and the value of the type column is '.pdf'.

Answer :

The row is not full-text indexed and an error message is written to the SQL Server Error Log
The answer is the row is not full-text indexed and an error message is written to the SQL Server Error Log. No columns in the row are full-text indexed and an error message is written to the SQL Server Error Log.

Question 5 :

In SQL Server 2005 Full-Text Search, you set up a replacement set like this:

SQL Server 2005
SQL Server 2K5

A search for "SS2K5" doesn't return results with fields that you know contain "SS2K5". What is wrong?

Answer :

The replacement set does not return results with the search terms, only the specified replacements.

Question 6 :

In the SQL Server 2005 thesaurus XML configuration file, what is the expansion set?

Answer :

Expansion sets are synonyms for the search term and returned as results if they appear along with the search term.
The expansion set is the group of values that are synonyms and can be substituted for the search term. For example, an expansion set can be "SS2K5", "SQL Server 2005", "SQL2K5". In this case, fields with any of these 3 values would be returned as a result for searches on "SQL Server 2005".

Question 7 :

You are initiating a new replication subscription for one of the remote offices, but don't want to transfer it across the network. The snapshot files are 725MB, too large for your CD burner and there is no tape drive at the remote office. What feature of SQL Server 2005 would help you get the snapshot transferred?

Answer :

Compressed Snapshots
In SQL Server 2005, you can specify a snapshot to be compressed and it will be compressed using the CAB format. If it compresses enough, you should be able to burn it onto a CD.

Question 8 :

The TRUSTWORTHY database property is by default?

Answer :

The correct answer is off – The TRUSTWORTHY database property indicates whether the installed instance of SQL Server trusts the database such that it can reach out and perform actions outside the database. By default it is OFF such that the database to reduce certain threats that can result from attaching a database that contains potentially harmful code

Question 9 :

Where are Notification Services event messages logged in SQL Server 2005?

Answer :

In the Windows Application Log
Event messages are logged in the Windows Application log.

Question 10 :

What would happen when you execute the code below in Query Analyzer (SQL Server 2000)
USE Northwind
EXECUTE sp_who

Answer :

Information about current SQL Server users and processes is displayed.
The explanation could be found in the Books Online under Creating a Stored Procedure. One of the sections, named System Stored Procedures which describes how SQL Server looks up the system stored procedure has this note: Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Question 11 :

How is Thesaurus data configured in SQL Server 2005 Full-Text Search?

Answer :

An XML file in the file system named tsxxx.xml where xxx is a code.
The thesaurus file for SQL Server 2005 is an XML file containing data and stored in SQL_Server_install_path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\ directory. There is one for each language and it is named tsxxx_.xml, where xxx is the three letter language code.

Question 12 :

What does a report model provide in SQL Server 2005 Reporting Services?

Answer :

A report model provides familiar business names for database tables and fields
A Report model provides business names and terms for database fields and tables. It allows for predefined relationships between tables as well as grouping items together logically.

Question 13 :

What can tracer tokens measure in SQL Server 2005 transactional replication?

Answer :

1 and 3
Tracer tokens measure two things. One is the amount of time elapsed between a command being committed on the publisher and being written to the distribution database. The other is from the writing to the distribution database and being committed to a subscriber. This allows you to determine the latencies for transactions moving through your replication topology.

Question 14 :

You want to script the execution of an Integration Services package from the command line for use from a Unix scheduler. What utility would you use?

Answer :

The dtexec.exe utility is used to configure and execute Integration Services packages from the command line.

Question 15 :

What does the sqlwb utility do?

Answer :

Opens SQL Server 2005 Management Studio.
sqlwb.exe actually opens Management Studio and can be configured to optionally open a solution, project, or script file when it starts.

Question 16 :

You want to automate the installation of SQL Server 2005 using SMO for your custom application and ensure that the encryption features are available with a service master key. What method would you call to create this key?

Answer :

The Regenerate method under the ServiceMasterKey object.
To create a Service Master Key in SMO, you would get a handle to the ServiceMasterKey object under the Server object and then call the Regenerate method.

Question 17 :

How can you determine which Service Broker ports are being used on your server?

Answer :

Query the system catalog view: sys.conversation_endpoints
The system catalog view sys.conversation_endpoints will show you which Service Broker endpoints, and therefore ports, are open on your server.

Question 18 :

In Full-Text Search, what is word breaking?

Answer :

Determining word boundaries.
Word breaking involves finding the boundaries of a word. This is different for different languages and SQL Server 2005 includes word breakers for 23 languages.
Can you call Notification Services APIs from unmanaged code?
Yes, but only for simple subscriptions.
Notification Services can be called from unmanaged code through a COM interface for simple subscriptions only. Condition based subscriptions are not supported through COM Interop.

Question 19 :

Which of the following is true about the Raw File Source in SQL Server 2005 Integration Services?

Answer :

It does not support an error output.
The raw file source has only one output and does not support an error output. It also reads faster than other data sources because it has no parsing or translation and does not use a connection manager.

Question 20 :

You wish to configure event logging for your SQL Server 2005 Notification Services instance. Where would you make this change?

Answer :

Edit the nsservice.exe.config file in the C:\Program Files\Microsoft SQL Server\90\NotificationServices\n.n.nnn\bin folder.
Event logging and most Notification Services configuration require editing an XML file. In this case, the NSservice.exe.config file is edited to set the appropriate editing level.

Question 21 :

Which of the following is not true about the Raw File Destintion connection in SQL Server 2005 Integration Services?

Answer :

It supports BLOB object data.
The Raw File Destination connection does not use a connection manager, supports NULL data, and only has one input. It also does not support BLOB data or have an error output.

Question 22 :

What message types exist in SQL Server 2005 Service Broker?

Answer :

These are defined for each contract.
Each application that sets up queues and contracts inside Service Broker must define the message types that are valid for the contract.

Question 23 :

What does the CEILING() function do?

Answer :

Returns the smallest integer greater than or equal to the value passed in.
CEILING() returns the smallest integer that is great than or equal to the value passed in.

Question 24 :

What is a dialog conversation in the SQL Server 2005 Service Broker.

Answer :

A dialog conversation is a conversation between services.
A dialog conversation is a conversation between services. A conversation includes messages being passed back and forth as part of a contract.

Question 25 :

What is row versioning in SQL Server 2005?

Answer :

Row versioning keeps a copy of each row for use by applications or transactions to prevent readers from being blocked by writers.
Row versioning is a method whereby the database engine keeps a copy of a row's data as it existed before the start of a transaction for queries to read this data and reduce locking contention if they are configured.