Archive

Archive for the ‘T SQL’ Category

SQL DBA – Integration Services evaluation period has expired

July 2, 2021 Leave a comment

 
I got an email from one SQL Server developer that he is not able to use import/export wizard and it is failing with below error:

TITLE: SQL Server Import and Export Wizard --------------------------------------

Data flow execution failed. Error 0xc0000033: 
{5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}: Integration Services evaluation period has 
expired. Error 0xc0000033: {5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}: Integration 
Services evaluation period has expired. ------------------------------ 

ADDITIONAL INFORMATION:  Integration Services evaluation period has expired.  
({5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}) ----------------------------------------

 

–> Investigate:

As per the above error message its clear that the SQL Server Instance that you had installed was under Evaluation of 180 days, because you didn’t applied any Product Key. So, now how can you make it usable again? All you need is a Product key of SQL Server and installation media to start an upgrade so that you can apply the new Product Key there.
 

–> Fix:

1. Open the SQL Server Installation Center and click on Maintenance link, and then click on Edition Upgrade:

SQL Evaluation expiry 03
 

2. Now on the Upgrade window Click Next and you will reach the Product Key page, apply the Key and click Next:

SQL Evaluation expiry 04
 

3. On the Select Instance page, select the SQL Instance that you want to fix and Click next. It will take some time and finally you will see a final window and click Upgrade:

SQL Evaluation expiry 05
 

4. Finally you will see the successful window, click on Close button:

SQL Evaluation expiry 06
 

5. Now Restart the SQL Server Service for this Instance, and you will see it running fine.
 

–> Finally, go back to SSMS and now you can connect to the SQL Instance.


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 Tips – Issues with BCP when you have two SQL Server instances

June 9, 2018 1 comment

 
I observed one thing here with BCP (Bulk Copy Program), when you have 2 versions of SQL Server installed on you PC or Server. I had SQL Server 2014 & 2016 installed on one of my DEV server.
 

So if you are executing Query from SQL 2016 instance, it was inserting records in SQL 2014 instance:

exec master..xp_cmdshell ‘BCP AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’

 

But even if you use BCP 2016 version, it was still inserting in SQL 2014 instance:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’

 

On inquiring a bit I came to know that by default the BCP command points to the Default instance of SQL Server, no matter from which instance you are executing the Query. MSDN BoL

So, you have to provide parameter to connect to server. If not specified, it would go to default instance
 

So your BCP command should go with the “-S” option, like this:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c -S DBServerName/InstanceName


Categories: SQL Tips Tags: ,

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.