SSMS : SQL server DB-Mail configuration using G-Mail

We can configure the gmail in our SQL server to send the emails to report the problems in the SQL server. We can configure any email services to SQL Server. All we need is information about the SMTP server name, Port number and whether email services using SSL connection or not. Following steps are used to configure email in SQL server.

Steps:

1. Check with “Sp_Configure”. You will notice that config_values and run_Values as 0. It means that email service from sql server is not enabled.

clip_image002[4]

2. Run the following Script to enable the email services in sql server

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

clip_image004[4]

3. Connect to SQL server database engine and go to Management

4. Right click on the “Database Mail” and click “Configure Database Mail”

clip_image006[4]

5. You will be shown up with the following wizard. Just skip that by clicking “Next”

clip_image007[4]

6. Select “Manage Database Mail Accounts and Profiles” and click “Next”

clip_image009[4]

7. Select “Create Profile” and click “Next”

clip_image011[4]

8. Enter the Profile Name and Description

clip_image013[4]

9. Click “Add” to Add SMTP accounts

10. You will be prompted with the following screen and Add your credentials. For gmail use the following as per screenshot.

i. email Address: yourusername@gmail.com

ii. Display Name: Your name

iii. Server name: smtp.gmail.com

iv. Port Number: 587 [Important note : if you do internet search to find out the port number, they refer with 465, but sql server is not recognizes this port. So use 587]

v. Check this server uses the SSL Connection

vi. In SMTP Authentication, Select “Basic Authentication”. Provide your Gmail username and password.

clip_image015[4]

11. You will be shown up with the following screenshot. Click “Next”

clip_image017[4]

12. You will get the success message

clip_image019[4]

13. Click close

To Test the email is working fine:

1. Go to Management

2. Right click on the “Database Mail”

3. Click “Send Test E – mail…..”

clip_image021[4]

4. Login to your gmail account and check you received an email from your sql server.

clip_image023[4]

Queries to verify to check the status of the email:

USE msdb ;
GO

SELECT * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems

--To find out failed mail items with description on it
SELECT items.subject,
    items.last_mod_date
    ,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id

Hope this helps Smile

T-SQL: Dynamic columns for Pivot

T-SQL’s Pivot query rotates the rows into the columns and perform the aggregations like the pivoting using the Excel. Problem here is we should be aware columns in the output. We had the requirement that column names should be dynamic. following example illustrate how to retrieve dynamic column names. 

Prepare Data:

Following query is used to prepare the tables.

CREATE Table ProductInformation
(
      CategoryName varchar(100 ),
      ProductName varchar(100 ),
      Price decimal(16 ,2)
)

-- Insert Category X
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('x' ,'Product A', 10)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('x' ,'Product B', 20)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('x' ,'Product C', 35)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('x' ,'Product D', 50)

--Insert Category Y
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('y' ,'Product M', 21)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('y' ,'Product N', 22)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('y' ,'Product O', 23)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('y' ,'Product P', 35)
INSERT into ProductInformation(CategoryName ,ProductName, Price) Values('y' ,'Product Q', 7000)

Output of ProductInformation

ProductInformationOutput

Dynamic Column names:

We have created the variable to save the category information and pass it on to the Pivot query which creates the columns dynamically. Look at the following code and output. For given category “Y”.

Declare @CategoryName varchar (100)

Set @CategoryName= 'x'

DECLARE @cols AS NVARCHAR( MAX),
    @query  AS NVARCHAR (MAX)

select @cols = STUFF(( SELECT distinct ',' + QUOTENAME (ProductName)
                    from ProductInformation where CategoryName=@CategoryName
            FOR XML PATH( ''), TYPE
            ).value( '.', 'NVARCHAR(MAX)')
        ,1, 1,'' )

set @query
      = 'SELECT CategoryName,' + @cols + ' from
         (
            SELECT CategoryName, Price, ProductNAme
            FROM ProductInformation where CategoryName=''' + Cast(@CategoryName as varchar (20)) +''')
          x
         pivot
         (
            SUM(Price)
            for ProductName in (' + @cols + ')
         ) p '

execute(@query )

Output for Y:

PivotOutputForY

Output for X:

PivotOutputForX

Look at the column names carefully, we have received different names and respective prices.

Hope this helps someone Smile.

SSMS : Preventing from Saving Table using designer

Sometime, we wanted to change the table design by renaming the column name, changing the data type and resizing the data type.  After changes are applied and change the save the table, you will get the dialog box saying “Saving is not permitted”. For example, I wanted to change the “Product” table in the “Adventure Works” database. I am changing the Name column from nvarchar(50) to nvarchar(100).

DatatypeChange

When you try to save you will get the following dialog box.

image

We have the data in the table. We don’t want to drop and recreate the table for this small change.

Configuration in SSMS:

  1. Click on Tools –> Options.
  2. Click on Designer. You will design the following screen.
  3. Uncheck “Prevent saving changes that requires table re-creation”. Take a look at the following screen.

PreventTableSaving

Now, you could save the table without any problem. If there are any dependency table, the dialog box will appear which warns about affected table, but this one wont harm anything.

Hope this helps someone.

Thanks,

Gowdhaman

Running SSIS package using Script component

