Querying fact tables for lower-level members (SAP BPC - MS version)

Top-level dimension members aren't represented at all in BPC fact tables (SQL side), so if you need to find records that belong to a top-level member, you either need to manually look up all of the lower-level members, or have a query do it for you. Here's one I put together using a basic recursive CTE that will give you all records that contain lower-level members, given a higher-level member:

WITH a
AS (
 SELECT *
 FROM mbrDataSrc_F
 WHERE [ID] = 'Top_Level_Datasource_Name' --top parent
         
 UNION ALL
         
 SELECT b.*
 FROM mbrDataSrc_F b
  JOIN a
   ON b.PARENTH1 = a.[ID]
        )
SELECT *
FROM tblFactForecast
WHERE DataSrc_F IN (
 SELECT [ID]
 FROM a
)

If you're not familiar with them, the SQL after the UNION is the recursive part (some more info on recursive Common Table Expressions). I use them a lot in place of derived tables for their slightly cleaner syntax, but don't usually have too much of a need for recursion.

BTW, I don't believe in SELECT *, except for ad-hoc queries, which this one was for me. There is an interesting discussion about it over at StackOverflow.com - basically, minimal performance gains in most situations, but more clean and safer to select the actual columns.

SQL job retry (with additional logic) on fail

Here's some code I put together to get a SQL Server 2005 job to retry on fail. I spent a while looking for how to just have the job scheduler retry on fail, and found nothing. Of course, right after I coded and tested this, I found out a way to easily retry individual steps on fail, which is probably the way you want to do it 95%+ of the time :-), but just in case someone has the need to retry a job programatically and insert additional logic, or have more control over the process, or just wants to see how it could otherwise be done (ok, maybe at this point I'm just trying to justify wasting 20 minutes of my life coding this manually...), here's the code:

USE msdb

IF(
SELECT COUNT(*)
FROM sysjobhistory
WHERE
-- status 0 = error
run_status = 0
-- isolate the step we're looking for
AND step_name = '(job outcome)'
-- job unique ID (find this out by going to the job properties, and selecting "Script")
AND job_id = '48F4EE80-62C7-4DFF-8F6F-D009F616DA07'
-- today's date in the same style as the job history table format
AND run_date = CONVERT(VARCHAR(8), GETDATE(), 112)
) > 0
BEGIN
-- insert additional logic here

EXEC sp_start_job @job_name='[JOB YOU WANT TO RETRY]'
END


And here's the easy way:
Right-click on the job, choose "Properties". Click on "Steps", and select the step you want to retry. Click on "Advanced", and set the "Retry attempts" and "Retry interval". Repeat for additional steps. Done. D'oh!

SQL Server 2000 not deleting backups

I'm sure there could be a lot more reasons for backup deletions failing than this, but detaching a database that was set to "offline" fixed the problem for me. No other error messages I could find - luckily I realized the problem started around the time we took that db offline, so it didn't take too long to troubleshoot.

sp_MSForEachTable

I had to improve performance on a just-restored database, and remembered of one of my favorite undocumented SQL Server commands, sp_MSForEachTable. In the below example, the "?" is the variable for where the table name should go in the statement you want to execute:

EXEC sp_MSForEachTable "UPDATE STATISTICS ?"

Another quick example - to print out all the table names in a db, just run:

EXEC sp_MSForEachTable "PRINT '?'"

FYI, there's also sp_MSForEachDB that lets you do the same operation on all databases on the server. Learning this alone was worth the price of Brian Knight's "Admin 911: SQL Server 2000" book.

Working with SQL Server in NetBeans 6.1

The JDBC-ODBC bridge wasn't recognizing any of the primary keys I had set up in SQL Server. After lots of head banging, here's the solution I came up with:

1. Download the appropriate driver. SQL Server 2000 requires the SQL Server 2000 JDBC driver (http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en).
SQL Server 2005 download: (http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en)
3. After installing, right-click on "Libraries" in your project, and choose "Add Library...". Next, give it a name (i.e. SQLServer2000), and select "Class Libraries".
4. On the next screen, find the JAR files (should be in C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\), and add them under the "Classpath" tab. It will now be available under "Libraries" for future projects.
5. You can now create a connection to a specific database under the "Services" tab (next to "Projects" and "Files" in the top left of the screen). Select "Microsoft Sql Server 2000 (Microsoft Driver)" and format the "Database URL" like this:

jdbc:microsoft:sqlserver//YOURSERVER:1433;DatabaseName=YOURDATABASE

1433 is the default port, though your DBA may have changed it.

I posted a simpler version of this on the NetBeans.org FAQ page - they had the following title with no content on the answer page:

"Cannot Select Tables From MsSql 2000 Because It Says No Primary Key Next To Each Table But The Tables DO Have A Primary Key. What Do I Do?"

Looks like somebody was really frustrated, and posted a question in the hopes that somebody would answer it :-) Of course I was really frustrated seeing a question with no answer, so I added one after I figured it out myself.