Dynamic transparent color

The default value of most color properties is "Transparent". If you want to dynamically set a color property, you can use the IIF-statement. This statement has the following signature:

=IIF([boolean],[return value if true],[return value if false])

A problem lies in the fact that both the true and the false part are obligatory. For setting the color the following IIF-statement can be used:

=IIF(ReportItems!txtBox.Value=5, "Palegreen", "Transparent")

But, the value Transparent does not exist! You cannot set it! Visual Studio throws the following warning:

The value of the background color property for the textbox ‘textbox’ is “Transparent”, which is not a valid background color.

It turns out that this property is not exported to the generated XML file, called rdl, if it is not explicitly set. The IIF-statement forces you to set it, but the value Transparent cannot be used. How to dynamically set the color property then?

You could use the value White instead. However, the background color for example gets rendered after the borders, so a white background covers part of a visible cell border. This is clearly visible on the report. Very annoying...

The only solution I could come up with is using a function that does not have an explicit return type. Then, if there is no return value, a non-existing object is returned.

Like this:

function Rank(val1)

if val1=5 then Rank = "Palegreen"

end function

Can you have the IIF-statement return a null (uninitialized object) value? The System.DBNull value that has replace the null value is just a type, not a value. So, this doesn't work.

Truncate staging tables

Before creating a backup of the database, you could clean out all staging tables to safe some space in the backup and to reduce the time it takes to make the backup. You could use the following simple SQL-loop statement:

EXEC sp_MSforeachtable "IF LEFT('?',11)='[dbo].[STG_' BEGIN PRINT '?' TRUNCATE TABLE ? END"

The statement expects a strict naming convention: all staging table names should start with STG_ and this prefix should not be used for any other table types. Also make sure you do not have any "permanent" data in your staging environment.

Note:
The PRINT-statement can be left out. It could help you debug the statement. Also the truncate statement does not work if there are foreign keys referencing the table.

Windows Update fix

Sometimes Windows Update gets corrupted. Some of its components or previously downloaded updates get mangled during the download or installation process. Here's a quick fix to clean out the folders that it uses (put the script in a batch file):

@echo off

NET STOP "Automatic Updates"

SET D=%windir%\SoftwareDistribution\DownloadDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\SoftwareDistribution\DataStoreDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\system32\CatRoot2DEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

NET START "Automatic Updates"

On execution, it wil re-download the main components and give you a "fresh" Windows Update installation.

Cannot decrypt the symmetric key...

Yesterday I got a Windows update of my .Net framework 2.0. After the update the system needed to be rebooted, so I did that. After that, my reportmanager in Internet Explorer returned with the following error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled)

The Reporting Services service (RSS) uses an asymmetric key to decrypt a symmetric key. This symmetric key is used to communicate with its repository in SQL Server. The asymmetric key is tied to the account with which the RSS runs. Change the account and the key is invalidated. Apparently, the update did something to this account, although it was "LocalSystem" and still is "LocalSystem".

Fortunately, the problem is easily solved by issuing the following statement at the command prompt I found on Kate Gregory's blog posted by a guy named Derek:

rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

It re-creates the assymetric key for the service account and stores it again in the rsreportserver.config file.

Return the size of pre-selected tables

A stored procedure I use a lot to get a resultset containing the size of a selection of tables in the database:

CREATE procedure sp_TableSize as
begin
SET ANSI_WARNINGS OFF

CREATE TABLE #temp
(
TABLENAME varchar(100),
ROWS int,
RESERVED varchar(25),
DATA varchar(25),
INDEX_SIZE varchar(25),
UNUSED varchar(25),
)

DECLARE @table nvarchar(100)

-- Select the tables for which to retrieve sizes
DECLARE cDWHTables cursor read_only for
SELECT name FROM sysobjects
WHERE LEFT(name, 4) in ('STG_','ODS_','DIM_','SYS_','FCT_','RPT_') OR
LEFT(name,3) = 'MD_'

OPEN cDWHTables

-- Store each record retrieved from sp_spaceused in temp table
FETCH NEXT FROM cDWHTables INTO @table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
insert #temp exec sp_spaceused @table
END
FETCH NEXT FROM cDWHTables INTO @table
END

UPDATE #temp SET
reserved = left(reserved, len(reserved) - 3),
data = left(data, len(data) - 3),
index_size = left(index_size, len(index_size) - 3),
unused = left(unused, len(unused) - 3)

INSERT #temp
SELECT 'TOTAL' as tablename, sum(rows) as rows,
sum(convert(int,reserved)) as reserved,
sum(convert(int,data)) as data,
sum(convert(int,index_size)) as index_size,
sum(convert(int,unused)) as unused
FROM #temp

--Return the results as data set
SELECT
TABLENAME = tablename,
ROWS = convert(int,rows),
CONVERT(int,reserved) as RESERVED,
convert(int,data) as DATA,
convert(int,index_size) as INDEX_SIZE,
convert(int,unused) as UNUSED,
AVG_DATA = convert(numeric(18,2),case when rows>0 then convert(float, data) / convert(float, [rows]) else 0 end),
TOTAL = convert(int,data) + convert(int,index_size),
TOTAL_MB = (convert(int,data) + convert(int,index_size))/1024
FROM #temp

-- Clean up
DROP TABLE #temp
CLOSE cDWHTables
DEALLOCATE cDWHTables

end

Replace the bold part to set the tables for which to return the size. After all records have been returned it adds a final total row to the set.

