BI-tch

SSRS: Disappearing MDX Queries

2011-01-12T11:14:00.008+11:00

The Reporting Services bug which removes the MDX you've previously typed into the query designer is not a new one. It has been well documented however for the purposes of clarity, I'll review it here too.

You have a SQL 2005 report which uses an SSAS data source. Rather than using the MDX designer, you write your own MDX because you need to do more than the weak-arse SSRS MDX designer can do. All works well you save, deploy, close, whatever. Some time later you come back to it to make a change. You open your Visual Studio project and when you click on the dataset the query is no longer there - you are presented with the blank MDX designer pane as if your query never existed.

It's worth noting that, your query isn't actually gone at this point. Just don't hit SAVE!
If you close the report, right-click it in the Solution and select View Code. Do a search for a tag. You should see your MDX still there.

The common answer directs you to the knowledge base and a particular hotfix which was included in SP1. However, as an SP3 user, I'm currently experiencing the same problem and installing the hotfix solved nothing.

I believe this knowledge base item is no longer relevant because when I look at my report's code-behind by selecting View Code, there is no tag which that hotfix presumes exists.

Googling the issue has also found suggestions like updating your version of SQL Express to the latest service pack. Well, I don't have SQL Express installed at all so it ain't that.

I have had the problem before but I couldn't find a reasonable solution then, as now, either. What did I do to fix it before, you ask? The answer is unfortunately quite painful. I reinstalled BIDS.

Loathe to go through that pain again, I started thinking about what changed since it stopped working this time. Well, loads obviously. But the most obvious change? Installing Visual Studio 2010 Professional.

So here I am installing Visual Studio 2010 SP1 Beta. Yep, beta. That's how desperado I am.

Did it work? I'll tell you as soon as it finishes installing :)

UPDATE: Can't remember what the outcome of this was because at some point I uninstalled VS2010 for some unrelated reason. Having just reinstalled it on Friday, I find that the problem is reoccuring. SP1 Beta is installed however obviously there's no fix there either. Have logged Connect Feedback.

SQL: Converting Column Datatype from DateTime to Uniqueidentifier

2010-10-14T15:48:00.003+11:00

Another day for weird annoying things.

I had a table on which I'd inadvertantly set a columns datatype to datetime when it should have been uniqueidentifier. The table was empty.

Resetting the datatype via script won't work due as SQL knows it can't implicitly convert the (nonexistant) data. So I tried using the GUI to change the datatype and I got incompatible operand errors. Tried doing an ALTER TABLE command and got the same error. Finally I tricked the little bugger by converting it to varchar first then to uniqueidentifier. Boom! Done. Sorted.

SSIS Doesn't Play Nice with Special Characters

2010-10-14T13:17:00.008+11:00

Oh SSIS you cantankerous old thing, you! I spent an hour today adding cleansing tasks into several data flows to replace special characters with other values ie "" to "-" and "[" to "(" etc. Only to have the content of those dataflows disappear when I closed then reopened the saved package. When I say disappear, I mean empty. Completely. Devoid of all steps. An empty shell.

Only dataflows which had the new special character cleansing step were empty - the rest were fine. This placed the blame squarely on the new steps. I should have twigged when I attempted to copy the step from one dataflow to another and got an error - something about HEX values being invalid. Stupid me, blindly continued creating them manually in each dataflow. Once done, I saved and closed. Then when I reopened, those dataflows were completely empty.

Fortunately being a concientous backer-upper-er-er, I had the previous version stored elsewhere. Sans special character cleansing of course.

So first, why do I need to do cleansing at all? These characters have passed through SSIS into the staging database with no problems. They have also passed through SSIS into the datamart database with no hiccups. Well the problem comes when I process the dimensions which use the datamart tables as their source. Then I get errors.

So, for whatever reason, cleansing ahoy!
But SSIS won't do it, as proven by my frustrating waste of an hour.
I tried using them directly in my source query with the same result. So I created udf_CleanseSpecialCharacters to handle it.

/**********************************************************************************************************************************************
HEADER
/***********************************************************************************************************************************************
FN Name: dbo.udf_CleanseSpecialCharacters
Author: Kristen Hodges
Date: 14 October 2010

-------------------------------------------------------------------------------------------------------------------------------------------
Purpose:
--------------------------------------------------------------------------------------------------------------------------------------------
Return a given string, cleansed of special characters

--------------------------------------------------------------------------------------------------------------------------------------------
OVERVIEW
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
NOTES
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
ASSUMPTIONS AND BUSINESS RULES
--------------------------------------------------------------------------------------------------------------------------------------------
It may be desirable in certain contexts to retain some characters whereas in others it is not
eg comma stored in table but not as a ssas dimension member unique key
The @iOptional_CharactersToCleanseList is an optional parameter which can be used to pass in a string of characters which should be stripped and
any others will be retained

The @iStripCharacters is a true/false flag which will strip the characters if true
or if false, the value will be replaced with particular values eg [ becomes -

--------------------------------------------------------------------------------------------------------------------------------------------
USAGE
--------------------------------------------------------------------------------------------------------------------------------------------
Called by:

Sample USage:
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,0,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,'&')

--------------------------------------------------------------------------------------------------------------------------------------------
CHANGE LOG
--------------------------------------------------------------------------------------------------------------------------------------------
AUTHOR DATE CHANGE
KHODGES 2010-10-14 Created

************************************************************************************************************************************************/

************************************************************************/

ALTER FUNCTION [dbo].[udf_CleanseSpecialCharacters](
@iStringToCleanse varchar(500)
,@iStripCharacters bit
,@iStripAllSpaces bit
,@iOptional_CharactersToCleanseList varchar(50)
)
RETURNS varchar(500) AS BEGIN

DECLARE @return varchar(500), @CurrentChar char(1), @CurrentReplacement varchar(10), @posn int

SET @return = @iStringToCleanse

set @CurrentChar = CHAR(5) --
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(58) -- :
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(59) -- ;
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(91) -- [
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(93) -- ]
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(37) -- %
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(38) -- &
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' and '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(124)-- |

if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(44) -- ,
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = SPACE(1)
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(36) -- $
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(64) -- @
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' at '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(46) -- .
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' dot '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

IF @iStripAllSpaces = 1 SET @return = REPLACE(@return,' ','')

RETURN @return
END

MDX: Calculated Measure: Opening & Closing Balance

2010-07-22T14:28:00.016+10:00

The scenario is this:

Opening Balance =

my previous closing balance

Closing Balance =

Opening Balance
plus new widgets received today
less widgets completed today

Note how I said that my opening balance equals my previous closing balance? In practical terms, that means:

count of all new widgets received up to today
less the count of all widgets completed up to today

In my case a widget refers to a "request", aka an application from a customer for a new credit card. I want to measure how many requests were worked on by my staff during a period of time. That "period of time" gets passed in from a report parameter or query and should always use the 'ReceivedDate' as it's point of reference because we don't want our Opening Balances changing over time (duh!).

