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

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.
  • Trigger for new Database

    I got a mail asking for trigger on new Database

    I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please?

    How to find Index information - sp_msindexspace,sp_helpindex,sp_mshelpindex

    Index are very important part for any database.
    We need to regularly monitor index and maintain index for better performance of the system.
    I have already written an article on how to find highly used index

    sp_changesubscription for changing subscriber password

    For DBA its a routine activity to change server passwords.
    If we are using replication between servers and if we are changing password of subscribers
    we have to change it on publisher also.

    we can use sp_changesubscription here to change password for the subscriber.

    We can use it for changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication

    set context_info -- Option to get parameter value during session across multiple batches

    /*some time we need to use parameter values to be available for multiple bathces in same session
    We can use context_info this case. Its value is available during current session and we can get its info from


    Maintenance :- Job History (sp_delete_backuphistory)

    Recently for one of my client I found that his backuphistory table has become very large. Since SQL Server do not clear backupjob history we need to clear it regular interval

    MDX:-Total of last N Days from Today.

    Since I dont have forums on this blog.I would like to post the problems by mail using blogs.
    Recently I got a problem to get sum of last N days from current Date.

    Table Partitions

    Table Partition is a new feature in SQL Server 2005.
    Here I am posting a sample code how to create a Partitioned table and view its meta data in system tables.
    /* We need to create a partition scheme and function on which we will partition the data */

    SQL 2008: Trace process in activity monitor

    In SQL Server 2008 when we open activity monitor we can see SPID and information related for that SPID . But what if we want to get detail information for that SPID.

    Whats new SQL Server Express 2008

    We all know SQL Server Express 2008 is coming with many new features.
    we can get some cool information for this from here



    script for insert data sql statement

    Some times we need insert into select statement for tables from one database to other database. Here is the script which will create the script for this process.

    How to get size info for database file using dbcc showfilestats

    CREATE TABLE freefilespace
             fileid       INT,
             filegroup    INT,
             totalextents INT,
             usedextents  INT,
             dbname       VARCHAR(256),
             filename     VARCHAR(2000)

    Undocumented Commands

    Note:-All the samples here are run against AdventureWorks sample DB

    DBCC proccache

    Provides procuedure cache information

    lets play with XML

    Here is Nested XML which contains data from 2 tables ,Customer and Order
    I want to get data for this 2 tables from XML

    New features in Named Sets in Analysis Service 2008

    One of the important feature in SQL Server 2008 is Dynamic Named Sets. Till SQL Server 2005 named sets were static. In SQL Server 2005 once named sets are executed they were remain static during whole session or query or cube. This was giving wrong results during Topcount or Bottomcount like functions.

    /* How to get linked values in one group using CTE*/
    create table table_a
    (col1 varchar(100),
    col2 varchar(100))

    Session propery SQL Server 2005

    In sql server we can get session property many ways
    1. dbcc useroptionsdbcc useroptions
    This will give us information about all the enabled setting for that session.
    In SQL Server 2005 two new options are available
    2. sessionproperty function

    Popular Posts