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

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

Get Missing Indexes from database on SQL Server

Get Missing indexes from a Database in SQL Server:


Create Proc p_getMissingIndexes
(@Databasename varchar(50)='GBRView')

as

--Declare @cmdstr as varchar(100)
--SELECT @cmdstr = 'use ' + @Databasename
--EXEC(@cmdstr)


SELECT --DB.name [Database],
D.statement
+ ' (' + ISNULL (D.equality_columns,'')
+ CASE WHEN D.equality_columns IS NOT NULL AND D.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (D.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + D.included_columns + ')', '') AS [Index Detail],

column_name, unique_compiles, avg_user_impact, last_user_seek, user_seeks, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
Inner join master.dbo.sysdatabases(nolock) DB on D.Database_id = DB.[DBID]
CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
where lower(DB.name) = lower(@Databasename)
ORDER BY D.index_handle, [statement];

Get Used indexes from SQL Server

use this Stored Procedure to get Used Indexes from a Database on SQL Server:


Create proc P_GetUsedIndexes (@Databasename as varchar(50))

As

DECLARE @cmdstr varchar(100)

SELECT DATABASE_ID,
DB_NAME(DATABASE_ID) AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
INDEX_NAME = (SELECT NAME
FROM SYS.INDEXES A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.INDEX_ID = B.INDEX_ID),
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS B
INNER JOIN SYS.OBJECTS C
ON B.OBJECT_ID = C.OBJECT_ID
WHERE --DATABASE_ID = DB_ID(DB_NAME()) AND
DATABASE_ID = (select [dbid] from master.dbo.sysdatabases(nolock) where name=lower(@DatabaseName)) AND
C.TYPE <> 'S'

Send Email from SQL server

exec master.dbo.xp_sendmail
@recipients ='myasin@clash-media.com',
@subject = 'testing SQL Server Emails',
@message = 'Free space on Z Drive has dropped below 2 Gigabyte'

will get error message if on 64-bit version:
"SQL Mail does not work with the 64-bit version of SQL Server"

Instead Can use:
Profile Name is the setting in:

[database] -> Management -> Database Mail -> Configure Database Mail


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServerDatabaseMailProfile',
@recipients = 'muhammad.yasin@clash.com',
@body = ' is scheduled to be renewed in 120 days - GET TO WORK!',
@subject = 'testing by yasin'

Get all Active Users, Query detail and Kill active session

Find out who is currently connected with SQL Server and is Active.

sp_who2 active

To get the detail of one particular process ID
sp_who2 96

Get the detail of query running by any process ID found by above query:

dbcc inputbuffer(96)

To Kill session use:

Kill 96

Get Free Space Available on Database server

Use this SQL Command to find the free space available on each drive on hard disk of database server:

Exec master.dbo.xp_fixeddrives