Exists Function – MDX Query

In MDX query is we want to find that particular set or tuples exists in the given second set ,we can use this function
If we provide optional measure group then it will also check the tuples has non NULL data in that measure group

Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )

Examples

Here in this example if the year value is between 2003 to 2004 we want to show Inside else we want to show Outside.
So we have to check that every year value is between 2003 and 2004 or not.

with member [Measures].[test]
as
iif(isempty((exists ([Date].[Fiscal].currentmember,{[Date].[Fiscal].
[Fiscal Year].&[2003]:[Date].[Fiscal].[Fiscal Year].&[2004]}).item(0),
[Measures].[Internet Sales Amount])),”Outside”,”inside”)

select {[Measures].[Internet Sales Amount], [Measures].[test]} on 0 ,
[Date].[Fiscal].[Fiscal Year] on 1
from
[Adventure Works]

How to delete job history – sp_purge_jobhistory

sp_purge_jobhistory
We can use this procedure to delete old history for jobs.

sp_purge_jobhistory
[ @job_name
or [ @job_id ,
@oldest_date
We can specify either job_name or job_id but not both.

We can get job_id from msdb.dbo.sysjobs table . job_id is uniqueidentifier.

If we specify @oldest_date then it deletes all his troy before this date else it will delete all job history.

Example

To delete all history for a specific job

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory
@job_name = N’Test job’ ;

To delete history for a specific job upto specific date

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory
@job_name = N’Test job’ ,
@oldest_date = ‘2008-10-01’

To delete history for all jobs

EXEC dbo.sp_purge_jobhistory

Troubleshooting Database Mail

Here I am giving some step to troubleshoot Database Mail related problems

Cant find sp_send_dbmail procedure
This procedure is in MSDB database.

User does not have permission on sp_send_dbmail
Run
EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’,
@membername = ” ;

Service broker is needed to activate external program which sends queued mails, else mail will be queued but will not be deliverd.

To check if service broker is enabled we need to run

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’

If its not enabled , we need to enable by this query.

ALTER DATABSE MSDB SET ENABLE_BROKER

If database mail procedures are not enabled enable it using surface area configuration

To check status of database mail procedures

EXECUTE dbo.sysmail_help_status_sp ;

To start database mail

EXECUTE dbo.sysmail_start_sp ;

SQL Server is using external application Databasemail90.exe , which connect to server using windows authentication and send mail

We can find it on
MSSQL.3\mssql\binn

Even after this if Database Mail does just queued mails and do not send them.
We have to check by running this application manually. If it sends mails then it means service broker is not starting this application.

Run this code in MSDB

ALTER QUEUE [InternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_ExternalMailQueueListener],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);

ALTER QUEUE [ExternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_sysmail_activate],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)

sp_addlinkedsrvlogin – To modify or add new login on linked server

sp_addlinkedsrvlogin

To modify or add new login for linked server

We all know we are creating linked server run queries on remote server. Then using four part naming convention we can run query on remote servers.
As a DBA we have to periodically change password for logins on servers. If we change password for logins on remote serves we also have to update it on linked servers we have created on other servers for it.

sp_addlinkedsrvlogin can solve this purpose. It is used to add new login or modify existing login .

sp_addlinkedsrvlogin @rmtsrvname =
, @useself =
, @locallogin =
, @rmtuser =
, @rmtpassword =

Ex.
if we have changed password for a specified login on a remote server and we want to update its password on linked server

sp_addlinkedsrvlogin @rmtsrvname =’192.168.0.51′
, @useself = ‘FALSE’ –specify false when using remote login and password
, @locallogin = null — not used because we are not mapping remote and local login
, @rmtuser =’sa’
, @rmtpassword = ‘test123’

This will update password for existing login .

If we want to map a login to other login on remote server

EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘test server’
, @useself = ‘false’
, @locallogin = ‘TestDomain\ABC’
, @rmtuser = ‘MaryP’
, @rmtpassword = ‘d89q3w4u’

More details we can find on BOL for it.