SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

SQL Server – Configure Database Mail to Send Email Using Gmail or Live Account

Posted by Prashant on May 24, 2013


In this post we will see how to configuring SQL Server Database Mail for sending mails using Gmail Account or Live Mail account. By using Database Mail a database application can send e-mail messages to users with SQL query results and also include additional attachment files in either plain text or html format.

In order to configure the database mail here are the steps:

1. Connect to Object Explorer → Expand Management folder → Right click  on Database Mail → Select Configure Database Mail

dbmail1

2. Click Next → In select configuration task window select Set up Database Mail by performing following tasks:

dbmail3

3. Click Next → In new profile window enter Profile Name and Description to identify the profile → Click Add…

dbmail4

4. In Add Account to Profile…. window select New Accoount

dbmail5

5. In New Database Mail Account window provide details of the email account which you want to use to send emails.

dbmail6

If you are using gmail account:

Account Name: Gmail
Description: Gmail account used for db mail
E-mail Address: emailid@gmail.com
Display Name: DBMail (you can specify whatever name that suits you)
Reply e-mail: emailid@gmail.com (any e-mail account you like to use)
Server Name: smtp.gmail.com
Port Number: 587
Check the This Server requires a secure connection (SSL)
Select Basic Authentication and provide your user name and password for the outgoing email account that you used the above step
Click OK → Next

If you are using Live mail account

Account Name: LiveMail
Description: Live mail account used for db mail
E-mail Address: emailid@live.com
Display Name: DBMail (you can specify whatever name that suits you)
Reply e-mail: emailid@live.com (any e-mail account you like to use)
Server Name: smtp.live.com
Port Number: 25
Check the This Server requires a secure connection (SSL)
Select Basic Authentication and provide your user name and password for the outgoing email account that you used the above step
Click OK → Next

More detail about smtp server setting refer Gmail SMPT Settings and Live SMTP Settings

6. In Manage Profile Security you can select the profile that you wants to make as public profile which can be accessible by all users of any mail host database.

dbmail8

7. Configure System Parameters here you can modify the default parameter settings as per your need e.g restricting file types not to allow in mail attachment, max size of file…etc.

dbmail9

8. Click Next → Finish

Upon on finish it will configure the database mail settings.

dbmail10

9. Click Close and you are done with the database mail configuration.

10. Once this is done try to send a test mail to check the email.

dbmail11 dbmail12

Here the email will be queued then will be processed by database mail. If you find this post helpful feel free to comment and share.

Posted in SQL Server | Tagged: , , , , | 1 Comment »

SQL Server – Date Formats

Posted by Prashant on May 22, 2013


Database developers commonly ask for date formats while comparing date columns, showing dates in different format and etc using SQL query. In this post we will see a consolidated list for various date formats that are available and we use in our queries.

Format

Standard

Query

Output

