Techie Pie

Came across an interesting problem in Altiris while working with Patch Management, and seems it is one of those undocumented tidbits.

I basically found myself in the situation where, post-install/repair, Patch Management wasn’t detecting that Patch Management for Windows Report Pack was installed when the application indicated that it was. This is a fairly common error, and anybody who has had to uninstall/reinstall PM via the Solution Center has probably had to deal with it at some point.

Patch Management Core has detected that the following component is not installed: Patch Management for Windows Report Pack This component is required by the page you tried to view and must be installed before you can continue.

But what made this (un)fun was that the standard solution to this problem - repair the NS install and then the SP - didn’t work. In fact, none of the solutions in the KB worked.

Now my stupid moment was in not checking the log file sooner; I assumed it was the standard problem that I had worked with before. However, I was wrong. There were two key items in the log file:

[CDATA[Transaction being rolled back automatically during dispose. To disable this warning rollback your transaction explicitly! Dispose location:

and

[CDATA[Failed to install product. [This index operation requires 2048 KB of memory per DOP. The total requirement of 8192 KB for DOP of 4 is greater than the sp_configure value of 1024 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.
The statement has been terminated.] ( Unhandled exception. Type=System.Data.SqlClient.SqlException Msg=This index operation requires 2048 KB of memory per DOP. The total requirement of 8192 KB for DOP of 4 is greater than the sp_configure value of 1024 KB set for the advanced server configuration option “index create memory (KB)”. Increase this setting or reduce DOP and rerun the query. The statement has been terminated.

The net effect of this is that the option is not actually installed by the Solution Center, even though the application indicates that it is installed.

Fortunately the solution was easy: In SQL 2005, modify the server properties so that Index Memory Size is configured to use dynamic values. I rebooted the Altiris server as a precaution, then tried things again. And it worked!

On several occasions I have been asked to move SQL 2005 system databases at some point in the server’s lifecycle. Moving master, msdb, model and tempdb isn’t difficult, but it does require some attention to detail (and server downtime).

Fortunately, the SQL 2005 Books Online include instructions on how to move system databases in SQL 2005. What you might want to know, however, is that when following these instructions there is an error under the section titled Moving the Master and Resource Databases. Specifically, some details are missing at lines 14-16. If you follow these instructions to the letter (like I do), you need make the following changes (the bolded lines are my additions):

14. MODIFY FILE (NAME=log, FILENAME-’new_path_of_master\mssqlsystemresource.ldf’) ;

GO

14a. Stop SQL

15. Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.

15a. Restart SQL in single user mode

16. Set the Resource database to read-only by running the following statement:

ALTER DATABASE mssqlsystemresource SET READ_ONLY ;

The change is necessary because in order to move mssqlsystemresource.mdf and mssqlsystemresource.ldf the SQL services need be stopped, but in order to set Resource to read only, the service needs to be in single user mode.

It’s a simple modification that keeps following the Microsoft instructions clean and error-free.

Today I found myself in the situation where I wanted to add some maintenance plans under SQL 2005, but got an error that looked like “Cannot find folder \Maintenance.”

The short version is that, somehow, the entry for the Maintenance folder in MSBD got nuked.  After some digging I found a forum thread with instructions on how to fix the problem.

All you need to do is run two SQL queries against MSDB.

1. Verify that the entry is lost:

exec sp_executesql N’exec [msdb].[dbo].[sp_dts_getfolder] @P1, @P2′,N’@P1 nvarchar(17),@P2 uniqueidentifier’,N’Maintenance Plans’,'00000000-0000-0000-0000-000000000000′

2. Recreate the entry:

exec sp_dts_addfolder ‘00000000-0000-0000-0000-000000000000′,’Maintenance Plans’,'08AA12D5-8F98-4DAB-A4FC-980B150A5DC8′

Many thanks to the original poster at the link above for providing the solution.

Just a minor note regarding my experience in setting up SQL 2005 Reporting Services for the first time…

Overall, a very smooth process. I specifically followed the instructions in Chapter 2 of Microsoft SQL Server 2005 Reporting Services (Step by Step), ISBN 978-0-7356-2250-0. There were only a few minor caveats:

1. I installed Reporting Services on an existing SQL 2005 SP2 installation, which meant that I had to go back and rerun SP2 for just that portion of the install before I could configure Database Setup under the Configure Report Server interface.

2. Even after doing this, I got a failure when I chose the option to upgrade the ReportServer DB. The simple fix was to click Apply yet again, and then everything finished like it was supposed to. Guess the DB upgrade had to be in place before the rest of the configuration scripts would run.

And that’s about it. I’ll blog more details about actually using Reporting Services as I get into it in more detail over the coming weeks.

For all its improvements, SQL 2005 still doesn’t provide native support for epoch date/time. So I had to write some functions to do the conversion for me, then configure computed columns to use the functions. Here’s how I did it:

1. Create a function to convert a date to epoch:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[date2timestamp](
@dateStr datetime
)
returns bigint
as begin

/** Now, find out how many seconds have existed between now and the epoch **/

return convert(bigint,
datediff(ss, ‘01-01-1970 00:00:00′, @dateStr))
end

2. Create a function that will convert epoch back to date:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[timestamp2date](
@numSeconds bigint
)
returns varchar(20)
as begin
/** Now, find out how many seconds have existed between @numSeconds and the epoch. **/

/** If you need to modify according to your TZ **/

/** set @numSeconds = @numSeconds + (60 * 60 * -6) — subtract CST **/

return dateadd(ss, @numSeconds, ‘01-01-1970 00:00:00′)
end

3. Since my task needed to calculate the difference between the to values, I had to create a third function:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[epoch_diff] ( @epoch_start bigint, @epoch_end bigint )

RETURNS bigint
AS

BEGIN
RETURN (@epoch_end - @epoch_start)
END

4. To configure the computed column in SQL 2005, you need to right-click on the table and choose Design from the pop-up menu. Add your new column (if not already created), and under Column Properties locate the property Computed Column Specification (Formula).

For this example, it assumes that there is another column in the table with the actual date value that you want to convert to its epoch equivalent. So in the (Formula) field insert the function:

([dbo].[date2timestamp]([fieldname]))

I left the Is Persisted value set to no.

SQL 2005 will go through the existing table data and add the new field value when you save the changes, and new row inserts will have the field value automatically inserted.

In your query code, you can access the difference between the epoch dates like this:

USE tablename

/** gets the number of seconds and divides by 60 for minutes. rounds to nearest whole number. Values less than 1 minute show up as zero. **/

SELECT (dbo.epoch_diff(epoch_start, epoch_end)/60) AS diff
FROM tablename