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

Dynamic Info Matrix

Hi

I recently had to do an Info matrix for a DWH with no referential integrity - all that they had were neatly named tables and key columns (but no foreign keys). There were 45 Facts and 49 dimensions. Out of sheer laziness I decided to rather create it using code than manually go and fill in the ticks. so here's the code to do it (output it to text using SSMS, and paste into your spreadsheet tool of choice, split the text data and voila !!!)

if Object_ID ('tempdb..#IM') is not null
drop table #IM
if Object_ID ('tempdb..#MasterList') is not null
drop table #MasterList

if Object_ID ('tempdb..#Dim') is not null
drop table #Dim
Go
Set Nocount on
go

declare @FactTable_Convention varchar(200) -- How are fact tables named
,@DimSK_Convention varchar(200) -- How are dim_sk's indicated
,@Warehouse_DB varchar(200) -- what is the warehouse name
,@IM_UsageIndicator varchar(20) -- what value is used to indicate that
-- a table uses a dim

Select @FactTable_Convention = '%_fact'
,@DimSK_Convention = '%_id'
,@Warehouse_DB = 'Some_DWH'
,@IM_UsageIndicator = 'a' --Webdings makes this a tick mark

-- Internal variables
Declare @intRow int -- tracks the current row
,@intRows int -- tracks the number of rows
,@Query varchar(max)



Create table #MasterList
(
RowID int identity (1,1)
,FactName varchar(200)
,SKName varchar(200)
)


Select @Query = 'Insert into #MasterList (FactName, SKname)'
+ ' Select SO.Name, case when SC.Name like '
+ char(39) + '%Date%' + char(39)
+ 'then ' + char(39) + 'Date dimension' +char(39)
+ ' else SC.Name end '
+ 'from '
+ @WareHouse_DB
+ '..SysObjects SO join '
+ + @WareHouse_DB
+ '..sysColumns SC on SO.ID = SC.ID where SO.XType = '
+ char(39) + 'U' + char(39)
+ ' and SO.Name like '
+ char(39)
+ @FactTable_Convention
+ Char(39)
+ ' and (SC.Name like '
+ char(39)
+ replace(@DimSK_Convention,',',' or SC.Name like ')
+ Char(39) + ') and SC.TypeStat=0' --TypeStat indicates ID columns

--Print @Query
Exec (@Query)



Create table #IM
(
RowID int identity (1,1)
,FactName varchar(200)
,Fact varchar(200)
)

Insert into #IM (FactName,Fact)
Select distinct FactName,
Replace(FactName,replace(@FactTable_Convention,'%',''),'')
from #MasterList


Create table #Dim
(
DimID int identity (1,1)
,DimKeyName varchar(200)
,Dimension varchar(200)

)

Insert into #Dim
(
DimKeyName
,Dimension
)
Select distinct case
when SKName like '%Date%'
then 'Date dimension'
else SKName
end,
case
when Replace(SkName,replace(@DimSK_Convention,'%',''),'')like '%Date%'
then 'Date dimension'
else Replace(SkName,replace(@DimSK_Convention,'%',''),'')
end
from #MasterList

Select @intRow = min(DimID)
,@intRows = max(DimID)
from #Dim

While @intRow <= @intRows --iterate Dim columns
begin

select @Query = 'Alter table #IM add ['
+ Dimension
+ '] varchar(5)'
from #dim
Where DimID = @intRow

--Print @Query
exec (@Query)

-- now check in which tables it occurs
Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39)
+ @IM_UsageIndicator
+ char(39)
+ ' from #IM IM join (select FactName from #masterlist where SKName= '
+ char(39) + DimKeyName + char(39)
+ ') List on IM.FactName = List.FactName'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39) + char(39)
+ ' Where ['
+ Dimension
+ '] is null'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @intRow = min(DimID)
from #Dim
Where DimID > @intRow

End

Alter table #IM
drop Column RowID


Alter table #IM
drop Column FactName

select *
from #IM

----Print courtesy of http://my-pc-hates-me.blogspot.com

Importing an image into an image column using SQL 2005

Hi

Ever had to import an image into a column ? Here is a simple Insert statement...

Insert MyLogoTable (Logo)
Select BulkColumn
from Openrowset( Bulk 'c:\SomeJPEGFile.jpg', Single_Blob) as Logo


Cheers

Shrink TempDB

Hi

Just a quick note: One of my colleagues ran massive updates through SSIS. Eventually the package "fell over" with the error message that the TEMPDB couldn't allocate more hard drive space to the page - the hard drive space. We got techie on the problem, but no DBCC Shrink_XXX commands helped. Eventually he found the (OH SO OBVIOUS) solution - restart the SQL Service...

Now you know too...

Z

"Errors in the metadata manager" when restoring a SSAS cube

I am sure, if you ever tried to move a cube from one machine to another using the tried and tested backup-and-restore method, you came up with the XML error:
“The dd2:MemberKeysUnique element at line 1, column 74295 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.

Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '...\Dimension Name.14.dim.xml'.
Errors in the metadata manager. An error occurred when loading the CubeName cube, from the file, '...\CubeName.147.cub.xml'.
(Microsoft.AnalysisServices)”

Wouldn’t you know it; Microsoft “spin-doctored” it as a feature not a bug. It turns out that you cannot restore a cube backed up in SSAS2K5 SP2 onto a machine running SSAS2K5 SP1. Their exact words :” This is by design”

As a workaround, If there is no SP2 available for the client, you can always delete the offending XML files and restore the cube again, but finding all the offending XML files can take some time.

At least now, you can quote Microsoft to yourself when you find the error… “Its by design”

You can check out their official splab on http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=243737