Mon dd yyyy hh:mmAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 0) May 22 2013  8:54PM
Mon dd yyyy hh:mmAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) May 22 2013  8:55PM
mm/dd/yy U.S SELECT CONVERT(VARCHAR(8), GETDATE(), 1) 05/22/13
mm/dd/yyyy U.S SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 05/22/2013
yy.mm.dd ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) 13.05.22
yyyy.mm.dd ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) 2013.05.22
dd/mm/yy British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) 22/05/13
dd/mm/yyyy British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) 22/05/2013
dd.mm.yy German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) 22.05.13
dd.mm.yyyy German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) 22.05.2013
dd-mm-yy Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) 22-05-13
dd-mm-yyyy Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 22-05-2013
dd mon yy - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) 22 May 13
dd mon yyyy - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) 22 May 2013
Mon dd, yy - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) May 22, 13
Mon dd, yyyy - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) May 22, 2013
hh:mm:ss - SELECT CONVERT(VARCHAR(8), GETDATE(), 8) 21:04:29
hh:mm:ss - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 21:04:58
Mon dd yyyy hh:mm:ss:mmmAM (or PM) Default + seconds + miliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 9) May 22 2013  9:08:20:677PM
Mon dd yyyy hh:mm:ss:mmmAM (or PM) Default + seconds + miliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 109) May 22 2013  9:08:32:853PM
mm-dd-yy USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) 05-22-13
mm-dd-yyyy USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) 05-22-2013
yy/mm/dd Japan SELECT CONVERT(VARCHAR(8), GETDATE(), 11) 13/05/22
yyyy/mm/dd Japan SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 2013/05/22
yymmdd ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) 130522
yyyymmdd ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) 20130522
dd mon yyyy hh:mi:ss:mmm Europe SELECT CONVERT(VARCHAR(30), GETDATE(), 13) 22 May 2013 21:11:25:890
dd mon yyyy hh:mi:ss:mmm Europe SELECT CONVERT(VARCHAR(30), GETDATE(), 113) 22 May 2013 21:11:36:110
hh:mm:ss:mmm - SELECT CONVERT(VARCHAR(16), GETDATE(), 14) 21:11:53:673
hh:mm:ss:mmm - SELECT CONVERT(VARCHAR(16), GETDATE(), 114) 21:12:40:130
yyyy-mm-dd hh:mi:ss - SELECT CONVERT(VARCHAR(19), GETDATE(), 20) 2013-05-22 21:13:15
yyyy-mm-dd hh:mi:ss - SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 2013-05-22 21:13:25
yyyy-mm-dd hh:mi:ss.mmm - SELECT CONVERT(VARCHAR(23), GETDATE(), 21) 2013-05-22 21:13:34.970
yyyy-mm-dd hh:mi:ss.mmm - SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 2013-05-22 21:13:45.093
mm/dd/yy hh:mm:ss AM (or PM) - SELECT CONVERT(VARCHAR(20), GETDATE(), 22) 05/22/13  9:14:09 PM
yyyy-mm-dd - SELECT CONVERT(VARCHAR(26), GETDATE(), 23) 2013-05-22
hh:mm:ss - SELECT CONVERT(VARCHAR(8), GETDATE(), 24) 21:14:31
yyyy-mm-dd hh:mm:ss:mmm - SELECT CONVERT(VARCHAR(26), GETDATE(), 25) 2013-05-22 21:15:21.130
yyyy-mm-dd hh:mm:ss:mmm ISO8601 SELECT CONVERT(VARCHAR(27), GETDATE(), 126) 2013-05-22T21:15:36.543
yyyy-mm-ddThh:mi:ss.mmm ISO8601 with time zone SELECT CONVERT(VARCHAR(30), GETDATE(), 126) 2013-05-22T21:34:57.127
dd mon yyyy hh:mi:ss:mmmAM(Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.) Hijri SELECT CONVERT(NVARCHAR(50), GETDATE(), 130) 13 رجب 1434  9:18:26:580PM
dd/mm/yy hh:mi:ss:mmmAM(Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.) Hijri SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 13/07/1434  9:15:58:400PM

Refer msdn for more details on date formats.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

SQL Server – Track Database Size Growth Trend

Posted by Prashant on February 13, 2013


One of my DBA friends asked me to if we can track the size growth of a production database happened on daily basis. However looking into the system tables, there is no option to get database growth over a period of time. So to achieve this report we need to have the database size information collected daily or what ever time frame we need the growth report. In this port we will go step by step for this task.

First create a table to keep the size information of each database with a date on which the information is collected:

CREATE TABLE DatabaseFileSize
(
[database_id] INT,
[file_id] INT,
[file_type_desc] NVARCHAR(120),
[name] NVARCHAR(128),
[physical_name] NVARCHAR(520),
[state_desc] NVARCHAR(120),
[size] INT,
[max_size] INT,
[growth] INT,
[is_sparse] BIT,
[is_percent_growth] BIT,
[collect_date] DATETIME
)
GO

Create a stored procedure to populate this table with the current database size information:

