SQL Database Mail Error The mail could not be sent to the recipients because of the mail server failure. Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available).

In a client's set up, there is a database mail server that sends mail externally via an Exchange server. Great, no issues with that... Until I wanted to set the test environment to also send test mails... For some reason I would get the following error:

• The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2015-12-05T17:18:36). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available).

I double-checked my config's, but no joy. So I went delving into Exchange (this is a Windows 2011 SBS Standard box)...


I took a step back, and asked myself what am I trying to do... I am trying to use Exchange to relay messages to the internet... RELAY, of course!!! SMTP Relay connectors!!!


Quick as a flash,

• I went to Microsoft Exchange -> Exchange On Premise -> Server Config -> Hub Transport
• I double clicked the SMTP Relay connector
• Clicked on Network
• Added the IP address of the test server to "Receive mail from remote servers"
• OK
• Ran the test again

TA-DA!!! It works


Hopefully this will save anyone out in CyberLand countless hours of testing :)


Enjoy


Z

IBM AS400 Linked Server Keeps Crashing SQL Server

I recently had to set up a new Linked Server on SQL Server 2012. It connects to a remote AS400 server using IBM iSeries OLE DB driver (IBM DB2 for I5/OS IBM DA400 Ole DB Provider)

Setting it up was no problem, except, whenever I ran a query, SQL Service would crash when the query finished returning results.

I found loads of good ideas (Switch of DEP, check for Heap Corruption, etc) but nothing worked. Finally I tried each parameter until it worked and got it working!!

To fix:
* Open Linked Servers -> Providers
* Right Click on ABMDA400, Properties
* Tick "Allow InProcess"
* Close the windows

BOOM - It works!!

From what I can gather, using it "out of process" protects SQL from errors on 64-bit machines,

"cannot create the window" message when you open SQL SSMS or Visual Studio

So, on our production machine, we started getting the error "cannot create the window" when most users start SQL Server Management Sudio (SQL 2012) and/or Visual Studio (VS2010).

Short version, Permissions to Machine.Config in C:\Windows\Microsoft.Net\\Config were changed when we installed the AS400 OLEDB drivers (IBM System i Access for Windows V6R1M0), and almost nobody had rights to the file (the only person with effective permissions was me, apparently because I installed it)

I reinstalled .Net,.. No joy... Reinstalled the drivers... no joy... Then I did the most obvious thing I can think of - Give "Everyone" read access on the file itself (right-click, properties, security).

Lo and behold, it works.

Analysis Services Unknown Role that already exists

I was trying to re-deploy a SSAS project using Visual Studio 2010. Every time it deployed, I got the error "the 'role' with 'id' = 'role' doesn't exist in the collection"... Except, we don't have a role called "role".

Googling just led me to things like manually trying to update the .role file and correct it... except WE DON'T HAVE A ROLE CALLED "ROLE"

Desperate times calls for... thinking outside the box!! So I opened the solution in Visual Studio 2013 and deployed... and it worked first time around!!

Not sure why this works, so anyone who sees this post is more than welcome to leave an explanation, but I'll take it as a cheap win, and just say that the problem is fixed :-)

Changing the ID of a cube

So, we decided to do a checkpoint at work, and redeploy the production cubes from 8 months ago. That way, we can run both cubes, and show users what has been improved. however, when trying to deploy the SSAS project, it kept trying to deploy to the production cube. To solve it:

Pre-amble: Assume our cube Database is called JoeSoapSales. 

I changed the name to JoeSoapSales_Feb2014 by renaming it, as well as changing it everywhere I could find in VS. Yet, when I deploy I get a message that the cube "JoeSoapSales" already exists.

Finally, thanks to a lot of googling, I found that, in the solution folder, there is a .database XML file ("JoeSoapSales.Database")... Just change the ID in there using your favourite text editor, and save... Sorted!!

TimeXtender deployment still references old server

We use an ETL tool called TimeXtender (v2014), which is a SSIS wizard.

We recently had to migrate to a new server. After changing the connections via the front-end wizard, saving and deploying the solution, I had an interesting experience. No matter how many times I ran it, my data didn't change. But when I logged into the old server, I was shocked - all my production data had disappeared. Logic told me that TimeXtender had tried to run against my old server, not the new one.

I confirmed all my connections were pointing to the old server.

I approached the vendor, who reconfirmed the basics (did I save, run the wizard, etc... Which I did do).

Eventually, I exported the project to XML, and opened it in notepad... Lo and behold the SSIS server was still set to the old server. I did a quick find/Replace, and imported the project back in... I ran it, and it worked.

