Friday, September 18, 2009

An error of this form appears while trying to start/delete/restart the BizTalk application in Administration console

FAQ

An error of this form appears while trying to start/delete/restart the BizTalk application in Administration console:

"Could not store transport type data for Receive Location 'Recv.Loc' to config store. Primary SSO Server 'Sql-server' failed. The external credentials in the SSO database are more recent."

Error details are:

SSO AUDIT
Function: SetConfigInfo
Tracking ID: df1dd0b0-c9d5-4012-bb97-336aa8df78b3
Client Computer: BTS06-Platform.contoso.com (mmc.exe:1884)
Client User: BTS06-PLATFORM\Administrator
Application Name: {D2241406-0767-4C13-98EB-43EECE80F8A0}
Error Code: 0xC0002A40, The external credentials in the SSO database are more recent.

Description

This error arises when the system date is not correct.

· Run the following query in SSODB database.

select dateadd(m,-1,ec_timestamp),* from SSODB..SSOX_ExternalCredentials
where datediff(m,ec_timestamp,getdate())<>0

select dateadd(year,-1,ec_timestamp),* from SSODB..SSOX_ExternalCredentials
where datediff(year,ec_timestamp,getdate())<>0

· If any rows are retrieved then there has been some changes in the system date. Run the following query in case any rows are retrieved in the first case.

update SSODB..SSOX_ExternalCredentials set ec_timestamp = dateadd(m,-1,ec_timestamp) where datediff(m,ec_timestamp,getdate())<>0

update SSODB..SSOX_ExternalCredentials set ec_timestamp = dateadd(year,-1,ec_timestamp) where datediff(year,ec_timestamp,getdate())<>0

· Now restart Host Instances and continue with the application.

Thursday, September 3, 2009

For sending mail from SQL u need to do 3 steps
1)Enable the SQL to send mail
2)Configure the Account and mail server details
3)Send mail


Step 1: Enabling the SQL to send mail
What i like about sql is it gives query to do everything no need to go for searching the option where its these in which menu and all
so here is the query to enable
--=======Enabling the SQL to send mail==============
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2 Configuring the mail account
Once again some bunch of query
--========Add the account====
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'BtAdmin', ---Account Name
@description = 'Mail account for Database Mail For BizTalk', --Short Note
@email_address = 'abc@gmail.com', --Mail Address to display /From address
@display_name = 'TIG Btadmin', --Display name in the mail header
@username='mdhusenm@BlastAsia.com', --User Name for login to mail server
@password='abcd', --Passwod for account
@mailserver_name = 'smtpout.secureserver.net' --SMTP mail server

-================Add the Profile===============
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'BtAdminProfile', --Profile Name
@description = 'BizTalk Profile used for database mail' --Short Note

--================Connect Account To profile=============
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'BtAdminProfile',
@account_name = 'BtAdmin',
@sequence_number = 1
--=====================Enable Profile to send==========
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'BtAdminProfile',
@principal_name = 'public',
@is_default = 1 ;

Step 3: Send mail
Use the SP msdb.dbo.sp_send_dbmail to send mails
declare @body1 varchar(100)
set @body1 = 'Html body''


EXEC msdb.dbo.sp_send_dbmail
@recipients='mdhusenm@BlastAsia.com', ------To mail address
@subject = 'My Mail Test', ------Subject Of mail
@body = @body1, ------Body of mail
@body_format = 'HTML' -----Formate of mail body HTML means the body will be displayed as HTML see my mail body it will display HTML

Remember Step 1 & 2 are only one time activity

You can test this by giving SMTP as Gmail SMTP if its not blocked in ur company or just use company ID