SQL - PowerCram Blog

SQL Server 2005 Remote Connectivity

Enable remote named pipe or tcp: All programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration -> Configuration for Services and Connections -> Remote Connections, choose either enable TCP or Named Pipe or both. Open Firewall TCP Port 1433 for SQL Database Engine. Open Firewall UDP Port 1434 for SQL Browser Service.

MS SQL Backup Database to Disk

To create a full database backup using Transact-SQL Execute the BACKUP DATABASE statement to create the full database backup, specifying: The name of the database to back up. The backup device where the full database backup is written. Example BACKUP DATABASE AdventureWorks TO DISK = ‘C:BackupsAdventureWorks.BAK’

SQL: How to extract Year, Month, Day, Hour, Minute and Seconds from a DateTime

The DATEPART function accepts two parameters : DATEPART ( datepart , date ) wheredatepart – specifies the part of the date to return. For eg: year, month and so ondate – is the datetime or smalldatetime value QUERY SELECTDATEPART(year, GETDATE()) as ‘Year’,DATEPART(month,GETDATE()) as ‘Month’,DATEPART(day,GETDATE()) as ‘Day’,DATEPART(week,GETDATE()) as ‘Week’,DATEPART(hour,GETDATE()) as ‘Hour’,DATEPART(minute,GETDATE()) as ‘Minute’,DATEPART(second,GETDATE()) as ‘Seconds’,DATEPART(millisecond,GETDATE()) as ‘MilliSeconds’ Note: When […]

SQL Server count items per day or per hour

Here are a couple of quick SQL statements to return counts based on entries or items or transactions per day or per hour. You can tailor as desired. — Count items per day SELECT Year, Month, Day, COUNT(Day) AS “Items per Day”FROM(SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,DATEPART(DAY,CreatedOn) DayFROM tableName) tempgroup by Year, Month, Dayorder by Year desc, Month desc, […]