My fact table contains a Received Date, a Completed Date and an ItemCount column (there are more but that's all I need for my calculated members). We have an ItemCount column because one request can be for multiple cards. If there's only one card in the request, it contains "1".

There are a multitude of ways to achieve this and here is just one...

First I want to count how many requests were completed in the period. I know what my Received Date member is because the report gives it to me... but how do I know which member of the Completed Date dimension to use? Obviously there is only one physical Date dimension but my cube refers to it multiple times - two of which are Received Date and Completed Date.

I have a problem in that my completed date and my received date are in two different dimensions in my cube so I can't just use any of the MDX relative functions such as ancestor/cousin, sibling, parallelperiod because they aren't relatives at all! What they do have in common though is they share the same member key.

I know the member key from the Received Date so I can use that to find my Completed Date member. But wait, I have multiple levels in my hierarchy; so I need to consider that too.

CREATE MEMBER CURRENTCUBE.[MEASURES].CompletedInPeriod AS

(STRTOMEMBER("[CompletedDate].[Fiscal-Year-Month-Day].[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Level.Name + "].&[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Member_Key + "]")
,Measures.ItemCount),
VISIBLE = 1 ;

The above code shows a simple way to get the answer. Manufacture a string to get the completed date based on the received date and then get the item count for that completed date. It may not be elegant, but it works!

Now I want to get my opening balance. Easy.

CREATE MEMBER CURRENTCUBE.[MEASURES].OpeningBalance AS

IIF([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.FirstSibling IS [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Parent, Measures.OpeningBalance)
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.PrevMember, Measures.ClosingBalance)
),
VISIBLE=1;

What this says is, if the current member is equal to the first member (at this level) then get the Opening Balance of my parent. Otherwise get the closing balance of the previous member. In practice that means, if I ask for June 1st, 2010 (the first day of the fiscal year) get the Opening Balance for June 2010. If I ask for June 2010 (the first month of the fiscal year) get the Opening Balance for 2010. If I ask for June 12th, 2010 then get me the Closing Balance for June 11th. If I ask for July 2010, then get me the Closing Balance for June 2010.

And yes, it's recursive ie it's constantly looping back on itself. Don't be scared. SSAS is pretty ok with it so you should be too!

Notice how the last portion refers to the Closing Balance of the previous member? Well I guess that means we need a Closing Balance calculation too! This ones' easy! Remember that the calculation is opening balance + new - completed. And if you think about that a bit you'll see that the opening balance is equal to the previous closing balance. So...

CREATE MEMBER CURRENTCUBE.[MEASURES].ClosingBalance AS
([ReceivedDate].[Fiscal-Year-Month-Day].PrevMember,Measures.ClosingBalance)
+ ([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember,Measures.ItemCount)
- Measures.CompletedInPeriod,
VISIBLE = 1 ;

It says, that the Closing Balance equals the Closing Balance of the previous member plus the ItemCount for this period less the ItemCount completed during this period (remember our first calculated member was "CompletedInPeriod". For example, If I ask for the closing balance for June 12th 2010, it will get the Closing Balance for June 11th 2010 ie my opening balance, then add the ItemCount for June 12 and minus the ItemCount completed on June 12.

And that's it. Process the cube and away you go.

Next up, I'm going to use this same calculated measure on different hierarchies in the date dimension. For example, I have a Fiscal-Year-Month-Day, a Fiscal-Year-Month, a Calendar-Year-Month-Day and a Calendar-Year-Month... and many more when you look at Quarters, weeks and semesters! But that's for another day...

Visual Studio 2010 Trial: SQL Project Limitation: Refactor/Rename

2010-05-20T15:13:00.010+10:00

Please, somebody correct me if I'm wrong...

** UPDATED **

Yep I'm an idiot. Somehow I installed Professional edition not Ultimate. And professional does not support schema compare or refactoring. Duh!

The Visual Studio 2010 trial claims to be functionally unlimited - just time-limited (90 days). BUT - from what I can tell, object name refactoring (renaming) is unavailable.

According to MSDN it can be done by "right-click the object in Schema view, point to Refactor, and then click Rename'.... or from the Data menu, point to Refactor etc...

However, there is no Refactor option in either location.

MSDN does state that if there are build errors, the menu option is not available. I double-check that, and build successfully it did.

Stumped!

Visual Studio 2010: Enabling Older DotNet Frameworks (or how I learnt to love multi-targeting)

2010-04-27T11:29:00.007+10:00

I've been told that SQL 2005 doesn't support CLR functions created with DotNet Framework 4.0 - I haven't tested it cos When attempting to create a new SQL 2005 CLR project using Visual Studio 2010, I found myself at a loss to work out how to create a .NET Framework 3.0 project ie using the new and improved multi-targeting feature.

The only option available to me in the 'New Project' dialog box was a 4.0 Framework. Plus a handy 'More Frameworks" option which took me to the download page for various dotnet frameworks. I already had 3.0, 2.0 and 1.1 installed on the machine so the need to redownload and reinstall them seemed irritatingly redundant.

So after much googling, I found the solution.

You have to install .NET Framework 3.5.

Ummm... that's it.

Close VS, install 3.5 framework, open VS and wacko!

Contrary to the installer instructions, I did not have to reboot.

SSAS: Vague Processing Errors About Connection Strings Are Not My Friend

2010-01-15T15:26:00.005+11:00

Oh Visual Studio, you crazy cat, you! I've just spent the past hour or so going stark raving mad. courtesy of a DBA who's less than 'expert' and a teeny little Visual Studio property I neglected to set properly.

When deploying my Analysis Services database, the processing kept failing with a vague error along the lines of "connection string is invalid" and "login timeout". Now, I know there's nothing wrong with the connection string cos it works fine in VS, right? Wrong!

When deploying a cube to the Integration environment, my DBA told me that I couldn't just deploy it (outrageous suggestion), no I had to give them the project, they would deploy it, then I had to reimport it from the new environment into Visual Studio. WTF? Ok, I'll play. I'm new here after all so who am I to question their processes?

So when I later needed to redeploy some changes I'd since made, the DBA told me I had to do the same again. At this juncture, I put up a big red stop sign and start asking some questions. Turns out they do that so the Impersonation works correctly. Why wasn't it working as expected is the real question they neglected to ask and solve. Instead of fixing the problem, the treated a short-term workaround as the solution, and stopped there, purely because they didn't know any better.

Having rarely used impersonation before I didn't have an immediate answer. I've always just use the service account as my credentials because I'm lazy and frankly I can't really see why, on a DEV box mind you, you'd bother? Whatever, I don't care so much, if that's how they want to do it I'm easy.

So the problem is that after redeploying the project, it wouldn't reprocess. As I mentioned earlier, I was getting these vague errors about logins and connections. Turns out the solution was really freakin' simple.

See that little "Remove Passwords" property? Yeah that's the sucker. That cheeky tyke was set to TRUE. Set it to false and whaddya know? Everything works a treat.

Aaaah... Friday 4pm... and time for a brewskie.

TSQL: Programmatically Create DDL scripts for Foreign Keys

2009-10-12T15:58:00.006+11:00

I'll admit it, I'm lazy... I hate typing exercises. I don't need the practice and I'm sure the person paying my somewhat exhorbitant fee doesn't want to fork out for something so mundane either. That means I'm always on the hunt for a ~~shortcut or two~~, I mean, process improvement.

These two snippets will generate the DDL to create all the foreign keys in the database.
The first will check if it exists first, the second drops it first. Use whichever suits.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.name + ' WITH CHECK '
+ char(13) + char(9) + 'ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13) + 'END'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

And the second one (ie does a drop first)...

SELECT DISTINCT 'IF EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.Name + ' DROP CONSTRAINT ' + fk.Name
+ char(13) + 'END'
+ char(13) + 'ALTER TABLE ' + s.name + '.' + t.name
+ char(13) + char(9) + 'WITH CHECK ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

Integration Services Gotcha #6631984: The Table Variable Data Flow Affair

2009-10-12T10:33:00.004+11:00

I encountered a teeny little SSIS glitch today that completely did my head in for a good 30 minutes. Ok, I'll admit it, more like an hour.

In my data flow, I had an OLEDB Source which declared and populated a table variable then returned some data, which included a join on my table variable.

The source query worked perfectly, returning plenty of data when tested through SSMS. As soon as I ran the data flow, it would be successful but zero data.

That freakin' table variable was messin' with me. Took it out, replaced it with a subquery and whammo! Data.

Oh SSIS, you pernickety old man, you!

SQL 2008: Using MERGE to do a Type 2 Change

2009-07-20T08:51:00.014+10:00

I'm going to do a brief overview of the SQL 2008 MERGE statement. Primarily for my own purposes - being to document the usage for type 2 changes so I don't forget it.

First let's begin with the basic type 1 usage.

All fairly self-explanatory. We have a source and a target. We join the two tables, identify new records, changed records and deleted records. And deal with them appropriately.

So let's move onto the type 2 changes. Here we need to:

Insert New Records
Disable changed records
Add a new record for the changed records

That's 2 inserts and an update. The MERGE won't allow that so we have to nest our merge inside an insert. Luckily this works because MERGE allows output!

Ignore the outer INSERT for the moment. We have a MERGE statement which is very similar to our type 1 MERGE. Compare the Source and Target, INSERT new records as required. The difference being that when the matched record contains a change, instead of updating the changed field, we set the CurrentFlag and the EndDate so as to disable the record.

Of course that means we still don't have a record which contains our updated values. So, now lets turn our attention to the OUTPUT clause.

This will be familiar to anyone who used it in SQL 2005 in much the same context - to identify rows which have been modified/added/deleted. Really the origin of MERGE seems to be strongly rooted in the SQL 2005 OUTPUT clause. On the upside, unlike the SQL 2005 version and UPDATE is an UPDATE and not a DELETE and an INSERT. Small but handy! Anyway, where were we? Let's return the columns we need to create a new record and a special $Action column - this tells us what actually happened ie UPDATE, INSERT or DELETE. We need this because the OUTPUT will return everything that occured in the MERGE.

Now we have a set of rows that have been actioned by the MERGE. Still, we haven't actually done anything with them.

To do that, we enclose the entire MERGE statement, including it's OUTPUT clause of course, into brackets so we can make it the FROM clause for an INSERT. Our OUTPUT clause is return the columns we need for the INSERT remember? So now we just add our INSERT INTO at the front, add an alias to the closed bracket of the FROM clause AND, drumroll... whack a WHERE clause on the end. This where clause just filters the OUTPUT from the MERGE so we only insert new records for those that were updated, ignoring those that may have been inserted etc in the MERGE.

Easy!

-------------------------------------------------------------------------------------------------

For the purposes of copy/paste-ability, here is a text version of the TSQL. Images have been used above for improved readability.

--A simple type 1 change
MERGE INTO DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED BY TARGET THEN --ie new
INSERT (CustomerId,CustomerName,IsActive) VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
)
WHEN MATCHED AND c.CustomerName <> stg.CustomerName THEN --ie changed
UPDATE SET c.CustomerName = stg.CustomerName
WHEN NOT MATCHED BY SOURCE THEN --ie deleted
DELETE

--a type 2 change
INSERT INTO dbo.DimCustomer
SELECT CustomerId, CustomerName, IsActive, IsCurrent, Eff_Date, End_Date
FROM
( MERGE DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED THEN INSERT VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
,1 --> IsCurrent
,GETDATE() --> Eff_Date
,NULL --> End_Date
)
WHEN MATCHED AND c.IsCurrent = 1 AND (c.CustomerName <> stg.CustomerName) THEN UPDATE SET
c.IsCurrent = 0
,c.End_date = GETDATE()
OUTPUT $Action as RowAction
,stg.CustomerId
,stg.CustomerName
,1 AS IsCurrent
,GETDATE() as Eff_Date
,NULL as End_Date
) m
WHERE m.RowAction = 'UPDATE'

SharePoint Magazine - Part 4 available now

2009-07-17T10:47:00.000+10:00

My next article for SharePoint Magazine is available now.

Dissecting Dashboards (Part 4 of 6)

Sharepoint Saturday

2009-06-18T12:15:00.000+10:00

http://www.sharepointsaturday.org/sydney/default.aspx

Can Dynamics CPM pick up where PerformancePoint left off?

2009-06-18T10:19:00.003+10:00

Can Dynamics CPM pick up where PerformancePoint left off? And how does all of this fit in with SSRS Report Builder and Project Gemini, a key part of Microsoft's self-service BI drive? When you say things like "Microsoft Forecaster is a budgeting and planning application that allows companies to build budgets based on specific objectives", one has to wonder!

And "Enterprise Reporting is designed for sophisticated group reporting and consolidation needs, and includes advanced multi-dimensional consolidations, eliminations and multicurrency capabilities. Enterprise Reporting’s planning environment is easily customizable to fit diverse budgeting and forecasting needs across all industries and organizations. Enterprise Reporting provides strong analytical modules to make it extremely easy to create new ad-hoc reports on the fly"

Seriously? That's quite a bundle of tasks. Enquiring minds want to know more!

PerformancePoint Planning: Bug in the bsp_DI_ValidateLabelTable Proc

2009-06-11T18:12:00.003+10:00

Somewhat irrelevant due to shut down of PerformancePoint Planning but it's an issue I encountered so I will record it for posterity and future generations (*snigger*).

There is a bug in the bsp_DI_ValidateLabelTable proc which causes it to fail when you are using a specific hierarchy in the model rather than the default 'all members' set. The problem arises when you pass in an incorrect model name.

The proc does not validate that the model name is actually valid before continuing (naughty naughty) which means you can actually cause errors if it just so happens that the value you pass in is a valid object - just not the name of the model itself. Let me show you want I mean.

First let's just mock up some object labels (please note these are NOT real. I would never be so generic but for the purpose of examination...).

Application: PPS
RootModelSite: PPS_Planning
Model: PPS

When calling PPSCMD you often refer to PPS:PPS_Planning.

When executing the bsp_DI_ValidateLabelTable proc, it asks for modelname as a parameter. In my example, I will have a scope in FK_relationships for PPS:PPS_Planning to identify the modelsite but that's not the one I need. Nope. I need PPS_Planning:PPS, which identifies the model.

But I'm half asleep and I've typed PPS:PPS_Planning in the past 24 hours so many times that I just don't think. Uh oh, all of a sudden the proc fails - but not after having made a few updates already.

Silly mistake to make, no question. Still, it does seem a bit dodgy to take an string input from a user, which is also a unique label used to identify a specific record, and not validate that it actually exists before making updates using that value!

SSIS: Write to the Output Window from a Script Task

2009-06-11T17:29:00.002+10:00

Want to write to the Output window during execution of a script task?

DTS.Events.FireInformation(1,"YOUR TITLE","Your Message","",0, False)

Really handy for debugging!

SSIS: Error when executing a data flow using the OLEDB SSAS 9.0 Provider

2009-06-11T15:42:00.003+10:00

I found an interesting problem today.

I had an SSIS package which contained, amongst other things, a data flow which had 3 OLEDB data flow sources which later get merged into one for insert into the destination. Each data source points to the same OLEDB connection which uses the Analysis Services 9.0 provider to connect to a mining structure. Each data source used the SQL Command access mode and a simple DMX statement in the command text.

When previewing the results of each query all worked fine and results were as expected. When it came to execute time on the server I kept receiving the most frustratingly vague error.

"Error code = 0x80040E05, External Code = 0x00000000:."

Mmmm. Yes... I know it well...

So I presumed it was somehow a permissions issue as the previews under my profile all looked fine. Only, the perms were the same. I made sure the connection property "RetainSameConnection" was set to FALSE. No difference.

So then I deleted 2 of the data flow sources and tried again. This time success. Somehow the connections were locking each other out. I set the RetainSameConnection to TRUE. This time even the previews failed.

So my workaround? I used a ForEach Loop to do them one at a time using the "SQL Command From Variable" data access mode on the data flow source.

Problem solved!

SQL SSIS: Vote for data viewer suggestion

2009-06-10T17:24:00.003+10:00

Vote for a feature request on Microsoft Connect for greater flexibility over when and where an SSIS data viewer is shown.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=465835

SQL: Retrieving the Metadata for a Query

2009-06-09T16:50:00.004+10:00

Sometimes you need to know what the metadata for a query looks like before runtime. This is particularly relevant with SSIS.

I use it rarely but every time I do have a need for it, I can never remember the name of the option and it takes me an hour at least to track it down again. So I'm hereby cementing it in stone so I don't forget it again!

SET FMTONLY ON

That's the one. It will return the columns and data types to the query engine. Note: it does not actually run the query or return any results. It's purely to return metadata about the query.

It's surprisingly handy from time to time.

SharePoint Magazine - Part 3 Out Now!

2009-06-03T12:30:00.003+10:00

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/kpis-–-who-am-i-and-where-do-i-come-from-part-3-of-6

SQL 2005: Scalar Resultsets and SSIS Variables

2009-06-03T11:21:00.005+10:00

Found an interesting SSIS tidbit today which I've amazingly never encountered before. Not quite sure how I've never managed to hit this problem before - it seems surprising to me but there you go, I guess it happens from time to time.

In an ExecuteSQLTask, I was returning a scalar value to a variable. The value returned was a bigint. My SSIS variable was a bigint. The step failed with an invalid type error. What the? Changed the type to an object. Success this time but not much help to me down the control flow because I can't read it without a ForEach - which I really don't wanna do for a scalar value. I mean that would just be silly.

According to Technet "With an OLE DB or Excel connection manager, the connection manager explicitly converts values of the following types, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES, to strings". Seems crazy to me! I changed the SSIS variable to a string and what do you know, success.

Nuts!

SQL 2008 SP1 - a must have!

2009-04-24T17:56:00.002+10:00

Some time ago I installed SQL 2008 on a VPC which was also running MOSS and PerformancePoint. Soon enough the VPC started to run out of memory. I tore my hair out for a few days because nothing I did made a good dent in the problem.

Till I found the silver bullet.

Consistently SSAS was high on the list of memory-hungry services - plateauing around the 500Mb mark. Yeah, that's a lot. With only a couple of cubes and no queries running it did seem to be hugely excessive.

What to do? Install SP1.

That's it.

Usage dropped down to somewhere around 30-80Mb. Much more memory friendly!

Interestingly, of the many bug fixes included, in SP1 (full list), a memory leak in SSAS is not one of them!

TSQL: Truncating Datetime

2009-04-09T12:29:00.004+10:00

It crops up all the time. You have a datetime column. In your select, you don't care about the time, you only care about the date because you want to group all records for a particular date, for example. So what's the quickest way to go about it?

Well let's start with the options...

CONVERT
FLOOR
DATEADD/DATEDIFF

If you are using SQL 2008, you could add another option and CAST to DATE. Perfect if the recipient of the data will accept it. I'm gonna assume however, as is most likely at this point in time, that you aren't using SQL 2008 yet as that does seem to still be the case in most organisations. I'm also going to assume that you need to return a datetime.

So, to option 1: CONVERT.

A popular choice because it's easily readable. Let me give you an example.

CAST((CONVERT(varchar(11),getdate(),120)) as DATETIME)

This will return 2009-04-09 00:00:00.000

Option 2: FLOOR

CAST(FLOOR(CAST(Getdate() as FLOAT)) as DATETIME)

Returns 2009-04-09 00:00:00.000

Obviously faster than convert because we aren't working with any strings.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)

Returns 2009-04-09 00:00:00.000 (shock horror!)

Guess what? This version is even faster again because we don't have any conversion to do at all.

To what degree are these options faster?

Well, a quick test by plonking a million records into a temp table which contains only a datetime column showed my execution times to be fairly similar but my CPU times were increasingly quicker.

CONVERT - 1591

FLOOR - 434

DATEADD - 382

The convert is horrible obviously and should never be considered even at the expense of readability. Clearly there's not a vast difference between FLOOR and DATEADD but I guess I like the DATEADD method for it's lack of conversion as well as it's performance gain, however small. It just seems cleaner to me.

SQL 2005 SSIS: Unpivot Transformation - Destination Column

2009-02-06T15:35:00.003+11:00

Came across an interesting little bug today in SSIS...

When using the Unpivot transformation, if you attempt to see the results of the Destination Column in a data viewer well, you can't... the column just isn't there. However, when the output is sent to an actual data destination it is created as expected. You just have no way of knowing that until you actually look at the destination itself.

Wasted quite a bit of time on that one!

No more PPS Planning...

2009-01-29T10:19:00.003+11:00

The news, as I'm sure everyone has now heard, is that PerformancePoint Planning is being retired. See the official press release at:

http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx

PerformancePoint Monitoring will be bundled into SharePoint as part of Office 14 - and be known as PerformancePoint Services for SharePoint. Wacko.

PerformancePoint Planning: Data Integration

2009-01-23T10:06:00.005+11:00

Quick Overview of the steps required in an ETL package to load a PerformancePoint Planning application:

Sync Dimensions from App to Staging (call ppscmd)
Sync Models from App to Staging (call ppscmd)
Stage Dimensions into Label table (data flow)
Load Dimensions (call ppscmd)
Stage Hierarchies into Label table (data flow)
Load Dimensions (call ppscmd)
Reindex Dimension tables (sql command)
Stage Fact into Label table (data flow)
Load Fact (call ppscmd)
Reindex MG tables
Deploy Model (call ppscmd)

This presumes you are using the 'Label' table approach.

SQL 2005 SP3 has landed

2008-12-22T11:02:00.003+11:00

SP3 for SQL 2005 is now available for download.

Included are a number of Bug Fixes , performance enhancements for using SSRS in SharePoint Integration mode, improved rendering of PDF fonts in SSRS, support for SQL 2008 Dbs with SQL 2005 Notification Services and support for Teradata Dbs as a data source for Report Models.

This Service Pack is cumulative and can be run on any version of SQL 2005.

PPS-P: Where, oh where, does my data go?

2008-12-17T15:22:00.016+11:00

A user enters a value into a PerformancePoint Planning form... so what happens next? And why are we the least bit interested?

Firstly, to answer the why do we care question, ask yourself this...

When do your extracts from PPS run (presuming you run it more regularly than say once overnight)?

Do users eg approvers expect to see updates immediately - particularly at certain times of the cycle ie month end?

What data do reviewers, approvers and report consumers expect to see? Approved? Currency Converted?

So we do care after all. So now to what actually happens. In fact, there are a few things that can happen. Let's examine each separately.

(1) The user selects the Save Privately action

The data is stored on the local file system - known as the Offline Cache. This location is usually C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache
In the Offline Cache, the Catalog.xml file stores a list of all assignments stored locally
In the Offline Cache, each assignment is stored in a .dat file. The dat file an xml file which contains binaries.
Data is NOT sent to the SQL server or the cube
The status of the assignment is still 'started'

(2) The user selects the Submit Draft action

The data is stored in the SQL table called MG_ModelName_Default_Partition
The resulting values from Definition Rules are stored in the MG table also however they're linked to the 'No Assignment' assigment
Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
The status of the assignment is 'Partial'

(3) The user selects the Submit Final action.

The data is stored in the SQL table called MG_ModelName_Default_Partition
The resulting values from Definition Rules are stored in the MG table also however they are linked to the 'No Assignment' assigment
Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
The status of the assignment is 'Submitted'

(4) A data movement job occurs

The data is copied from the source SQL table called MG_ModelName_Default_Partition to the destination table called MG_ModelName_Default_Partition
Data is aggregated when the scope of a dimension (in the data association) has been set to "All" on the source model.
All data is copied: Partial, Submitted, Approved

Then of course the question is, how long does all this take? Sadly, there's no simple answer to that question, as so aptly discussed by Peter Ebley. To paraphrase Peter, there are a few steps that occur:

Send it to the server (Status = Pending)
Depends on the size of the submission (How many rows? Is the workbook attached? How many dimensions?) and network bandwidth.
Process the Queue (Status = WaitProcess)
Depends on the Planning Process Poll Interval (recommendation is 1 minute) and how many items are in the queue and of course, how long it takes to validate the submission.
Cube Processes
Depends on the OLAP Cube Refresh Interval property in the Workflow tab of PAC.

Weighing in on Project Gemini

2008-12-17T09:26:00.003+11:00

My reception to Project Gemini to date has been lukewarm. Sure, self-service cubes in Excel sounds sexy and looks sexy... but... we don't let our users have free reign with data for a reason. These controls didn't just happen because we want to keep ourselves in a job (I hope!).

I'm gonna take a stab and suggest that every BI pro has walked into a job and found an environment where there are 2000 reports on a single system and some of them consume all the machine resources and take 10 minutes to execute. That sort of malarky. So we did the hard slog, got the BA's back up and whipped everyone back into line. The users were happier because the reports execute quickly and were easy to find, you were happier because there are far fewer reports to maintain, the server team are happier because the box didn't have to be rebooted every day and the BA's are happier because everyone else is happy (one hopes!).

This is why we put controls in place.

So. Doesn't this leave Project Gemini dead in the water in the real world?

Well, I read an interesting perspective from Patrick Husting which made me re-think my thoughts (non-pun intended).

"People are always going to do self service reporting in some way whether we like it or not, no matter how good the underlying data is, so why not do it in a controlled manner where everything is audited and logged and the IT team has full visibility of what is going"

Ok so, to quote from a really crappy movie I saw recently "just because it is, doesn't mean it should be". But I think he might actually be right. To a point.

I really do hate to be a naysayer. And I'm not just saying that. But, I have a big BUT (please no jokes!).

Where is all this magical data that is being analysed coming from in the first place? From systems. Well guess, what, we don't let users have hollus bollus access to our databases to avoid the suituations caused by our predecessors.

Actually that's not my only issue... users do NOT have a big picture view of the world. They don't care what other parts of the business are doing and they are not interested in how data aggregates upwards beyond their little fish pond. They are not interested in whether there is a single source of truth or if their version of the truth is different to someone else's. They are not interested in network bandwidth or performance at other sites.

Nor should they care about this stuff, that's not their job. It's ours.

It's all very well to say "everything is audited and logged" but aren't we supposed to operate preventatively not reactively?

So I have to ask, isn't that what something like ProClarity is for? To allow users to slice and dice data however they choose while ensuring that the structures and models have been put in place to consider the big picture?

You know what, I think I've just talked myself back to my original stance. It's sexy and I like it but I can't envision practical applications in the real world yet.

That said, I'm always happy to be proven wrong.

PerformancePoint Planning - Enable/Disable Spreading

2008-11-18T15:21:00.003+11:00

Contrary to the incorrect instructions on Office Online, to enable and disable spreading on a matrix you must do the following:

From the Advanced group on the PPS ribbon, you select Options and the SpreadingType drop-down allows you to enable/disable spreading.

Using SAP as a Data Source

2008-11-11T10:00:00.005+11:00

I was recently asked a question which I thought might be of value to everyone.

Can you please let me know...
1. Is share Point and Performance Point can be used in SAP BI or is it strictly Integrated with MircoSoft Technologies!!

So my response was...

I’m assuming you mean “can you use SAP as a data source?”

Firstly, SharePoint isn’t a BI technology as such. It’s a collaboration, portal & document management tool. However it does have the ability to create lists and simple KPIs which you could use SAP data as a source for if you wanted to.

That said, I’m sure your SAP operations team would never allow you to link directly to SAP – you would need to extract into a database first.

Secondly, you can certainly use SAP as a data source for both PerformancePoint modules - Planning and Monitoring & Analyzing. However you would never link directly to the system from PerformancePoint. You would almost always extract from your transactional system into a datawarehouse and that would be your data source (there may be exceptions from time to time but generally it's is wise to separate your reporting engine and your transactional engine. There are numerous gains in doing so - security, performance, aggregation, modelling).

The other key consideration is licensing. SAP is pretty strict about licensing when it comes to extracting data so you would need to take that into consideration also.

Hope that answers your question!

PerformancePoint Planning - Impersonate a Contributor during Form Development

2008-11-05T14:55:00.003+11:00

It was be absolutely invaluable to have the ability to impersonate a 'contributor' during form development.

Currently, when developing a form, I have 2 sesssions of Excel open - one as me and one as a contributor. This means I need to create a cycle and an assignment also and republish every time I make a change to the form I'm working on. Merely to validate that I am getting 'yellow cells' as expected (which is by no means a sure thang!).

A feature, similar to Analysis Services where I can impersonate a particular user/role would eliminate the need for all that extra effort - republishing, regenerating etc.

If you too would value this functionality, please vote for it at https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=380070&SiteID=181

PerformancePoint Planning - Object Names

2008-11-05T13:32:00.004+11:00

Just a quick FYI....

In PerformancePoint Planning, objects are named as follows:

Cubes: ApplicationLabel_ModelSiteLabel
Databases: As specified in PAC during create
Application Label must be unique and cannot be changed
When connecting to an application, the Application Name is displayed

Microsoft Azure.... the blue loo

2008-11-04T09:01:00.003+11:00

Microsoft announced it's Azure service last week. In their words Azure is a tool that allows you to "Build new applications in the cloud - or use interoperable services that run on Microsoft infrastructure to extend and enhance your existing applications". From our, the BI developers', perspective Azure is hosting not just data but also Visual Studio solutions ie developing and deployment within the cloud.

Not that it's limited to Microsoft tools and environments of course.

So I immediately joined the CTP for SQL Services. I've downloaded the SQL Data Services SDK and have been placed on a waitlist for access to the cloud.

From what I can tell so far, you can use REST or SOAP for CRUD operations and LINQ for queries. Interestingly, there is no 'schema' as such - new properties can be added to entities as required - no need to add a new 'column'. What that means in practical terms I'm yet to find out. I suppose it will become apparent once I can access the cloud.

More info will follow shortly.

What’s New in SQL 2008 – Part 3 – Table Type

2008-10-28T15:01:00.003+11:00

The new Table type allows you to save a table definition in the database which can be reused later to define table variables, stored procedure parameters and functions. To create a Type, we use the CREATE TYPE clause, for example:

CREATE TYPE dbo.ProductList as TABLE (

    ProductName varchar(200) NULL,

    ProductCategory varchar(200) NOT NULL)

Important Notes:

The metadata is visible using sys.table_types

Variables/parameters cannot be set to NULL – if no table is supplied, an empty table is generated

```
Not affected by transaction rollbacks
```
```
Do not cause recompilations
```

Examples


Create Table-Type Variables	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES ('Grey Slate Paver 10x10','Paver'), ('Blue Slate Paver 10x10','Paver'), ('Heritage Blue 12x6','Brick')
Creating a Stored Procedure with a Table-typed Parameter	CREATE PROC dbo.uspGetOrdersforProduct ( @t as dbo.ProductList READONLY, @StartDate as Date, @EndDate as Date ) AS SELECT o.OrderId, o.OrderDate, o.CustomerId, o.ProductId, o.Amount, o.Qty FROM tOrder as o INNER JOIN tProduct t on t.ProductId = o.ProductId INNER JOIN @t as f on f.ProductCategory = t.ProductCategory and (t.ProductName is NULL or t.ProductName = f.ProductName) WHERE o.OrderDate between @StartDate and @EndDate
Passing Table-parameters into Stored Procedures	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES (NULL,'Paver'), (NULL,'Brick') EXEC dbo.uspGetOrdersforProduct @t

What’s New in SQL 2008 – Part 2 – Data Types

2008-10-28T14:52:00.000+11:00

Date and Time

Data Type	Size (bytes)	Example	Notes	Client Providers
DATE	3	'2009-10-01'		OLEDB: DBTYPE_DBDATE ADO.Net: DateTime ODBC: SQL_DATE
TIME	3-5	'13:12:00.1234567'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIME ADO.Net: TimeSpan ODBC: SQL_TIME
DATETIME2	6-8	'2009-10-01 13:12:00.1234567'	Accurate to 100 nanoseconds Can specify precision ie decimal places eg DATETIME(3)	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP
DATETIMEOFFSET	8-10	'2009-10-01 13:12:00.1234567+02:00'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP

There are a number of new functions which can also be used with these new datatypes including:

SYSDATETIME (returns DATETIME2), SYSUTCDATETIME, SYSDATETIMEOFFSET (includes time zone in return value) – Current Date and Time
SWITCHOFFSET – to change the timezone for a DATETIMEOFFSET value
TODATETIMEOFFSET – convert non-timezone-aware values to time-zone-aware

Other

Data Type

Size (bytes)

Useage

Returns

Notes

HIERARCHYID

VARBINARY
eg 5 bytes for 100k records at 6-Levels

DECLARE @h hierarchyid = hierarchyid::GetRoot()

DECLARE @c hierarchyid

SET @c = @h.GetDescendant(NULL, NULL)
SELECT @c.ToString()

'/1/'

Represents position in a hierarchy

Actually a CLR function with several methods eg

GetDescendant/IsDescendantOf
GetLevel/GetRoot
GetReParentedValue
ToString/Read/Write (CLR)

What's New in SQL 2008 - Part 1 - Functionality

2008-10-28T14:50:00.004+11:00

Function	Usage Example	Notes
Declare & Initialise	DECLARE @iValue as integer = 100;
Compound Assignments	SET @iValue -= 50 UPDATE dimCustomer SET Balance += 200	+= -= *= /= %=
Multi-Row Inserts Using VALUES	INSERT INTO dimProduct (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate) VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00'), ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00'), ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00')	The insert is executed as a single transaction. Interestingly, it can also be used like this: SELECT * FROM (VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00') ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00') ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00') ) as p (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate)
MERGE	MERGE INTO dbo.dimProduct as d USING dbo.dimProductStage as s ON d.ProductKey = s.ProductId WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.ProductCategory = s.ProductCategory WHEN NOT MATCHED THEN INSERT (ProductName,ProductCategory) VALUES (s.ProductName, s.ProductCategory) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.ProductId as DeletedId, inserted.ProductId as InsertedId;
Grouping Sets	SELECT Year(OrderDate) as OrderYear, SalespersonId, OrderType, count(OrderId) as OrderCount, sum(Amount) as OrderAmount FROM factOrders GROUP BY GROUPING SETS ( (OrderType, Year(OrderDate)), (SalespersonId,OrderType, Year(OrderDate)), (SalespersonId, Year(OrderDate)), (Year(OrderDate)), ());	() empty brackets represents an 'ALL' grouping
Filtered Indexes	CREATE NONCLUSTERED INDEX idxOrders_Product ON factOrders(OrderId,ProductId) WHERE CreditFlag IS NULL
Change Data Capture	to be discussed in further detail in an upcoming post	Tracks data changes into a table. Enabled using sys.sp_cdc_enable_db or sys.sp_cdc_enable_table Disabled using sys.sp_cdc_disable_db or sys.sp_cdc_disable_table Uses SQL Agent

