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

Dynamic MetaData Reader

Hi All

Hot on the heels of the dynamic info matrix, here is script to extract dynamic metadata... Same deal - run and copy and paste into excel...

if object_Id('tempdb..#Output') is not null
drop table #output
go
Set Nocount on
go
declare @DataWarehouse varchar(200) -- the name of the DW DB
,@Dimension_Convention varchar(20)
,@Fact_Convention varchar(20)
,@SurrogateKey_Convention varchar(20)

select @DataWarehouse = 'SOME_DW'
,@Dimension_Convention = '%_dim'
,@Fact_Convention = '%_fact'
,@SurrogateKey_Convention = '%_id'



Create table #Output
(
RowID int identity (1,1)
,TableType varchar(max)
,TableName varchar(max)
,ColumnName varchar(max)
,KeyColumn varchar(max)
,ExampleQuery varchar(max)
,Example varchar(max) default ''
,[Description] varchar(max)
,DataType varchar(200)
)


declare @intRow int
,@intRows int
,@Query varchar(max)

select @Query = '
Insert into #output
(
TableType
,TableName
,ColumnName
,KeyColumn
,DataType
)
select case
when SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Dimension' + char(39) + '

When SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Fact' + char(39) + '
end as TableType
,SO.Name as TableName
,SC.Name as Columnname
,SC_Key.Name as Columnname
,ST.name + case
when SC.TypeStat < 2
then ' + char(39) + char(39) + '
else ' + char(39) + ' (' + char(39) + '+ convert(varchar(20),SC.Length) + ' + char(39) + ')' + char(39) +
'end as Suffix
from ['+ @DataWarehouse + ']..SysObjects SO
join [' + @DataWarehouse + ']..SysColumns SC
on SO.ID = SC.ID
join sys.Types ST
on SC.xType = ST.system_type_ID
left outer join ['+ @DataWarehouse + ']..SysColumns SC_KEY
on SO.ID = SC_KEY.ID
and SC_Key.TypeStat = 1
Where SO.XTYPE = ' + char(39) + 'U' + char(39) + '
and (SO.Name like ' + char(39) + @Fact_Convention + char(39) + ' or SO.Name like ' + char(39) +@Dimension_Convention + char(39) + ')'

exec (@Query)

Update #Output
set ExampleQuery = 'Select top 1 ['
+ ColumnName
+ '] from ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '] where ['
+ KeyColumn
+ '] = (select max(['
+ KeyColumn
+ ']) From ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '])'


Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output

while @intRow <= @intRows
begin
Select @Query = 'Update #Output set Example=('
+ ExampleQuery
+ ') where RowID = '
+ convert(varchar(max),@intRow)
from #Output
Where RowID = @intRow

print @Query
exec (@Query)

Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output
where RowID > @intRow
end

/* Update the description logic as needed */
Update #Output
set [Description] = case
When ColumnName = KeyColumn
then 'The primary key on the ' + TableName + ' table'

When ColumnName like @SurrogateKey_Convention
Then 'A surrogate key referenced to ' + replace(ColumnName, @SurrogateKey_Convention, '')

When ColumnName like '%datasource%'
Then 'Indicates where the data came from'

When ColumnName like '%tmstamp%'
then 'The creation date and time of the transaction'

When Datatype = 'datetime'
then 'Indicates the ' + replace(replace(columnName,'date',''),'Date','') + ' date'

when right(Upper(Columnname),2) = 'NO'
Then 'The ' + Left(ColumnName,len(ColumnName)-2) + ' number'

when right(Upper(Columnname),5) = 'DESCR'
Then 'Describes the ' + left(ColumnName,len(columnName)-5)


end

select TableType
,Tablename
,ColumnName
,DataType
,isnull(Example, 'N/A') as Example
,[Description]
--,*
from #Output
Order by 1,2,3,4,5