Excel + SQL Server Linked Server Troubleshooting

Following is a great article to troubleshoot errors thrown by OLEDB when connecting an Excel file from OPENROWSET query:

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Gives step by step resolution to multiple errors like:

- Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

- Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

- SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part

of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

On client deployments my recurring issue was setting my access to temp folders for service accounts.

Build Hierarchy From Delimited String

In this post we create a parent child hierarchy (n level) from a delimited string like [Parent_Child_LeafChild].

What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:

Stage1

--Stage2

----Stage3

DECLARE @separator_position INT -- This is used to locate each separator character

DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned

DECLARE @separator char(1) --Used in WHERE clause

declare @StageLevel varchar(255) = 'Stage1_Stage2_Stage3'

declare @holder varchar(255) = @stagelevel

SET @separator = '_' --Separator A.K.A. Delimiter

SET @StageLevel = @StageLevel + @separator --append ',' at the end

-- select PATINDEX('%[' + @separator + ']%', @StageLevel)

Declare @Level int = 0

Declare @ParentId int

  WHILE PATINDEX('%[' + @separator + ']%', @StageLevel) <> 0

      BEGIN -- patindex matches the a pattern against a string

             SELECT @separator_position = PATINDEX('%[' + @separator + ']%',@StageLevel)

             SELECT @array_value = LEFT(@StageLevel, @separator_position - 1) 

                      set @level = @level +1

                     --select @array_value, @level

                     IF @level =1 --This is parent node

                     Begin

                           insert into Hierarchy (StageLevelName, StageLevel)    

                           values (@array_value, @Level)

                     END   

                     ELSE

                     BEGIN

                           insert into Hierarchy (StageLevelName, StageLevel, ParentID)

                           values (@array_value, @Level, @ParentId)

                     END

                     set @ParentId = @@IDENTITY

                     select @ParentId

                     --Moving to end of array

                     SELECT @StageLevel = STUFF( @StageLevel, 1, @separator_position, '')

      END

The end result will be a table with each row as a recursive hierarchy. Plus it has level information for depth.

Moving Closer To MCSA: SQL Server 2012

Last week I passed:
Exam 457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 1

Which will help me to qualify for Microsoft Certified Solutions Associate for SQL Server 2012. Now am preparing for 458 (Part 2).

The exam was moderately hard and I had doubts about couple of questions. Anyways getting through the course material helped me a lot in knowing SQL Server 2012.

Cheers!