Sending mails Through SQL Server Using SMTP

Sending Automated emails or paging to to servers / Users are important ,these mails can serve as alerts if something is changed or failed in a system.

  • First we have to Assign permission to Certain user who wants to execute system procedures.
GRANT EXECUTE ON master..sp_OACreate to Username

  • Enable Advanced options by :

USE MASTER

--Enable Database Mail XPs Advanced Options in SQL Server 
  SP_CONFIGURE 'Database Mail XPs', 1 RECONFIGURE WITH OVERRIDE
   GO
  SP_CONFIGURE 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
  GO
  • Then ,start the Database Mail Configuration Wizard
    1. From Object Explorer, connect to an instance of SQL Server.
    2. Expand Management, right-click Database Mail, and select Configure Database Mail.
    3. Choose the Set up Database Mail option to set up Database Mail for the first time.

    databasemail1

  • Execute the following command now:
EXEC msdb.dbo.sp_send_dbmail

@profile_name='username',

@recipients='yourrecepient@recepientdomain.com',

@subject='Testing Email from SQL Server',

@body='It Worked!Email sent successfully',

@body_format='HTML',

@from_address='sender@yourdomain.com',

@reply_to='sender@yourdomain.com'

 

Thanks

 

How to Insert from Joins

Here is an example of inserting into a temporary table from multiple joins:

select a.col,b.col,c.col into #temp1

from table1 a

join table2 b on a.column1ID= b.column2ID

join table3 c on a.column1ID= c.column3ID

where c.col= ‘anydata’

 

Connectivity – Network Related Error :

Today while connecting my  MS SQL 2012  Server after a long time , i came across an error :
A network-related or instance-specific error occurred while establishing a connectionto SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Solution : 

I checked services of my pc , by typing the “services.msc” in the run , which opens the services dialog box .Then started the services of SQL Server which were not in running state.

I was then able to connect

Best Regards,

 

Working With Indexing -Part 1

OVERVIEW :

  • An index is a database’ essential  structure and a component which makes faster , quicker and easier to find data based on more than one columns.
  • We can think of an index in database just like of searching a word in a dictionary .
  • For example : If you want to search a word : “Computer” , you have two paths to go to that word : First , go page by page in sequential word by word order , second path is : You quickly start searching through the Alphabet , like first in C then  : ->o->m->p->u->t->e->r .
  • The first path will take for example : 1 minute to reach the word , the second path will take 10 seconds to reach the word . This is the beauty of Indexing , it reduces the path to the best optimal path and saves time.

 

Useful Script for checking SQL job History from Query

USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(‘000000′ + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,’:’),3,0,’:’) RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then ‘failed’
when 1 then ‘Succeded’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
end as ExecutionStatus,
h.message MessageGenerated,*
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where j.name =’jobname’
ORDER BY j.name, h.run_date, h.run_time
GO

What is Stored Procedure (Part 1)

Stored Procedure

  • The Stored procedure is actually a piece of code that can be saved in order to reuse it and saves a lot of time instead of writing the queries over and over again.
  • Depending on the need , SQL stored procedure can also use parameters as Input and Output.
  • It is a secure place to write your code , for instance : A stored procedure can be encrypted which secures it from unauthorized access.
  • Permissions can be given to only particular users to access the stored procedure to have a better control of security .
  • When a stored procedure is executed  , it gets stored in the cache memory which increases performance and efficiency.
  • They are executed after writing execute statement.
  • Better error handling can be done in the stored procedure.

Working on Sets 1

The difference between UNION and UNION ALL Operator :

There is a difference between union and a union All operator which users are not aware of:

The union all operator will result in a set which will not eliminate the duplicates from the resulting set , means it will return the duplicate rows if residing in the set .

On the other hand union operator will eliminate the duplicates from the resulting set .

Whenever , you see a ALL keyword with union or intersect or except operators , it means it will give all the results including the duplicate records in the set .

 

 

 

Creating SQL Tables

Tables are the database structures where your data actually gets stored.

Now , we shall create a database table  ,you can write the create table below script to create  :

use mydatabase;       –assuming this is the name of your database

CREATE TABLE dbo.Student      — dbo is schema & student is table name
(
studentid INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(20) NOT NULL,
admissiondate DATE NOT NULL,
phoneNumber VARCHAR(18) NOT NULL,
TeacherId INT NULL
);

 

Connecting to SQL -2 (Errors – Named Pipes)

Named PIPES:

Named Pipes Provider, error: 40 – Could not open a connection to SQL Server 

Users / developers often  come across this type of errors while connecting /opening  MS SQL Server.  Here is quick review of how to overcome this issue.

To connect to MS SQL Server , we need network protocols , which connect  and communicate with SQL. There are normally four Network Protocols  to communicate to MS SQL .

  • Shared Memory
  • Named Pipes
  • TCP/IP
  • VIA

To overcome the above error of Named Pipes , Follow the instructions below :

  • Go to Start Menu of your windows, open the Microsoft SQL Server tab , find the SQL Server Configuration Manager .
  • Go to the protocols of SQL Server in the SQL Server Configuration Manager And enable the Name Pipes on the right window .Find the below Screen Clipping for reference :
  • Named Pipes 1