PATHITEMREVERSE() and Unbalanced Trees

Recently I wanted to analyze some web log information, and decided to try it out in a BISM.  After getting the data loaded up, I noticed that I had not done any parsing on a field that indicated the page/control that was being logged.  This information was in an unbalanced hierarchical format, delimited with “/”.  I was really only concerned with the leaf level of this tree, as this is the information that needed to be reported to my end users.

After looking around for the right text function to parse this, i wasnt able to find a REVERSE() function.  As with most things in DAX, you will find a way to perform the operation if you take a step back and review what you are trying to do.  In this particular situation, I was trying to reverse a path, and there just so happens to be a PATHITEMREVERSE().  If you are not yet familiar with PATH(), PATHITEM(), these are functions that allow you to traverse a parent/child relationship.  So, in my example, I could parse the string in the following manner:

Field1:  /Controls/Screens/ScreenName

The first issue is that PATHITEMREVERSE() takes the input in the format of PATHITEM(), which is pipe delimited.  Our first task is to get the data into that format –

=SUBSTITUTE(table[Field1], “/”,”|”)

Output:  |Controls|Screens|ScreenName

Now, we can apply the pathing functions.

=PATHITEMREVERSE(SUBSTITUTE(table[Field1], “/”,”|”), 1)

Output:  ScreenName

With the path reversed, we chose to select the first node in the path, which was exactly what we were looking for.  If this were a balanced tree, we would not have needed to reverse it and we could have just used PATHITEM() and indicate that we were looking for node 3.  Using SUBSTITUTE() along with PATHITEMREVERSE() can also be used to search in reverse for a general string, as long as you replace what you are looking for with a |.

Implementing Paging in a SQL 2012 Stored Procedure

With SQL Server 2012, the OFFSET clause is introduced. In combination with FETCH, you can construct a query to pull “pages” of information. Of course, this assumes that your underlying dataset is unchanging or you are comfortable with your underlying data set to cause fluctuations in your paged data. See below for one such implementation. Here we are looking for “page 2”, where each page has 3 items on it.

/* set up a test table */
	declare @temp table(id int not null)
	insert into @temp(id)
	values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

/* set your params */
	declare @page_number int = 2
	declare @per_page int = 3

/* determine the offset */
	declare @offset int = (@page_number * @per_page) - @per_page

/* select the page of information */
	select
		t.id
	from @temp t
	order by
		t.id
	offset @offset rows
	fetch next @per_page rows only

Changing the page # or the # of items per page will change your results, and those can be parameters for your stored procedure. Ensure that your data set is unchanging, or that you are okay with fluctuations in what data is on your pages!

SQL 2012 Logging Enhancement – Failed to open the explicitly specified database

There are times when an error will present itself in the SQL Logs and it is difficult to determine what is taking place.  This is especially true when dealing with many instances, many databases, and some of them talking to each other.   For at least one error, the message is now quite a bit clearer.  Below is what you will find in the SQL Logs for Error: 18456, Severity: 14, State: 38.

SQL Server 2008 R2:

Message

Login failed for user  ‘<LoginName>’. Reason: Failed to open the explicitly specified database. [CLIENT: <ip address>]

SQL Server 2012:

Message

Login failed for user ‘<LoginName>’. Reason: Failed to open the explicitly specified database ‘<DatabaseName>’. [CLIENT: <ip address>]

Now you can actually tell from the log what database the attempt was for!

Reloading Agent Settings in SQL Agent Logs

Update:  This issue is resolved as of SQL Server 2012 SP1, which can be downloaded here.

After upgrading to SQL Server 2012, you may notice that your SQL Agent Log is sprinkled with “Reloading agent settings” every 90 seconds.  This does not appear to be harmful as this is just the automatic registry refresh.  However, this can be painful if you have set up log monitoring.  Until this is resolved, the workaround as detailed on Connect is to update your registry to disable the auto refresh.  The downside here is that if you change any of the default agent settings, you may need to restart agent for them to take effect.

Go to registry on your SQL Server machine and navigate to corresponding SQL Instance registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL11.YOUR_INSTANCE_NAME\SQLServerAgent
Change registry settings called “AutoRegistryRefresh” from 1 to 0.