Login Failed The user is not associated with a trusted SQL Server connection: SQL Server 2005

This occurs when you try to login using SQL Server user cardinals without configuring SQL Server to use Windows and SQL Server authentication.

Resolution:

[1] Open SQL Server management studio – [2] Login to SQL server using windows authentication – [3] Right click on SQL Server instance and go to properties – [4] Expand security tab and select 'SQL Server and Windows authentication mode' under 'Server Authentication'

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding - Microsoft SQL Server 2005

Symptom:

SQL Server throws an exception 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2) when try to connect from a remote computer. Most probable symptom for the error is TCP Port 1433 being blocked by the windows firewall in a computer running windows XP(SP2).

Solution

Scenario 1

Make sure you have enabled remote connections from the SQL Server surface area configuration and allow TCP/IP, Named Pipe protocol or combination as necessary.

• To Enable Remote connections: [1] SQL Server 2005 – [2] Configuration Tools – [3] SQL Server Surface area configuration – [4] Click on 'Surface area configuration for services and connections'.
  
• Expand [1] Database Engine – [2] Remote Connections – [3] Select Local and remote connections – [4] Select the desired protocol (In this scenario I have used TCP/IP and Named Pipes)
  
• Restart the SQL Server.
  

Scenario 2

Open TCP Port 1433 – This is the default port that SQL server use for remote connections. By default windows XP firewall deny access of this port:

• Open [1] Control Panel – [2] Windows Firewall – [4] Click on 'Add Port'
  
• Add: Name = SQL Server (Any meaningful name), Port Number = 1433 (by default, if you have changed the default port you have to make sure you enter the corresponding port number)

DATEDIFF Function - TSQL - Microsoft SQL Server 2000/2005

Ex: DATEDIFF(yyyy, applicant.DateOfBirth, GETDATE()) = 28 [age of applicant in years]

datepart - Abbriviation
year - yy, yyyy
quarter - qq, q
month - mm, m
dayofyear - dy, y
day - dd, d
week - wk, ww
hour - hh
minute - mi, n
second - ss, s
millisecond - ms
microsecond - mcs
nanosecond - ns