Reporting services failed to start after upgrading to SQL 2008 R2

I recently upgraded my x64 version SQL 2008 to SQL 2008 R2. After everything completed without error, the Reporting services failed to start. There are two exceptions in the Reporting Service log related to performance counter.

Error creating counter.  Category: MSRS 2008 R2 Windows Service, Counter: Report Requests, Instance: MSSQLSERVER.  Error Description: System.InvalidOperationException: The requested Performance Counter is not a custom counter, it has to be initialized as ReadOnly.

ERROR: Appdomain:1 DefaultDomain failed to initialize. Error: System.NullReferenceException: Object reference not set to an instance of an object. at System.Diagnostics.SharedPerformanceCounter.GetCategoryData()

After I tried to repair it, the following error has occurred:

 

The file does not exist on the DVD at all.

Since I could not figure out any solution and unable to repair it, I then uninstalled and re-installed the SSRS component. After that, the Reporting Services started successfully.  

I searched the internet and found the following post related to the repair bug:

http://connect.microsoft.com/SQLServer/feedback/details/588703/senior-developer

The workaround is to copy the setup files to a shared or local dir, and rename the \\1033_ENU_LP\x64\Setup\SSCERuntime.msi to SSCERuntimr_x86-enu.msi. Now, the pair runs successfully.

Read Write Image Data Using T-SQL

Recently I need to display an image as logo on the SSRS reports. Since the logo needs to be replaced easily without modify the reports, I plan to store the image in a SQL table and use it as a database source image. The Image could be stored in either [VARBINARY](MAX) or [IMAGE] data type.

I use the OPENROWSET T-SQL script to upload the image file into the table. Then I could use the SELECT statement to retrieve it in SSRS.

CREATE TABLE [dbo].[Images](

[ImageID] [int] IDENTITY(1,1) NOT NULL,

[ImageName] [nvarchar](40) NOT NULL,

[ImageFile] [varbinary](max) NOT NULL,

) 

INSERT INTO [dbo].[Images]

([ImageName]

,[ImageFile])

SELECT 'Logo.png', * FROM OPENROWSET(BULK N'C:\MyPhoto.png', SINGLE_BLOB) as Logo

GO

To promote this image data to different systems, I need to create INSERT or UPDATE T-SQL statements to facilitate it.

For VARBINARY data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Then, use SUBSTRING to read in the data in 1024 bits
  
• Use PRINT function to generate the Insert/Update statement that using .WRITE(expression, NULL, 1024) to append the image data in small pieces.

DECLARE @ptrval varbinary(1024), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1    

SET @start = 0

PRINT 'INSERT INTO [dbo].[Images]

           ([ImageName]

           ,[ImageFile])

     VALUES

           (''Logo''

           ,CAST('''' AS VARBINARY))'

While @start < @Len 

BEGIN

    SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

    PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

    PRINT @ptrval

    PRINT ', NULL, 1024 )

    WHERE [ImageName] = ''Logo'''

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

        PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

        PRINT @ptrval

        PRINT ', NULL, 1024 )

        WHERE [ImageName] = ''Logo'''

        SET @start = @Len

    END

END

• After executing above query, the following result will be created and could be used as Insert/Update statements.

INSERT INTO [dbo].[Images]([ImageName] ,[ImageFile])VALUES ('Logo', CAST('' AS VARBINARY))

UPDATE [dbo].[Images] SET [ImageFile].Write(0xFF123462ABCE657033ED..........., NULL, 1024 )WHERE [ImageName] = 'Logo'

UPDATE [dbo].[Images] SET [ImageFile].Write(0x5C493D2522B62D1C894E3C..................., NULL, 1024 ) WHERE [ImageName] = 'Logo'.......

For IMAGE data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Use TEXTPTR() to retrieve a VARBINARY pointer.
  
• Then use READTEXT function to read in the data in small pieces.

DECLARE @ptrval varbinary(16), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1 

SELECT @ptrval = TEXTPTR(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @start = 0

While @start < @Len 

BEGIN

    READTEXT dbo.Images.ImageFile @ptrval @start 1024;

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        READTEXT dbo.Images.ImageFile @ptrval @start @end;

        SET @start = @Len

    END

END

• After executing above query and storing the results in a text file,  Construct the Insert statement with a pointer and using UPDATETEXT statement to append the image data.

EXEC(N'INSERT INTO [dbo].[Images] ([ImageName], [ImageFile]) VALUES (''Logo'',0x89504E470...)')

EXEC(N'DECLARE @pv binary(16)

SELECT @pv=TEXTPTR([ImageFile]) FROM [dbo].[Images] WHERE [ImageName]=''Logo''

UPDATETEXT [Report].[Images].[ImageFile] @pv NULL NULL 0x493D2522B62D.......

....

');

Now, I could execute the T-SQL statement created against any new server to upload the image data.

SSIS Eliminating Duplicate Rows using The PARTITION BY clause

Recently I reviewed a post from  SQLServerCentral about how to eliminate the duplicated rows. We used this kind technique in the ETL process to de-duplicate rows from source tables.

Here is the sample code snippet used in OLE DB source :

SELECT

ROW_NUMBER() OVER (PARTITION BY LocationCode ORDER BY SourceID DESC) RowNumber,

SourceID,

LocationCode,

Column1,

Column2

FROM dbo.DimLocation WITH(NOLOCK) WHERE ......

The Row_Number() function will return a number started from 1 based on the PARTITION BY clause and ORDER BY clause. Whenever the column RowNumber is greater than 1 in the result set above is a duplicate row. We could then add a conditional split based on this column to send those rows that having RowNumber > 1 to error flow for review or drop them on the floor.

Should you use the source primary key order ( SourceID column as example above) or others to make the decision in your query?  This is a very import question to ask. You need to know your business rules well  in order to pick the correct data to load into the destination table, otherwise you may drop the right data on the floor during the de-duplicate process.

Here is the post Eliminating Duplicate Rows using The PARTITION BY clause by Suresh Maganti which I encourage you to check out.

Mariner is looking for talent

My employer, Mariner, is currently looking for talent individuals with strong SSIS, SSRS, and SSAS experience.  Mariner is based in Charlotte, North Carolina. 

We are a small company with several SQL MVPs and MCPs who are talented, dedicated, and fun to work with.  If you have experience developing Microsoft BI solutions and like to work in a truly collaborative team environment, then please consider Mariner.

The best way to inquire is through the email address on the Jobs page of the Mariner corporate website.