---

So, the vendor contacted me this morning. It turns out, that next to the Login credentials button, there is a "Advanced" button, which doesn't change the login properties, but mostly SSIS properties, including server name.

Access Denied to .Net folders when processing a SQL 2012 SSAS Cube

We recently migrated to a new server. The deployment went smooth, up unto the point where we had to process the cube. When trying to process, it failed immediately. The error was: "Errors in the high-level relational engine. The following exception occurred while an operation was being performed on a data source view: Configuration system failed to initialize;An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config)
Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied.."

Access to path? Easy-peasy fix... except it wasn't. I added the service accounts to the admin groups, gave the folders explicit permissions, no joy. Suddenly I felt very silly - such a simple looking problem, and I can't get it sorted? Google yielded no help... So, out of desperation, I yielded to my AD Admin  days, and thought what I would do then... So, to resolve:

• Reset the permissions of the service accounts
• Added the service accounts to a group with the right permissions
• Changed the SQL and SSAS Service accounts to my own account
• Stop and restart the SQL & SSAS Services
• Changed the SQL and SSAS Service accounts back to the Service Accounts
• Stop and restart the SQL & SSAS Services

Voila, it worked :)

Auto-shrink all Database logs

One of the key differences between an OLTP system and a Data Warehouse, is that a DW can be completely truncated and reloaded from the OLTP, since it is purely an aggregation and reporting area. So while Log files are important for OLTP, you don't need have heavy logging on the DW. I always set my DW's to "Simple". However, even in "Simple" mode some logging does occur, and log files grow over time. Not all my customers have the luxury of a DBA, and in order to help them out, I have written this script to automatically shrink log files.

Some notes before you use it:

• You must have the necessary permissions on the databases
• Change the "SomeNameFilter" in the code to filter out only databases you want to filter. 

EXEC sp_MSForEachDB
' use ?;
declare @SQL varchar(2000);
select @SQL = ''DBCC SHRINKFILE ('' + name + '')''
from sys.database_files
where type = 1
and name like ''SomeNameFilter%'';

