Category: SQL

Categories
Micro$oft Microsoft SQL

Simulate SQL Server Activity on a Disk Subsystem with SQLIOSim from Microsoft

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 SQLIOSim utility from Microsoft see KB231619.

NOTE: The SQLIOSim utility replaces the SQLIOStress utility. The SQLIOStress utility was formerly named the SQL70IOStress utility.

Categories
Linux SQL

SQL Server 2005 Remote Connectivity

  1. 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.
  2. Open Firewall TCP Port 1433 for SQL Database Engine.
  3. Open Firewall UDP Port 1434 for SQL Browser Service.

Categories
getdate Linux SQL Windows

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 Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

You can replace part with the any of the part in the specified above items.

For example, for hours:

SELECT DATEPART(Hh,getdate())

Categories
Blog Format for Blog HTML Linux SQL Windows

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 tools:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:BackupsAdventureWorks.BAK'

Here’s the HTML – just substitute your commands.

Line 1 (BACKUP DATABASE AdventureWorks):

<code><span lang=”EN-US” style=”color: blue; font-size: 10pt;”>BACKUP DATABASE</span></code> <code><span lang=”EN-US” style=”color: black; font-size: 10pt;”>AdventureWorks</span></code><span lang=”EN-US” style=”color: black; font-family: ‘Courier New’; font-size: 10pt;”></span>

Line 2 (TO DISK = 'C:BackupsAdventureWorks.BAK'):

<code><span lang=”EN-US” style=”color: blue; font-size: 10pt;”>TO DISK =</span></code> <code><span lang=”EN-US” style=”color: red; font-size: 10pt;”>’C:BackupsAdventureWorks.BAK'</span></code>

Categories
Linux SQL Transact-SQL Windows

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

Categories
IIS Linux LogParser SQL Windows

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 with LogParser in the LogParser Forums.

More on LogParser

Categories
Backup Database Linux Microsoft Query Analyzer SQL Transact-SQL Utility Windows

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:

  1. The name of the database to back up.
  2. The backup device where the full database backup is written.

Example

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:BackupsAdventureWorks.BAK'

Categories
Date Linux Query SQL Time Windows

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

The DATEPART function accepts two parameters :

DATEPART ( datepart , date ) where
datepart – specifies the part of the date to return. For eg: year, month and so on
date – is the datetime or smalldatetime value

QUERY

SELECT
DATEPART(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 using a smalldatetime, only information up to the ‘minute’ gets displayed. Seconds and milliseconds are always 0.

Categories
Linux SQL Windows

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) Day
FROM tableName
) temp
group by Year, Month, Day
order by Year desc, Month desc, Day desc

————————————————
— Count items per hour

SELECT Year, Month, Day, Hour, COUNT(Hour) AS “Items per Hour”
FROM
(
SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,
DATEPART(DAY,CreatedOn) Day, DATEPART(HOUR, CreatedOn) Hour
FROM TableName
) temp
group by Year, Month, Day, Hour
order by Year desc, Month desc, Day desc, Hour desc

Keywords:
sql server something per day
sql count per hour
sql count per month
sql count time of day
“sql server” count
sql server day count
count per hour in sql
count records per hour per day sql
day,hour,minute format in sqlserver
entries per day sql select

Categories
Linux SQL Time Windows Windows 2000

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

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO