T SQL | SQL with Manoj

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

October 25, 2016 2 comments

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]
GO

CREATE DATABASE [NewDatabase] ON 
( FILENAME = N'E:\NewDB\NewDatabase.mdf' ),
( FILENAME = N'E:\NewDB\NewDatabase_log.ldf' )
 FOR ATTACH
GO

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: Attach Database

SQL Trivia – Multiple ways to get list of all tables in a database

August 22, 2016 Leave a comment

This is one of a typical Interview Question you might face while going for an opening of a SQL Developer. This looks very simple question but its also an important question to judge a person if he/she knows SQL basics and has really worked in SQL Server or not.

–> So, here are following ways you can query the SQL Server metadata and system tables to get list of all tables in a particular database, by using:

1. sys.tables

select * from sys.tables

2. sys.objects with filter of type = ‘U’

select * from sys.objects where type = 'U'

3. sys.sysobjects with filter of type = ‘U’

select * from sys.sysobjects where type = 'U'

4. INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'TABLE'

5. sp_msforeachtable undocumented function

exec sp_msforeachtable 'print ''?'''

Out of all these the best option is to use the 4th one, i.e. INFORMATION_SCHEMA.TABLES views, as Microsoft itself suggest to use it instead of first three & the 5th option.

Categories: SQL Trivia

SQL Error – SHOWPLAN permission denied in database

August 10, 2016 Leave a comment

So, you are working on a SQL Query Performance issue in your Production environment, but you just have read access and can just execute SELECT statements to check the data.

But when you try to check what execution plan that query is creating/using you are getting blocked as you don’t have sufficient privileges for the same, and getting this error:

Msg 262, Level 14, State 4, Line 283
SHOWPLAN permission denied in database ‘AdventureWorks2014’.

For checking Execution plans you don’t need to have admin privileges, just granting access on SHOWPLAN setting will work for you.

So, you can your DBA to grant you access by using below query:

USE AdventureWorks2014
GO

GRANT SHOWPLAN TO user_name -- replace user_name here
GO

Categories: SQL Errors Tags: SHOWPLAN, SQL DBA, SQL Error

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: Database Schemas

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

July 20, 2016 Leave a comment

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
Thread: MAPPING
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:

MERGE ABC.tblXYZ AS tgt
USING STG.ABCtblXYZ AS src
ON tgt.[PK_ID] = src.[PK_ID] 
WHEN MATCHED THEN  
	UPDATE SET <tgt.column = src.column>
WHEN NOT MATCHED BY TARGET THEN 
	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 Tags: SQL error 21, write at offset

Newer Entries Older Entries