CREATE PROC usp_util_CollectDatabaseSize
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DatabaseFileSize
(
[database_id],
[file_id],
[file_type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
[collect_date]
)
SELECT
[database_id],
[file_id],
[type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
GETDATE()
FROM sys.master_files
SET NOCOUNT OFF
END
GO

Now we need to populate the table over a period of time, so we need to create a SQL Agent job to execute this stored procedure and schedule this job to run everyday.

USE [msdb]
GO
/****** Object:  Job [Collect_Database_Size_Info]    Script Date: 02/13/2013 23:47:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/13/2013 23:47:43 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect_Database_Size_Info',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Domain\User', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBSize]    Script Date: 02/13/2013 23:47:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBSize',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC usp_util_CollectDatabaseSize',
@database_name=N'AuditDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyDBSize',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'445fb175-517b-4221-b6b8-6c6877dc7c24'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now we have the information over the period of time and we can run our below query to make a report out of this data.

Track the whole database size growth trend over a period of time:

--DB Size Growth Trend
SELECT
DB_NAME(database_id) AS DatabaseName,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS DBSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,collect_date
ORDER BY DatabaseName,collect_date

Track the log file and data file(s) size growth trend over a period of time:

--DB File Size Growth Trend
SELECT DB_NAME(database_id) AS DatabaseName,
(CASE file_type_desc
WHEN 'ROWS' THEN 'Data'
WHEN 'LOG' THEN 'Log'
END) AS FileType,
physical_name AS PhysicalPath,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS FileSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,physical_name,file_type_desc,collect_date
ORDER BY DatabaseName,collect_date

Posted in SQL Server | Tagged: , , | Leave a Comment »

SQL Server – Search Text in Stored Procedure

Posted by Prashant on February 8, 2013


Here is the query to search text in stored procedure definition:

DECLARE @SearchText NVARCHAR(500)
 SET @SearchText = 'Hierarchy'
SELECT DISTINCT
 o.name AS [Object Name],
 o.type_desc AS [Object Type],
 m.definition AS [Definition],
 m.uses_quoted_identifier
FROM sys.sql_modules m
INNER JOIN sys.objects
 o ON m.object_id=o.object_id
WHERE m.definition LIKE '%' + @SearchText + '%'
 AND o.name LIKE 'usp_%'

Posted in SQL Server | Tagged: , , , , , | Leave a Comment »

SSIS – Error: Could not get a list of SSIS packages from the project

Posted by Prashant on January 28, 2013


While working on a ssis project recently I was trying to build the solution however it was failing again and again without showing any error in Error List !!! and when I checked the Output List it was saying the below message (refer Fig-1).

Error: Could not get a list of SSIS packages from the project.
===== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped =====

SSIS-Error-CantGetPackageList

Fig-1

This message says there is some missing objects in the project (.dtproj), as CreateDeploymentUtility property was set to True for this project the ssis was not able to show the actual error message.

So set the CreateDeploymentUtility to False and build the project again to get the actual error. In my case there was a package added in the project however because of some source control issues the .dtsx file was not available in the project directory.

Now that you got the error to fix; go ahead and fix the issue then set CreateDeploymentUtility property to True and build the project successfully.

Posted in SSIS | Tagged: , | Leave a Comment »

SSIS – Create New Excel File Dynamically to Export Data

Posted by Prashant on January 12, 2013


Recently I got a requirement to export error data to a newly created excel file every time the SSIS package runs. The challenge here was to create new excel file with time stamp every time the package ran and export data to that newly created file.

In this post will demonstrate this step by step.

  • Create a template file to export the data. You need this template file to set up the package for the first time. Once the setup is done you can delete the template.
  • Create connection managers:
    • Add Excel Connection Manager pointing to the template excel file created before.
    • Add OLEDB Connection Manager pointing to the desired database (here I am using AdventureWorks2008R2 database)
  • Add an Execute SQL Task and use the below script as SQL Statement as shown in fig-2:
CREATE TABLE Data (`DepartmentID` LongText, `Name` LongText, `GroupName` LongText)

Fig-1
Fig-1

ExportExcel-Package11
Fig-2

  • Now setup a Data Flow Task to export the data.
    • Add a Data Flow Task to the package. Open the Data Flow Task, inside Data Flow tab add a OLEDB Source, select SQL Command as data access mode and use your query to fetch the required data. I will use the below query:
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
  • Add an Excel Destination to the package. In the excel destination editor select Table or View as Data Access Mode, select the name of excel sheet and do the required mapping. Set ValidateExternalMetadata property to False.

ExportExcel-Package12

Fig-3

  • Now go to Expressions property of Excel Connection Manager and set ExcelFilePath with below expression:
"S:\\ETL Lab\\CreateNewExcel\\ExportData_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"

and the package is ready to run.

You can download the sample package here.

Posted in Excel, SQL Server, SSIS | Tagged: , , , , | 12 Comments »

Posted by Prashant on December 3, 2012


Originally posted on SQL Journey:

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

1

Now create a table where CHECK constraint will refer to this function to check if the…

View original 214 more words

Posted in SQL Server | Leave a Comment »

SQL Server – Resolve Collation Conflict with Temp Table

Posted by Prashant on December 2, 2012


When one of our projects deployed to production application was throwing a strange error and while debugging it is found that one of the stored procedure getting failed because of collation conflict issue.

The reason behind this error is the stored procedure used a temp table to join a table on string column. Temp tables are created on TempDB, so if you don’t specify collation for a string columns they inherit the collation of TempDB. If the collation of user database doesn’t match with the collation of TempDB and you are comparing and/or joining on string columns, it may cause this collation conflict issue.

Resolution:

There are many ways to resolve this issue. However the best way as per me is to change to specify the collation of the string column of temp table to database default, so that it will inherit the collation of .user database.

CREATE TABLE #tmpTable(IDCol INT, StringCol VARCHAR(100) COLLATE DATABASE_DEFAULT)

Smart coding :)

Posted in Interview Questions, SQL Server | Tagged: , , , | Leave a Comment »

SQL Server – List of Tables Used in Stored Procedure

Posted by Prashant on December 1, 2012


Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!

Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?

…and that inspired me to write this post.

We spend some time researching system tables and here is the findings to find list of tables used in a procedure.

;WITH procs
AS
(
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
)
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Let me know if you have any better idea for this.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: