Skip to main content

Posts

Showing posts from March, 2012

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
FROM
msdb.dbo.sysmail_event_log 
ORDER BY
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error descript…

Extracting QuickBooks data using SSIS Package...

To extract data from QuickBooks, I used QODBC driver.
You can download a trial version of it using below linkhttp://qodbc.com/default.htm
When we installed the QODBC driver, if ODBC entry is not available for it, we can add it as System Data Sources. For that go to the Control Panel --> Administrative Tools -->Data Sources(ODBC)
Then you will get the ODBC Data Source Administrator window. In that click on the System DSN and you will get a screen as below
In that click on the add button and select the QODBC Driver for QuickBooks as shown in the below figure.
Then you have to configure the ODBC connection. Initial set up window is as given below.
If you want to set a different company profile, click on the Browse button and select the qbw file as shown in the below figure.
Then you can use the created ODBC connection in SSIS package.
Create a new Integration Service Project and add a Data source. In the New Data Source window, select the Provider asOdbc Data Provider as in the below figu…

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.
SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))
Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.
Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gi…

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.

Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

FROM clause have the same exposed names...

When I tried to get the matching item records from two databases, I got the below error.
The objects "Database1.dbo.Sales" and "Databse2.dbo.Sales" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
The query used was SELECT DISTINCT Database1.dbo.Sales.ItemName FROM Database1.dbo.Sales INNER JOIN Databse2.dbo.Sales ON Databse1.dbo.Sales.Id=Databse2.dbo.Sales.Id

It is because that the table name is same in those two databases. To avoid it we have use aliases.
The query is shown below
SELECT DISTINCT Table1.ItemName FROM Databse1.dbo.Sales AS Table1 INNER JOIN Databse2.dbo.Sales ON Table1.Id=Databse2.dbo.Sales.Id
Databse1.dbo.Sales AS Table1 will fix the error.