SQL query to Create Index

Use the following query to create index on a table in SQL Server.

CREATE INDEX IX_Log_timestamp
ON log(timestamp)

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")