PREEMPTIVE_OS_GETPROCADDRESS is a new wait type that was introduced in SQL Server 2008 to track time taken by GetProcAddress to load the entrypoint in the DLL when Extended Stored Procedure calls are made by the SQL Server instance. However, the way the wait time is tracked by this wait type has a little quirk which Rob Dorr had documented in a blog post.

Based on a discussion on #sqlhelp thread on Twitter, I found the need to demonstrate this unique quirk with the help of an example. I am using the following command to list you the sub-directories on my C: drive using the command:

exec master..xp_dirtree ‘C:\’

Next I monitored the wait statistics for the session executing this Extended Stored procedure using the T-SQL script below with a 1 second delay:

WHILE (1=1)
SELECT [session_id], [status], [command], [start_time], [cpu_time], [reads], [writes], [wait_resource], [wait_time], [last_wait_type], [wait_type]
FROM sys.dm_exec_requests
WHERE [session_id] = 53 -- Session ID that is executing the Extended Stored Procedure

WAITFOR DELAY '00:00:01'
RAISERROR ('', 10, 1, N'Waits');


The output of this script was as follows:

session_id status        command          start_time              cpu_time    reads    writes      wait_time   last_wait_type                        wait_type

53         runnable      EXECUTE          2011-07-20 00:59:44.330 62          0        0           712         PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         running       EXECUTE          2011-07-20 00:59:44.330 234         0        0           1713        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         runnable      EXECUTE          2011-07-20 00:59:44.330 452         0        0           2714        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

As you can see above the wait time keeps increasing even though I can see that the session is performing work due to the following results:
a. The results window in SSMS for the session executing the extended stored procedure was churning out results
b. Using a Process Monitor, I could trace the filesystem activity on the C: drive for the SQL Server process

You could run into this issue while using any Extended Stored Procedure (system or user) for a SQL Server 2008 or SQL Server 2008 R2 instance. Since I know that the session is performing work, the only two other things that I would check if there are resource bottlenecks w.r.t. memory or I/O if some filesystem related activity is being performed by the extended stored procedure. Most of the issues where I have see this wait type causing confusion is when the extended stored procedure is performing disk related activity. At that time, Process Monitor will put your doubts to rest along with the information that I mentioned above to confirm that that the procedure is actually doing some work and not stuck or perceived as “hung”.

Excel via Linked Servers


Recently, I had replied to post on the #sqlhelp Twitter hashtag regarding configuring a Linked Server to an Excel file using the GUI in Management Studio. This is very much possible. I use a linked server to pull data from .xls file on a 64-bit SQL Server instance for an application that I maintain.

Using the 64-bit ACE provider, you can now do this. The data source which is masked in the above picture is the location of the Excel file with the full file path.

Once you have this configured, you can access the Linked Server catalogs by expanding the Linked Server in Object Explorer. Each table listed in the catalog is actually an Excel sheet.

This is fairly simple task but since this isn’t an explicit example out there for this, I thought I would do a quick post on the same.

Addedum: April 4th, 2010. After my colleague, Evan pointed out the server side support policy for ACE.

Disclaimer: The ACE redistributable link does mention the following:

The Access Database Engine 2010 Redistributable is not intended:

  • As a replacement for the Jet OLEDB Provider in server-side applications.
  • To be used within a service program or web application that relies on a Windows service.
Technorati Tags: ,,

Removing primary transaction log file

Recently I was discussing about removing transaction log files on the #sqlhelp hashtag on Twitter. The question was whether we could remove an existing transaction log file after adding a new one. During the course of the discussion, I learnt that the file that was being removed was the primary transaction log file of the database.

When you attempt to remove the primary log file of a database from Management Studio or using ALTER DATABASE command, you will receive the following error:

Drop failed for LogFile ‘dbLogTest_log’.  (Microsoft.SqlServer.Smo)

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020)

This is by-design. We do not allow the removal of the primary transaction log file of user databases. This was first introduced in SQL Server 2000.

Tibor (Blog), a SQL Server MVP, has already blogged about how to remove transaction log files using T-SQL commands here. So, I am not going to duplicate the post here again by providing the same T-SQL commands.

Data Type Mapping for OLEDB Providers

I recently had a question on the #sqlhelp hashtag on Twitter regarding how OLE DB Providers map the data columns to SQL Server data types.

The Data Type Mappings for SQL Server for distributed queries are mentioned here. The DBType values for each data that you are retrieving from a non-SQL data source like Oracle, Excel, Access can be found out using RowSet Viewer. The Microsoft® Developer Network (MSDN®) Platform SDK contains an OLE DB RowsetViewer sample application written in Microsoft Visual C++®. This application enables you to connect to either the Microsoft OLE DB Provider for AS/400 and VSAM or the Microsoft OLE DB Provider for DB2, open a table window, type the host file name or DB2 database, return a rowset, and browse the contents.

Using Rowset Viewer you can get the DBType of each column returned from the remote data source. The function used is IColumnsRowset::GetColumnsRowset. Using this you can create the necessary schema on the SQL Server database which will act as the destination for the data received from the remote data source.

Happy DB Scheming! Smile