SharePoint Magazine - Part 2 available now

2008-10-28T11:12:00.001+11:00

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/sell-sell-sell-why-build-a-dashboard-anyway-part-2-of-6

PerformancePoint Planning: Error When Deleting Members of a Member Set

2008-10-14T12:14:00.006+11:00

Unfortunately I discovered a problem in PPS Planning today which caused me a few headaches... thought I would pass it on to you!! The issue occurs when editing/creating a dimension member set... a save will fail if you do an outdent and a delete member immediately afterwards. Let me demonstrate.

Let's presume you have a structure something like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Salaries
--- --- --- --- Base Salary
--- --- --- --- Bonuses
--- --- --- --- Allowances
--- --- --- Tax Expense

But you want to remove the Salaries level and end up with a structure that looks like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Base Salary
--- --- --- Bonuses
--- --- --- Allowances
--- --- Tax Expense

If you do this:

Outdent the 3 leaf level members
Remove the Salary level member
Save

You will get an error when you save similar to the following:

In order for it to work correctly, you MUST (in this order):

Outdent the 3 leaf level members
Save
Remove the Salary level member
Save

I have logged this problem on Microsoft Connect. Please feel free to vote! https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=375153&SiteID=181

SharePoint Magazine - Part 1 Out Now!

2008-09-18T10:07:00.004+10:00

