The SQLIOSim utility simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years. For more information and to download […]
Category: SQL
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 […]
How to get only hour from getdate() function in sql server
select datepart(hour,getdate()) as [hours] DatePart Function will retrieve part of the date. Syntax:DATEPART(part, datetime) Part—-Ms for MillisecondsYy for YearQq for Quarter of the YearMm for MonthDy for the Day of the YearDd for Day of the MonthWk for WeekDw for the Day of the WeekHh for HourMi for MinuteSs for Second You can replace part […]
Format SQL statements with HTML to look like SQL Server Management Studio
Have you ever tried to post a SQL statement in a blog or web page, but cannot figure out how to format it to look like it does in SQL Server Management Studio or Query Analyzer? Compare this statement: BACKUP DATABASE AdventureWorks TO DISK = ‘C:BackupsAdventureWorks.BAK’ To this one formatted to appear like SQL management […]
Using Transact SQL to get length of TEXT field [T-SQL]
To calculate the lenght of a TEXT field the LEN function used to calculate the length of VARCHAR fields won’t work. You need to use the DATALENGTH T-SQL function: SELECT DATALENGTH(myTextField) AS lengthOfMyTextField
Log Parser 2.2 – work with IIS log files and more
Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. Download LogParser 2.2 from Microsoft. Get help […]
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 […]
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 […]
SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
Get Current Date & Time select GetDate() SQL Server 2000/2005 SELECTCONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnlyGO SQL Server 2008 SELECTCONVERT(TIME,GETDATE()) AS HourMinuteSecond,CONVERT(DATE,GETDATE(),101) AS DateOnlyGO