How to create an auto generated row count within a query using SQL Server

By Evan Schmidt

This mini-blog describes how to dynamically generate an ID column within a SQL Query using SQL Server 2008.

 

We will start with a simple table called “Table1”:

Column1

Yes

No

Maybe

 

 

We want to create an Auto Generated ID for each row returned from the table. For Example:

Column1	AutoGeneratedID
Maybe	1
No	2
Yes	3

 

 

The following SQL Statement can be used to generate this identity column:

select Column1

, ROW_NUMBER() over (order by Column1) as AutoGeneratedID

from dbo.Table1

 

Note how the row count is assigned based on the ORDER BY clause specified within the OVER statement.

 

Import Excel File from a SharePoint site to SQL Server

By Evan Schmidt

This article explains the steps required to successfully import an Excel 2007 File, hosted on a SharePoint site, to SQL Server 2008 R2 using OPENROWSET.

 

1) Install the Office 2010 Data Access Components

Install the Office 2010 Data Access Components which contains the “Microsoft.ACE.OLEDB.12.0” provider.

 

2) Enable Distributed Queries

Change the settings for the SQL server database to allow distributed queries.

 

EXEC sp_configure ‘allow updates’, 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure ‘show advanced options’, 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

RECONFIGURE WITH OVERRIDE;

GO

 

3) Ensure the SQL Server service account allows file system interaction

In my experience running the SQL Server service under the “Network Service” account does not correctly pass permissions.

Option A) Change the account which runs the SQL server service to Local System account

Option B) Change the account which runs the SQL server service to an account that has administrator rights on the machine

 

4) Ensure that account running the query has access to the SharePoint site

This will ensure that Excel files located on the SharePoint site can be accessed. Test this by downloading the file manually.

 

5) Ensure that Windows Server can browse SharePoint directories

On Windows Server 2008, install the Desktop Experience component and enable the WebService Service.

 

6) Ensure that the IE security settings allow browsing the SharePoint site

Add the SharePoint site to the trusted sites list and turn trusted site security level to low.

 

7) Use correctly Formatted SQL with $ after the sheet name

The SQL Statement should looks like this:

 

SELECT *

FROM OPENROWSET (          

            ‘Microsoft.ACE.OLEDB.12.0’,

            ‘Excel 12.0;Database=\\sharepoint directory\file name.xlsx’,

            ‘SELECT * FROM [Sheet Name$]’

)