All the SQL Server Agent Jobs details are logged into the MSDB Database tables .

These tables contains information for each SQL server Agent job like : JobID, sessionID, RunDate , Enabled Status, Owner, Job Steps, Last Run outcomes etc . Tables which are commonly used are :

select * from [dbo].[sysjobactivity]

select * from [dbo].[sysjobhistory]

select * from [dbo].[sysjobs]

select * from [dbo].[sysjobschedules]

select * from [dbo].[sysjobservers]

select * from [dbo].[sysjobsteps]

select * from [dbo].[sysjobstepslogs]

We can modify data using (INSERT, Update and delete ) using merge statement based on conditional logic. The format is little different as follows :

We use the target table name in MERGE clause .

And source table in USING clause .

We define merge condition by specifying a predicate in ON clause like as we do in join .

The merge condition defines which of the rows in source table matches with the rows in target table.

In WHEN MATCHED THEN clause we define the action to be taken when rows are matched.

And In WHEN NOT MATCHED THEN we define the action when the rows are not matched.

For example :

MERGE INTO dbo.targettable AS TGT
USING dbo.sourcetable AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.personname = SRC.personname,
TGT.phone = SRC.phone,
TGT.address = SRC.address

WHEN NOT MATCHED THEN
INSERT (personid, personname , phone, address)
VALUES (SRC.personid, SRC.personname , SRC.phone, SRC.address);

The MERGE statement defines an UPDATE action when a match is found, setting the target personname , phone, and address values to those of the matched rows from the source table.

The MERGE statement defines an INSERT action when a match is not found, it inserts the rows from the source to the target table

Backup :

Full database backups: A full database backup copies all data from the database pages to including all objects, system tables, and other data. The Transactions that occur during the backup are also recorded. Full database backups allow you to perform a complete restoration of the database as it existed at the time the backup operation is performed.

Differential backups: They back up data that has altered since
the last full backup. Differential backups require less time than full database backups.

Incremental backups: An incremental backup backups and saves everything that has changed since the last backup of any type whether (full or differential).

For example, suppose that you do a full backup every Sunday.

On Monday, you do a differential backup of everything that has changed since Sunday.

On Tuesday, you have two choices: you can back up everything that’s changed since Sunday (differential), OR  you can back up only the data that has changed since Monday’s backup (incremental).

Transaction log backups :   transaction log backup records all the changes that have
occurred since the previous transaction log backup and then truncates the transaction
log. A truncation removes transactions from the log that have been committed to the
database or cancelled. Transaction log backups use incremental way as the logs are stored in sequence.

File and filegroup backup : This type of backup is intended to increase flexibility in scheduling to full backups, in particular for very large databases. File and filegroup backups are also useful for large databases that contain data with varying update characteristics meaning, some filegroups allow both read and write operations, whereas some are read-only.

I was recently working on a project in which I used the Google Maps API , I came across an error : This page didn’t load Google Maps correctly. See the JavaScript console for technical details . 

Resolution : 

I checked in the console tab in Developer tools in my internet Explorer , which threw the below error:

Google Maps API error: MissingKeyMapError https://developers.google.com/maps/documentation/javascript/error-messages#missing-key-map-error

So , I searched google support for this issue and found the solution on the below link:

https://developers.google.com/maps/documentation/javascript/get-api-key

Hit the Get A Key button and get your key to insert in your respective code , for example :

Key :  SyCj0as435MQBpLO5aLr9ArAurPARU   

Just add the key in the script calling the maps like this :

<script async defer src=”https://maps.googleapis.com/maps/api/js?key=SyCj0as435MQBpLO5aLr9ArAurPARU&callback=initialize”>

Thanks

The following query is for finding records in a table which  belong to previous(last) month. This can be useful for finding records and creating reports on those records’ basis for example : Number of patients for last month in a hospital , total number of transactions done for last month in a bank , etc .

select * from table where
datepart(m,yourdatecolumn) = DATEPART(m,DATEADD(m,-1,getdate())) AND 
DATEPART(yyyy, yourdatecolumn) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

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.

  

• 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

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’

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

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 :
•