The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P73: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 342 Chapter 8 ETL Techniques 1. Determine if ad hoc queries have already been enabled on your server. Run the following statements in a query window in SSMS. A zero in the run_value column in the result set means ad hoc queries are disabled and a one means they are enabled. EXEC sp_configure show advanced 1 RECONFIGURE EXEC sp_configure Ad Hoc Distributed Queries 2. Ensure that ad hoc distributed queries are enabled. Run the following statements EXEC sp_configure show advanced 1 RECONFIGURE EXEC sp_configure Ad Hoc Distributed Queries 1 RECONFIGURE 3. By default the various OLE DB providers on the server allow ad hoc access. View the list of the providers on your server by connecting to your SQL Server instance in the SSMS Object Explorer. Then expand Server Objects I Linked Servers I Providers. Right click on a provider and choose Properties to view its properties. Is it set to disallow ad hoc access What about the other providers Using the OPENROWSET Function The OPENROWSET function can be used to access data from data sources external to SQL Server. This can be a great way to interact with data in external sources without requiring administrators to first configure a linked server. The OPENROWSET function can also be used as yet another way to bulk load data into SQL Server. OPENROWSET is a table-valued function. That means that the result of the call to the function is actually a table. The implication is that you use the OPENROWSET function call in place of a table reference in statements that work with tables like SELECT INSERT UPDATE and DELETE . Start by seeing how to use SQL Server to access external data. ETL Techniques Chapter 8 343 The basic syntax of the OPENROWSET Function looks like this OPENROWSET provider_name datasource user_id password provider_string catalog. schema. object query The provider_name is the name of the OLE DB provider you wish to use when connecting to the external database. For SQL Server you can use SQLNCLI. You can find out what
