Advertisements

Archive

Archive for the ‘T SQL’ Category

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


Advertisements
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.


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 Trivia – SQL Server Code names

January 25, 2018 Leave a comment

 
Every time the SQL Server Product team starts working with their new version of SQL Server they give a Code Name to it, and before the final name is announced (which goes with the year name) the Code name gets popular among them, developers, users and the whole SQL community. But sometimes its very tricky and confusing to keep track of the Code Names for the older versions.

So, here is a list of SQL Server various versions I’ve compiled which lists the SQL Server release Final Name with Code Name, and other details like Year released, Version number and OS supported:

Year Final Name Version Code Name OS Support
2017 SQL Server 2017  14.0 Helsinki Win & Linux
2016 SQL Server 2016  13.0 Windows
2014 SQL Server 2014  12.0 Hekaton Windows
2012 SQL Server 2012  11.0 Denali Windows
2010 SQL Server 2008 R2  10.5 Kilimanjaro Windows
2010 SQL Azure DB  10.25 Cloud DB Azure
2008 SQL Server 2008  10.0 Katmai Windows
2005 SQL Server Analysis Services  – Picasso Windows
2005 SQL Server 2005  9.0 Yukon Windows
2003 SQL Server Reporting Services  – Rosetta Windows
2003 SQL Server 2000 x64 (64 bit)  8.0 Liberty Windows
2000 SQL Server 2000 x86 (32 bit)  8.0 Shiloh Windows
1999 SQL Server 7.0 OLAP Services  – Plato Windows
1998 SQL Server 7.0  7.0 Sphinx Windows
1996 SQL Server 6.5  6.5 Hydra Windows
1995 SQL Server 6.0  6.0 SQL95 Windows
1993 SQL Server 4.21  4.21 Windows
1992 SQL Server 4.2  4.2 OS/2
1991 SQL Server 1.1 (16 bit)  1.1 Pietro OS/2
1990 SQL Server 1.0 (16 bit)  1.0 Filipi OS/2

 

You can also check my post on [SQL and its history] !!!


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.