Monday, December 9, 2013

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.

Tuesday, November 26, 2013

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:

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
                           insert into Hierarchy (StageLevelName, StageLevel)    
                           values (@array_value, @Level)
                           insert into Hierarchy (StageLevelName, StageLevel, ParentID)
                           values (@array_value, @Level, @ParentId)
                     set @ParentId = @@IDENTITY
                     select @ParentId
                     --Moving to end of array
                     SELECT @StageLevel = STUFF( @StageLevel, 1, @separator_position, '')

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

Wednesday, October 16, 2013

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.