SQL Server blocked access to statement OPENROWSET / OPENDATASOURCE

Sometime you may get below error when you execute query using OPENROWSET statement:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

This error is coming because Ad Hoc Distributed Queries configuration parameter is disabled. To enable Ad Hoc Distributed Queries you had to execute below commands:

sp_configure 'show advanced options',1  
reconfigure 
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure 

After executing above commands your problem will be solved.

You can do above changes in following ways also:

Right Click in SSMS on the SQL Server instance from the object explorer pane and choose Facets from the drop down menu.

In the facets window change the Facet to Surface Area Confuguration and set the AdHocRemoteQueriesEnabled to True

Find last execution time of Stored Procedure

SQL Server stores the execution plan of statements before executing any statement.In order to find the last execution we just need to query the SYS.dm_Exec_query_stats DMV.

SELECT ST.Text AS SQL, QS.creation_time,QS.lASt_Execution_time,QP.dbID,QP.ObjectID
FROM SYS.dm_Exec_query_stats AS QS
CROSS APPLY SYS.dm_Exec_sql_Text(QS.sql_handle) AS ST
CROSS APPLY SYS.dm_Exec_Text_query_plan(QS.plan_handle,DEFAULT,DEFAULT) AS QP WHERE ST.Text like '%<SP_Name>%'

Find all tables without Triggers in SQL Server

Sometime you need to know all tables without trigger before adding, changing  or dropping column in a table.  Here are two ways to know that:

SELECT S1.Name FROM SysObjects S1 LEFT JOIN SysObjects S2 ON
S1.ID =S2.Parent_Obj
AND S2.XType = 'TR'
WHERE S2.Name IS NULL
AND S1.XType = 'U'
ORDER BY S1.Name

SELECT T.TABLE_Name FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN (SELECT OBJECT_Name(o.Parent_Obj) AS TableName
FROM SysObjects O
WHERE OBJECTPROPERTY(O.[ID], 'IsTrigger') = 1
) TR ON T.TABLE_Name= TR.TableName
WHERE TR.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
ORDER BY T.TABLE_Name

Backup History

Here is a useful script to get the backup History for all databases in SQL Server:

SELECT S.Server_Name, S.Recovery_Model, S.Database_Name, M.Physical_Device_Name,
    CASE S.[Type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType,
    S.Backup_Start_Date, S.Backup_finish_Date,
    CAST(DATEDIFF(second, S.Backup_Start_Date,S.Backup_Finish_Date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeElapsed,
    CAST(CAST(S.Backup_Size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size,
    CAST(S.Dirst_LSN AS VARCHAR(50)) AS First_LSN,
    CAST(S.Last_LSN AS VARCHAR(50)) AS Last_LSN
FROM msdb.dbo.BackupMediaFamily AS M 
INNER JOIN msdb.dbo.BackupSet AS S ON M.Media_Set_ID = S.Media_Set_ID
ORDER BY S.Backup_Start_Date DESC