I've commenced a 6-part series on building PerformancePoint dashboards in SharePoint which you can read at SharePoint Magazine.

I'll be releasing a new part each week for the next 6 weeks covering the various aspects of building dashboards - including why bother at all!

Have a read if you're interested. Feedback so far has been excellent.

Interesting Read

2008-08-25T11:57:00.001+10:00

Great article comparing the PIVOT (SQL 2005+) function with doing it the old-fashioned way! Take a read...

http://www.sqlservercentral.com/articles/T-SQL/63681/

SQL: Concatenating Rows into a Scalar Value

2008-07-03T16:36:00.014+10:00

This is an issue which pops up from time to time, often when passing values back to .Net applications.

The Problem
Taking values in rows and creating a single string value containing a delimited list of all those values.

The Old Solution
The method I have used in the past is to use the FOR XML clause and build a string that way. It works.

DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = Name + ','
FROM AdventureWorks.HumanResources.Department
WHERE GroupName = 'Executive General and Administration'
FOR XML PATH ('')
SELECT @DepartmentName AS DepartmentNames

The Sexier Solution
This idea comes courtesy of Ken Simmons at SQL Tips... so simple and I'm kind of annoyed that I've never tried it myself!

DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'  
FROM AdventureWorks.HumanResources.Department
WHERE GroupName = 'Executive General and Administration'
SELECT @DepartmentName AS DepartmentNames

