CategorySQL Server

Changing the Default Encoding of SQL files in SSMS

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

SQL Server script to rebuild all indexes on all tables

You can use this script to generate SQL commands to rebuild all of the indexes on all of the tables in the currently selected database.

Check Upcoming Scheduled Jobs for SQL Server Agent

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

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]

 

 

© 2018 joehanna.com

Theme by Anders NorénUp ↑