Installing DTS Components

I had to work on a installation with DTS packages and was forced to install the DTS components "DTS Designer Components" and "DTS Run-Time Components".

After the installation I could still not open DTS packages. I got a error like
SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

A quick search gave me the thread "SQL Server 2000 DTS Designer components are required to edit DTS packages - in SQL 2008". I changed the environmental variable as described where I put the value for DTS before anything from SQL Server 2008 and Visual Studio.
The thread is saying that restarting Management Studio is enough to get the new setting, but I had to restart the workstation. Perhaps as I am not local administrator with the my normal user.

Reference

MSDN Library: "Support for SQL Server 2000 DTS in SQL Server 2008 R2"
SQL Server Forums: "SQL Server 2000 DTS Designer components are required to edit DTS packages - in SQL 2008"

Could not load file or assembly Microsoft.AnalysisServices

After a Service Pack upgrade on a SQL 2005 installation from SP2 to SP4, a job failed with
Executed as user: *****. A .NET Framework error occurred during execution of user-defined routine or aggregate "NameOfUserDefinedStoredProcedure":   System.IO.FileLoadException: Could not load file or assembly 'Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)  System.IO.FileLoadException:      at nnn.mmm.fff(String s. The step failed.

Searching the internet gave me the MSDN Forum thread "Framework error:Could not load file or assembly 'Microsoft.AnalysisServices, Version=9.0.242.0". Here I was directed to KB949080 and the handling of assemblies.

A look at the assemblies in the database with
SELECT * FROM sys.assemblies;
and their files with
SELECT * FROM sys.assembly_files;
gave me the details to refresh the assembly registrations with
ALTER ASSEMBLY [Microsoft.AnalysisServices]
FROM N'C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.DLL';

Now everything is working again.

Reference

MSDN Library: "ALTER ASSEMBLY (Transact-SQL)"
KB949080: "... Assembly in host store has a different signature than assembly in GAC..."

Using a SMO object as parameter value

It looks like a SMO object is implicit converted to a String object, when it is used in a function call by a parameter name.

I have a function, where the parameter is defined as
function Import-FileGroup {
param ([Microsoft.SqlServer.Management.Smo.Database]$smoDatabase = $(throw 'Value requered for parameter "smoDatabase" in function Import-FileGroup.'))

...
}

When I call the function with a parameter name
Import-FileGroup -$smoDatabase $smoDb
it fail with this message
Import-FileGroup : Cannot process argument transformation on parameter 'smoDatabase'. Cannot convert the "-" value of type "System.String" to type "Microsoft.SqlServer.Management.Smo.Database".
At U:\sqladmin\dn_sqladmin\Development\Import-Database.ps1:***** char:17
+ Import-FileGroup <<<<  -$smoDatabase $smoDb
    + CategoryInfo          : InvalidData: (:) [Import-FileGroup], ParameterBindin...mationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Import-FileGroup

But when I call the function without a parameter name
Import-FileGroup $smoDb
everything is working fine.

Not what I expected, but I have now made the note for future use...

The variable „$smoDb“ is created like this
$smoSrv = New-Object Microsoft.SqlServer.Management.Smo.Server 'SANDY.sqladmin.lan'
$smoDb = $smoSrv.Databases['my_database']

"BACKUP DATABASE is terminating abnormally."

This morning I had a error on a SQL 2000/Windows Server 2000 installation in a backup by a Maintenance Plan.
In the SQL Agent job the error was shown by
Executed as user: *****. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed.

The Maintenance plan log said:
[11] Database ServicedeskDB: Database Backup...
    Destination: [********201103082203.BAK]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on '********201103082203.BAK' failed, status = 112. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

In the SQL Error Log There were these entries.
2011-03-08 22:04:11.52 spid66    BackupMedium::ReportIoError: write failure on backup device '********201103082203.BAK'. Operating system error 112(error not found).
2011-03-08 22:04:11.53 spid66    Internal I/O request 0x6B514990: Op: Write, pBuffer: 0x06350000, Size: 983040, Position: 4470543872, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xA771600, OffsetHigh: 0x1, m_buf: 0x06350000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: ********201103082203.BAK
2011-03-08 22:04:11.53 backup    BACKUP failed to complete the command BACKUP DATABASE [*****] TO  DISK = N'********201103082203.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT

By the SQL Error Log it could look like a storage failure :-(
Not a nice way to start the day.

When I looked at the Windows System Log there were no entries on storage failure.
But looking at the storage I saw that there were 4.15 GB available, and that the last backup took 4.16 GB.

After a quick cleanup and a manual execution of the job - with success - the conclusion is that the error indicates a lack of available storage.