Which commands are allowed when the "Ad Hoc Distributed Queries" server option is set to 0 in SQL Server 2005?

Microsoft SQL Server FAQs

(Continued from previous question...)

Which commands are allowed when the "Ad Hoc Distributed Queries" server option is set to 0 in SQL Server 2005?

Answer: OPENDATASOURCE calls

Explanation: This command allows the use of OPENROWSET and OPENDATASOURCE commands.

(Continued on next question...)

Other Interview Questions

• What's SQL Server
• Can you use a CASE statement in a WHERE clause?
• How large a database can you have in this edition of SQL Server 2005? ...
• What is one of the downsides of using RAID 5 as opposed to RAID 1?
• To optimize the configuration of tempdb for performance, what setting should be used for the tempdb recovery model?
• Which query will return a list of the synonyms in your database?
• You are building an application for a Smartphone and want to use SQL Server Compact Edition. However you do not want this to run as a service all the time because of power concerns. What can you do?
• Which of the following classes provide the context of the caller to SQLCLR Assemblies? Submitted by Michael Coles
• How can you get SQL Server 2005 to simulate multiple processors for query testing?
• You restart your SQL Server on a Sunday morning after some maintenance ...
• Which of the following CREATE ASSEMBLY statement WITH PERMISSION_SET clauses sets ...
• With SQL Server 2005, Oracle servers can be included in your replication topology. How can an Oracle server function?
• In SQL Server 2005, what permissions does a user need to successfully execute the USE command?
• If you want to apply a hot fix to SQL Server 2005, do you need to apply them all in order?
• What does this return on SQL Server 2005? select NEWSEQUENTIALID()
• Which key does the OPEN MASTER KEY statement open?
• How can you ensure they do not change the underlying tables without also changing the view and not disrupt their workflow?
• You want to partition these past orders columns into a new table. What type of partitioning is this?
• What does this code return? select 6 | 7 Figure it out without running it.
• What is an example of hardware partitioning?
• To which versions of SQL Server 2005 can you upgrade an SQL Server 2000 MSDE SP3 edition database?
• You wish to initialize subscribers to your SQL Server 2005 database using FTP. Which versions can you do this with?
• You have upgraded your SQL Server 2000 on Windows 2000 server to SQL Server 2005 to take advantage ...
• What does the DUMP command do?
• You have just upgraded your server from a single processor by adding 3 additional processors....
• What is the best way to have your junior DBA do this?
• What is the timestamp datatype used for?
• In SQL Server 2005, if you initialize a subscription using FTP, how is the password sent?
• What rights does a user need to perform a truncate table command SQL Server 2005?
• What is the quickest way to get them working?
• You wish to prevent anyone from using the sa account on your SQL Server 2005 instance, but still keep it available if your polocy changes. What can you do?
• You wish to prevent anyone from using the sa account on your SQL Server 2005 instance, but still keep it available if your polocy changes. What can you do?
• What is a zettabyte?
• You are configuring a SQL Server 2005 Reporting Services instance for email delivery of reports. What are your options for delivering email?
• In SQL Server 2005 Analysis Services, what does the Synchronize Database Wizard allow you to do?
• Which of these options cannot be set in the SQL Server 2005 Integration Services Bulk Insert task?
• What is the maximum number of instances of Analysis Services 2005 can be installed on a server?
• How many dimensions can you have in a SQL Server 2005 Analysis Services database?
• Which index rebuild operation in SQL Server 2005 can be performed online?
• On which port does the default instance of SQL Server 2005 Analysis Services listen to by default?
• What does CONTINUE do in T-SQL?
• In SQL Server 2000, how can you send an alert without sending a message to the client?
• Which permission in SQL Server 2005 allows you to switch your security context to that of another user with the EXECUTE AS statement?
• What does this WHERE clause evaluate to?
• What value should you use for the BROKER_INSTANCE parameter?
• Which type of XML variable requires a\one and only one root element?
• Which of these is not a parameter you can pass to the LOGINPROPERTY function?
• What does the NULLIF function do?
• When using the SQLCMD tool, what does the -V parameter do?
• What should you have applied a patch for by now on your Windows 2003 and XP systems?
• You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?
• In the Windows world, if you hit the "Enter" key on your keyboard between two single quotes and inserted this value, what data would be stored in the column?
• You wish to back up the symmetric key used to protect data on the Reporting Services 2005 instance installed on Sales01. From your workstation, what command do you use?
• Which table would you query in your report to get logging information about a job step?
• You want to speed up your SQL Server 2005 Service Broker queue by placing an index on it. What is the best way to do this?
• Which is these is not necessary for a well-formed XML document?
• You want to create a covering index for this query on your SQL Server 2005 database ...
• On which port does the dedicated administrator connection listen in SQL Server 2005?
• What is the default type of XML data if you do not specify an arguement?
• You created a SQL Server 2005 Service Broker queue ....
• When you use the sp_xml_preparedocument stored procedure in SQL Server 2000, what is returned?
• What does the function ORIGINAL_LOGIN return in SQL Server 2005?
• You are performing a number of SELECT queries on one of your SQL Server 2005 Service Broker queues and you get a call that applications using the queues are running slowly. What can you do?
• What's an allocation unit?
• Your SQL Server 2005 Express Edition locks up frequently and cannot be accessed. How can you enable the dedicated administrator connection?
• What does VARP() do in SQL Server 2005?
• When declaring an XML document, which type allows text nodes at the top level?
• Can a contained in SQL Server 2005 Integration Services enlist in a transaction started by a sibling container?
• What does a return value of -1 mean from the function XACT_STATE() in SQL Server 2005?
• In SQL Server 2005, what is the maximum size of an index key?
• When you declare an XML variable in SQL Server 2005, what are the two types of typed XML data. What are they?
• In Service Broker for SQL Server 2005, you encounter the following setting in one of your queues...
• Can a deterministic computed column be full-text indexed in SQL Server 2005? Submitted by Matija Lah
• What does the PAD_INDEX parameter do when you create an index in SQL Server 2005?
• In SQL Server 2005, what does the CREATE SERVICE command do?
• What does HAS_DBACCESS() do?
• In SQL Server 2005 T-SQL, what does the CONTINUE statement do?
• You need to remove a large table from your database ...
• If you set RETENTION = ON in your SQL Server 2005 Service Broker queue, what messages are retained?
• How can you quickly determine if any of these indexes are really being used?
• Which commands are allowed when the "Ad Hoc Distributed Queries" server option is set to 0 in SQL Server 2005?
• When creating a SQL Server 2005 Service Broker message type, what does the EMPTY validation option mean?
• You cannot remember what permissions that the db_accessadmin fixed database role has ...
• When a user on a remote SQL Server wants to execute a stored procedure through a linked server on your local server, what security context is used?
• Which database holds the tables that Database Mail uses to store mail messages and other data in SQL Server 2005?
• You are wondering if your indexing scheme for the SQL Server 2005 database that you are managing is appropriate?
• Error severity 13 indicates what?
• In which order do you perform an upgrade to SQL Server 2005 for replicated databases?
• How many Service Packs will be released for SQL Server 2005 in 2007?
• You cannot execute procedures on the 2005 server. Why not?
• What is the recommended way to send mail from SQLAgent in SQL Server 2005?
• When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?
• What the heck does ATN2 do?
• How does a differential backup know which extents have changed so that it can be very quickly run?
• If you run this, what does it return? select applock_mode('public', 'SalesApp', 'Transaction')
• How many rows are returned by these two select statements?
• What does the Queue Reader Agent do in SQL Server 2005 replication?
• What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?
• The Sort component in SQL Server 2005 Integration Services is what type of component?
• If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?
• Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?
• What does this return?
• You have installed a US English SQL Server 2000 instance with the default options, collation, and sorting...
• Where does Profiler store its temporary data in SQL Server 2005?
• What is the Service Broker Identifier
• You are looking to import a large amount of data from a remote OLEDB data source that is not a text file. Which of the following techniques can you use?
• How are modified extents tracked in SQL Server 2005 (which internal structures)?
• What does this return? select (1324 & 1024)
• What does the Log Reader agent in SQL Server 2005 replication do?
• What type of update did you perform?
• What does a @@fetch_status of -2 mean in SQL Server 2005?
• You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
• What is the cost threshhold for parallelism in SQL Server 2005?
• You have a Scalable Shared Database setup for reporting purposes on SQL2. You want to be able to keep a point in time view of the reporting database each month. What can you do?
• Which framework would you use to manage this database programatically?
• Which collation is used for Service Broker messages sent between the instances?
• What does the max full-text crawl range option do?
• Which of the following is not an allocation unit in SQL Server 2005?
• Which of the following is the best use for a Scalable Shared Database in SQL Server 2005?
• You are loading 100 rows of data into a narrow table that is heavily used by your production inventory queries...
• How can SQL Server Agent Mail send messages in SQL Server 2005?
• What is the scale of measurement for the cost threshold for parallelism setting in SQL Server 2005?
• Which of the following statements best describes the filter capabilities of Report Builder?
• True or false, Report Builder supports user defined run time parameters?
• Using Report Builder that is bundled with Reporting Services 2005, which of the following would work as a way to add a derived field to a report?
• If you absolutely need a report to look the same regardless of what OS or viewing software is being used, which of the following file formats would be the best choice?
• True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?
• When discussing image support in Report Builder, which of the following is the most accurate statement?
• Using Report Builder, which of the following is the best statement about the formatting options for boolean columns?
• What is the easiest way to capture the SQL statement for a Report Builder report you're troubleshooting?
• Clicking File, Save in Report Builder does which of the following?
• Which of the following choices show the three report formats supported by Report Builder
• Using Report Builder, which of the following statements is correct about formatting numbers?
• True or false, Report Builder supports using the LIKE function inside filters?
• Which RAID levels store parity information?
• You have a large table that you wish to partition to improve performance...
• On which platforms can you use Instant File Initialization to improve database growth performance in SQL Server 2005?
• How do you recover the snapshot?
• Using Reporting Services 2005, it is true or false that subreports execute a query against the datasource once for every detail row in the report?
• You have noticed in both your SQL Server 2000 and 2005 instances that when a database grows in SQL Server, there is a delay in the database response. Why is that?
• Which utility is used to administer SQL Server 2005 Notification Services instances?
• After you've completed a backup for your Sales server to disk...
• What is the result of the following query?
• Out of the box Report Builder supports two report level fields that can be shown on a report. Which option below has those two options?
• Can a particular event in SQL Server 2005, such as the CREATE USER command, have more than one DDL trigger assigned to it?
• Can you re-associate the application with the same SSNS instance?
• How many users can be added to an application role in SQL Server 2005?
• What algorithm is used to encrypt the Database Master Key when it is created?
• Which protocols support Kerberos authentication on SQL Server 2005?
• you want to be sure that SQL authenticated logins must abide by the password policy.. .
• How can you you be sure that a SOAP queries that are looking for a WSDL response will be provided?
• Where can you view the list of server-scoped DDL triggers?
• You want to be sure that your reporting solutions using a database snapshot are properly protected from disaster. How can you back up your database snapshots?
• When you install SQL Server 2005 and create a new database, is a Database Master Key created?
• What does the DEALLOCATE statement do in SQL Server 2005?
• Report Builder has no built in support for source control. Which of the following choices could be implemented if you decided that those reports needed to be under source control?
• Setting the TRUSTWORTHY database property to ON allows you to execute the following from within the context of the database: Submitted by Charles Hawkins
• What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?
• Which of the following is not available to you?
• Which types of replication work with Oracle publishers?
• What does @@textsize return?
• In configuring the thesaurus configuration file for SQL Server 2005 Full-Text Search, you set the diacritics_sensitive value to 1. What does this mean?
• What does the CREATE SERVICE statement do in SQL Server 2005?
• What does the CREATE SERVICE statement do in SQL Server 2005?
• The queries still seem slow immediately after snapshot creation. Why?
• In Reporting Services 2005, how is Report Builder typically deployed to end users?
• What happens when a transaction fails on the publisher?
• 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?
• When creating a full-text index on a BLOB column ...
• In SQL Server 2005 Full-Text Search, you set up a replacement set like this...
• In the SQL Server 2005 thesaurus XML configuration file, what is the expansion set?
• What feature of SQL Server 2005 would help you get the snapshot transferred?
• The TRUSTWORTHY database property is by default?
• Where are Notification Services event messages logged in SQL Server 2005?
• What would happen when you execute the code below in Query Analyzer (SQL Server 2000) Submitted by Jacek Osuchowski...
• How is Thesaurus data configured in SQL Server 2005 Full-Text Search?
• What does a report model provide in SQL Server 2005 Reporting Services?
• What can tracer tokens measure in SQL Server 2005 transactional replication?
• 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?
• What does the sqlwb utility do?
• What method would you call to create this key?
• How can you determine which Service Broker ports are being used on your server?
• In Full-Text Search, what is word breaking?
• Can you call Notification Services APIs from unmanaged code?
• Which of the following is true about the Raw File Source in SQL Server 2005 Integration Services?
• You wish to configure event logging for your SQL Server 2005 Notification Services instance. Where would you make this change?
• Which of the following is not true about the Raw File Destintion connection in SQL Server 2005 Integration Services?
• What message types exist in SQL Server 2005 Service Broker?
• What does the CEILING() function do?
• What is a dialog conversation in the SQL Server 2005 Service Broker.
• What is row versioning in SQL Server 2005?
• What does @@MAX_PRECISION return?
• Which of the following columns can be indexed with SQL Server 2005 Full-Text Search?
• When starting SQL Server 2005 from the command line, what does the -h switch do?
• Which of the queries is the best one for Janice to use to return the information she has been requested to provide?
• In SQL Server 2005, you want to monitor your transactional replication and determine how long it takes for a transaction to move from the publisher to a subscriber. What would you use to help you do this?
• How many credentials can one login be mapped to?
• You are looking to secure your publisher to prevent unauthorized logins from accessing the data through replication. Where do you add the list of logins that have access?
• What does the ERROR_PROCEDURE() function return?
• Credentials in SQL Server 2005 are used to access what?
• Which of the following is an invalid file name for a database file?
• What does @@options return?
• What do you need to do now to ensure you can recover the routes in the event of a disaster?
• You are looking to setup a large Notification Services application...
• You want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?
• You are troubleshooting a Service Broker application ...
• You are implementing replication across the Internet for a large bank that wishes to move up to date pricing information...
• Can you use Full-text search to index this column?
• To recover an instance of Notification Services in 2005 on another server, what information would you need?
• What type of connectivity does the readpipe/makepipe utility test?
• In SQL Server 2005, which of the following schema changes are supported for the publication objects of a replicated database?
• You want to disable the receipt of messages in one of your databases by the Service Broken. How should you do this?
• You are building a .NET assembly that will access the registry of the local machine for a factor used in a computer column. What permission set should you assign it?
• No column permissions are assigned. What should you do?
• How can you determine which user is executing statements in Profiler?
• Assuming the column name is correct, which of the following is a valid cast operation in SSIS _expression language?
• There are two types of subscriptions in SQL Server replication. What are they?
• What is the scope of the control permission in SQL Server 2005?
• When testing connectivity in SQL Server, you use which utility in conjunction with the readpipe utility to establish a connection?
• In replication, what is each set of the source data that is replicated from the source server called?
• In SSIS, what is the difference between output columns and external columns?
• You want to be sure your database server if properly secured. You have two instances, a default instance and a named instance installed. Which ports do you need to open?
• You have a relatively new SQL Server 2005 and msdb is corrupt. How can you fix this database without a restore? (data loss is acceptable)
• In SQL Server 2005 Integration Services, if you want to import a flat file very quickly that contains only integer data, what type of parsing should you use?
• In a new default SQL Server 2005 installation, what is the status of the dedicated administrator connection?
• In the REPEATABLE READ isolation level, what phenomena is still possible?
• What else should you configure on these instances?
• You have specified that a SQL Server 2005 login must respect the password policy on a Windows XP host. Which of the following are valid passwords for the user Kendall?
• A certificate is which type of security mechanism?
• Which of the following services is instance-aware?
• What What does the term ACID refer to?
• SELECT USER_NAME() -- Returns Andy
• What format are images rendered in if they were stored originally in another format?
• What does the tablediff utility do?
• Which severity levels of errors can be corrected by a user (as opposed to those that an administrator or Microsoft must work to fix.)
• What is the granularity in seconds of the timestamp datatype?
• How many plans must you setup?
• How can you easily monitor to see if this assembly is being used?
• You wish to ensure you can recover your SQL Server 2005 database to a point in time if necessary. Which recovery models can you use?
• Does a root element in an XML document necessarily contain all the content for a well-formed document?
• Which of the following datatypes can be represented in a SQL_VARIANT datatype?
• What's the difference between a server login and a database user?
• For regulatory reasons, you must maintain an exact text copy of your XML documents in your SQL Server 2005 database. What datatype of storage should you choose?
• What is a bookmark lookup?
• Yesterday's QOD How are comments denoted in an XML document?
• How secure are encrypted stored procedures, triggers and views?
• You then write a function to use this assembly, but it does not work. What could be the problem?
• The Service Broker operates on messages in which fashion?
• What security role should you assign them?
• What does this method in SQL Server 2005's SMO equate to in T-SQL? db.CheckTables(RepairType.None)
• In SQL Server 2005, can you include a user-defined role as a member of another user-defined role?
• In SQL Server 2005, what does instance aware mean for a service?
• If you use the EXECUTE AS clause to impersonate a user at a database-scoped level...
• The nesting level for stored procedures in SQL Server 2005, but how many databases can you access inside one stored procedure?
• Is it possible to build an index on a view
• What does REVERT do in SQL Server 2005?
• What does SEND do in SQL Server 2005?
• Where is varchar(max) data stored?
• What will occur with the following code in SQL Server 2005 if executed by Alice?
• What T-SQL function can achieve this?
• You want to disable the trigger dEmployee on the HumanResources...
• In SQL Server 2005, if you want the keys securing your data to expire over time, what type of key encryption would you choose?
• Which is larger in size in SQL Server 2005 if you are using symmetric keys?
• What result would you expect from this statement? Select * from dbo .MyTable
• You are using SQL Server 2005 and you wish to find a number of details about stored procedures created...
• A .NET assembly running with UNSAFE permissions can do which of the following?
• What does the SQL Writer service do?
• Which key provides the strongest encryption?
• This administrator will own the jobs. Which role should you assign?
• What level of permissions does a user need to create an assembly with UNSAFE permissions?
• Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?
• What could be the problem if you know the service is working properly?
• In SQL Server 2005, which network protocols use endpoints?
• You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?
• What port do you need to open on your server firewall to enable named pipes connections?
• When the network service built-in account connects to a network resource, what is the context?
• A certificate is like which type of key in SQL Server 2005 encryption?
• How many connections are available in SQL Server MSDE 2000?
• What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin
• How many of these servers can you consolidate?
• To maintain the best performance of your SQL Server 2005 databases, how should you deploy encryption technologies?
• In SQL Server 2005, which network protocols use endpoints?
• You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?
• Can you give me an overview of some of the database objects available for use in SQL Server 2000?
• What is an index? What types of indexes are available in SQL Server 2000?
• What does NULL mean?
• What is a primary key? What is a foreign key?
• What are triggers? What are the different types of triggers in SQL Server 2000?
• How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?
• What is a performance consideration of having too many indexes on a production online transaction processing (OLTP) table?
• What can be used to ensure that a field in a table only accepts a certain range of values?
• What is the difference between a return parameter and an OUTPUT parameter?
• What is a correlated sub-query? How can these queries be useful?