Team - SQL Scripts

Team SQL Scripts

**Find Donation Transaction IDs**				
 
> `Select DonationtransactionsID from Donationtransactions where dateadded > '2014-11-29'`
 
 
**Find Event Transaction IDs**
 
> `Select EventTransactionsID from EventTransactions where dateadded Between '2014-03-07 01:00:00' and '2014-03-10 11:00:00'`
 
 
**Event Force Re-download in RE**
 
> `UPDATE BBNCTransactions SET ProcessedDate = NULL, ProcessedUserID = NULL, DirtyUserID = NULL WHERE
TransactionID IN (4525, 4524, 4521, 4519, 4518, 4487) and TransactionType = '{172A5D9A-5241-493c-A2C2-EAC164C095B0}'`
 
 
**Donation Force Re-download in RE**
 
> `UPDATE BBNCTransactions SET ProcessedDate = NULL, ProcessedUserID = NULL, DirtyUserID = NULL WHERE
TransactionID IN (4525, 4524, 4521, 4519, 4518, 4487) and TransactionType = '{5705B543-4033-4a3a-BBCD-A731403EE1E6}'`
 
 
**Donation Force Re-download in BBNC**
> `update dbo.donationtransactions set processed_date = null where DonationTransactionsID in (###,###,###)`
 
 
**Event Force Re-download in BBNC**
> `Select EventTransactionsID from EventTransactions where dateadded Between '2014-03-07 01:00:00' and '2014-03-10 11:00:00'`
 
 
**Find Users With Supervisor Rights**
 
> `Select * from Clientusers where InternalUser = 1 OR isSupervisor = 1`
 
 
**Create Table Showing BBNC Space Usage**
 
> `CREATE TABLE #TableSpaceUsed`
 
 (
           
           Table_name NVARCHAR(255),
           Table_rows INT,
           Reserved_KB VARCHAR(20),
           Data_KB VARCHAR(20),
           Index_Size_KB VARCHAR(20),
           Unused_KB VARCHAR(20)
 
 )
 
> `INSERT INTO #TableSpaceUsed`
 
> `EXEC sp_MSforeachtable 'sp_spaceused ''?'''`
 
> `SELECT Table_name,Table_Rows,`
> `CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB,`
> `CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as dataKB,`
> `CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as reservedKB,`
> `CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB`
> `FROM #TableSpaceUsed`
> `ORDER BY dataKB DESC`
> `DROP TABLE #TableSpaceUsed`
 
 
**Opt-Back In For Emails**
 
> `delete from dbo.emaillist_globalsubscription`
> `where emailaddress in`
> `('email address', 'email address')`