What Were the Actual Results from Testing?
Sadly, the sexier solution under-performs in comparison to the FOR XML solution.

Method	Milliseconds	BytesReceived	Rows Returned
FOR XML	78	9891	3
COALESCE	265	10505	60921

Obviously this lag is due to the number of rows which are returned to the client. Based on the stats, the FOR XML does all it's aggregation and concatenation server-side whereas the COALESCE method does it client-side.

So I guess that means the old way is still the best. Or is it? I wonder in what other circumstances COALESCE could be used? Ok so it under-performed in this instance but I suspect there are other uses which could be of great value. Got any ideas?

Reporting Services: Passing MultiValue Parameters

2008-05-20T10:49:00.018+10:00

I recently came across an article by Wayne Sheffield on SQL Server Central which contained a very neat idea for passing multi-value parameters from SSRS to a SQL stored proc by using XML.

Because SQL stored procs can't handle arrays, it can't handle parameters with multiple values. There are a few ugly ways around this of course by using delimiters and manipulating strings but that just isn't pretty at all. Wayne's idea is to use XML string parameters.

So SSRS would send a string in the following format:

<root>
       <node>
              <element>element data</element>
       </node>
</root>

It would look something like this:

<customers>
       <customer>
              <customerid>1234</customerid>
       </customer>
</customers>

Wayne has written a bit of code which you can add to your report or create a DLL for which can then be referenced by your report.

Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String
       **************************************************************************
       Returns an XML string by using the specified values.
        Parameters:MultiValueList - a multi value list from SSRS
        Root, Node, Element - String to use in building the XML string
        **************************************************************************
        Dim ReturnString = ""
        Dim sParamItem As Object
        ReturnString = "<" & Root & ">"
        For Each sParamItem In MultiValueList
               ReturnString &= "<" & Node & "><" & Element & ">" & Replace(Replace(sParamItem,"&","&"),"<", "<") & ""
       Next
       ReturnString &= ""
       Return (ReturnString)
End Function

This code would be referenced in your Reporting Services parameter like:

ReturnXML(Parameters!MultiValue.Value, "Customers", "Customer", "CustomerId")

To then use your XML parameter within the stored proc:

Select CustomerId, CustomerName, ActiveFlag
From tCustomer a
INNER JOIN @ipCustomerList.nodes('/Customers/Customer') AS x(item) ON a.CustomerId = x.item.value('CustomerId[1]', 'integer')

Pretty handy no?

Datamining Part II - Terminology

2008-05-08T10:42:00.007+10:00

Datamining, like all other IT subjects has it's own lingo. This quick blog post will explain them.

Datamining
Datamining attempts to deduce knowledge by examining existing data

Case
A case is a unit of measure.
It equates to a single appearance of an entity. In relational terms that would mean one row in a table. A case includes all the information relating to an entity.

Variable
The attributes of a case.

Model
A model stores information about variables, the algorithms used and their parameters and extracted knowledge. A model can be descriptive or predictive - it's behaviour is driven by the algorithm which was used to derive it.

Structure
A structure stores models.

Algorithm
My definition here is from the perspective of datamining rather than a general definition. An algorithm is a method of mining data. Some methods are predictive (forecasting) and some are relative (showing relationships). 7 algorithms are included with SQL Server 2005.

Neural Network
An algorithm designed to predict in a non-linear fashion, like a human neuron. Often used to predict outcomes based on previous behaviour.

Decision Tree
An algorithm which provides tree-like output showing paths or rules to reach an end point or value.

Naive Bayes
An algorithm often used for classifying text documents, it shows probability based on independant data.

Clustering
An algorithm which groups cases based on similar characteristics. Often used to identify anomalies or outliers.

Association
An algorithm describes how often events have occured together. Defines an 'itemset' from a single transaction. Often used to detect cross-selling opportunities.

Sequence
An algorithm which is every similar to the association algorithm except that it also includes time.

Time Series
An algorithm used to forecast future values of a time series based on past values. Also known as Auto Regression Trees (ART).

Cluster
A cluster is a grouping of related data.

Discrete
This is more a statistical term than a strictly datamining term however it is used frequently - hence it's inclusion here. Discrete refers to values which are not sequential and have a finite set of values eg true/false

Continuous
Continuous data can have any value in an interval of real numbers. That is, the value does not have to be an integer. Continuous is the opposite of discrete.

Outlier
Data that falls well outside the statistical norms of other data. An outlier is data that should be closely examined.

Antecedent
When an association between two variables is defined, the first item (or left-hand side) is called the antecedent. For example, in the relationship "When a prospector buys a pick, he buys a shovel 14% of the time," "buys a pick" is the antecedent.

Leaf
A node at it's lowest level - it has no more splits.

Mean
The arithmetic average of a dataset

Median
The arithmetic middle value of a dataset

Standard Deviation
Measures the spread of the values in the data set around the median.

Skew
Measures the symmetry of the data set ie is it skewed in a particular direction on either side of the median

Kurtosis
Measures whether the data set has lots of peaks or is flat in relation to a normal distribution

SQL Server Releases

2008-05-05T09:04:00.005+10:00

Microsoft have announced that they are changing their approach to releases for SQL Server. This is interesting because SQL Server releases can be a touchy subject for businesses, particularly those with big server farms. Inevitably the development team wants the Service Pack to be installed ASAP whereas the server team is keen to protect their stable server and pretend service packs don't exist. This means a lot of pushing and shoving.

This new approach should help to alleviate the pressure a little but I'm not altogether convinced.

· Smaller Service Packs which will be easier to deploy
I suspect smaller service packs will make server teams less inclined to come to the party because less inclusions on a per service pack basis inherently implies more service packs.

· Higher quality of Service Pack releases due to reduced change introduced
It's all very well to say that the quality is better but that's a very airy fairy 'benefit' which I can't imagine will go down very effectively with server teams as an argument for implementation. It's just not very quantitative which means server teams are likely to ignore it.

· Predictable Service Pack scheduling to allow for better customer test scheduling and deployment planning.
On this point, I demure. This can have a huge impact on getting releases implemented. Presuming of course that you can get your server team to operate on a scheduled release process themselves. It's all very well for the vendor to do it but if the server team doesn't ALSO do it, there's no gain. That said, I believe that such a process SHOULD be followed. I just don't see it as terribly likely. I fervently hope to be disproven.

It's really easy to be cynical about this approach and say 'my organisation will never do this'. Which is the trap I've fallen into here I realise, but the fact of the matter is, good on Microsoft for considering these issues and attempting to find ways to improve them. The approach is right and a positive move. Now the onus is on us to follow in their footsteps. This should be a wakeup call to server and development teams to find more common ground, to develop processes which satisfy everyone's needs and to communicate with each other better.

For more details:
http://blogs.msdn.com/sqlreleaseservices/archive/2008/04/27/a-changed-approach-to-service-packs.aspx

Datamining – Reviewing the Data Source

2008-04-15T09:36:00.008+10:00

I'm currently going through the Training Kit for Exam 40-445 – the MCTS SQL BI exam. And data-mining, joy of joys, an area where I'm not exactly at expert level, is the focus of my study. And of course, by all reports, data-mining makes up a large chunk of the exam.

In the book, they provide sample code for the 4 moments of a particular attribute which can be used to validate test vs training data in reviewing source data for a data mining project.

The Four Moments

Moments are individual measures or parameters of the moment method. The moment method estimates and equates samples of data. The moments most commonly used in data mining are:

Mean – the centre point of the data set
Standard Deviation – measures the spread of the values in the data set around the centre point
Skew – measures the symmetry of the data set ie is it skewed in a particular direction on either side of the centre point
Kurtosis – measures whether the data set has lots of peaks or is flat in relation to a normal distribution

Datasets

In reviewing source data, you need a training data set (to teach the patterns) and a test data set to validate whether that which was learnt is in fact correct!

In the code below, we are returned 2 rows – a row for each dataset - showing the 4 moments for both. If the values are fairly similar in both rows then we know the sampling is valid.

Sample Code

This example uses, from the AdventureWorksDW database:

the 'CustomerKey' column in order to identify records for sampling

the 'Age' column as the attribute which we are measuring the 4 moments against

--populate testing data
Select * into TK445_Ch09_TestSet
from vTargetMail
where rand(checksum(newid())%1000000000+CustomerKey) <.2

-- populate training data
Select * into TK445_Ch09_TrainingSet
from vTargetMail
where rand(checksum(newid())%1000000000+CustomerKey) <.2
except select * from TK445_Ch09_TestSet

--can be used to validate that datasets have been populated
--select 1,* from TK445_Ch09_TrainingSet union all select 2,* from TK445_Ch09_TestSet

SELECT
Mean = MIN(m.mean),
StdDev = Min(m.StdDev),
Skew = SUM(((Age*1.0-m.mean)/m.StdDev)*((Age*1.0-m.mean)/m.StdDev)*((Age*1.0-m.mean)/m.StdDev))*MIN(m.corrfact1),
Kurt = sum(square(square(((Age*1.0-m.mean)/m.StdDev)))) * MIN(m.corrfact2) - min(m.subfact)
FROM TK445_Ch09_TrainingSet v
CROSS JOIN (SELECT
mean = AVG(age*1.0),
stddev = STDEV(Age),
corrfact1 = count(*)*1.0 / (Count(*)-1) / (count(*)-2),
corrfact2 = count(*)*1.0 * (Count(*)+1) / (count(*)-1) / COUNT(*)-2 / COUNT(*)-3,
subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
FROM TK445_Ch09_TrainingSet) m

UNION

SELECT
Mean = MIN(m.mean),
StdDev = Min(m.StdDev),
Skew = SUM(((Age*1.0-m.mean)/m.StdDev)*((Age*1.0-m.mean)/m.StdDev)*((Age*1.0-m.mean)/m.StdDev))*MIN(m.corrfact1),
Kurt = sum(square(square(((Age*1.0-m.mean)/m.StdDev)))) * MIN(m.corrfact2) - min(m.subfact)
FROM TK445_Ch09_TestSet v
CROSS JOIN (SELECT
mean = AVG(age*1.0),
stddev = STDEV(Age),
corrfact1 = count(*)*1.0 / (Count(*)-1) / (count(*)-2),
corrfact2 = count(*)*1.0 * (Count(*)+1) / (count(*)-1) / COUNT(*)-2 / COUNT(*)-3,
subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
FROM TK445_Ch09_TrainingSet) m

Microsoft 70-556 Exam - MCTS Performance Point

2008-03-13T18:07:00.006+11:00

Last week I completed and successfully passed the 70-556 exam - which gives me an MCTS PerformancePoint certification.

The exam was relatively simple - I scored highly in all areas but one. Obviously a weak point there on my part... my success in the other areas was due to a week and a bit of study using the Rational Guides to PerformancePoint (authored by my esteemed colleagues Adrian Downes and Nick Barclay) - the Planning volume and the Monitoring & Analyzing Volume. Much thanks to them!

The exam is strongly skewed towards Planning - only a few questions on Monitoring & Analysing - and the M&A questions were very simple. There are quite a few questions on Business Rules and Configuring Models & Dimensions - and these are complex areas so spend time on them in your study.

If you are doing the exam and using the Rational Guide books, don't ignore the Bonus chapters - you'll need them!

Next up is the 70-445 (MCTS SQL BI) which is much harder... I'm currently spending a lot of time with the Microsoft Training kit. Wish me luck...

Using TSQL Queries for AD Authentication

2008-02-21T13:05:00.004+11:00

Yes you can directly query AD using TSQL queries. And it's REALLY easy.

Why you ask – well, LDAP can be painfully slow if you have to trawl through groups eg if you are storing the results in a table or outputting them to screen or suchlike. I've found this method to be pretty reliable and reasonable with regards to performance.

First things first… on a SQL server, you need to create a linked server to AD as follows:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

That's all the configuration you have to do. You are good to go!

Below is an example of the TSQL syntax.

Note that the DC is the domain in which the AD resides – in this instance it has three parts MyDomainName,DC=MyTopLevelDomain,DC=MyDomainCountryCode eg DC=microsoft,DC=com,DC=au. In this example, I am returning the username of all users who are a member of the AD group called GAMyADGroupName. This example checks if a particular user is a member of a particular AD group:

