SQL Hyenas

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.

I have started working on MDX queries also. Basic syntax looks like SQL queries but still it is far different than SQL queries. While working I found some difficulties to apply some tricky logic in queries, so I thought to jot down the tricks for others will helpful for some one. So I am hopping that you should know the basic logic to write MDX queries and below are some defined situation and its logic. Checking which hierarchy is selected on Axis: CASE     when NOT IsError(Extract( Axis (0), [Dim Date].[Calendar_Year]). Count ) then (Formula for Calendar year hierarchy ) when NOT IsError(Extract( Axis (1), [Dim Date].[Calendar_Year]). Count ) then ( Formula for Calendar year hierarchy ) when NOT IsError(Extract( Axis (0), [Dim Date].[Fiscal_Year]). Count ) then ( Formula for Fiscal hierarchy ) when NOT IsError(Extract( Axis (1), [Dim Date].[Fiscal_Year]). Count ) then ( Formula for Fiscal hierarchy ) else '-1' END H...

Sometimes we need logic where you want to hold multiple row values in single Comma separated string. Previously we were using cursor function to achieve this functionality but now a days in SQL 2012 we can achieve this functionality in simple and faster way. Below are some tricks and function by using them you can achieve the functionality of converting multiple rows in one comma separated string and converting comma separated string into multiple rows. Converting multiple rows in one comma separated string 1. Create Temp Table: Create one temp table and insert sample record in it: 2. Query: Create table #temp(email varchar(50)) insert into #temp values ( 'a.@gamil') insert into #temp values( 'b.@gamil') insert into #temp values( 'c.@gamil') Select * from #temp 3. Write Sql  COALESCE  function for comma separated string DECLARE @Names VARCHAR(8000)   SELECT @Names = COALESCE(@Names + ',', ...

Introduction SQL Server 2008 Full-Text Search feature can be used by application developers to execute full-text search queries against character based data residing in  a SQL Server table. To use full text search the developer must create a full-text index for the table against which they want to run full-text search queries. For a particular SQL Server Table or Indexed View you can create a maximum of one Full-Text Index. The full-text index can be created for columns which use any of the following data types - CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT, VARBINARY, VARBINARY (MAX), IMAGE and XML. Each full-text index can be used to index one or more columns from the base table, and each column can have a specific language which is supported by SQL Server 2008 Full-Text Search. Full-Text Search in SQL Server 2008 supports more than 50 different languages such as Arabic, Chinese, English, Japanese and Spanish etc. For the complete list of supported full-text languages, run th...

Using SQL Server you can send Database mails. DB Mails are more secure that SQL mails because mails are in encrypted format. This new future in Database is includes after SQL Server 2005. Database Mail has many enhancement over SQL Mail. This mail is depend on SMTP (Simple mail transfer protocol). Database mail depends on Service Broker so this service must be enabled for Database Mail. Here we will learn how to configure database profile to send database mails. Step 1: Configuration Email to send mail:     Connect to Database Engine .     Expand Management Section .      Double click on Database Mail and click Next.      Select Manage Database Mail account and Profiles and Next.      Select Create New Account.      Add Account Name, Description, Email Address, Server Name and Port Number. For Gmail Server Name : smtp.Gmail.com Port Number :...