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

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

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 

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)

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

How to Find a SQL Server Database Object in entire sql server

Script to find the weather an object is exist in entire sql server or not, if yes in which database it is exit. Below is the  better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in each and every  local database. Using below query we are searching for UdfUserAccessLevel

Declare @SqlStmt nvarchar(500)

/* drop the temporary -tblTempDBObjects table if already exists which store all objects information */

If Object_Id('tempdb..#tblTempDBObjects') is Not Null

Drop table #tblTempDBObjects

/* create temporary tblTempDBObjects table in temp db*/

Create TABLE tempdb..#tblTempDBObjects (

dbName sysname,

objName varchar(250),

objtype char(2)

)

/*assign string value of sql statement to insert all objects to variable */

Select @SqlStmt = 'sp_msforeachdb ''Insert tempdb..#tblTempDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''

Exec sp_executesql @SqlStmt

/* searching for equired oj=bject in temptable --- UdfUserAccessLevel */

Select * From tempdb..#tblTempDBObjects Where objName like '%UdfUserAccessLevel%'

RETURN

Above statement will give below resultset and it contain database name, object and object type.