SQL and ML Spaces

The error is due to the incompatibility of the database you are trying to import as compared to the version of SQL server you are trying to import as destination server.

The error is :

“Database ‘pubs’ cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server.
You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.
Could not open new database ‘pubs’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 950)”

Possible reasons:

Trying to restore an older version of the databases into higher version of SQL Server engine (based on compatibility mode)

Possible Solutions:

• One way is to extract the source database by exporting to SQL script and executing on the destination server

• or use SQL Database Migration wizard. Even though it was created specifically for migration on premise DB to cloud database it works for on premise DB to on premise migration too)

It is ACL list error (access control list).

Possible reasons:

• SQL Server service (sqlsrvr.exe) should have RW permissions on the folder that contains the .BAK file (check service account SQL engine is using)

• Trying to create the MDF and LDF file for the intended database at a different location as compared to the default directories

Possible Solutions:

• Add the service account running the SQL Server to have full RW access to the folder that contains the .BAK file using security permissions or

• move the .BAK file (or MDF/LDF files depending on restore or attach option) to the default folder for SQL Server backups or

• starts SSMS as administrator or

• change the user account running the SQL service to an account who has admin privileges to the folder containing .BAK file, attach DB and then revert back with original account

Summary of solutions : SQL Server service account should have access to the folders where it is reading or writing to avoid ACL permissions issues.

Error 5120 is a sharing violation of the files we are trying to open.

Possible reasons:

• Incorrectly configured path of MDF and LDF files
• Either of MDF or LDF files are missing
• Because the file is already attached to one of the existing databases on the server

Possible Solutions:

• When attaching the MDF file, remove the log file (assuming you have taken full back of the DB before detach and attach) by clicking on remove using UI
  or
  use SQL script

[cc lang=”TSQL”]

— Attaching MDF File without ldf file
CREATE DATABASE PubsDB ON
(FILENAME = N’D:\MSSQL\DATA\PubsDB.mdf’)
FOR ATTACH_REBUILD_LOG
GO

— if one file missing
CREATE DATABASE PubsDB ON
( FILENAME = N’D:\MSSQL\DATA\PubsDB.mdf’)
FOR ATTACH
GO

— recreate all log files
EXECUTE sp_attach_single_file_db @dbname = ‘PubsDB’,
@physname = N’D:\MSSQL\DATA\PubsDB.mdf’
GO

[/cc]

It is a clear indication that either MDF or LDF files is missing.

Try below options:

• give permissions to the account running SQL Server service to the location where MDF & LDF files are located

• open SSMS with admin privileges

• modify the MDF and LDF files properties as below

right-click on the file in Windows Explorer, select Properties
select the Security tab
Click Advanced
Click Change Permission
Uncheck “Include inheritable permissions…”, a window will open
Click Remove (removes all permissions), the window will close
Click Add
Enter your login name and click OK, the permission window will open
Check Full Control – Allow
Click OK, OK, OK, OK

Here are some important perfomon counters that can be captured to track the performance of SQL Server.

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the application to the storage system

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the storage system to the application

Disk read/sec and disk write/sec – track the physical data being transferred

Disk Queue length let’s us know how busy the system

% Processor time – shows the processor load on the server.

System: Processor Queue Length – determines how many tasks are waiting on the CPU.

Memory : Available Mbytes – tells the memory currently in use

How does SQL Server read and write ?

• normally Reads in 64K data extents
• normally Writes in 8K data pages

Simple answer is No.

in fact when you try to issue a DML statement it complains:

Invalid use side-effecting operator Insert within a function

This is one of the limitation of SQL server function as defined in books online.
Functions are used to fetch data and stored procedures are ideally suited for all DML operations.

The error is normally generated when migrating the applications or code segments that used to run prior to SQL Server 2012 to later versions of SQL Server 2012 (Because it was deprecated in SQL Server 2012).

• When can you get it ?
  When the SQL script was created in earlier versions before SQL Server 2012 ()i.e compatibilty mode < 110). So installation of older databases like pubs, northwind fail.

• When trying to create a sharepoint 2003 site (WSS 2.0) collection as it relies on this system stored procedure using SQL Server 2012

• when configuring reporting services for sharepoint 2010 (non SP1) using SQL Server 2012 (SQL 2012 only works with SP 2010 SP1 or higher) – so slipstream SP1 for Sharepoint 2010 installation

• When configuring ACM (audit and control management) server for Sharepoint

• When creating WebMarshal database

Solution:
replace the occurrence of sp_dboption with ALTER DATABASE statement manually.

Please see attached sp_dboption SQL file when needed.

version 1

version 2

SQL Server 2012 introduced a new function called as concat().

Previous versions of SQL Server, the null values in any of the concatenated fields was handled by either using ISNULL or COALESCE functions or by CASE END statements.

Concat handles NULLs in any of the participating fields and suppresses them without additional code handling.

So use CONCAT() wherever applicable.

[cc lang=”SQL”]

— sample case when middle name is null
select * from [dbo].[employee]
where emp_id = ‘PMA42628M’

— simple concatenation fails
select fname + minit + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

— handling NULLs before SQL Server 2012
——————————————————————————-
select fname + isnull(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + coalesce(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + case when minit is null then ‘ ‘ else minit end + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

— handling NULLs after SQL Server 2012
——————————————————————————-
select concat(fname, ‘ ‘,minit, ‘ ‘, lname) as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

[/cc]