Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

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.

attach-db-access-denied
 

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


 

Categories: SQL Errors Tags:

Get SSRS Report Stats, Usage report and Runtime

October 12, 2016 Leave a comment

 

SELECT 
       Cat.[Name] AS ReportName, 
       Cat.[Path] AS ReportPath, 
       ELog.[Format] AS ReportFormat, 
       ELog.[UserName] AS RunBy, 
       ELog.[TimeStart] AS RunDateTime, 
       CONVERT(nvarchar(10), ELog.[TimeStart], 101) AS RunDate,  
       CASE(ELog.[Source])
              WHEN 1 THEN 'Live'
              WHEN 2 THEN 'Cache'
              WHEN 3 THEN 'Snapshot' 
              WHEN 4 THEN 'History'
              WHEN 5 THEN 'AdHoc'
              WHEN 6 THEN 'Session'
              WHEN 7 THEN 'Rdce'
              ELSE 'Unknown'
       END AS [DataSource],
       CASE(ELog.[RequestType])
              WHEN 0 THEN 'Interactive'
              WHEN 1 THEN 'Subscription'
              WHEN 2 THEN 'Refresh Cache'
              ELSE 'Unknown'
       END AS RequestType,
       1 AS [NumRuns], 
       ELog.[TimeDataRetrieval],
       ELog.[TimeProcessing],
       ELog.[TimeRendering],
       ELog.[ByteCount],
       ELog.[RowCount]
FROM ELogecutionLog AS ELog 
INNER JOIN [Catalog] AS Cat ON ELog.ReportID = Cat.ItemID 
WHERE Cat.Type = 2
AND ELog.[TimeStart] >= '2016-08-01'
ORDER BY ELog.TimeStart DESC


 

Categories: Reporting Services Tags: , ,

Windows Service stuck at “Starting” status

September 14, 2016 Leave a comment

 
Today while installing an ETL utility one of the related Windows Service got stuck on Starting mode. I tried restarting the server 2-3 times but everytime it got stuck on “Starting” status. So to know what happening behind the scene I thought to check in Event Viewer.
 

–> You can open “Event Viewer” by any of the below options:

1. Shortcut: Eventvwr.msc

2. Type “Event Viewer” in search box on Windows 8 and above.

3. Open Control Panel –> System and Maintenance –> Administrative Tools –> Event Viewer
 

Now on the “Event Viewer” window go to: Windows Logs –> Application

Check the logs on the General or Details tab:

Timestamp: 12/14/2016 9:39:21 AM
Message: HandlingInstanceID: d87a52b3-0b01-4c7f-b044-06f00e02fb6a
An exception of type ‘System.Exception’ occurred and was caught.
—————————————————————-
12/14/2016 09:39:21
Type : System.Exception, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : Service has not started for ‘6.0’ minutes due to unavailable/inaccessible SQL Server ‘SQL_Instance_name’ / Database ‘dbName’. Service will start once server / database is online or permissions are granted.ETLxyzService

The above error log means that the Service account that I was using to run the service was not setup on the SQL Server instance.
 

So, I added the Service account from SSMS, under Object Explorer –> Security –> Logins:

– General Tab: Provide the Service Account as Login name

– Server Role Tab: set as “sysadmin”

Now after few seconds the Service that was stuck on Starting mode changed to Running status.
 

So if you also observed similar “Starting” status for your service, there could similar or some other reason, but by checking on the “Event Viewer” you will get to know the exact reason and fix it !!!

Categories: Windows Tags: ,

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: , ,