SELECT *
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName
FROM ''LDAP://DC=csr,DC=com,DC=au''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND memberOf = ''CN=GAMyADGroupName,OU=Other,OU=Global Groups,OU=Security Groups,DC=MyDomainName,DC=com,DC=au''
AND sAMAccountName = ''MyUserName''')

SSIS Synchronous/Asynchronous Script Transformations in a Data Flow

2008-02-18T13:21:00.006+11:00

Asynchronous just means it consumes all rows before progressing to the next step in the data flow – unlike most other transformations where the entire data flow is executed for each row before the next row is commenced. Script transformations can be synchronous (eg Lookup) or asynchronous (eg Sort or Aggregate).

A synchronous script transformation is identified because the output is linked to a specified input. This is done in the Inputs and Outputs area of the task by entering the ID from the Input properties in the SynchronousInputID field of the Output properties. For each row of input, there will be one row of output.

An asynchronous script transformation is identified firstly because the SynchronousInputID field of the Output properties contains "None" or "0". When a new row is to be added, it is explicitly done using:

Output0Buffer.AddRow()

In order to aggregate data, flatten rows or concatenate data from different rows, you simply set a flag to identify when a new row should be added. To do this you need to declare a boolean in the class but outside the main sub:

Public Class ScriptMain

Inherits UserComponent
Private blnFirstRow As Boolean = True
Dim opMessage1 As String = ""

Public Overrides
Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

In this example, I'm taking all rows and flattenning them into one row so the first step in my sub is:

If blnFirstRow = True Then Output0Buffer.AddRow()
blnFirstRow = False
opMessage1 = opMessage1 & " " & Row.ipMessage.ToString

Note that I then concatenate a variable with a new value… Alternately, you could set a flag so that a new row is added when certain conditions are met such as the value of the ipCustomerNo changes.

One other thing to add - you need to use the PostExecute() sub to write to variables.

VS Db Pro – Script Deployment - Database Context

2008-01-08T16:22:00.001+11:00

When deploying databases using Db Pro the scripts which are executed have a database context. Visual Studio will automatically include a 'USE [YourDbName]' where YourDbName is taken from the project's BUILD property. This is the context for the scripts to execute. This is important because if you change it in any of the scripts it can mess with your deployment.

For example, you create a script called 'AgentJobs.sql' in the Post-Deployment folder and add a reference to the script in Script.PostDeployment.sql. This means your AgentJobs script will be executed when you do a deployment. Now assuming you add a 'USE MSDB' statement in the script, your deployment runs successfully HOWEVER any scripts which execute after AgentJobs eg Data.sql (it executes in alphabetical order) will now have MSDB as the context rather than [YourDbName] as expected.

So make sure that you use fully qualified references to objects in other databases and DON'T use 'USE'!

Sharepoint 2007 (MOSS) – List “My” Sites

2007-10-25T15:17:00.001+10:00

So you want to have a web part which lists the sites which "I" have access to… easier said than done.

Below is my code for doing this… except it doesn't work. Well, it works for me because I'm an administrator but not for other users. It iterates through all site collections in the farm and and does GetSubWebsforCurrentUser. Which is fine, because I have permissions to read the properties of all site collections – a normal user of course, does not!

I tried using elevated authority and all sorts of tricks but so far haven't been able to finagle it. I do have one idea… that is to drop all the info I need into a string array using elevated authority and then do GetSubWebsforCurrentUser on site collections where no errors are raised for the authenticated user. I haven't had a spare second to try that out yet but I will…

So for the benefit of others – and hopefully someone else will have a brainwave in the meantime – here is the errant code in question…

using System;
using System.Text;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Web;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Publishing;

namespace CSR.Sharepoint.WebParts

{

[Guid("0d546513-7447-4211-8fc9-75117af712b0")]

public class MyCollaborationSites : System.Web.UI.WebControls.WebParts.WebPart

{

public MyCollaborationSites()
{
this.ExportMode = WebPartExportMode.All;
}

protected override void Render(HtmlTextWriter writer)
{
SPWebApplication oWebApp = SPContext.Current.Site.WebApplication;
SPSiteCollection oSites = oWebApp.Sites;

foreach (SPSite site in oSites) {
SPWeb oWeb = site.RootWeb;
int ilevel = 1;
if (!PublishingSite.IsPublishingSite(site))//don't include portal
{
GetSubSites(ilevel, oWeb, writer);
};
};

}

private void GetSubSites(int ilevel, SPWeb oWeb, HtmlTextWriter writer)
{
Boolean alternate = false;
foreach (SPWeb oSPWeb in oWeb.GetSubwebsForCurrentUser())
{

if (ilevel != 1)
{
if (alternate == true)
{
writer.Write("<tr><td class=csr-RowGrey><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
}
else
{
writer.Write("<tr><td class=csr-Rowwhite><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
alternate = true;
}
}

if (oSPWeb.GetSubwebsForCurrentUser().Count > 0)
{
ilevel = ilevel + 1;
GetSubSites(ilevel, oSPWeb, writer);
}
}
}
}
}

Sharepoint 2007 (MOSS) - Unpublishing/Expiring a Site

2007-10-11T10:45:00.001+10:00

It's not immediately obvious how to expire a site… particularly for used to SPS 2003. In 2003, you could just set the end date to sometime in the past or if you really wanted to, the start date to some time in the far off future. Unfortunately with MOSS it's a little more complicated.

You'd think that the "Unpublish" option on the Workflow menu of the Page Editing Toolbar would do the trick but sadly, no! What it does do is roll the page back to it's previous version. Definitely handy. But what if you want the page (which has several versions) to be entirely invisible to users? In theory, you could delete it… but I ask you, how many of your content managers will want to delete page just because it's expired now? None I'd wager!

So what's the answer? Page Scheduling (also referred to as Publication Dates). You set a start and end date for publication and the page is only visible during that period. The period may be from "Immediately" to "Never" so you don't have to think about it to much. Easy huh? Yeah, well….

Every site CAN have publication dates however not every site DOES have them. It has to be enabled. To enable Page Scheduling do the following:

Go to the site in question
Go to Site Actions > Site Settings > Modify Pages Library Settings

From Versioning Settings, ensure Versioning and Content Approval are both enabled
From Manage Item Scheduling, ensure Enable Scheduling is ticked

Unfortunately no, it does not apply to subsites of the current site. For the administrators who want to know why - because what you are effectively doing is amending the properties of the Pages list in the site content for that site.

So still you ask, how do I expire the page???? Ok…

Show the Page Editing toolbar
In the status section of the toolbar, it will have the version, the status and the Publication date. Click on the date (it's a hyperlink)
The page scheduling options will open
Set the end date to the desired date/time
Hit OK and from the Page Editing Toolbar hit Publish

Again, seemingly simple…. unless you're changing pages migrated from SPS 2003. Mostly I've found the Start and end dates are blank. You can't set the start date or the end date to some time in the past - you will get an error message telling you so. It's a slight irritation sure but is it really necessary? What's the big deal already… just let me set the date to a point in the past! So you can set the start date to 'Immediately' and the End date to 5 minutes from now and once you hit Publish and those 5 minutes have past, the page will magically no longer be visible.

SQL CLR .Net Function - Split

2007-10-10T14:14:00.002+10:00

Having needed a split function many times in the past, I've always utilised patindex and done it the hard way…. And when it comes to nvarchar values, forget about it! So today I took a few minutes to write a SQL CLR function to do it.

The function takes a string and a delimiter and returns a table of string values.

Here are the instructions for those who may want to do the same thing…

Create a Visual Studio project of the type Database Projects \ SQL Server\ SQL-CLR \ C# SQL Server Project – I called it udfclrSplitString but you know, call it whatever you like!
Add the following code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public class CSRSplitString
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable udfclrSplitString(SqlString InputString,SqlString Delimiter)

{

return InputString.Value.Split(Delimiter.Value.ToCharArray(0, 1));
}

public static void FillRow(object row, out string InputString)
{
InputString = (string)row;
InputString = InputString.Trim();
}
}
Build that sucker
Drop the dll onto the SQL server somewhere relevant
If CLR is not already enabled on the server, execute the TSQL code:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
In the desired database, execute the following TSQL code:
CREATE ASSEMBLY CSRSplitString
FROM 'D:\<YourDLLName>.dll'
WITH PERMISSION_SET = SAFE
In the desired database, execute the following TSQL code:
CREATE FUNCTION udfclrSplitString
(@String nvarchar(4000),@Delimiter nvarchar(1))
RETURNS
TABLE (ArrayItem nvarchar(4000))
AS EXTERNAL NAME CSRSplitString.CSRSplitString.udfclrSplitString;

Where external name is MyDotNETAssembly.MyClassname.Method
To test it, try:
select *
from udfclrSplitString('Kristen,Patrick,Julian,Werner,Koe',',')

You should get 5 rows back …
If you want to test it using a table column as the input try something like:

Select a.name as DbNameOriginal,s.*
from msdb.sys.databases a cross
apply udfclrSplitString(a.name,'_') s
where a.name like '%_%'

You will need to have the function in the msdb database for this example to work
To make this function automatically available in all new databases created on that server, execute steps 6 + 7 in the model system database.

Sharepoint MOSS 2007 - MySite Default Locale

2007-10-09T16:44:00.001+10:00

The default locale setting in the MySite template is English-US. To amend this, the simplest approach is:

Navigate to http///MySite/_layouts/regionalsetng.aspx *
Update the Locale as desired and hit OK

* this assumes your MySite host is at /MySite so adjust as necessary

As to sites which have already been created, this won't fix them. The bottom line is that there is NO supported global fix according to Microsoft as it is by design.

As a bodgy workaround you could update every site in the webs table of your _Content database (and limit it to where fullurl like '%personal%'. Simply do an update on the Locale column setting it to the Id of the locale you want. Clearly this is NOT a recommended workaround and may well break any support agreements you have with Microsoft.

Sharepoint MOSS 2007 - Working with the BDC (Business Data Catalog)

2007-10-08T15:35:00.001+10:00

Having just started working with the BDC, here's what I've encountered so far.

Using BDCMetaMan

On advice from others I started using this tool however I found it to be a tad unwieldy in that I couldn't work out how to do everything I wanted – for example, use a Stored Proc as my source.

So I started with BDC MetaMan, took the output it gave me and then just screwed around with it to make it work with stored procs. In doing so, I found that writing application definitions is nowhere as complicated as others had lead me to believe. That said, it does make for an easy start point where I could just replace values and avoid the tedium of building up all the XML tags in the right order and syntax and so on…

Application Definition

The basic structure is this:

How to use SQL Authentication

Thanks to Nick Kellet for doing the hard slog and solving this problem….http://planetmoss.blogspot.com/2006/11/using-single-signon-with-database.html

The basic steps are:

Enable the Microsoft Single-sign-on service using a domain account (in windows Services)

Create an SSO Application
Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Server Settings
Enter Display Name, Application Name (note: this field is what you will enter into your application definition in the LOB instance properties) and Email Address. They don't have to be anything in particular. Ensure the Account Type is Group.

Associate the SSO Application with the domain users group or some other desired group
Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Account Information. Select the SSO application and type in the "<domain>\domain users" group. Then key in the SQL username and password. Give the application SELECT and EXECUTE rights (assuming you are using Stored Procedures).

Configure your connection string in the application definition
<Properties>
<Property
Name="AuthenticationMode"
Type="System.String">RdbCredentials</Property>
<Property
Name="DatabaseAccessProvider"
Type="System.String">SqlServer</Property>
<Property
Name="RdbConnection Data Source"
Type="System.String">SERVER NAME</Property>
<Property
Name="RdbConnection Initial Catalog"
Type="System.String">DATABASE NAME</Property>
<Property
Name="RdbConnection Integrated Security"
Type="System.String">false</Property>
<Property
Name="RdbConnection Pooling"
Type="System.String">true</Property>
<Property
Name="SsoApplicationId"
Type="System.String">SSO APPLICATION NAME</Property>
<Property
Name="SsoProviderImplementation"
Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>

Import the application definition
Central Admin > SSP > Business Data Catalog > Import Application Definition

Configure permissions for the application definition
Central Admin > SSP > Business Data Catalog > Permissions > Copy all to descendants (or apply the entity you want to set if not inheritable)

Add a BDC List web part to a page, configure it and fingers crossed, it will work!

One BIG caveat… getting this to work across multiple domains is going to be a problem. For now, I’m ignoring that issue and hoping it doesn’t rear it’s VERY ugly head…

How to use Windows Authentication

Haven’t tried it so I don’t know. But… if you are having problems my first suggestion would be to consider the Kerberos double-hop issue. If you don’t know what that is, google it… it’s a reasonably simple fix, if you are friendly with your AD administrator(s)!

Detailed Application Definition

It’s fairly self-explanatory. The bits in green are the bits you have to fill in.

Notes:
· The available data types are your standard .Net data types.
· Refer http://msdn2.microsoft.com/en-us/library/ms546541.aspx for a BDC overviewBelow is my first pass attempt so caveat emptor… it ain’t perfect folks! Feel free to add your comments with anything you can see that can be done better or anything that I’ve got wrong!

What’s Next - Got any ideas?

So next, I’ve been charged to come up with a BDC-based list where some manual info is also stored – bodgy explanation but allow me to demonstrate.

BDC list contains 20 columns. To this list, we want to add some manually entered data. The list will contain around 2000 records. The BDC data will be current and the data editor will update the manual fields when time permits. The web part should output like:

RequestNo Title DueDate Contact Type Owner OutageRequired OutageNotification

Where OutageRequired and OutageNotification are sourced from manual data and the rest are from the BDC.

So my initial thought was to have a BDC lookup in my manual list where the RequestNo, OutageRequired and OutageNotification are entered. I really hate this idea. It’s ugly and impractical. It means that the user has to know the Request number upfront rather than being able to immediately see which request numbers haven’t been manually updated because the 2 fields are empty . Haven’t had a second to think it through properly yet but if anyone has a better idea please speak up!

The SQL Troubleshooting Checklist

2007-10-03T16:52:00.000+10:00

read this handy article from SQL Server magazine and thought I'd share it with everyone... a good one to stick on the pinboard!

The Troubleshooting List

By Andy Warren, 2007/10/03
Total article views: 177 Views in the last 30 days: 162

Most DBA's grow into their jobs and part of that process is the baptism by fire that occurs when things go wrong, especially on the performance side of things. I started to make this performance centric, but so many of the steps and thoughts are common with other problems that I tried to make it a little more generic. In this article I've listed some tips and thoughts about what to do - and why - when the sudden performance dip strikes or other serious problem occurs. It's probably not a perfect list, but the key is to build a process that keeps you on track.

#1 - It's Usually Blocking
When I get a call that 'everyone is locked up', 'everyone is slow', etc, the first thing I look for is blocking. Various ways to check, but the tried and true technique is to execute 'select * from master..sysprocesses where blocked > 0'. Works on SQL 7 forward and takes about 15 seconds to type in and run, then you can decide what action to take. This usually occurs because someone is doing something bad like bulk loading records during the day. You can find and kill the blocking spid, but it's worth looking to see what the spid is doing first. If it's been running for three hours you might be better off to let it finish rather than wait for it to roll back.

#2 - It's Not Blocking, Is It Limited to One Application/Database/Physical Location?
I'm usually asking this question while I'm headed to a machine to work on step #1. If it's NONE of those things I can probably rule out recent application deployments, recent proc changes, network problems to a WAN site. If it is one physical location, I immediately get an email out to the network team to have them check all their hardware. Sometimes a router will blip, or maybe they just rebooted it and forgot to tell everyone. In practice it's usually not the network, but sometimes it is! If it's just one application and/or database, I'm immediately thinking 'what has changed'? If you know how to do wait state analysis this is a good technique to think about applying here but it takes time and you can often shortcut with the right questions.

#3 - If it's One Application, Can They Reproduce the Issue?
This is key. If they can show you it's always slow when they add a customer, look up an order, etc, you can quickly zoom in by profiling and filtering to a single host name. That will let you see exactly what is being executed and look for something with a long duration - long being subjective, but anything close to 30 seconds is a good candidate for any immediate look and anything on down to 5 seconds might need to be checked. Ideally here you can refer back to a baseline so you know what those procedures usually cost. Usually application problems can be traced back to either a change (app code or database), or SQL has generated a new plan for a query that isn't performing well, or both. In the worst case get a developer to step through the code against the production database because it's not always a SQL problem. Check whatever error logging system the application uses and the event log of the machine they are using it on.

#4 - It's One Application, But They Cannot Reproduce on Demand
These can be miserable to solve. I usually start with running separate profiler sessions on a couple users I can trust to call or email when the problem happens and to be able to tell me a lot about what they were doing at the time. I've seen some of these solved by the low tech technique of using a camcorder to capture everything that happened, more recently I've used Morae to let me go back in time to see what happened. Both of those are more often needed for code issues rather than SQL issues, but sometimes you're just looking for clues. If you can get the users to log when it happens you can try to match up in Profiler, the hard part is the query plans may be fine but they are being blocked or bogged down by something happening elsewhere on the server. So you have to have a deep focus on the user AND be looking at the server holistically.

#5 - Sometimes it's the data
This might happen because a lot of data has been added and the statistics don't match the current state of the data, or just because in some cases there is a lot more data than the developer expected. I dealt with a problem like this once where IT users had not flipped a bit in a table that allowed users to cache the results of an expensive query on the client, as a result they kept running that query over and over and over, killing performance. Flipped the bit and magically all was well!

#6 - Rarely Is it the Hardware
Perhaps most common in this category is a drive in a RAID set failing and no one noticing, resulting in a performance decrease because parity is being used to restore the missing data on the fly. I've also seen more than once incidents where 'the server is down' but someone doing work in the server room unplugged/knocked loose the wrong power/network cable. For the most part the hardware will either work or it won't. Doesn't hurt to have some check, but unless the server is actually unavailable this should be further down the list.

#7 - It's Usually Because Someone Changed Something!
I bet we all have had a few of these happen. Having a good change control process that is logged can really help when you're under stress. Something as minor as deploying a proc change where they added a column or order by can have a huge performance impact if the query is no longer covered. Sometimes the rules change after a hotfix (less often) or a service pack (more often) and while hopefully the changes are good, sometimes it isn't. Larger companies may be applying patches on a regular basis and after a while you start to ignore them, until something goes wrong. I'll also add a plug here for rebooting one extra time after applying a service pack because I want to be absolutely sure there are no pending changes waiting to be made live on the next reboot which might be months away - who would think of the last service pack as being an issue when it was applied three months ago?

#8 - It's Incredibly Important to Communicate
As soon as you know an incident is going to last more than few minutes someone needs to get the word to operations. Work may need to be redirected to other sites, hourly employees put on break or sent for an hour of required but not all that important training, or in worse cases sent home for the day. Even smaller companies may have hundreds of employees/customers affected and the cost per hour of down time can be significant. Tell operations what you know via your boss, let them decide. It's also important to immediately communicate problems to everyone in IT. A lot of times you'll get an immediate reply back from someone who was bulk loading data, rebooting a switch, etc, that will immediately explain the problem. You also will need people to help you chase down leads so that you can stay focused, and someone to make sure you get something to eat and drink while you work.

#9 - Know when to ask for Help
I think the more experienced you are the easier this is to do. If you're a brand new DBA you hate to get the credit card from your boss to call PSS, or ask for a consultant to come, because you don't want to look inept. Absolutely there is a risk to that, but on the other hand, anything beyond a few hours and the cost of either of those pales in comparison. Try setting the stage before the incident by having one call to PSS pre-approved and consider establishing a relationship with a SQL consultant just in case. And it doesn't have to just be PSS, lot's of good challenging problems get posted in the forums here and on MSDN every day and the people that answer them like a good challenge and the learning it brings. Lean on your peers too, whether within the company or someone you met at the local user group. With experience you get more comfortable with the idea that you know what you need to know, you know what you don't know, and that it is not a sign of weakness to ask for help. Sometimes easier to say than to do!

#10 - Learn Your Lesson
I bet easily 8 out of 10 times when I'm finally done resolving a performance problem that I can look back and see that I didn't ask the right question, didn't really listen to an answer, took off following the first lead rather than being methodical, or any of a bunch of other things I should have done - in hindsight. This isn't about beating yourself up or holding yourself to some incredibly high standard, but rather stepping past our pride enough to really learn a lesson.

#Extra Credit - The Challenge
It's been a while since one was posted, but Steve and I had a lot of fun writing about many of the near disasters we've been involved in, often of our making. So my challenge to you is post your own performance issue checklist, or tell us about the problem you should have solved sooner and why. Yeah, we may smile a bit when we read them, but I bet more of us smile because we've done the same thing, or because we know that's one trap we won't fall in to. As always I hope you've learned something or sparked an idea or two, and look forward to an interesting discussion!

Sharepoint 2007 (MOSS) Memory Errors – EventID 6482/6398/7076 - Attempted to read or write protected memory

2007-09-20T11:52:00.001+10:00

Sharepoint 2007 (MOSS) Memory Errors – EventID 6482/6398/7076

If you are seeing the following error in your event log: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt"….

…Microsoft do have a Hotfix for this problem – however you will have to contact Microsoft Premier Support to get a copy. Unfortunately if you do not have Support, I can't give you a copy of our hotfix as it is password protected and the password is only valid for 7 days!

The patch in question is a .Net 2.0 hotfix titled 303311_intl_x64_zip.exe for 64bit servers and 303312_intl_i386_zip.exe for 32bit.

BSM 2005 & MOSS x64 don’t play well together….

2007-09-12T19:57:00.001+10:00

Here's what you have….

Machine1: Windows 2003 x64 with MOSS 2007 x64
Machine2: Windows 2003 x32 with BSM 2005

Here's where you want to end up:

Machine1: Windows 2003 x64 with MOSS 2007 x64 and BSM 2005 WebPart (only)
Machine2: Windows 2003 x32 with BSM 2005

The article How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows implies that BSM 2005 can't be used in a x64 MOSS environment because the BSM webparts require ASP.Net 1.1 which is 32bit only and IIS can't have BOTH 32bit and 64bit websites running at the same time.

This is shown when attempting to install the BSM web parts on the MOSS server.

On the MOSS server, run the server.msi from the BSM compatibility pack
Select a custom installation > Web Part components only
It fails because .NetFramework 1.1 is not installed
Install .NetFramework 1.1 on the MOSS Server
Execute cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
.Net 1.1 Web Service Extension is set to allowed.

Outcomes:

MOSS site doesn't work - error is "Service Unavailable"
Compatibility pack (web parts only) still fails because WSS 2.0 (or SPS 2003) is not installed

Question

Can BSM web parts be installed in a MOSS x64 environment? If so, how??

This is the question I have posed to Microsoft Premier Support, it looks like the answer is no but I want confirmation from MS before giving up!

**************** UPDATE FROM MICROSOFT ****************

From: Biao Zhang Sent: 2007年9月13日 14:04To: 'Hodges, Kristen'Subject: RE: SRZ070912000200:BSM2005: Can BSM web parts be installed in a MOSS x64 environment? If so, how??Importance: High

Dear Kristen,

I understand this question is very urgent and I have made some research, here is the update:

1.Business Scorecard Manager Server 2005 does not have 64-bit version and officially, Business Scorecard Manager Server 2005 (including web parts) does not support Windows 64-bit platform.

Sharepoint 2007 (MOSS) Upgrade - TempDb issues

2007-08-21T12:39:00.000+10:00

In the middle of a Sharepoint 2007 (MOSS) upgrade....

One key piece of advice I can give - allow TempDb to have PLENTY of space when doing your initial full crawl.... in fact you may find that the % increase approach may not work during this process because it can't get enough space to fulfil it's very hungry needs! A Mb increase approach should work fine.

You may see errors such as "The transaction log for database 'tempdb' is full."

During later incremental crawls it shouldn't need any special handling, this just seems to apply to the intial full crawl.

Visual Studio Db Pro (DataDude) Headaches

2007-06-25T09:44:00.000+10:00

So, no question, Db Pro is a great tool. However, it is not without it's headaches.

When working with TFS and multiple team members, we have had no end of synching problems and regular out of memory errors. This only seems to occur when multiple users are working on the same Db Pro project.

As you can imagine, this has added to development time on projects... ok not hugely but certainly a day or maybe two in terms of charge-able hours and in terms of developer frustration and tense working environment!

My advice to you would be keep it to single user projects where possible... if not, maybe wait for SP1 or Orcas before switching to DataDude.

BUG REPORT: VS Db Pro - TFS - Get Latest Version doesn't update error list

2007-06-18T16:12:00.000+10:00

When using Visual Studio Database Professional (DataDude) and Team Foundation Server there are synching problems between team members when using "Get Latest Version" - the error list does not update to reflect changes. Allow me to give you an example:

DeveloperA deletes TableA and checks in. At this point, StoredProcA is in error because it depends on DatabaseObjectA - this is shown in DeveloperA's error list in Visual Studio.
DeveloperB does "Get Latest Version" on the project
DeveloperB corrects StoredProcA and checks in.
DeveloperA does "Get Latest Version" on the project

After the Get Latest, you would expect that DeveloperA's error list is now clear of the aforemetioned errors. Unfortunately it isn't. To get rid of these errors, Visual Studio must be restarted OR each error double-clicked. And every now and then, only a reboot will resolve it.

Have logged it with Microsoft Connect... you can view/watch it at https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=285730

******************************** UPDATE ********************************

This is the response from Microsoft.

Unfortunately, this is expected behavior. The database project doesn't know when all of the files have finished being updated, so it doesn't know when to refresh itself. When the project is in this situation, there is an information message in the info strip at the top of schema view telling you that you need to refresh. You can refresh the project one of these ways: . do a refresh on the solution explorer (button on the toolbar of solution explorer) . open the file that was modified . build . close/reopen the project

FEATURE REQUEST: VS DbPro - Refactor/Delete function

2007-06-18T13:15:00.000+10:00

If you have been using Db Pro (aka DataDude) you may have hit this little headache during your build phase... if you delete a column, table, stored proc, function or other object, references to it are not deleted thus leaving your project in an error state which is simple to fix however time-consuming and unnecessary.

I've suggested this should be a prompted action whenever you do a schema view delete... and if the action is not carried out, then all affected items should be flagged.

If you've hit this little nugget of joy, please vote for the feature request...

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=285727

************** UPDATE FROM MICROSOFT **************

This is a good suggestion, thank you for your feedback. We are considering your suggestion for future release.
Posted by Microsoft on 22/08/2007 at 9:45 AM

************** ANOTHER UPDATE FROM MICROSOFT **************

We are concerned about two potential problems around how we understand your suggestion:1) Cascading deletions. If we were to enable deletion of dependent objects automatically, the cascade could be disasterous2) Statement level references. Do you envision deletion of referencing statements within programmability objects or deletion of the programmability object (function, sproc, etc.) as a whole?We do believe that the deletion of child objects, such as indexes or triggers on a table being deleted, is beneficial.We'd be interested in additional details you could provide on the scenario that you envision
Posted by Microsoft on 8/22/2007 at 9:45 AM

I agree that a cascading delete would be far too heavy-handed. An excellent solution would be something similar to a 'schema compare'.

For example, let's say I select Refactor>Delete on an object called [Qty] on the [Order] table. A window appears looking very like the schema compare window which lists all objects dependent on [Order].[Qty] broken down by schema type. When an object is selected, the object definition is shown below with the references to [Order].[Qty] highlighted and below that the update script (and the cursor in the update script is positioned to the current object).

I have 3 Update Actions available to each object in the list - Skip, Drop and Update.When I select, Drop, the Update script is automatically updated to carry out that action.When I select Update, I then remove or replace references to [Order].[Qty] in the update script for the current object.When I select Skip, the object is not changed in any way.

Once all changes are done, I can then hit 'Write Updates' to apply the changes including the delete of [Order].[Qty].

This implies that a 'Find Orphaned References' should also exist which identifies references to deleted objects ie when the 'Skip' update action was selected. And again, a similar approach could be taken as outlined above.

This approach covers both your questions ie eliminating the impact of cascading deletes and amending programmability objects as well as deleting them.
Posted by khodges on 8/29/2007 at 5:33 PM