Excel via Linked Servers

image

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: ,,

What does cntr_type mean?

Have you ever wondered if the cntr_type column value in the sys.sysperfinfo or sys.dm_os_performance_counters output has a significant meaning or not. Well since the column value is there, it obviously has a meaning. Additionally, if the raw values represented by the output of some of the counter types is considered at face value, then your performance base lining can take a severe beating.

Each cntr_type value meaning can be found from the WMI Performance Counter Type or Windows Server Performance Counter Type documentation on MSDN. 

The common counter types in SQL Server are:
PERF_COUNTER_RAWCOUNT | Decimal | 65536
Raw counter value that does not require calculations, and represents one sample.

PERF_COUNTER_LARGE_RAWCOUNT | Decimal | 65792
Same as PERF_COUNTER_RAWCOUNT, but a 64-bit representation for larger values.

PERF_COUNTER_COUNTER | Decimal | 272696320
Average number of operations completed during each second of the sample interval. NOTE: For "per-second counters", this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used. For example, batch requests/sec is a per-second counter, it would show cumulative values.

PERF_COUNTER_BULK_COUNT | Decimal | 272696576
Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.

PERF_AVERAGE_BULK | Decimal | 1073874176 | Decimal | 537003264
Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.

PERF_LARGE_RAW_BASE | Decimal | 1073939712
Base value found in the calculation of PERF_RAW_FRACTION, 64 bits.

Example:
If you had the following values:
SQLServer:Plan Cache | Cache Hit Ratio | Temporary Tables & Table Variables | 381
SQLServer:Plan Cache | Cache Hit Ratio Base | Temporary Tables & Table Variables | 386
Then the Temp Table/Variable cache hit ratio percentage would be: 98.7% (approx.)

You can use the query below to get the comments for each counter type as discussed above:

select object_name,counter_name,instance_name,cntr_value,
case cntr_type 
	when 65792 then 'Absolute Meaning' 
	when 65536 then 'Absolute Meaning' 
	when 272696576 then 'Per Second counter and is Cumulative in Nature'
	when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
	when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 
end as counter_comments
from sys.dm_os_performance_counters
where cntr_type not in (1073939712)

 

Documentation on MSDN:

WMI Performance Counter Types

http://msdn.microsoft.com/en-us/library/aa394569(VS.85).aspx

SQL Server 2005 BOL Topic

sys.dm_os_performance_counters (Transact-SQL) 

The broad classes of counters are as follows:

Non-computational Counter Types

http://msdn.microsoft.com/en-us/library/aa392713(VS.85).aspx

Basic Algorithm Counter Types

http://msdn.microsoft.com/en-us/library/aa384813(VS.85).aspx

Counter Algorithm Counter Types

http://msdn.microsoft.com/en-us/library/aa389384(VS.85).aspx

Timer Algorithm Counter Types

http://msdn.microsoft.com/en-us/library/aa393909(VS.85).aspx

Precision Timer Algorithm Counter Types

http://msdn.microsoft.com/en-us/library/aa392755(VS.85).aspx

Queue-length Algorithm Counter Types

http://msdn.microsoft.com/en-us/library/aa392905(VS.85).aspx

Base Counter Types

http://msdn.microsoft.com/en-us/library/aa384811(VS.85).aspx

Statistical Counter Types

http://msdn.microsoft.com/en-us/library/aa393663(VS.85).aspx

Downloading that cumulative update or hotfix…

I recently answered a question on Twitter regarding the availability of Cumulative Update downloads for specific platforms.

Starting from SQL Server 2005, our Release Services team moved to an Incremental Servicing Model to deliver fixes for issues identified on current releases of SQL Server[s]. We now release a Cumulative Update package for a Service Pack periodically which contains all the fixes released since the RTM version of the Service Pack and hence the name “Cumulative Update”.

 image

 image

Now you click on the link mentioned in the picture below, you will get an option to download the packages associated for all the platforms.

image
I know this is quite trivial information but I have answered this question a few times and hence the post.

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.

The GenNext for SQL Server

The SQLPASS Keynote on Day 1 of the Summit kicked off a slew of announcements which would ensure that Twitter received it’s fair share of activity for SQL Server. Look up the #sqlpass hashtag on Twitter to understand what I am saying. There are over 4,500 registrations from 49 countries for the streaming keynotes.

Among all the announcements, the one that created the maximum amount of buzz was the next major version of SQL Server, code-named Denali CTP1. As soon as Ted Kummert, Senior VP at Microsoft, made the announcement, a flurry of Tweets went out from the Tweeps attending the Keynote announcing to the world about Denali. SQL MVP Aaron Bertrand (Twitter | Blog) posted walkthrough for installing Denali on his blog. Read his narration of the Keynote session here.

The key features of Denali are:

  • SQL Server AlwaysOn – A Mission Critical Platform that helps in increasing up-time, reduce unplanned downtime due to OS patching, simplify High Availability management, support for multiple secondaries and multi-site clustering.
  • Column-based query accelerator – This will help improve query performance manifold and reduce the time taken for performance tuning.
  • SQL Server Tools code-named “Juneau – A unified environment which is a one-stop-shop for your developers and helps accelerate your time-to-market.
  • IT administration enhancements – Improved security facilitated through various improvements built into the product. You can now take advantage of the powerful XEvents to monitor SQL activity.
  • Beyond relational enhancements – Performance improvements for FileStream, Full-text search, 2D-Spatial Support and FileTable.
  • Pervasive Insight – Expand the use of Business Intelligence to business users by providing new and engaging ways to discover insights through a drag-and-drop interface, smart and powerful data querying and interactive storyboarding to allow users to quickly create and share visual presentations of large datasets.
  • Read more about it here.

    Other announcements include:

    Microsoft Code-name “Atlanta”

    Microsoft codename Atlanta is a secure cloud service that proactively monitors your Microsoft SQL Server deployments. It helps you avoid configuration problems, reduce downtime, improve performance, and resolve issues faster. Atlanta agent can analyze both versions x86 and x64 versions of SQL Server 2008 and above. Now you get a vital insight into multiple known issues that you could plague your SQL Server installation without having to manually check for known issues. Atlanta will re-define pro-active monitoring for SQL Server instances.

    SQL Server 2008 R2 Parallel Data Warehouse (PDW)

    Microsoft SQL Server 2008 R2 Parallel Data Warehouse (previously code named project “Madison”) is a highly scalable appliance that delivers performance at low cost through a massively parallel processing (MPP).

    New Path to Microsoft Certified Master: Microsoft SQL Server 2008

    If you have an aspiration to earn the coveted title of a Microsoft Certified Master for SQL Server, then there is a new path to do so. Click on the above sub-title to learn more.

    If you feel that you need more food for thought, then read the Press Pass: Microsoft Introduces New Offerings for Business Intelligence and Mission-Critical Workloads

     Watch Rushab Mehta, SQL Pass President, and Ted Kummert, Senior VP at Microsoft, discuss SQL Server: