Archive

Archive for the ‘SQL Errors’ Category

SQL Error – “SELECT TOP 100” throws error in SparkSQL – what’s the correct syntax?

January 23, 2020 Leave a comment

 
In SQL Server to get top-n rows from a table or dataset you just have to use “SELECT TOP” clause by specifying the number of rows you want to return, like in the below query.

But when I tried to use the same query in Spark SQL I got a syntax error, which meant that the TOP clause is not supported with SELECT statement.

%sql
Select TOP 100 * from SalesOrder

Error in SQL statement: ParseException:
com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException:
mismatched input ‘100’ expecting (line 1, pos 11)

== SQL ==
Select top 100 * from SalesOrder
———–^^^

 

As Spark SQL does not support TOP clause thus I tried to use the syntax of MySQL which is the “LIMIT” clause.

So I just removed “TOP 100” from the SELECT query and tried adding “LIMIT 100” clause at the end, it worked and gave expected results !!!

%sql
Select * from SalesOrder LIMIT 100

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.

 


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>';