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 – How To Fix It ?

Objective

When I was trying to execute an  .exe file in my PC using xp_cmdshell command, I got an error which says the sys.xp_cmdshell component is turned off.

Ans Here is the error message…

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.

1 error

1 : Error messgge

Reason

By Default, xp_cmdshell is Disabled in SQL Server. So We have to enable it to use the feature.

What is xp_cmdshell  ?

Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.

In another way xp_cmdshell is a alternative of DOS prompt for SQL Server.

To Learn more use below link…

Reference : docs.microsoft.com

How to Fix the issue ?

Then what I did is Enable the xp_cmdshell  using SQL Server configure option.

Here are Steps to do it…

2 show advanced options

2 : Step 1 – Run  SP_Configure ‘Show advanced options’

3 re configure

3. Step 2 – Run RECONFIGURE

4 enable cmd shell

4. Step 3 – Run CONFIGURE ‘xp_cmdshell’ to Enable

In here you have top add “1” if you want to Enable the Feature. If you want to Disable the feature, then add “0”.

4 re configure again

5. Step 4 – Again Run RECONFIGURE

5 OK

6. Step 5 : Then Run the quarry you want to execute

Then the quarry will run properly.


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Thanks !

References :

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option

Fix Error : Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

Reason

When I was running a quarry with a JOIN, I got an error which says “Cannot resolve the collation conflict

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

0-error

Image 1 : Error

Then I looked what is the reason for the error and I got the reason is Collations are not matching in tables that I have joined.

So here I’ll show you that How was the error occurred and I I did solved the error…

First I’ll create two tables “Customer” and “Person”.

1-tables

Image 2 : Sample Tables Customer/Person

Now I’m going to JOIN these two tables by CustomerName of customer table and CustomerName of Person table.

Before that, I’ll add different collations to the name fields in both tables.

First will change the collation of “Person” table..

Go the the designer of the table and select “Collation” menu. Then we can see a dialague box to select a Collation.

2-cp1

Image 3 : How to change the collation of a column

3-select-collation

Image 4 : Select SQL_Lathin1_General_CP1_CI_AI for CustomerName in Person Table

Then lets change the collation of the CustomerName in Customer table..

4-cp-1250

Image 5 : Select SQL_Lathin1_General_CP1250_CI_AS for CustomerName in Customer table

Yes. Now we have done the changes to both tables. So Now we are ready to run the quarry with the JOIN…

5-error

Image 6 : Error happen when run the quarry

Oh ! It gives an error …

What is the difference.. ?

SQL_Lathin1_General is common for both collation types.

CI means Case Insensitive. (we can use CS to make it case sensitive)

AI means Accent Insensitive and AS means Accent Sensitive

Then the next difference is CP1 and CP1250..

What is CP1 and CP1250 ?

Both of CP1 and CP1250 are code page architectures..

According to Microsoft technical page , CP1 is code page 1252 and CP1250 is code page 1250 which are…Rules to define how the bit pattern of the characters are sorted and compared. 

Learn more about code Pages @technet.microsoft.com

So Lets Solve the error..

What we have to do is add Collation as “DATABASE_DEFAULT” for both table in the JOIN. Below image describes how to do it..


SELECT * FROM [dbo].[Customer] c
INNER JOIN [dbo].[Person] p
ON c.[Name] COLLATE DATABASE_DEFAULT =p.[CustomerName] COLLATE DATABASE_DEFAULT 

6-result

Image 7 : Solve the error using DATABASE_DEFAULT

Now the error is not there and the quarry gives the result successfully.

Thanks !


To Test the result, Create a Database “TestDB” and please use below script to create a the tables and get sample data that I have added.


USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (1, N'Buddhima', 24, 1)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (8, N'Chrystophe', 45, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (9, N'Patel', 30, 2)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (10, N'Nikolas', 20, 3)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (11, N'Ann', 35, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (12, N'Mike', 50, 4)
SET IDENTITY_INSERT [dbo].[Customer] OFF
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (1, N'Buddhima')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (2, N'Ann')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (3, N'Mike')
SET IDENTITY_INSERT [dbo].[Person] OFF

 

Fix Error : Incorrect syntax near ‘*=’. In SQL Server (SQL JOINS)

Reason :

I have Migrated  one of my Database from SQL Server 2000 to SQL Server 2016.  And when I was running my applications after the migration of databases, I got some exceptions and then I looked what are the reasons for them..

The Error message was : 

Incorrect syntax near ‘*=’.

I have created a Sample Database with two tables to show you how it occurred and how to fix it.
Here I have wrote a sample quarry to show you how the error has occurred.


SELECT
ID, Name, Age,
CountryName, CountryCapital
FROM Customer cu,Country c
WHERE
cu.CountryID *= c.CountryID

This quarry works in SQL Server 2000 but, it gives and error in SQL Server 2016.

1-qry-sql-2000

Image 1 : LEFT JOIN in SQL SERVER 2000 using  “*=”

2-sql-2016-qry-error

Image 2 : LEFT JOIN in SQL SERVER 2016 using “*=”

Then I looked what is the reason and I got the reason for the error.

The reason is SQL Server 2016 does not support to write quarries with JOINS using ” =*” and “*=” .

But in SQL Server 2000, we can write quarry  with JOINS using  ” =*” and “*=” .

According to above quarry, there is a Left Join.

You can lean more about SQL JOINS using below reference..

w3schools.com/sql/sql_join

Solution :

  1. A one solution is we can change the compatibility level of the database in SQL Server 2016 using database properties.
  2. But the Solution I used is use  “LEFT JOIN” instead of  “*=” .

Then the error has fixed..

But for more information I’ll show you how we can write SQL JOINS in SQL SERVER 2000 and LATER VERSIONS of SQL SERVER 2005 to SQL SERVER 2016.

LEFT JOIN/LEFT OUTER JOIN

LEFT JOIN we can also call as LEFT OUTER JOIN. But both of them gives us the same result. These are use before the WHERE statement.

In SQL Server 2000, we can write LEFT JOIN as “*=”.  But this is using after the WHERE statement.

Then result shows us all records in the LEFT hand side tables and matching values in RIGHT hand side table.

3-using-left-outer

Image 3 : Difference ways to use LEFT JOIN in SQL SERVER 2000

4-using-left-outer

Image 4 : LEFT JOIN in SQL SERVER 2016

RIGHT JOIN/RIGHT OUTER JOIN

RIGHT JOIN  we can also call as the RIGHT OUTER JOIN. But both of them gives us the same result.

In SQL Server 2000, we can write RIGHT JOIN as “=*”.

Then result shows us all records in the RIGHT hand side tables and matching values in LEFT hand side table.

4-ridgt-outer-2000

Image 5 : Different ways to use  RIGHT JOIN in SQL SERVER 2000

5-right-outer-2016

Image 6 : RIGHT JOIN in SQL SERVER 2016

INNER JOIN

INNER JOIN shows only the matching values in both tables. This join we can use in both SQL SERVER version in the same way.

6-inner-join-2000

Image 7 : INNER JOIN in SQL SERVER 2000

7-inner-join-2016

Image 8 : INNER JOIN in SQL SERVER 2016

FULL JOIN/FULL OUTER JOIN

Using FULL JOIN it shows all matching and outmatching  values in both tables. It shows outmatching values as NULL.

FULL JOIN we can use in both SQL SERVERS in the same way.

8-full-join-2000

Image 9 : FULL JOIN in SQL SERVER 2000-2016

 

Thank You !

IF YOU WANT TO TEST THIS, PLEASE CREATE A DATABASE “TestDB”

OR run below quarry.


CREATE DATABASE [TestDB]

and then run this quarry. There and sample data to text your quarries.


USE [TestDB]
GO
/****** Object: Table [dbo].[Country] Script Date: 1/2/2017 6:18:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NULL,
[CountryCapital] [varchar](50) NULL
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Customer] Script Date: 1/2/2017 6:18:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Country] ON

INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (1, N'Sri Lanka', N'Sri Jayawardhanapura')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (2, N'India', N'New Dhilli')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (3, N'Beljium', N'Bruselles')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (4, N'United Kingdom', N'London')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (5, N'United States of America', N'Washington, D.C.')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (6, N'Japan', N'Tokyo')
SET IDENTITY_INSERT [dbo].[Country] OFF
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (1, N'Buddhima', 24, 1)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (8, N'Chrystophe', 45, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (9, N'Patel', 30, 2)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (10, N'Nikolas', 20, 3)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (11, N'Ann', 35, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (12, N'Mike', 50, 4)
SET IDENTITY_INSERT [dbo].[Customer] OFF

How to Fix Error : 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.

Problem :

When I was executing a stored procedure in a SQL Server 2005 Database from my C# application, it gave me an error and here is the result I saw when I was debug it..

ole auto error 2

Image 1 : Exception Message

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.

And also I got below error messages with this error. I did not include all information of that message because it is too long..

SQL Server blocked access to procedure ‘sys.sp_OASetProperty’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server…
SQL Server blocked access to procedure ‘sys.sp_OAMethod’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server…
SQL Server blocked access to procedure ‘sys.sp_OADestroy’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server…

Then I searched solutions to fix this error and I found the solution.

Solution :

According to the message we have to enable the OLE Automation feature in Surface area configuration.

Here are steps to Enable the feature..

0 surface search

Image 2 : Search -> SQL Server Surface Area Configuration in Start menu

1 surface search

Image 3 : Select and click Surface Area Configuration for Features

Then you will enter to another form which contains features. Then Select “OLE Automation”

3 ole uttomation

Image 4 : Select OLE Automation

 

4 enable ole automation

Image 5 : Check the Enable OLE Automation Check box

Then Click “OK”, And Click “Apply”.

But when I click “OK”, I got another error that..

User does not have permission to perform this action.

You don’t have permission to RECONFIGURE statement.

5 enable error

Image 6 : Permission error

To fix this error, I had to give permission to the user..

6 set permissions

Image 7 : Give permission to the user

Then you can apply OLE Automation Feature..

Thanks !