Adding Pipe Delimited Option for Report Rendering in SSRS

One of my customers created a report used to send data to another system via a file transfer. The problem is the transfer is not direct and requires a process to convert the CSV to pipe delimited. The system receiving the data has a format it expects that isn’t configurable and must be pipe delimited. Reporting Services comes with the ability to export a comma delimited out of the box, but that’s the only text file option by default.

After a little chat with Mr. Google I found that adding a new data rendering extension in SSRS is quite simple and any text file output you may want is just a change to the reportserver.config file away.

Create Temp table in SQL Server

Example:


select LogID, Severity, Timestamp, Win32ThreadId, Title
into #Start_time
from log(nolock)
where Message like '%ValidateAndActivate%'
and Severity='Start'
and timestamp between '2011-01-29' and '2011-01-30'

failed to validate bam portal website

Open the Server Manager MMC snap-in (right-click on Computer and select the Manage option).
Expand the features node.
Select the Web Server node.
Look through the role services. Check for IIS 6 Management Compatability and see if it says it is installed.
If not installed, click Add Role Services on right of screen. Select the IIS 6 Management Compatability and install.
Go back to BizTalk configuration, the BAM Portal feature should now install without any issues.

In add window components under IIS
select option IIS 6 compantibality.

Failed to connect to the SQL database SSODB on SQL Server

One of the very commone things, but hard to solve because most people don't know the answer. I have searched days for an answer and hopefully this answer is the one for you too.

The best solution I found was to re-register the ssosql.dll on the following manner:



1. start the visual studio command prompt

2. go to the ssosql.dll C:\Program Files\Common Files\Enterprise Single Sign-On

3. regasm ssosql.dll



and probably you are finished.

http://www.biztalkgurus.com/forums/t/14313.aspx

Add/Drop Primary Key

ALTER TABLE T_DeleteMSISDN
add P_id int IDENTITY(1,1)PRIMARY KEY CLUSTERED


Drop Primary Key

ALTER TABLE T_DeleteMSISDN
drop Constraint PK__T_DeleteMSISDN__32E0915F

ALTER TABLE T_DeleteMSISDN
drop column P_ID

Change the alternate background colour of rows in SSRS

Select the row in the report to change the alternate colours:

From properties, click on BackgroundColor and then expression,

Write the following code in the Expression window.

=iif(Rownumber(nothing) mod 2,"White","AliceBlue")

SSRS Report taking long time to load

If stored procedure is working fine in SQL Server but taking long time in SSRS, use the command "With recompile" in stored procedure like:

-----------------------------------------------
Create PROCEDURE [dbo].[spGBRGetAllCallRecords]
@pMSISDN varchar(50),
@pStartDate datetime,
@pEndDate datetime

with recompile
AS
SET FMTONLY OFF

---------------------------------------------