xp_cmdshell help; The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

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. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Execute below statements to enable "xp_cmdshell" and 

EXEC sp_configure 'xp_cmdshell', 1  

GO  

RECONFIGURE  

GO

After executing above statements if you are facing below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Try below statements to enable advanced options  

EXEC sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

EXEC sp_configure 'xp_cmdshell', 1;  

GO  

RECONFIGURE;  

GO

How to check the database size in PDW

Using below DBCC command via NEXUS query editor or SQL Server DATATOOL or Visual Studio

USE DATABASE_NAME

dbcc pdw_showspaceused

You can view the size in Mega Bytes  

Script to disable/enable all triggers (Database Level) in a database & Enable or Disable Server level triggers (SERVER LEVEL)

Use below script to disable or enable all triggers in MyDatabase  (Database level) 

To disable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

The following example disables all DDL triggers that were created at the server scope. (Server Level Triggers) 

USE master

GO

DISABLE Trigger ALL ON ALL SERVER;

GO

USE master

GO

ENABLE Trigger ALL ON ALL SERVER;

GO

Migrating SQL Server 2005 DTSX Packages to SQL Server 2012

Migrating SQL Server 2005 DTSX Packages to SQL Server 2012

Login into the SSMS of sql server 2005 Expand the Management à Legacy à Data Transfarmation Services tab in object explorer shown as below pic it displayes all the dtsx packages located in source server i.e. SQL Server 2005

Right click on Data Transfarmation Services and select Migration Wizard

In Package Migration Wizard – Choose Source Location Screen the source as Microsoft SQL Server and give the server name and select the authentication mode(either windows authentication or SQL Server authentication) .  And click on Next button

Next  screen In Package Migration Wizard – Choose Destination Location, select the destination as DTSX File specify the Folder name where you saving  these packages. And click on Next

Next  screen In Package Migration Wizard – List Packages, here select the DTSX Packages to you want to migrate. And click Next

Next  screen In Package Migration Wizard – Specify a Log file, give the path and log file name, it will log all migration information. And click Next

Next  screen In Package Migration Wizard – Complete the Wizard, This will provide the summary of migration. Finally click on Finish.

Next  screen In Package Migration Wizard – Migration the Packages, Last screen indication what are the packages migration any errors/warnings given information.

And you can find the DTSX files which are selected to migrate in Destination folder shown in  above image. And copy the DTSX package files into destination server i.e SQL Server 2012

Login into the Destination server i.e. SQL Server 2012  . select Project Conversion Wizard from program files    

It will start Integration Services Project Conversion Wizard click Next on initial screen

In Locate Package screen select the Source as File System and give files location copied from SQL Server 2005 in Folder.  And click on Next

It will display the list of all available DTSX packages in the folder. Select the packages which you want to migrate. Shown as in below screen. And Click on Next

It will ask the Path and Project Name to store upgraded packages as ispac (Integration Services Project Deployment File)  format. Click Next till Review Screen.

Click on Convert button in Review & click on close button in success screen, it creates DTSUpgrate2005.ispac in above mentioned output path.

Select the Deployment Wizard in Integration Services

In Integration Deployment Wizard – Select Source Screen Select Project Deployment file radio button and in path section give the .ispac file location to deploy all dtsx package files

In Integration Deployment Wizard – Select Destination screen Enter the destination server name in Server name and select the path by clicking Browse button.

Click next and click Deploy in Review screen.

It will create dtsx files in Integration Services Catelogs à SSISDB à your prject name folder

Note  :If any DTSX package file will have any deprecated task or files used while converting you will get the errors.

SQL Server Latest service packs

Product Version	Latest Service Pack
SQL Server 2014	n/a
SQL Server 2012	SP2
SQL Server 2008 R2	SP2
SQL Server 2008	SP3
SQL Server 2005	SP4
SQL Server 2000	SP4