Now “CREATE OR ALTER” Stored Procedure, Function, View, Trigger with SQL Server 2016

November 19, 2016 Leave a comment

SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.

But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].

Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like:

1. Stored Procedures (SP)

2. Functions (UDFs)


4. Triggers

–> Now you can create a new Stored Procedure without checking its existence, simply by using the new CREATE OR ALTER option, like below:

CREATE OR ALTER PROCEDURE dbo.spgetEmployeeDetails
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID

… you can execute the above code multiple times and it won’t fail. First time this CREATEs the SP, next time it will ALTER it.

–> Previously you need to add an IF EXISTS() condition to check if the SP already exists or not. If exists then drop and then create a new SP, like:

	DROP PROCEDURE dbo.spgetEmployeeDetails

CREATE PROCEDURE dbo.spgetEmployeeDetails
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID

SQL Server 2016 1st Service Pack (SP1) is out (download, new features & enhancements)

November 17, 2016 1 comment

Almost 6 months back i.e. on 1st June 2016 Microsoft released SQL Server 2016 RTM, i.e. full and final version, which you can [check and download here].

Yesterday (i.e. 16-Nov-2016) Microsoft released the 1st Service Pack (SP1) of SQL Server 2016.

–> Download:

To download the SQL Server 2016 SP1 you can Register and Download the Full version or Free evaluation version (180 days).

… or you can Download the new Setup utility here, which provides you option to do a Basic or Custom installation, or download the ISO or CAB file (~2.5 GB).

… or you can also just download the Service Pack (SP1) (~550 MB), instead of the whole Setup (~2.5 GB).

–> What’s new SP1:

1. Features which were only available in Enterprise edition are now enabled in Standard, Web, Express, and LocalDB editions, link.

2. List of Bugs and issues fixed, link.

3. CREATE OR ALTER syntax for Stored Procedures, Views, Functions, and Triggers.

4. DBCC CLONEDATABASE (source_database_name, target_database_name), with optional WITH NO_STATISTICS, NO_QUERYSTORE. Creates a duplicate database by cloning Schema, metadata and statistics, without the data.

5. OPTION (USE HINT(‘hint1’, ‘hint2’)), support for a more generic query hinting is added, link.

6. Post this Service Pack (SP1) Parallel INSERTs in INSERT..SELECT to local temporary tables is disabled by default and will require TABLOCK hint for parallel insert to be enabled.

7. New DMVs are added, and some enhanced:
   – sys.dm_exec_valid_use_hints to list hints
   – sys.dm_exec_query_statistics_xml to return showplan XML transient statistics
   – sys.dm_db_incremental_stats_properties to check incremental statistics for the specified table
   – New column instant_file_initialization_enabled is added to sys.dm_server_services, to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.
   – New column estimated_read_row_count is added to sys.dm_exec_query_profiles
   – New columns sql_memory_model and sql_memory_model_desc are added to sys.dm_os_sys_info, to provide information about the locking model for memory pages, and to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.


Do check & Like my FB Page.

SQL Error – Unable to open the physical file. Operating system error 5: “5(Access is denied.)”

October 25, 2016 Leave a comment

Today I Detached a Database from SQL Server and moved the mdf/ldf files to an another drive, but while Attaching those files from the new location I got error below by using following script:

USE [master]

( FILENAME = N'E:\NewDB\NewDatabase.mdf' ),
( FILENAME = N'E:\NewDB\NewDatabase_log.ldf' )

Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file “C:\NewDB\NewDatabase.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 1802, Level 16, State 7, Line 4
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Well the error clearly mentions that the error is due to the limited access of Service Account by which SQL Services are running, it doesn’t have access to the new folder location where the database mdf/ldf files are moved.

So, you just need to go to the new folder location, check the folder Properties and go to Securities tab, select the Service account and click on Edit button.

On the new Security popup window click under the Allow checkbox for Full Control permission.


Now execute the above Attach script and the Database will be attached without any issues.


Categories: SQL Errors Tags:

SQL Trivia – What all schemas cannot be dropped in a SQL Server database?

August 4, 2016 Leave a comment

–> The following four built-in database schemas cannot be dropped:

1. The “dbo” schema: is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

2. The “guest” schema: is used to contain objects that would be available to the guest user. This schema is rarely used.

3. The “sys” schema: is reserved by SQL Server for system objects such as system tables and views.

4. The “INFORMATION_SCHEMA” schema: is used by the Information Schema views, which provide ANSI standard access to metadata. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. Information schema views are based on sys catalog view definitions.

… notes from: Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012

Categories: SQL Trivia Tags:

SQL Error – Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option

Today while running an ETL I encountered an error I’d never seen before. The Informatica ETL triggers a Stored Procedure with 2 parameters which takes table names and creates a dynamic MERGE statement. The SP was failing and the Informatica Monitor was showing following error:

Severity: ERROR
Message Code: CMN_1022
Message: Database driver error…
CMN_1022 [EXEC spMergeTables ‘STG.ABCtblXYZ’,’ABC.tblXYZ’
Microsoft SQL Server Native Client 11.0: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
SQL State: 42000 Native Error: 2754
State: 1 Severity: 16
SQL Server Message: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Database driver error…
Function Name : ExecuteDirect

Thus, somewhere within this SP the error is being generated with severity greater than 18.

So, to identify the cause I tried to execute the above MERGE statement directly and I got the same error:

EXEC spMergeTables 'STG.ABCtblXYZ','ABC.tblXYZ'

(0 row(s) affected)
Msg 2754, Level 16, State 1, Procedure spMergeTables, Line 107
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.


In the SP after creating the dynamic string for the MERGE statement the SP executes the string variable, so I replaced the EXEC(@str), with PRINT(@str), to PRINT the MERGE statement rather than executing it, which came as follows:

ON tgt.[PK_ID] = src.[PK_ID] 
	UPDATE SET <tgt.column = src.column>
	INSERT (<Columns list>) 
	VALUES (<column values>);

On executing the above MERGE statement, I got the actual error which is as follows:

Msg 823, Level 24, State 12, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x000000270e0000 in file ‘F:\TempDB\tempdb2.ndf’.
Additional messages in the SQL Server error log and system event log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

So, its clear now that there was something wrong with the tempdb file and the OS is returning error with severity as 21, which is greater than 18.

As this was a tempdb file issue, thus I restarted the SQL Server services and reconnected the SSMS client, and re-ran the Job, and this time the ETL got executed successfully🙂

Categories: SQL Errors