Advertisements

Archive

Archive for the ‘SQL Errors’ Category

SQL Python Error – ‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

August 29, 2018 Leave a comment

 
You are running a Python script by using sp_execute_external_script SP but its throwing this error:
 

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 27]
‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

 

You can refer to my blog post on setting up ML with Python with SQL Server, link: https://sqlwithmanoj.com/2018/08/10/get-started-with-python-on-sql-server-run-python-with-t-sql-on-ssms/

This fix will also work with R support with SQL Server.

 


Advertisements

SQL Job creation failing (having VBScript step) after SQL Server 2016/2017 upgrade

May 21, 2018 Leave a comment

 
Yesterday my friend pinged me and told that he is facing some issues while executing a SQL Job DDL script. They had upgraded their SQL Server version from 2008 to 2016, and while creating SQL Jobs they were facing below error:

Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 28 [Batch Start Line 2]
The specified ‘@subsystem’ is invalid (valid values are returned by sp_enum_sqlagent_subsystems).

 
I checked the code and on inquiring for a moment I saw that one of the SQL Job step was configured for a VB Script, as shown below:

...
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
@job_id=@jobId, 
@step_name=N'xyz VBScript', 
@step_id=3, 
@cmdexec_success_code=0, 
@on_success_action=4, 
@on_success_step_id=0, 
@on_fail_action=5, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, 
@subsystem=N'ActiveScripting', 
...

On quickly checking on net I came to know that this feature has been discontinued and should not be used.

** Important *\* This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
MS BoL link

 

This MS BoL link also mentions about the discontinued feature:

ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


SQL Server 2017 Setup error – VS Shell installation has failed with exit code 1638

May 14, 2018 Leave a comment

 
Today while installing SQL Server 2017 on my PC I got this popup on the Installation Progress tab. Earlier I had installed SQL Server 2017 but had to uninstall it for some reason. Now today while re-installing it I was getting this error.

TITLE: Microsoft SQL Server 2017 Setup
——————————
The following error has occurred:

VS Shell installation has failed with exit code 1638.

——————————
BUTTONS: OK
——————————

 

… and once you click OK, the installation resumes, but ends in error again, with following error details:

Error details:
§ Error installing Microsoft Visual C++ 2015 Redistributable
VS Shell installation has failed with exit code 1638.
Error code: 1638
Log file: C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20180824_225215\VCRuntime140_x64_Cpu64_1.log
Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000 &ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400×1638 to get help on troubleshooting.

 

Resolution:

– As per the second error, the SQL Server setup tries to install Microsoft Visual C++ 2015 Redistributable, but as I already have Visual Studio 2017 installed, thus it is failing to install a previous version of VC++, so you need to:
   1. Uninstall Visual Studio 2017.
   2. Install SQL Server 2017
   3. Install Visual Studio 2017 back again.

– Or, rather than uninstalling whole VS 2017, you can simply:
   1. Uninstall the VC++ 2017 Redistributable
   2. Install SQL Server 2017
   3. Install the VC++ 2017 Redistributable (download: x86, x64)


 


SQL Error – Cannot open backup device ‘https://xyz.blob.core.windows.net/xyz/xyz.bak’. Operating system error 50(The request is not supported.).

February 6, 2018 1 comment

 
Today I got an email from someone regarding an issue he was facing. He was trying to Backup SQL Server database to URL i.e. the Azure Blob Storage, by issuing below Backup command:

USE [master]
GO

BACKUP DATABASE [ManTest]
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak'
 WITH 
	COMPRESSION  
    ,STATS = 5;  
GO

And he was getting following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
‘https ://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak’.
Operating system error 50(The request is not supported.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

By checking the above error, you can make out that we are not able to connect to the Azure Storage resource due to some access issues. I checked online and found that you need to create Credentials on SQL Server from where you want to access the Azure Storage resource, and there are few methods for the same:
 

–> Method #1: Using storage account identity and Access Key

Azure Access Keys authenticates your applications when making requests to the Azure storage account. You will have to create a Credential on SQL Server end by providing the Access Key from your Azure Storage account, check below:

USE [master]
GO

CREATE CREDENTIAL [DBBackupCred] 
WITH IDENTITY = 'mantestaz'  -- Storage Account Name
,SECRET = 'xyx35HWTOnkDpiHkNWayz2Gsw6Figyxyx=='; -- Access key
GO

– Now you can fire the BACKUP command with the additional WITH CREDENTIAL option so that the Access can be authenticated for storing and writing backups on Azure Blob Container:

USE [master]
GO

BACKUP DATABASE [ManTest]  
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak' 
     WITH CREDENTIAL = 'DBBackupCred'   
    ,COMPRESSION  
    ,STATS = 5;  
GO

38 percent processed.
77 percent processed.
99 percent processed.
Processed 328 pages for database ‘ManTest’, file ‘ManTest’ on file 1.
100 percent processed.
Processed 3 pages for database ‘ManTest’, file ‘ManTest_log’ on file 1.
BACKUP DATABASE successfully processed 331 pages in 15.792 seconds (0.163 MB/sec).

… and its done successfully.
 

–> Method #2: Using Shared Access Signature (SAS)

A Shared Access Signature (SAS) is a URI that grants restricted access rights to Azure Storage resources. You can provide a shared access signature to clients who should not be trusted with your storage account key but whom you wish to delegate access to certain storage account resources. By distributing a shared access signature URI to these clients, you grant them access to a resource for a specified period of time.

Here is the syntax to create SAS Credential:

USE [master]
GO

CREATE CREDENTIAL 
	[https://<storageaccountname>.blob.core.windows.net/<containername>] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = '<SAS_TOKEN>'; 

SQL Error – SQL Server Installation fails | Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed)…

January 9, 2018 1 comment

 
Few days back I got this query from a developer who was trying to install SQL Server, it was not getting installed and was giving below error:

Microsoft.SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed) , included attributes (0), excluded attributes (Compressed, Encrypted).


 

–> RCA: As the above error message mentions that the Directory is Compressed, it gives you an idea that the SQL Server installation is not supported on compressed drives. So, if you are installing SQL Server on a drive which has compression enabled it will give you this error.
 

–> Fix: You have to check the Drive properties and see if Compression is enabled, like shown below and un-compress it by un-checking the option.