"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

SSIS Execute SQL Task Parameters

Keep getting the "descriptive" message
"[Execute SQL Task] Error: Executing the query "{some SP Name}" failed with the following error: "Procedure or function '{Some SP Name}' expects parameter '{Some Param}', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

Here are 2 REALLY BASIC Checks you can do...

1. Make sure you have the ? in your query...
e.g. exec Some_SP ?

2. It could be that your parameter names are wrong...

The parameter names that you use depend on the connection type that the Execute SQL task uses.

Connection type Parameter name
ADO
Param1, Param2, …

ADO.NET and SQLMOBILE
@

ODBC
1, 2, 3, …

EXCEL and OLE DB
0, 1, 2, 3, …

check out TechNet for some more info.

Dynamic SQL Kill statements

Hi all

Like me, I am sure you get miffed when you do testing, and you need to restore a db to a specific backup point, only to get the message " database "Cannot restore database "xxx" because it is currently in use". Then it’s back to running the tedious "sp_who, kill " queries (or using the management tool).

Below is a script I wrote that you can use to kill all the connections to a list of DB's.

You'll notice the variable in one of the first lines, named " @List_Of_Databases". To use the script, simply type the name of the DB's you want to disconnect, separating with a comma. Then run it... It will kill all processes running against those DB's listed. Then you can drop/restore the db to your heart's content.

One word of warning - I have seen that, if SSIS is in "execution" mode (whether it is completed or not), and you run the script, it dies a horrible, painful death.

Enjoy...
/* START OF SCRIPT */


use master
go
Set NoCount on
go
declare @List_Of_Databases varchar(200)
-- List of databases to disconnect
/**********************************************************
User types the list of DB's here
***********************************************************/
select @List_Of_Databases =
'DB1, DB2, DB3, DB4'




--Parse the list to eliminate case problems
Select @List_Of_Databases = UPPER(RTRIM(LTRIM(@List_Of_Databases)))
-- Table to store DB's
Create table #DatabaseList
(
DBNAME varchar(200)
)
--Build the query to get the list of DB's
select @List_Of_Databases = 'select name from sysDatabases where UPPER(RTRIM(LTRIM(Name))) in (' + char(39) + replace(replace(replace(@List_Of_Databases,' ,',','),', ',','),',',char(39) + ',' + char(39)) + char(39) + ')'
--Get the DB's that match the names
Insert into #DatabaseList
exec (@List_Of_Databases)
-- Temp table to store SPID's in use
Create table #Table
(
RowID int identity(1,1)
,SPID int
,UserName varchar(200)
,DBName varchar(200)
,LoginTime datetime
,LastRun datetime
,Program varchar(200)
)
--Get the SPID's
Insert into #Table
(
SPID
,UserName
,DBName
,LoginTime
,LastRun
,Program
)
select spID
,LogiName
,SD.Name
,Login_time
,Last_Batch
,Program_Name
from master.dbo.sysProcesses SP
join master.dbo.sysDatabases SD
on SP.dbID = SD.dbID
join #DatabaseList DB_List
on SD.Name = DB_List.DBNAME
Where spID <> @@SPID
declare @RowID int
,@MaxRows int
,@SQL varchar(200)
,@SPID int
,@UserName varchar(200)
,@DBName varchar(200)
,@LoginTime datetime
,@LastRun datetime
,@Program varchar(200)
-- Set for Loop
Select @RowID = isnull(min (RowID),1)
,@MaxRows = isnull(max (RowID),0)
from #Table
--Perform Loop
While @RowID <= @MaxRows
begin
-- Get process details
Select @SPID = SPID
,@UserName = UserName
,@DBName = DBName
,@LoginTime = LogInTime
,@LastRun = LastRun
,@Program = Program
from #Table
where RowID = @RowID
-- Build query to kill process
Select @SQL = 'kill ' + convert(varchar(20),@SPID)
-- Kill process & show message
exec (@SQL)
Print 'Killed process details' + char(13)
+ '======================' + char(13)
+ 'Process ID: ' + convert(varchar(20),@SPID) + char(13)
+ 'UserName: ' + @UserName + char(13)
+ 'DataBase: ' + @DBName + char(13)
+ 'User Login time: ' + convert(varchar(50),@LoginTime,100) + char(13)
+ 'Last Query run by user: ' + convert(varchar(50),@LastRun,100) + char(13)
+ 'User Interface: ' + @Program + char(13) + char(13)
-- Iterate loop
select @RowID = min(RowID)
from #Table
Where RowID > @RowID
end
if @RowID is not null -- No rows were processed
begin
print 'No processes were killed'
end
-- Clean up
Drop table #Table
drop table #DatabaseList
dbcc FREEPROCCACHE WITH NO_INFOMSGS

/* END OF SCRIPT */