SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Posts Tagged ‘SQL Journey’

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 – 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: 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 »

SQL Server: Keyboard Shortcuts – Part 1

Posted by Prashant on July 8, 2010


Writing clean code is an important part in development activity. People sometimes ignore this may be because of some or other reason, which creates difficult to understand the code in later stage when it need changes. Here are some quick keyboard shortcuts for SQL Server Management Studio, which helps to write clean code and speed up development works.

So the below shortcuts related to code editing and execution in SSMS.

Purpose Keyboard Shortcut
Open New Query Window CTRL + N
Comment selected text CTRL + K then CTRL + C
Make selected text to UPPER case CTRL + SHIFT + U
Make selected text to LOWER case CTRL + SHIFT + L
Display estimated execution plan CRTL + L
Execute query F5 (Or) ALT + X (Or) CTRL + E
Show / hide result pane CTRL + R
Toggle between Query & Result pane F6

 

So have quick and clean coding…

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

Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server

Posted by Prashant on July 2, 2010


Problem:

While performing mathematical operations it throws Divided by zero error.

Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered.

 

This situation often arises in production databases if the script has not been tested with sufficient data before putting the script to production database. This happens when a number is divided by 0 (zero).

Solution:

There can be many ways to handle this error. Here are some of my workarounds in SQL Server.

  1. Using NULLIF & ISNULL/ COALESCE
  2. Using CASE
  3. Using ARITHABORT & ANSI_WARNINGS

Method: 1

SELECT ISNULL(Number1 / NULLIF(Number2, 0), 0) AS [Result]
FROM tbl_err_8134

In this method uses NULLIF. In this case when the divisor is 0 (Zero) it will return NULL to the divisor, so the result will also became NULL. Then by IFNULL it returns 0 as the result is NULL here.

Method: 2

SELECT CASE WHEN Number2 = 0 THEN 0 ELSE Number1 / Number2 END AS [Result]
FROM tbl_err_8134

In this method uses CASE. Here when the divisor is 0 (Zero) it will return 0 as result or else the result will be division of two numbers.

Method: 3

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
GO

SELECT ISNULL(Number1 / Number2, 0) AS [Result]
from tbl_err_8134

Here when ARITHABORT & ANSI_WARNINGS are set to OFF it will continue processing and will return NULL as a result. To know more about ARITHABORT you can follow this link.

Download the complete script file here.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , | 6 Comments »

 
%d bloggers like this: