01 | April | 2011 | TroubleshootingSQL

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: Twost,Linked Servers,#sqlhelp

Yesterday I worked with Argenis [Blog | Twitter] regarding an interesting startup failure for a SQL Server standalone instance.

The error in the SQL Errorlog was:

2011-03-30 14:43:18.46 Server      Large Page Extensions enabled.
2011-03-30 14:43:18.46 Server      Large Page Granularity: 2097152
2011-03-30 14:43:18.46 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.46 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.55 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.56 Server      Large Page Allocated: 32MB
2011-03-30 14:43:38.07 Server      Error allocating 16777216 lock owner blocks on startup

After trying the removal of the trace flags that were enabled, we still couldn’t get the SQL instance to startup. The next thing we did was to start the SQL instance from command prompt using the following parameters:

sqlservr.exe –c –m -T3608 -f

The above started the SQL Server instance in minimal configuration mode without any errors. Now the above lock block allocation seemed a bit too large. So we checked the sp_configure output for the configured value of the “locks” configuration option. And voila, we had our answer! The value of “locks” was set to 16777216. We then changed this value to 0 (ZERO) which is the default using sp_configure and restarted SQL instance. This time the SQL instance came online!

Takeaway:

If the SQL instance fails to start, try starting the SQL instance using minimal configuration. If SQL comes online, check the configuration settings for any untoward configuration values.

Reference:

Wiki: SQL Database Engine Startup Failures

https://troubleshootingsql.com/2010/11/23/wiki-sql-database-engine-startup-failures/