Pause SQL Server service before restarting

Did you know that by pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance? This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.

When a server is paused you will see messages similar to this in the SQL Server error log:

Error: 17142, Severity: 14, State: 0.
SQL Server service has been paused. No new connections will be allowed. To resume the
service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.
Login failed for user ''. Reason: SQL Server service is paused.
No new connections can be accepted at this time. [CLIENT: ]

Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.

Thank Thomas Larock for his blog post: http://thomaslarock.com/2016/01/pause-sql-server-service-before-restarting/








Advertisements

Evolution of SQL Server 2014

Found these images on Google and it’s something I would like to share with you guys:
http://sqlmag.com/sql-server-2014/evolution-sql-server-2014

evolution-of-sql-server


SQLServer-evolution-to-2014



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.

It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')



Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6








SSIS Anchor Modeling example (tutorial)

About
This Anchor Modeling SSIS example is brought to you by:

Clint Huijbers (BumbleBI IT)

Bas van den Berg (C2H)

The first version of the demo was created by Bas and shared on AnchorModeling.com.

Tutorial Video by Bas
Advice you to watch it first 🙂

Tutorial-by-Bas-van-den-Berg


New version of the SSIS package
The fast performing package (final step in his tutorial) was rebuild by Clint and is described below:

Download
To download the SSIS package and T-SQL scripts, click here.


The Anchor Model (AM)
The AM is about telephone calls, that includes two Anchors (Call and Phone), two Ties and three Attributes:

Anchor Model with mnemonics
AM-Model-with-mnemonics

Anchor Model without mnemonics
AM-Model-without-mnemonics


How to install

1) Create a new database called ‘AM_SSIS_Example

2) Create all AM-objects by executing the script: ‘Generated SQL Script by AM Online Modeler.sql

3) Generate source data by executing the script: ‘Create dbo_Calls table (source).sql
Give it a few minutes, it will generate 691.200 rows

The table dbo.Calls is already filled with demo data:
dbo_Calls

4) Create a Batch-table for metadata by executing the script: ‘Create BA_Batch table.sql

The database should now have a couple of tables (also Views, SPs and UDFs):
SQL-Server-Tables

5) Open the SSIS package in BIDS/SSDT and execute the package

The SSIS package demos two scenarios:
#1 – Insert records by using the INSERT-trigger on view dbo.lPhone
#2 – Insert records directly into the Anchor and Attributes
(by using the fast load / bulk insert and parallel processing of the Attributes)

SSIS-Package-Design

Raw File
A Raw File Source is used to temporary store and quickly re-load the full dataset, located in ‘C:\Temp\AM_SSIS_Example_RAW_FILE‘. Important: make sure to manually create/verify this Raw File. You’ll be able to find it here:

SSIS-Raw-File-Connection

Open the ‘Raw File Destination Editor‘-window (right-click and ‘Edit’) and verify the Raw File’s location:

SSIS-Raw-File

6) Watch the show

SSIS-Package-Executed

7) Verify the results by executing the script: ‘Verify the results.sql

AM-data-loaded

8) Increase the volume!
Would you like to re-process all data and perhaps with a lot more records? 😀
Truncate all tables by executing the script: ‘Truncate and delete all data.sql’
Now, drop the table dbo.Calls.
Execute the script ‘Create dbo_Calls table (source).sql‘ again, but this time increase the variable @EndDateTime with just a couple of days.
Have fun!

Coyote

How To: Display unicode characters in Grid Results (SQL Server Management Studio – SSMS)

Is SSMS not displaying unicode characters?
You might want to change the default font to: ‘Arial Unicode MS

In SSMS, go to:
Tools‘ -> ‘Options‘ -> ‘Environment‘ -> ‘Fonts and Colors‘ -> Select ‘Grid Results’

Here you’re able to change fonts for many windows (including ‘Grid Results’).
Also, you can click ‘Use Defaults‘ to (re-)set all settings to default values.

SQL-Server-Management-Studio-SSMS-Unicode-Grid-Results-Settings

Before:
SQL-Server-Management-Studio-SSMS-Unicode-Grid-Results-Before

After:
SQL-Server-Management-Studio-SSMS-Unicode-Grid-Results-After


SQL Scalar-valued Function – Strip characters from a string by using a mask/match-expression (alpha-numeric, non-numeric, etc.)

A great addition to your SQL Cookbook!

A few examples:

Alphabetic only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^a-z’)

Numeric only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^0-9’)

Alphanumeric only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^a-z0-9’)

Non-alphanumeric:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘a-z0-9’)

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'

/*
Alphabetic only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

Alphanumeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
*/

WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

RETURN @String

END

SQL Agent – Disable All Jobs (script)

Another addition to your cookbook, comes in very handy during releases!

The scripts below are easier, but if you want to avoid updating system tables directly…please scroll down.

————————————————–

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Enable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE [Name] LIKE 'Admin%';
GO

————————————————–

Disable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 0;
GO

Enable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 1;
GO

Disable All ENABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 1 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

Enable All DISABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs