SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Posts Tagged ‘Prashant Pattnaik’

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: , , | 2 Comments »

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 – 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: , , , , | 15 Comments »

SQL Server: Kill process running xp_cmdshell

Posted by Prashant on August 6, 2010


In our last post we discussed how to run a VB Script from SQL Server. Here I will share something interesting related to that task. Well, while extending the program logic of my VB Script I have used some message boxes to check the flow of some variable values. I also ran that script from command line, that finished successfully.

Now, when I try it to run my TSQL code (which uses xp_cmdshell to run that script) and observed that it is taking an unexpected time to complete !!!  After sometime I think of killing the process and killed, but still the query was running where I got a Success message from KILL !!!

Here is the reason behind that…after testing my script I forgot to remove/comment the message boxes, which cause my TSQL to run for an indefinite time.

Solution:

  • The script or application we are calling from TSQL (xp_cmdshell) should be self-sufficient, i.e. application should not have any user interaction like InputBox or MessageBox, Visual Windows…etc.
  • To stop the query which call such applications, we need to end the process from windows Task Manager.

Note: xp_cmdshell always run the target application in background and user can’t end it interactively to regain the control from that application.

More information for this topic is available here on Microsoft Support site or refer Book Online. Hope this helps you.

lated to

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

SQL Server: Running VB Script From TSQL

Posted by Prashant on August 3, 2010


In this post we will see if we can run a VB Script from SQL Server.  Yes, SQL Server allows to run external Scripts. So, here is the process how to run a VB Script from TSQL Code.

Well to test this lets first create a sample VB Script which will just display a message.

Dim VarSetMessage
VarSetMessage = "Running VB Script from SQL Server..."

'Display message
WScript.Echo VarSetMessage

Save it as “myMsg.vbs” on “C:\” drive of your machine. Okey, before running this script from SQL Server, lets see how to run it from Command Prompt. To do this:

Start => Run => cmd => ENTER
Type Cscript C:\myMsg.vbs => ENTER

(Refer below image for result)

Now to run the VB Script from SQL Server just run the same command line we tested above using xp_cmdshell from SQL Server.

EXEC XP_CMDSHELL 'CScript C:\myMsg.vbs'

and we are done !!!

Note: You may need to configure xp_cmdshell if not configured before.

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

SQL Server: SET vs SELECT in Variable Assignment

Posted by Prashant on July 14, 2010


Most often we are using SET and SELECT interchangeably while assigning values to variable, however there are differences between them. In this post we will discuss some diff between SET and SELECT.

  • SET can assign value to only one variable at a time, where as SELECT can assign values to multiple variables at once.

Example:

--Declare variable
DECLARE @myVar1 INT, @myVar2 VARCHAR(10);

--Assign value to variable using SET
SET @myVar1 = 1234;
SET @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2

--Assign value to variable using SELECT
SELECT @myVar1 = 1234, @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2
  • SET operation will raise an error if when result of a query returning multiple values is used to assign value to a variable. However  SELECT will not raise any error and assign the last record of query result to the variable. So, Microsoft recommends SET for variable assignment

Example:

CREATE TABLE #TestVar(myID int, myComments VARCHAR(30))
INSERT INTO #TestVar VALUES(1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(4, 'E')

--Using SET
--Declare variable
DECLARE @myVar3 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SET @myVar3 = (SELECT myComments FROM #TestVar WHERE myID = 6)
--Extract varible values
SELECT @myVar3


--Msg 512, Level 16, State 1, Line 8
--Subquery returned more than 1 value.
--This is not permitted when the subquery follows =, !=, <, <= , >, >= or
--when the subquery is used as an expression


--Using SELECT
--Declare variable
DECLARE @myVar4 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SELECT @myVar4 = myComments FROM #TestVar WHERE myID = 4;
--Extract varible values
SELECT @myVar4;
  • In the above case if the query result will not return any values then the variable value will be NULL either using SELECT or SET.
  • While talking in terms of performance variable assignment using SET will take less time as compared to SELECT.

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

SQL Server: Remove Non-Alphanumeric Characters from a Column

Posted by Prashant on July 13, 2010


In database we often need to clean the non-alphanumeric characters in some column of a table. Here is the code I use to remove non-alphanumeric characters in Sql Server.

Create the table with sample data:

CREATE TABLE STRSTORE(COMMENT VARCHAR(2000))

INSERT INTO STRSTORE(COMMENT) VALUES
('1.We^lcom&<e> to Sql{Journey}.&'),
('2=https://sqljourney.wordpress.com/'),
('3#[S$tring] &Cle<@anUp'),
('4-We* are'' do+ne~')

Remove non-alphanumeric characters from the column.

WHILE @@ROWCOUNT > 1
UPDATE STRSTORE
SET COMMENT = REPLACE(COMMENT, SUBSTRING(COMMENT, PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT), 1), '')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT) <> 0

SELECT	*
FROM STRSTORE

Let me also know if you have some better ideas.

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

 
%d bloggers like this: