SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Posts Tagged ‘SQL Server’

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 »

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 »

SQL Server: Allow Only Alpha Numeric Characters to a Column

Posted by Prashant on June 16, 2011


Here is how to restrict non alpha numeric characters to a column in SQL Server

--Create Demo Table
CREATE TABLE AlphaNumDemo ( DemoCode NVARCHAR(100) )
GO
--Restrict Special Characters
ALTER TABLE AlphaNumDemo ADD CONSTRAINT chk_AllowAplhaNumericCharactresOnly
CHECK (DemoCode NOT LIKE'%[^a-zA-Z0-9 ]%')
GO
--Insert Test Data
INSERT INTO AlphaNumDemo
 VALUES ('demo1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo 1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo-1')
INSERT INTO AlphaNumDemo
 VALUES ('#1Demo')
GO
--Check Data
SELECT
 DemoCode
FROM AlphaNumDemo
GO

--Cleanup
DROP TABLE AlphaNumDemo
GO

In the above demonstration observe the records with special characters will not be inserted to the table.

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

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 »

 
%d bloggers like this: