Monday, March 23, 2015

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




Friday, March 6, 2015

Information for SQL Server End of Mainstream support

Microsoft will offer a minimum of 10 years of support for Business, Developer, and Desktop Operating System (consumer or business) Software Products. Mainstream Support for Business, Developer, and Desktop Operating Systems will be provided for 5 years or for 2 years after the successor product (N+1) is released, whichever is longer. Microsoft will also provide Extended Support for the 5 years following Mainstream support or for 2 years after the second successor product (N+2) is released, whichever is longer.  


Products Released Lifecycle Start Date Mainstream Support End Date Extended Support End Date
Microsoft SQL Server 2000 Service Pack 4 06-05-2005 08-04-2008 09-04-2013
Microsoft SQL Server 2005 Service Pack 4 13-12-2010 12-04-2011 12-04-2016
Microsoft SQL Server 2008 R2 Service Pack 3 26-09-2014 08-07-2014 09-07-2019
Microsoft SQL Server 2008 Service Pack 4 30-09-2014 08-07-2014 09-07-2019
Microsoft SQL Server 2012 Service Pack 2 10-06-2014 11-07-2017 12-07-2022
Microsoft SQL Server 2014 Standard/Enterprise 05-06-2014 09-07-2019 09-07-2024
Microsoft SQL Server 7.0 Service Pack 4 26-04-2002 31-12-2005 11-01-2011

Thursday, March 5, 2015

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK"


If you are getting below issue : 

After creating linked server and when you try to run the query using ssms 
select * from  OpenQuery(ORACLE_LINK, 'select * from xxx_table_history') 
you get encounter below issue.  

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK" 

or

Execute permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys' (Microsoft SQL Server, Error 229)



Resolution : 

Login to the server where you are getting this error go to 

Server Objects --> Linked servers --> Providers --> right click on [OraOLEDB.Oracle] and select properties 


In properties window click on ALLOW INPROCESS 


Monday, March 2, 2015

Script to shrink all database log files in SQL Server

The below script is useful when ever you want to shrink all log files of user databases ( mostly you may need to do in test & dev environments) to free up the disk space. if you ran below script you can get the  DBCC SHRINKFILE script for each database. Change the results to text option before executing below script shown in Pic :1. Copy the results

(Pic : 1)
in new window past the copied results shown in Pic : 2 and execute it to shrink the log file.

SELECT  CHAR(13)+CHAR(10) + 'USE ['+ DB_NAME(database_id)+']'+CHAR(13)+CHAR(10) + 'GO'+CHAR(13)+CHAR(10) + 'DBCC SHRINKFILE (N'''+name+''' , 0, TRUNCATEONLY)'+CHAR(13)+CHAR(10) + 'GO'  FROM sys.master_files where file_id = 2   

(Pic :2)

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...