We can run the SSIS package in many ways using SSIS package. If you have lot of child package to be called in your master package, We can use the “Execute Package Task”. But if you have to pass some dynamic variables to be passed from the parent package to the child package. We can use the “Script Component” to run the SSIS package. We have Dts.Variables to assign the variables dynamically. Following are Steps.

  1. Create SSIS package
  2. Drag “Script Component” to control flow.
  3. Double click on “Script Component”. You can see “ReadOnlyVariables” and “ReadWriteVariables”. To test, we are selecting two variables.
    1. ChildPackageParam
    2. ChildPkgLocation
  4. If you have any parameters to pass, then you can use PackageParam. Otherwise, we don’t need to map package parameter.

Following is the code used to run the SSIS package using the script task.

public void Main()
        {
            string pkgLocation = Dts.Variables["ChildPkgLocation"].Value.ToString();

            try
            {
                Package pkg;
                Microsoft.SqlServer.Dts.Runtime.Application app;
                DTSExecResult pkgResults;
                app = new Microsoft.SqlServer.Dts.Runtime.Application();
                pkg = app.LoadPackage(pkgLocation, null);

                pkg.Variables["Varaible1"].Value = Dts.Variables["ChildPackageParam"].Value.ToString();

                pkgResults = pkg.Execute();

                bool isFieragain = false;
                
                Dts.Events.FireInformation(0, "Calling package", String.Format("Package Execution successfull and package path is:[{0}]", pkgLocation), string.Empty, 0, ref isFieragain);

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Calling package", String.Format("Error in calling your master package:[{0}]", ex.Message), string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

Hope this helps.

Thanks,

Gowdhaman

SSIS : FTP Task–Step by Step

FTP task is used to do file operations using SSIS in our remote ftp. We can see more information about FTP task in technet. Following is the step by step walkthrough on how to configure your FTP task to send a file from local system and remote ftp.

  1. Right click on the connection manager and click “New connection”. You will be prompted with the following screen.

clip_image001

  1. Select “FTP” and click “Add” button. You need to enter the credentials, if you have any specific ftp portal to be used you can contact your customer or administrator for the following details. In my case, I have configured it locally.
    • Server name
    • Port name [ if they configured any]
    • User Name and password of portal to connect. Please check with your customer about the permission [read / write]. You need to be sure that your going to download files, send the files , deleting the file in your ftp portal and get appropriate permission.
    • Others are optional parameters, if you want to configure about retries and timeout, you can do it here.

clip_image002

  1. Drag your “FTP task” into your “control flow”.
  2. Click on the “FTP task” to configure. If you want to set package to fail on FTP connection problem, you can set the property “StopOnFailure” to true. Take a look at the following screenshot.

clip_image003

  1. Click on the “File Transfer”. Need to mention the local and remote path of the FTP to do the file operations.
    • Local parameter, If we are preparing the file information in the variables, we need to set to true for “IsLocalPathVariable” to true. Otherwise create new file connection.
    • Operations, Select the required operations you want to do. In my case, I wanted to send my local file to the remote FTP, so I have configured it for “Send files”
    • Remote parameter, If we are preparing the file information in variable, we need to set the “IsRemotePathVariable” to false. Otherwise click on the RemotePath, you will be notified the folder structure inside the remote FTP.

Important:

Remote path will not take a full path like “ftp://localhost/<YourFolder>”. So if your using the variable, use it like “/<YourFolder>”.

clip_image004

Testing:

Package looks like follow. I have done task to send a local file to the remote ftp site.

clip_image005

I am sending the files from my local machine to the remote site ftp://localhost/TestXml/. , once I run file is send to the portal. Please take look at the following screenshot

clip_image006

Other implementations:

  1. We can send all the files in the folder using the for each loop container and call the FTP task to send the files into remote ftp.
  2. We can download the file and move the file to our local environment and use it for the ETL purpose.

Thanks,

Gowdhaman

SSRS Parameter pattern validation

One of our client wanted to validate SSRS parameters.If parameter is “Non-Alphanumeric”, then we need to write the message saying that “You have entered the Non Alphanumeric data [Value entered in the parameter], which is invalid” We have achieved as follows. Otherwise report should return the data properly.

We have added the Text box in the SSRS report body and entered the following expression.

= IIF(Parameters!Param1.Value like &quot;*[a-z][0-9]*&quot; OR Parameters!Param1.Value like &quot;*[0-9][a-z]*&quot;,string.Empty,string.Format(&quot;You have entered the Non Alphanumeric data [{0}], which is invalid&quot;,Parameters!Param1.Value))

image

Thanks

Gowdhaman

SSAS- Clearing Cache

To measure the performance of the query. We need to clear the SSAS cache, because the recent result will be stored in the cache memory. So the MDX query will quickly return the result set. To find out the correct timing of the query run. We need to clear the cache. Following is the XMLA query, will just work on the MDX query window.

XMLA Query:

&lt;Batch xmlns=&quot;http://schemas.Microsoft.com/analysis services/2003/engine&quot;&gt; 
   &lt;ClearCache&gt; 
      &lt;Object&gt; 
         &lt;DatabaseID&gt;Database ID&lt;/DatabaseID&gt; 
         &lt;CubeID&gt;Cube ID&lt;/CubeID&gt; 
      &lt;/Object&gt; 
   &lt;/ClearCache&gt; 
&lt;/Batch&gt; 

How to find the Database ID:

  1. Click on the Analysis service database name
  2. Click on the properties

image

We can use the same way to fetch the Cube ID from the Cube properties.

Thanks,

Gowdhaman Smile