Note:
The insert of the total row can alternatively be written using the COMPUTE-clause on the main select statement.

Backup to network share

You can use the following TSQL statement to backup a database to a network share in SQL Server 2000:

BACKUP DATABASE TO DISK = '\\unc\folder\filename.bak'
WITH RETAINDAYS=0, INIT

This will create a full backup each time it is executed. The RETAINDAYS=0 option makes sure that the previous backup will always get overwritten. Not the safest thing, but can be needed if you are in a tight spot regarding disk space.

This backup file will also appear then in Enterprise Manager. Select Tools, Backup database and check the destination listbox.

To check if the backup is complete and readable:

RESTORE VERIFYONLY FROM DISK = '\\unc\folder\filename.bak'

This way you can have more control over your backup procedure than using maintenance plans, e.g. by putting them in a Execute SQL Task in a DTS package.

Alternatively, using Enterprise Manager, you can add a backup device via management\backup and have in point to \\unc\folder\filename.bak. Right-click it, select "backup a database" and click "Ok".

Note:
It is not recommended to backup directly to a network share, but sometimes there is no alternative.

Note 2:
Setting the RETAINDAYS property to e.g. 5 days means it will be impossible to overwrite the backup file during the next 5 days! Beware of that!

Concatenating text files

Jamie Thomson has a post on concatenating multiple text files (using the ForEach Loop) and subsequently loading this concatenated file in one go. You can find the post here:

http://blogs.conchango.com/jamiethomson/archive/2006/06/22/4116.aspx

I use to do this with a small batch file. The batch file is called concat.bat and takes in the following parameters: %1 for the extension of the files to concatenate, e.g. txt and %2 for the folder in which the files are stored.

The body of the batch file is as follows:

set I=
for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
if not defined I goto EXIT
copy "%2"*.%1 "%2"\backup
copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
ren "%2"*concat.tmp *.%1
:EXIT

Example of the usage of the batch file:
The batch files is called with %1 = .txt and %2 = c:\temp. Say there are three files in c:\temp called test1.txt, test2.txt, and test3.txt with create dates 2006/01/01, 2006/01/02, and 2006/01/03 respectively. The result is one textfile called text1.txt containing the content of all three files.

Explanation of each line of code:

Set I=
dimensions the variable I.

for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
iterates over all files in folder c:\temp with extension ".txt", ordered descending by create date. Each iteration "do set I=%%f" assigns the filename to variable I. After iteration I contains the value of the last filename in the list. This is the file created first according to create date, since the list is sorted decendantly.

if not defined I goto EXIT
checks if I has a value, if not, the batch ends. I.e. there where no .txt files in the folder.

copy "%2"*.%1 "%2"\backup
copies the three sourcefiles to a backup folder. This is done for auditing purposes.

copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
concatenates all .txt files to one file with the filename stored in I. So, a file called test1concat.tmp is created, containing the union/concatenation of the three files.

if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
does a check to ensure the .tmp file has been created and deletes the three source files, so the inbox in emptied.

ren "%2"*concat.tmp *.%1
renames the .tmp file to the final filename, in this case text1.txt.

EXIT:
exits the batch file.

Currently it only works if all files have the same filename length, so 5 (="textX") in the case of the example. Of course this can be optimized, e.g. search for the first dot and take the left part of the file name.

I have no idea if this approach is better with regard to performance. I haven't tested it on thousands of files and have not done a comparison to the approach used in the post by Jamie. On the other hand, this approach will also work nicely in DTS, where there is no ForEach Loop.

Note:
The "for /F" functionality is not available in all Windows versions. It is available in Windows 2003 server and Windows XP.

Strange HAVING-clause behaviour

Say you want to return a resultset containing the last two days per week. First, construct the demo table:

CREATE TABLE TEST_TIME (WEEKCODE INT, ISODATE CHAR(8))
INSERT INTO TEST_TIME VALUES (1, '20060101')
INSERT INTO TEST_TIME VALUES (1, '20060102')
INSERT INTO TEST_TIME VALUES (1, '20060103')
INSERT INTO TEST_TIME VALUES (2, '20060108')
INSERT INTO TEST_TIME VALUES (2, '20060109')
INSERT INTO TEST_TIME VALUES (2, '20060110')
INSERT INTO TEST_TIME VALUES (2, '20060111')

The following SQL query will give the desired resultset:

SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
WHERE t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE

It returns 4 records, as espected:

1 20060102
1 20060103
2 20060110
2 20060111

Just for fun, I tried the same thing using a GROUP BY and HAVING-clause like this:

SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
GROUP BY t.WEEKCODE, t.ISODATE
HAVING t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE

Far less efficient, but one would expect it to return the same resultset. But it does not! In fact it returns 28 records:

1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111

That is even more than there are in the table! This is very strange behaviour. Stripping the HAVING-clause returns 7 records, as expected. But adding it results in a cartesian product of the 7 source records by the 4 top-2 records (2 for week 1 and 2 for week 2) resulting in 7x4=28 records.

From BOL:
"The HAVING clause is then applied to the rows in the result set that are produced by grouping. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function."

So, first the grouping, THEN an extra filtering on that set. How can filtering 7 records result in 28 records? Furthermore, the 28 records are not distinct records. Something you would expect when using a GROUP BY-clause...

Conclusion is that in case of a correlated having-clause query the having-clause is evaluated before the group by-clause.