if @SQL <> ''''
begin;
exec (@SQL);
end '

Enjoy!!

Setting up POP3 and SMTP Relays with Exchange 2010

Wow, recently had SO much fun struggling with Exchange 2010, to set up a POP3 connectors for external mailboxes and an SMTP relay for an internal SQL mail server. The challenge was, that when I could relay internal mail to external, my POP mailboxes fell over, and vice versa...In the end, I had to delete all the connectors Exchange created by default, and set them up manually.

It all started when I was trying to send a SQL mail using CDO. I kept getting "-2147220977" error code, which translates into "0x8004020FL - The server rejected one or more recipient addresses" (Convert the code to hex, and look at TechNet for the meaning). When I rewrote my code to C# (.Net), it popped up with "5.7.1 Unable to relay". So, at this point, I was sure SMTP was the problem.

I deleted all the SMTP connectors, and recreated one for outgoing routing, and it worked... Or so I thought... even though the mail goes out, I now could not download any mail from POP3 mailboxes. When I started the POP3 download, Event log had an error event :"Cannot connect to the SMTP server 'localhost' on port 25. The error code was 0x800ccc0f. Verify that the Microsoft Exchange Transport service is running and that the Exchange receive connectors are properly configured"



Googling did not help much, as it seems not a lot of people have had this problem before, so out of desperation, I took to my trusty whiteboard, and designed what I wanted it to do (I know, such a developer thing to do, but hey, it worked). I came up with an action plan, executed it, and lo and behold, it worked.

This is what I did...

1. In the Exchange Management Console, go to Server Configuration, Hub Transport, and delete ALL receive connectors

Now let the fun start:

1. Using the Wizard, Create a new Connector, called "POP3 Mailbox Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add specific IP 127.0.0.1
   . For the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add "127.0.0.1". This will tell the server which machine will be doing the POP3 pickups. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Go to Authentication, and enable Basic Authentication only. This will allow the POP connector to send credentials to the remove POP mailbox server
6. Click on Permission Groups, and set Anonymous Users and Exchange Servers. This will let the current server connect to the boxes, either through the NW Server account or through and Exchange account
7. Click on Apply, and the POP3 component is set.


Now for the SMTP relay

1. Using the Wizard, Create a new Connector, called "SMTP Relay Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add the specific IP address of the exchange server
   for the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add the IP of the server you want to send emails from. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Click on Permission Groups, and set Exchange Servers. This will let the current server allow connections from the sending server
6. Go to Authentication, and enable Transport Layer Security (TLS) and Externally Secured. This will allow the server to receive the internally generated emails, and get the response from external domains
7. Click on Apply, and the SMTP component is set.

Now test, and there ya go... Hopefully this will make your searching simpler than what I had to go through to get it to work :)

Great Plains Error: User is busy doing a final reconcile

We had a user who's laptop kept crashing, and it would lock him out of GP. One of the errors were "User xxx is busy doing a final reconcile. Please post later" when trying to post CB batches I found an amazing lack of info on the web re this error. So, I delved into SQL, and queried each and every table for the cashbooks I could find (CB%). I eventually found a table "CB900034" that looked like it held the locks. I deleted his record, and voila, it worked. Whether it is the right way to do it or not, I can't say, but the ends justify the means... the code I used was:

delete CB900034 where CHEKBKID = 'the cashbook id goes here'

Data Profiling Script

I recently needed to profile a database - pull out the schema, and also do some basic data quality checks. So, in the spirit of laziness, er automation, I wrote the following:

if object_Id('tempdb..#Output') is not null
drop table #output
go
Set Nocount on
go
declare @DataBase varchar(200) -- the name of the Database

Select @DataBase = 'Put Your Table Name here' -- Remember to enclose in quotes

if OBJECT_ID('tempdb..#Output') is not null
drop table #Output

select SO.name as TableName
,SC.name as ColumnName
,ST.name as DataType
,SC.length as DatTypePrecision
,CONVERT(int,0) as [RowCount]
,CONVERT(int,0) as DistinctRowCount
,CONVERT(int,0) as MinLength
,CONVERT(int,0) as MaxLength
,CONVERT(float,0) as AVGValue
,CONVERT(varchar(200),'') as MinValue
,CONVERT(varchar(200),'') as MaxValue
,CONVERT(bit,0) as isDateField
,CONVERT(bit,0) as isValidDate
into #Output
from sysObjects SO
join sysColumns SC
on SO.id = SC.id
join sys.Types ST
on SC.xType = ST.system_type_ID

where so.name = @DataBase

declare curLoop cursor
For
Select TableName
,ColumnName
,DataType
from #Output

Declare @TableName varchar(200)
,@Column Varchar(200)
,@DataType Varchar(200)
,@SQL varchar(max)
Open CurLoop

Fetch Next From curLoop
into @TableName
,@Column
,@DataType


While @@FETCH_STATUS = 0
begin

Select @SQL = 'update #Output set [RowCount]= (select count([' + @Column + ']) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)

Select @SQL = 'update #Output set [DistinctRowCount]= (select count(distinct [' + @Column + ']) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)

Select @SQL = 'update #Output set [minLength]= (select min(len([' + @Column + '])) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)

Select @SQL = 'update #Output set [MaxLength]= (select max(len([' + @Column + '])) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)

Select @SQL = 'update #Output set [AVGValue]= (select AVG([' + @Column + ']) from [' + @TableName + ']) where [DataType] in (''int'',''float'') and [ColumnName]=''' + @Column +''''
BEGIN TRY
exec (@SQL)
END TRY
BEGIN CATCH
Select @SQL = 'update #Output set [AVGValue]= 0 where [ColumnName]=''' + @Column +''''
END CATCH

Select @SQL = 'update #Output set [MinValue]= (select min(convert(varchar(max),[' + @Column + '])) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)

Select @SQL = 'update #Output set [MaxValue]= (select max(convert(varchar(max),[' + @Column + '])) from [' + @TableName + ']) where [ColumnName]=''' + @Column +''''
exec (@SQL)


Select @SQL = 'update #Output set [isDateField]= case when columnname like (''%Date%'') then 1 else 0 end where [ColumnName]=''' + @Column +''''
exec (@SQL)
--print @SQL

Select @SQL = 'update #Output set [isValidDate]= 1 where [ColumnName]=''' + @Column +'''and [isDateField]=1 and IsDate([MinValue]) = 1 and IsDate([MaxValue]) = 1'
exec (@SQL)


Fetch Next From curLoop
into @TableName
,@Column
,@DataType

end

close curLoop
Deallocate curloop





select *
from #Output

SQL Error 18456 State 28000

Hi

I recently came accross a machine that had me confused... The connection from this machine to our production SQL server refused to work... here is what I tested:

Before I start, here are our actors:
* Machine A - The client that won't connect
* Machine B - My Dev machine
* SQL A - Our Production SQL server
* SQL B - Our dev server
* Login A - The login of the regular user of Machine A
* Login B - My admin login
* Profile A - The user's profile
* Profile B - my windows profile

So, to the test:
1. Profile A - Machine A to SQL A using Login A... Failed
2. Profile A - Machine A to SQL A using the "sa" account... Failed
3. Profile A - Machine A to SQL B... Works 100%
4. Profile B - Machine A to SQL A using Login A... Success
5. Profile B - Machine A to SQL A using the "sa" account... Failed
6. Profile B - Machine A to SQL B... Works 100%
7. Profile A - Machine B to SQL A using Login A... Works 100%
8. Profile A - Machine B to SQL A using the "sa" account... Works 100%
9. Profile A - Machine B to SQL B... Works 100%

So, the credentials are correct. It's only when you connect Machine A to SQL A that there is an error.

Lots of googling revealed a lot of people saying stuff like "Check your passwords"... NOT HELPFUL!!! At this point, if you hadn't checked the passwords, you deserve a kick in the patootie!!

So, I asked a DBA what he tought, and he suggested I check the SQL Network Client Configuration. There was an alias to the server... it was 100% correct (the right IP, and the right details), but I deleted it anyway...

LO AND BEHOLD, IT WORKED!!!

Go figure!!!

Building Excel 2007 Pivot Tables connected to cubes using VBA

Hi.

I recently embarked on the idea of building a pivot table connected to a cube. It seemed so simple !? Just build a MDX query, pass it to Excel, and voila!!! OOPS!!! Excel cannot take MDX queries. If you want to add rows, columns and measures, you have to do it it manually. And Then I started searching for the how to... Would you believe it... There are myriads of examples, and yet each one of them are only people who recorded a macro, then pasted the code. It helps to point you in the right direction, but I kept hitting stumpling blocks (as opposed to a stumbling block - a stumpling block is where you just don't know anymore... you are stumped).

So here is some code I developed. It builds the connection, clears the existing tables, and recreates a pivottable dynamically. The key though, is that I added 2 mods (AddFieldToTable and AddFilterToTable), that you just pass params to, and it will take care of the rest. Write once, use many times!!!

It may be easier to step through the code, than trying to explain it.

Please take a look at it,and comment if this helps...

Regards,

Zanoni...




Option Explicit
'An enum for the type of filter to be applied (one item or multiple items
Private Enum xlFilterType
SingleItemFilter = 0
MultipleItemFilter = 1
End Enum

'An enum to indicate if an item must be part of the list of visible items, or if it is an item to be removed
Private Enum xlFilterVisibility
Visible = 0
Hidden = 1
End Enum

'Collection to store the filter values
Private mColFilterFields As Collection

'An instance of an array for the values
Private MultipleFilterFields() As String


'==========================================================================================
'Main sub to build the Pivot Table
'==========================================================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Public Sub GenerateResultSet()
On Error Resume Next
Dim strPartner, strYear, strScheme As String
Dim DestSheet As Excel.Worksheet
Dim DestRange As Excel.Range
Dim myPVT As Excel.PivotTable

'Set the sheet where the pivottable will be placed
Set DestSheet = ThisWorkbook.Worksheets("Sheet2")

'Set the range on the sheet where the pivottable will be placed
Set DestRange = DestSheet.Cells(10, 1)

Set mColFilterFields = New Collection

'Get the values for the filters
strPartner = DestSheet.Cells(1, 2).Value
strYear = DestSheet.Cells(2, 2).Value
strScheme = DestSheet.Cells(4, 2)

'Remove existing pivottables from the sheet
ClearExistingPivotTables DestSheet
DestRange.Select

'Build the connection to the cube
CreateConnection DestRange, "MyTable"

Set myPVT = DestSheet.PivotTables("MyTable")

'To make it look neater, hide the sheet while building
DestSheet.Visible = xlSheetHidden

'Hide the field list
ThisWorkbook.ShowPivotTableFieldList = False

'For performance, stop auto updating
myPVT.ManualUpdate = True

'Add the pivottable fields & filters
AddFieldToPivot myPVT, "Dimension Number1", "Attribute number 1", xlPageField
AddFilterToField myPVT, "Dimension Number1", "Attribute number 1", xlPageField, SingleItemFilter, "Some Value"

'Add another Dim Field
AddFieldToPivot myPVT, "Dimension Number 2", "Attribute number 2", xlPageField
AddFilterToField myPVT, "Dimension Number 2", "Attribute number 2", xlPageField, SingleItemFilter, "Some other Value"

'Add another Dim Field, and do a multiple filter
AddFieldToPivot myPVT, "Dimension Number 3", "Attribute number 3", xlRowField, True

'Hide e.g. the Unknown member
AddFilterToField myPVT, "Dimension Number 3", "Attribute number 3", xlRowField, MultipleItemFilter, "Unknown", Hidden

'Hide e.g. a blank member
AddFilterToField myPVT, "Dimension Number 3", "Attribute number 3", xlRowField, MultipleItemFilter, "", Hidden

'Hide e.g. SomeFunnyMember
AddFilterToField myPVT, "Dimension Number 3", "Attribute number 3", xlRowField, MultipleItemFilter, "SomeFunnyMember", Hidden

'Add some measures
AddFieldToPivot myPVT, "Measures", "[Measure 1]", xlDataField
AddFieldToPivot myPVT, "Measures", "[Measure 2]", xlDataField
AddFieldToPivot myPVT, "Measures", "[Measure 3]", xlDataField

'Update the table
myPVT.Update

'Show the sheet
DestSheet.Visible = xlSheetVisible

'Set the sheet focus
DestSheet.Select
End Sub


'==========================================================================================
'Adds a field to the PivotTable
'Parameters:
' PivotTableObject - A reference to the pivottable
' Dimension - Name of the dimension being added (For a measure, use "Measures"
' DimAttribute - Name of the attribute being added
' FieldType - Will the field be pleaced in the page area, Row, column, or value
' SuppressSubTotal - Must the subtotal be shown or hidden
'==========================================================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Private Sub AddFieldToPivot(ByRef PivotTableObject As PivotTable, ByVal Dimension As String, ByVal DimAttribute As String, ByVal FieldType As XlPivotFieldOrientation, Optional ByVal SuppressSubTotal As Boolean)
On Error GoTo AddFieldError
Dim Hierarchy, FilterHierarchy As String

'Format and build the full hierarchy name
Dimension = "[" & Replace(Replace(Dimension, "]", ""), "[", "") & "]"
DimAttribute = "[" & Replace(Replace(DimAttribute, "]", ""), "[", "") & "]"
Hierarchy = Dimension & "." & DimAttribute
FilterHierarchy = Hierarchy & "." & DimAttribute


Select Case FieldType
'Add rows/Columns
Case xlColumnField, xlRowField
With PivotTableObject.CubeFields(Hierarchy)
.Orientation = FieldType
Select Case FieldType
Case xlColumnField
.Position = PivotTableObject.ColumnFields.Count + 1
Case xlRowField
.Position = PivotTableObject.RowFields.Count + 1
End Select
End With

If SuppressSubTotal Then
PivotTableObject.PivotFields(FilterHierarchy).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End If

'Add Page Fields
Case xlPageField
With PivotTableObject.CubeFields(Hierarchy)
.Orientation = xlPageField
.Position = PivotTableObject.PageFields.Count + 1
End With

'Add measures
Case xlDataField
PivotTableObject.AddDataField PivotTableObject.CubeFields(Hierarchy)
End Select
Exit Sub

AddFieldError:
Select Case Err.Number
Case 9
MsgBox "Could not find the attribute " & DimAttribute & ".", vbCritical + vbOKOnly
Exit Sub
Case Else
MsgBox Err.Description, vbCritical + vbOKOnly
Resume Next
End Select
End Sub

'==========================================================================================
'Adds a filter to the PivotTable
'Parameters:
' PivotTableObject - A reference to the pivottable
' Dimension - Name of the dimension being added (For a measure, use "Measures"
' DimAttribute - Name of the attribute being added
' FieldType - Will the field be pleaced in the page area, Row, column, or value
' FilterType - is it one item, or multiple items
' Style - Must the filter remove an item from a full list, or add it to an emplty list
'==========================================================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================

Private Sub AddFilterToField(ByRef PivotTableObject As PivotTable, ByVal Dimension As String, ByVal DimAttribute As String, ByVal FieldType As XlPivotFieldOrientation, ByVal FilterType As xlFilterType, ByVal FilterValue As String, Optional Style As xlFilterVisibility)
On Error GoTo AddFilterError
Dim Hierarchy, FilterHierarchy, FilterMember As String
Dim MultiFilterList() As String
'Format and build the full hierarchy name
Dimension = "[" & Replace(Replace(Dimension, "]", ""), "[", "") & "]"
DimAttribute = "[" & Replace(Replace(DimAttribute, "]", ""), "[", "") & "]"
Hierarchy = Dimension & "." & DimAttribute
FilterHierarchy = Hierarchy & "." & DimAttribute
FilterMember = Hierarchy & ".&[" & FilterValue & "]"

Select Case FilterType
Case SingleItemFilter
PivotTableObject.CubeFields(Hierarchy).EnableMultiplePageItems = False
PivotTableObject.PivotFields(FilterHierarchy).CurrentPageName = FilterMember

Case MultipleItemFilter
PivotTableObject.CubeFields(Hierarchy).EnableMultiplePageItems = True

MultipleFilterFields = GetFieldArrayFromCollection(Hierarchy)
ReDim Preserve MultipleFilterFields(UBound(MultipleFilterFields) + 1)
MultipleFilterFields(UBound(MultipleFilterFields)) = FilterMember
If Style = Visible Then
PivotTableObject.CubeFields(Hierarchy).IncludeNewItemsInFilter = False
PivotTableObject.CubeFields(Hierarchy).PivotFields(FilterHierarchy).VisibleItemsList = MultipleFilterFields
Else
PivotTableObject.CubeFields(Hierarchy).IncludeNewItemsInFilter = True
PivotTableObject.CubeFields(Hierarchy).PivotFields(FilterHierarchy).HiddenItemsList = MultipleFilterFields
End If
WriteFieldArrayToCollection Hierarchy, MultipleFilterFields()
End Select

Exit Sub

AddFilterError:
Select Case Err.Number
Case 9 ' Array not yet initialised
ReDim MultipleFilterFields(0)
Resume
Case Else
MsgBox "Could not add a filter to " & Dimension & " for value " & FilterMember & Chr(13) & Chr(13) & Err.Description, vbCritical + vbOKOnly
Exit Sub
End Select

End Sub

'==========================================================================================
'Creates the Excel Connection to the Cube
'Parameters:
' DestinationRange - The Excel Range where the cube must be placed
' TableName - The reference name you want to give the pivottable
'==========================================================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Private Sub CreateConnection(ByRef DestinationRange As Range, ByVal TableName As String)
On Error Resume Next
'TODO: Add Variables instead of hardcoding
Dim ConnectionName, ConnectionString, ConnectionCommand As String

ConnectionName = "MyConnection"
ConnectionString = "OLEDB;Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=YourServerName;Initial Catalog=YourASDatabase"
ConnectionCommand = "YourDefaultCube"

ThisWorkbook.Connections(ConnectionName).Delete
ThisWorkbook.Connections.Add ConnectionName, "Connection used to connect to HIP DW GP Cube", ConnectionString, ConnectionCommand, 1

ThisWorkbook.PivotCaches.Create(xlExternal, ThisWorkbook.Connections(ConnectionName), xlPivotTableVersion12).CreatePivotTable DestinationRange, TableName, xlPivotTableVersion12

End Sub
'==========================================================================================
'Clears all Pivot tables from the sheet
'Parameters:
' MySheet - The reference to the sheet to clear
'======================================e====================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Private Sub ClearExistingPivotTables(ByRef mySheet As Worksheet)
Dim myPVT As PivotTable

For Each myPVT In mySheet.PivotTables
myPVT.PivotSelect "", xlDataAndLabel, True
Selection.ClearContents
Next myPVT
End Sub
'==========================================================================================
'Gets an array of filter fields from a collection
'Parameters:
' Hierarchy - The key to the item to be retrieved
'======================================e====================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Private Function GetFieldArrayFromCollection(ByVal Hierarchy As String) As String()
On Error Resume Next
GetFieldArrayFromCollection = mColFilterFields(Hierarchy)
End Function


'==========================================================================================
'Writes an array of filter fields into a collection
'Parameters:
' Hierarchy - The key to the item to be retrieved
' FieldArray - The updated array to be written back into the collection
'======================================e====================================================
'Date By Action
'==========================================================================================
'6 Jan 2010 Zanoni Labuschagne Created
'==========================================================================================
Private Sub WriteFieldArrayToCollection(ByVal Hierarchy As String, FieldArray() As String)
On Error Resume Next
mColFilterFields.Remove Hierarchy
mColFilterFields.Add FieldArray(), Hierarchy
End Sub

Dynamics Integration Manager Niggles

Being a newbie to GP IM (yet another ETL tool), I recently battled to get journals through to GP, and here is what I learned:

• If you ever have an issue where GP doesn't return your columns, check your query... I had one calling a stored proc, and in the proc, had multiple statements... when running the proc in SSMS, it works, but in GP IM it doesn't... At the top of the proc include:
  "Set Nocount On"
  
  It turns out, it only runs up to the result it gets, then stops. This statement means don't return any row counts.
  


• Make sure that, under Destination Mapping -> Entries-> Options, you have use "Use Source Recordset" for Recordset. If you don't, the batches will be created but won't have any transactions in them.
  


• GP's ODBC connection can't have ANSI NULL translations. I had a "where" clause in the stored proc source stripping rows with no account number, but the rows were still being returned!?. I had to find another way to remove these rows (I joined to another source table to filter empty rows out)
  

Excel 2007 Hangs when opening a .XLSM workbook

I recently had a massive amount of scripting in a workbook. I saved it, but when I tried reopening it, it froze. I almost cried.

First, try repoening it while pressing Shift (this disables the macros)

If no success

To fix it:
* Click the Start Menu then select Run. Type "regedit" (without the quotation marks) into the Run dialog box and click OK.
* Select My Computer at the top of the Registry tree in the left pane of the Registry Editor.
* Then backup the registry by selecting File - Export from the Registry Editor menu, entering a name for the registry backup file and clicking OK.
* Navigate the the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
* With the "Security" key highlighted, select Edit - New - DWORD Value from the Registry Editor menu.
* Type "ExcelBypassEncryptedMacroScan" (without the quotation marks) for the new value name and hit enter
* Then double-click on the newly added value and change the DWORD Value from 0 to 1 and then click OK.
* Exit the Registry Editor and the exit and restart Excel 2007 for the change to take affect.

Forwarding emails from Exchange to an External address

Okay, so this may be simple for an exchange / AD guru, but myself, I had to set this up recently, and it was actually very painless...

1. In AD, create a contact for the address you want to send to
2. In Exchange (on SBS2003), open the account on which you want to apply the forwarding
3. In Exchange General, click on Delivery Options
4. Select "Forward To"
5. Select the new contact
6. choose whether the emails must be sent to both mailboxes, or only the external

As easy as that... not that a DBB developer should have to know it, but hey, it cvan't hurt to have abit of knowledge of other products?

Metadata errors when trying to access a cube via SSMS

I recently found a cube that you could not query. No MDX, No SSMS feedback, nothing... (Yes, I had permissions to access it)

Short solution

Moved the XML files in the cubes data folder to a backup folder somewhere else (you can delete, but you may need these files later). Then, I restarted SSAS service, reprocessed the cuby, and voila!!!

Moving a user-profile in windows XP

We recently had a situation, where SSIS caching a lot of data in the TEMP folder of the user profile. The default drive only had about 2 GB available, while the E-Drive had about 100GB. We couldn't find a setting to force SSIS to use the E_Drive, so we moved the profile to the E-drive. This is how to move it...

* Log in as an admin account BUT NOT THE PROFILE WE WANT TO MOVE!!! ( Make sure the user is also not logged in on the machine)
* Right-click "My Computer" -> Properties -> Advanced -> User Profiles -> Settings -> Move profile
* We moved the profile to the E-Drive ("E:\Profiles\MyUserName")
* Run REGEDIT
* Go to HKEY_LOCAL_MACHINE -> Software -> Microsoft -> Windows NT -> Current Version -> Profile List
* Ignore the folders with the "short names"
* Look at each folder, and look at the value for ProfileImagePath to see the user name
* Change the path to the folder where you copied the profile to (e.g. ("E:\Profiles\MyUserName")
* Close Regedit, and log back in with the user

When we reran the packages, lo and behold, the E-Drive started getting hit. Problem solved

Splitting number of seconds into time

I recently helped on a workflow BI system, and most of the measures were durations, e.g. a workflow took 2000 seconds to do. Problem is, you can't tell an end-user a step took 2000 seconds - it makes no sense. Rather, they want to know it took 33 minutes, 20 seconds. So, heres a UDF to do just that...

if Object_ID('fn_SplitSecondsIntoTime') is not null
drop function dbo.fn_SplitSecondsIntoTime
go
create function dbo.fn_SplitSecondsIntoTime(@intSeconds int)
/************************************************************
Splits a certain amount of seconds into its time components
************************************************************/
Returns @SplitTime Table
(
Days int
,Hours int
,Minutes int
,Seconds int
,LongOutputString varchar(200)
,ShortOutputString varchar(200)
)
as
begin

Declare @intRemainder int
,@intDays int
,@intHours int
,@intMinutes int

Select @intRemainder = 0

Select @intDays = @intSeconds / (60*60*24)
Select @intRemainder = @intSeconds % (60*60*24)

Select @intHours = @intRemainder / (60*60)
select @intRemainder = @intRemainder % (60*60)

Select @intMinutes = @intRemainder / 60
Select @intRemainder = @intRemainder % 60

Select @intSeconds = @intRemainder

Insert into @SplitTime
Select @intDays as Days
,@intHours as Hours
,@intMinutes as Minutes
,@intSeconds as Seconds
,convert(varchar(10),@intDays)
+ ' days, '
+ convert(varchar(10),@intHours)
+ ' hours, '
+ convert(varchar(10),@intMinutes)
+ ' minutes, '
+ convert(varchar(10),@intSeconds)
+ ' seconds'
,case
when @intDays > 0
then convert(varchar(10),@intDays) + 'd'
else ''
end
+ case
when @intHours > 0
then convert(varchar(10),@intHours) + 'h'
else ''
end
+ case
when @intMinutes > 0
then convert(varchar(10),@intMinutes) + 'm'
else ''
end
+ case
when @intSeconds > 0
then convert(varchar(10),@intSeconds) + 's'
else ''
end

Return

End
go

-- USAGE EXAMPLE
select * from dbo.fn_SplitSecondsIntoTime(2000)

Shrink inactive database log files

A friend of mine recentrly wrote a script that will step through ALL the NON-Active databases on a SQL Server, Truncate the Log files and then shrink all the database files. In the current state of the script, it will leave NO free space in any of the database’s, but there is a value to change the percentage you would like to have free. I have added comments to the script as well. I ran it on my laptop and it took about 14 seconds to run across 47 databases and freed up about 20GB + of space.

--Creates a table variable and inserts all the non active databases into the the Variable
DECLARE @DatabaseList TABLE (DatabaseID INT IDENTITY(1,1), DatabaseName VARCHAR(50))
INSERT INTO @DatabaseList
SELECT Name FROM sys.Databases
WHERE STATE = 0

--Stores the SQL String to execute in the Loop
DECLARE @SQL VARCHAR(255)

--Holds the Count of Databases to Step through
DECLARE @DatabaseCount INT
SET @DatabaseCount = (SELECT MAX(DatabaseID) FROM @DatabaseList)

PRINT 'Databse Count: ' + CAST(@DatabaseCount AS VARCHAR(4))

--WHILE Loop
WHILE @DatabaseCount <> 0
BEGIN
IF @@ERROR = 0
BEGIN
--Builds the truncation query for the database
SET @SQL = (SELECT 'BACKUP LOG ' + DatabaseName + ' WITH TRUNCATE_ONLY'
FROM @DatabaseList
WHERE DatabaseID = @DatabaseCount)
--Executes the Query
EXEC (@SQL)
--Shows the Status
PRINT @SQL + ' : HAS BEEN EXECUTED SUCCESSFULLY'

--Prepares for the next database
SET @DatabaseCount = @DatabaseCount - 1
END
ELSE
--RETURNS ERROR MESSAGE
RAISERROR ('Either the state of a database has change while you were executing this statement or a DEADLOCK has occured.',16,1)
END

--Creates a Cursor (Please note, this statement should not be run often that is why i created a cursor.
--Cursor's should be avoided if possible. You can't execute a DBCC statement that returns results within
--a WHILE statement that is why i used a cursor here.
DECLARE @DatabaseName VARCHAR(50)
DECLARE @DatabaseCursorCount INT
DECLARE @DBCC CURSOR
SET @DBCC = CURSOR FOR
SELECT Name
FROM sys.databases
WHERE STATE = 0
OPEN @DBCC

FETCH NEXT FROM @DBCC
INTO @DatabaseName

WHILE (@@FETCH_Status = 0)
BEGIN
SET @DatabaseCursorCount = COUNT(@DatabaseName)
WHILE @DatabaseCursorCount <> 0
BEGIN
--Shrinks the database, the ( 0 ) in the parameter list is the amount of free space that
-- should be left in the database after the shrink. Change it to 10 if you want 10% free afterwards
DBCC SHRINKDATABASE (@DatabaseName,0,TRUNCATEONLY)
PRINT @DatabaseName
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
END
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
FETCH NEXT FROM @DBCC
INTO @DatabaseName
END

CLOSE @DBCC
DEALLOCATE @DBCC