SQL Server Error: Invalid use of schema or catalog for OLE DB provider "SQLNCLI11" for linked server "*". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

SQL Server Error:

Recently I was building a new database infrastructure on SQL Server 2012 to run a  third party application called Relativity by kCura and received below error on application side. After I resolve this error, just thought to share on blog so that it can help others.

Invalid use of schema or catalog for OLE DB provider "SQLNCLI11" for linked server "*". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Resolution:

Connect to the SQL Server via SSMS (SQL Server Management Studio) and go to
Server Node --> Server Object --> Linked Servers --> Provider and right click on SQLNCLI11, go to Properties and uncheck the “Level zero only” and click OK and restart SQL Server Service.

The above steps will resolve this error.

Thanks!

SQL Server Error: (Msg 7919, Level 16, State 3, Line 1) Repair statement not processed. Database needs to be in single user mode.

SQL Server Error:

If you're trying to run check database integrity with command "DBCC CHECKDB"  with 

REPAIR_ALLOW_DATA_LOSS, you will receive below error.

USE master

go

DBCC CHECKDB(N'Database Name',REPAIR_ALLOW_DATA_LOSS)

Msg 7919, Level 16, State 3, Line 1

Repair statement not processed. Database needs to be in single user mode.

Resolution:

If the database is in MULTI_USER mode, SQL Server will not allow you to run DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option. However you can run the DBCC CHECKDB command with this option. In order to fix page error and run this command you need to set database in SINGLE_USER mode. 

You can below command to set the database into SINGLE_USER mode.

USE [master]

GO

ALTER DATABASE [Database Name] SET  SINGLE_USER

GO

Once your database is set into single user mode, you can run checkDB command with REPAIR_ALLOW_DATA_LOSS option. 

Note: If you are fixing this issue in production environment make sure to perform this activity in the maintenance window.

Msg 2552, Level 16, State 1 (The index "*" (partition 1) on table "*" cannot be reorganized because page level locking is disabled.)

SQL Server Error:

Failed:(-1073548784) Executing the query "IF object_id('tempdb..#DatabaseList') IS NOT NULL

..." failed with the following error: "The index "IX_*" (partition 1) on table "*" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Msg 2552, Level 16, State 1, Line 1

The index "Index Name" (partition 1) on table "Table Name" cannot be reorganized because page level locking is disabled.

Resolution:

I have daily job that identify the index fragmentation and perform index reorganize or index optimization based on the level of fragmentation. The job was getting failed on the above error (first) and further drill down on the issue, I found that index rebuild was getting failed with the second error (shown in red).  It was very clean from the error that page level lock is prevented and SQL Server is unable to get lock on the page in order to modify index.

When I looked at the properties of the index, I found that "use page locks when accessing the index" was unchecked, which was preventing SQL Server to take lock on the index.

In order to allow SQL Server to take lock on the page, this option should be checked.  

 

Alternatively you can use the below script to allow page lock on the index. 

USE [Database Name]

GO

ALTER INDEX [Index Name] ON [dbo].[Table Name] SET ( ALLOW_PAGE_LOCKS  = ON )

GO

Microsoft SQL Server, Error: 4064 (Cannot open user default database. Login failed.)

SQL Server Error:

TITLE: Connect to Server
------------------------------

Cannot connect to * (Server Name).

------------------------------
ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user '*'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Resolution:

Make sure user has permission on the default database, which is generally "master" database. if you are creating a user with public role on SQL Server as assigning appropriate permissions on the individual database, make sure to assign default database as master. 

Now go the option on the SQL client connection screen as shown below and click on options.

Now change the "connect to database" as master or the database that you have assigned as default database on the SQL server you are connecting too. 

Now click on  connect and you should be able to successfully connect with your SQL Server.

SQL Server: Script to get backup size

Script to get total and average backup size on the SQL Server.

  ;With CTE_BKPSize AS

  (SELECT @@SERVERNAME AS ServerName,CAST(backup_finish_date AS DATE) AS BackupDate, SUM(CAST(CAST(compressed_backup_size AS FLOAT)/1024/1024 AS DECIMAL(11,2))) AS CompressedSizeMB

  FROM [msdb].[dbo].[backupset]

  GROUP BY CAST(backup_finish_date AS DATE)

  )

  SELECT ServerName, COUNT(*) AS NoOfDays, SUM(CompressedSizeMB) AS TotalBKPSize, CAST(AVG(CompressedSizeMB)  AS DECIMAL(13,2)) AS AvgBKPSize FROM CTE_BKPSize

  GROUP BY ServerName

Msg 15281, Level 16, State 1, Line 1 (The configuration option 'sys.sp_OACreate' does not exist, or it may be an advanced option.)

Scenario:

I was trying to use OLE automation stored procedure "sp_OACreate" for calculating size of the folders and got following error. Use below script to enable "Ole Automation Procedures" using "sp_configure" option. I hope that will help others.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

Resolution:

Use master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ole Automation Procedures', 1

GO

RECONFIGURE

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.

SQL Server Error: Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 (SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.)

I was trying to enable xp_cmdshell on one  of our SQL Server in order to calculate folder size and got below error, just thought to share on blog to help users if somebody also getting similar errors.


I ran below command and got following error.

Command:

USE master

GO

EXEC xp_cmdshell N'dir C:\Ashish_Files'

Error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Resolution:

Obviously you need to enable xp_cmdshell using the server configuration option, please use below command to enable that.

USE master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

You'll get following message.

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.