SSRS Temp files filling drive

If you have SSRS files filling your temp drive, look for the RSTempFiles.

Default folder:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\RSTempFiles

You can move this folder to a different drive by copying the RSTempFiles folder to the target drive (for this example "R:\RSTempFiles").

Make sure that the SSRS service account alias has full permissions on the folder

Default local machine service account alias:

[NT SERVICE\ReportServer]



Locate the rsreportserver.config file

Default folder:
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer


In this file Located the <Service> tag and add entry for FileShareStorageLocation with new path:


<Service>
  <FileShareStorageLocation>
    <Path>R:\RSTempFiles</Path>
  </FileShareStorageLocation>

...
</Service>


After saving the file restart the SSRS instance.  It should start using the new location.



For more info about the files stored in this folder:

https://blogs.msdn.microsoft.com/jgalla/2008/06/30/all-those-temporary-files-rstempfiles/

sp_hadr_isprimary

USE master;
GO

IF OBJECT_ID('dbo.sp_hadr_isprimary','P') IS NULL
   EXEC ('CREATE PROCEDURE dbo.sp_hadr_isprimary AS PRINT 1;');
GO

--return 1=current instance is primary, 0=current not primary
ALTER PROCEDURE dbo.sp_hadr_isprimary
( @agname sysname = 'AG_GROUP_NAME' )
AS
   DECLARE @PrimaryReplica sysname
         , @ThisReplica sysname
, @retval int = 0;
BEGIN
   SET NOCOUNT ON;
   SET @ThisReplica = cast(ServerProperty('ServerName') as sysname);

          SELECT @PrimaryReplica = hags.primary_replica
            FROM sys.dm_hadr_availability_group_states hags
      INNER JOIN sys.availability_groups ag
         ON ag.group_id = hags.group_id
           WHERE ag.name = @agname;

   IF UPPER(@PrimaryReplica) =  UPPER(@ThisReplica)
    BEGIN
      SET @retval = 1
    END

          SELECT @retval "IsPrimary"
               , @agname "AG_Name"
               , @PrimaryReplica "PrimaryReplica"
               , @ThisReplica "ThisReplica"
   RETURN @retval
END;
GO



Example Usage:

declare @retval int

exec @retval = master.dbo.sp_hadr_isprimary

SELECT @retval "return_value"

bare bones

To start SQL Server and have it only recover the master database and leave all others offline, can use this command:

SQLServr.exe -T3608 -T3609

Also, if you have a named instance, will need to include the -s parameter with the instance name.

SQLServr.exe -T3608 -T3609 -sINSTNAME

various security functions

--execute as login = 'sa';
--execute as user = 'guest';

SELECT @@version
     --current context / execute as
     , user "user" --same as user_name()
     , user_name() "user_name" 
     , current_user "current_user"  
     , session_user "session_user" 

     --current login unless execute as
     , system_user "system_user" 
     , suser_name() "suser_name"  

     --original context
     , ORIGINAL_LOGIN() "original_login" 

GO

Example checking for DML type of trigger

Most of the time it is better to have separate insert/update/delete triggers if you need to do different things based on the DML type.  If you did want to have common code then could call a stored procedure with the DML type as a parameter.

In the rare instances where it may be more practical to have one trigger to rule them all, here is an example of how to check for the type of DML activity of the transaction executing the trigger.


ALTER TRIGGER dbo.tr_test_iud
   ON  dbo.test
   AFTER INSERT,UPDATE,DELETE
AS 
   DECLARE @trgtype varchar(10)
BEGIN
   SET NOCOUNT ON;

   IF EXISTS (SELECT TOP 1 1 FROM inserted)
      IF EXISTS (SELECT TOP 1 1 FROM deleted) SET @trgtype = 'UPDATE'
      ELSE SET @trgtype = 'INSERT'
   ELSE SET @trgtype = 'DELETE'

   PRINT @trgtype
END
GO


Keep in mind also, it is best practice to always code for there being more than one record involved in the transaction resulting in multiple records in the insert/deleted tables.

Server not configured for DATA ACCESS

Error: Server 'SERVER_NAME' is not configured for DATA ACCESS.


Resolution:
exec sp_serveroption 'SERVER_NAME', 'data access', 'true'
go

SQL Configuration Manager Cannot connect to WMI provider

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.


To work around this problem, open a command prompt, type the following command, and then press ENTER:

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2016        130
Microsoft SQL Server 2014        120
Microsoft SQL Server 2012        110
Microsoft SQL Server 2008 R2   100
Microsoft SQL Server 2008        100
Microsoft SQL Server 2005        90


https://support.microsoft.com/en-us/kb/956013