SQL

BigInt = Int64 (Long)
Int = Int32 (Integer)
SmallInt = Int16 (Short)
TinyInt = Byte

e.g. byte recurrenceType = reader.GetByte(8);

The MSSQL server for the SharePoint farm I manage always seems to be running out of room. After a little rummaging, I found the culprit – the database log files. For databases that ranged from 10MB to 45MB, the log files were as large as 450MB – which, when you calculate the space used by 50+ databases, all adds up.

To solve the issue I created a new scheduled task on the server which triggers the following script (found at the end of this post) once a week.

DECLARE @DBName varchar(255)
DECLARE @DBLogicalFileName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR

SELECT DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.Name
FROM sys.master_files MaTableMasterFiles
WHERE

-- ONLINE
MaTableMasterFiles.State = 0

-- Only look at databases to which we have access
AND has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

-- Not master, tempdb or model
AND db_name(MaTableMasterFiles.database_id) NOT IN ('Master','tempdb','model')

AND type_desc LIKE 'log'
GROUP BY MaTableMasterFiles.database_id, MaTableMasterFiles.name
ORDER BY 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY')
EXEC ('Use [' + @DBName + '] DBCC SHRINKFILE ("' + @DBLogicalFileName + '")')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

To simply returning a limited number of records while using SubSonic I added a position field to the View.

SELECT ROWID= ROW_NUMBER() OVER ( ORDER BY ProductID ASC), ProductID, [Name]
FROM Production.Product

SQL Server 2005 ROW_NUMBER()

To return one or more records from a Microsoft Access database in a random order, you need to use the Rnd() function. When called directly from within an SQL query, the random seed is cached – resulting in your records always returning in the same order. To get around this issue, you need to ensure that a different value is passed to the Rnd() function each time it is called – this gives Access a kick and forces it to make use of the new seed.

<%
Randomize()
intRandom = Int(1000 * Rnd) + 1
sqlQuery = "SELECT Rnd(" & (-1 * (intRandom)) & " * Id), Id, Title, Description " & _
"FROM MyTable " & _
"ORDER BY 1"
Set objRS = objConn.Execute(sqlQuery)
%>

Just adding these code snippets as a reminder for future projects.

MySQL
SELECT someColumn FROM someTable ORDER BY RAND() LIMIT 1

MS SQL Server 7.0 and above (running on Windows 2000)
SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

MS SQL Server 7.0 and above (not running on Windows 2000) 
SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))