Category: SQL Server

Changing the Default Encoding of SQL files in SSMS

February 6, 2017 / / 5 Comments

Overview

The default encoding for SSMS sql files is UTF-16, more specifically, either Western European (Windows) - Codepage 1252 or Unicode - Codepage 1200. These encodings play havoc with a git diff as these encoding appear as binary files.

The preferred encoding is Unicode (UTF-8 with asignature) - Codepage 65001

Steps to resolve

  1. From within SSMS, open the sql template file named SQLFile.sql, by default in one of these locations: –
    1. %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    2. %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    3. %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
    4. %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
  2. Resave using correct encoding:
    1. File => Save As
    2. Click the arrow next to the Save button
    3. Choose the relevant encoding: Unicode (UTF-8 with asignature) - Codepage 65001

All new query windows will default to UTF-8 files

Check Upcoming Scheduled Jobs for SQL Server Agent

July 16, 2013 / / 0 Comments

Here is a handy script for checking upcoming scheduled jobs for the Microsoft SQL Server Agent.

[fancy_pre_box]
SELECT job.[job_id] AS [JobID]
,job.[name] AS [JobName]
–Convert integer date yyyymmdd and integer time [h]hmmss
–into a readable date/time field
,CONVERT(VARCHAR(50),
CAST(STUFF(STUFF(CAST(jobsch.[next_run_date] AS VARCHAR(8))
,5
,0
,’-‘)
,8
,0
,’-‘) AS DATETIME)
+ CAST(STUFF(STUFF(CAST(RIGHT(‘000000′
+ CAST(jobsch.[next_run_time]
AS VARCHAR(6))
, 6) AS VARCHAR(8))
,3
,0
,’:’)
,6
,0
,’:’) AS DATETIME)
,100) AS [Next Run Time]
FROM [msdb].[dbo].[sysjobs] AS job
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS jobsch
ON (job.[job_id] = jobsch.[job_id])
LEFT JOIN [msdb].[dbo].[sysschedules] AS syssch
ON (jobsch.[schedule_id] = syssch.[schedule_id])
WHERE job.[enabled] = 1
AND jobsch.[next_run_date] IS NOT NULL
AND jobsch.[next_run_date] > 0
ORDER BY jobsch.[next_run_date]
,jobsch.[next_run_time];
[/fancy_pre_box]

 

Checking Sent Email Messages on SQL Server

September 6, 2012 / / 0 Comments

Here are some handy scripts for checking the status of email messages sent via Database Mail on Microsoft SQL Server.

[fancy_header3 variation=”wheat”]Sent Messages Log[/fancy_header3]

[fancy_pre_box]

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC

[/fancy_pre_box]

[fancy_header3 variation=”wheat”]Failed Messages Log[/fancy_header3]

[fancy_pre_box]

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

[/fancy_pre_box]

[fancy_header3 variation=”wheat”]All Messages – Regardless of status[/fancy_header3]

[fancy_pre_box]

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC

[/fancy_pre_box]