November 2010 (1)
August 2010 (1)
July 2010 (1)
June 2010 (3)
July 2009 (3)
June 2009 (1)
May 2009 (1)
February 2009 (1)
January 2009 (1)
November 2008 (3)
October 2008 (4)
September 2008 (9)
August 2008 (6)
July 2008 (3)
June 2008 (3)
January 2008 (1)
November 2007 (2)
October 2007 (6)
September 2007 (5)
August 2007 (22)
July 2007 (6)
June 2007 (1)
May 2007 (3)
April 2007 (27)
March 2007 (8)
February 2007 (6)
September 2006 (2)
August 2006 (4)
July 2006 (9)
June 2006 (17)
May 2006 (20)
April 2006 (12)
March 2006 (9)
February 2006 (4)
January 2006 (3)
December 2005 (2)
November 2005 (4)
October 2005 (5)
September 2005 (37)
August 2005 (83)
July 2005 (6)

Active Directory / LDAP (0)
ASP.Net (19)
Blackberry Development (4)
c# (34)
c++ (3)
Code Camp (1)
Excel (1)
Exchange (3)
Front Page 2003 (6)
FTP User Editor (4)
HTML / CSS / DHTML (8)
IIS (146)
IIS - Log Parser (7)
IIS / FTP (12)
IIS / Tools / Administration (42)
IIS / Tools / Authentication (6)
IIS / Tools / Compression (8)
IIS / Tools / Crash & Hang (12)
IIS / Tools / ISAPI Filters (17)
IIS / Tools / Log Files (17)
IIS / Tools / Scripts (28)
IIS / Tools / Security (9)
IIS / Tools / SSL (6)
IIS 7 (3)
Internet Information Server (1)
Me (Chris Crowe) (6)
MIME Types (1)
Misc (72)
Oulook Express (2)
Silverlight (1)
SQL Server (27)
SQL Server CTE (1)
Vista (15)
Vista Gadgets (8)
Visual Studio (11)
Voice over BroadBand (1)
Windows (33)
Windows Powershell (3)
Windows Sharepoint Services (0)
Windows Sharepoint Services (15)
Windows Vista (14)
Wine Cellar (1)
WMI (8)

Archive

August 2008 (6)

Some resources from Microsoft about SQL 2008

I just received my latest copy of TechNet Flash and it included a section on SQL Server 2008.

I have copied it here for anyone who may not receive TechNet flash - well work signing up for.

For overview resources, check out:
. SQL Server 2008 Videos
. Webcasts: 24 Hours of SQL Server 2008 for IT Professionals


If you are looking for assistance with SQL Server 2008 planning and installation, here are some helpful links:
. Planning a SQL Server Installation
. Installing SQL Server 2008
. Quick Start Installation
. Installation How-to Topics
. Locating Previous Versions of SQL Server for SQL Server 2008 migration using Microsoft Assessment and Planning Toolkit


Once you've installed SQL Server 2008, these resources can help you optimise your database environment:
. Configuring SQL Server 2008
. Database Engine Tuning Advisor Overview
. System and Sample Databases.

You can also find guidance for upgrading to SQL Server 2008 or migrating to SQL Server 2008.

If you are upgrading, be sure to check out Using Upgrade Advisor to Prepare For Upgrades


Why does T-SQL have such limited support for date & time functions?

I have used T-SQL for quite a few years now and I have always wondered why it has such bad support for date and times?

OK, I can create a data type called DateTime, but where is the function to return just the date portion?

There are functions to return the Year, Month and Day of a DateTime variable, but not the hour, minute or second!

Now even with DateTime2 we do not really get a lot more functionality. It would seem very common to want to format a date and time in a custom way but none is provided.

Now I can write a whole set of functions in a .NET library and go through the process of getting them installed onto a SQL Server but why is this basic sort of functionality not supported in the product in native code.

I know a lot of people have written a lot of different T-SQL Solutions to formatting dates etc, but it seems to me that it would take a developer only 1 day to implement native functions into T-SQL that provide a lot more functionality than having to write c# code and load the SQL-CLR just to format a date!

Here is the latest date & time functions as listed by the SQL Server 2008 BOL.

SYSDATETIME
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

SYSDATETIMEOFFSET
Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

SYSUTCDATETIME
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

CURRENT_TIMESTAMP
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

GETDATE
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

GETUTCDATE
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

DATENAME
Returns a character string that represents the specified datepart of the specified date.

DATEPART
Returns an integer that represents the specified datepart of the specified date.

DAY
Returns an integer that represents the day day part of the specified date.

MONTH
Returns an integer that represents the month part of a specified date.

YEAR
Returns an integer that represents the year part of a specified date.

DATEDIFF
Returns the number of date or time datepart boundaries that are crossed between two specified dates.

DATEADD
Returns a new datetime value by adding an interval to the specified datepart of the specified date.

SWITCHOFFSET
SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.

TODATETIMEOFFSET
TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

And here is MySQLs

ADDDATE()(v4.1.1)
Add dates

ADDTIME()(v4.1.1)
Add time

CONVERT_TZ()(v4.1.3)
Convert from one timezone to another

CURDATE()
Return the current date

CURRENT_DATE(), CURRENT_DATE
Synonyms for CURDATE()

CURRENT_TIME(), CURRENT_TIME
Synonyms for CURTIME()

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
Synonyms for NOW()

CURTIME()
Return the current time

DATE_ADD()
Add two dates

DATE_FORMAT()
Format date as specified

DATE_SUB()
Subtract two dates

DATE()(v4.1.1)
Extract the date part of a date or datetime expression

DATEDIFF()(v4.1.1)
Subtract two dates

DAY()(v4.1.1)
Synonym for DAYOFMONTH()

DAYNAME()(v4.1.21)
Return the name of the weekday

DAYOFMONTH()
Return the day of the month (0-31)

DAYOFWEEK()
Return the weekday index of the argument

DAYOFYEAR()
Return the day of the year (1-366)

EXTRACT
Extract part of a date

FROM_DAYS()
Convert a day number to a date

FROM_UNIXTIME()
Format UNIX timestamp as a date

GET_FORMAT()(v4.1.1)
Return a date format string

HOUR()
Extract the hour

LAST_DAY(v4.1.1)
Return the last day of the month for the argument

LOCALTIME(), LOCALTIME
Synonym for NOW()

LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
Synonym for NOW()

MAKEDATE()(v4.1.1)
Create a date from the year and day of year

MAKETIME(v4.1.1)
MAKETIME()

MICROSECOND()(v4.1.1)
Return the microseconds from argument

MINUTE()
Return the minute from the argument

MONTH()
Return the month from the date passed

MONTHNAME()(v4.1.21)
Return the name of the month

NOW()
Return the current date and time

PERIOD_ADD()
Add a period to a year-month

PERIOD_DIFF()
Return the number of months between periods

QUARTER()
Return the quarter from a date argument

SEC_TO_TIME()
Converts seconds to 'HH:MM:SS' format

SECOND()
Return the second (0-59)

STR_TO_DATE()(v4.1.1)
Convert a string to a date

SUBDATE()
A synonym for DATE_SUB() when invoked with three arguments

SUBTIME()(v4.1.1)
Subtract times

SYSDATE()
Return the time at which the function executes

TIME_FORMAT()
Format as time

TIME_TO_SEC()
Return the argument converted to seconds

TIME()(v4.1.1)
Extract the time portion of the expression passed

TIMEDIFF()(v4.1.1)
Subtract time

TIMESTAMP()(v4.1.1)
With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments

TIMESTAMPADD()(v5.0.0)
Add an interval to a datetime expression

TIMESTAMPDIFF()(v5.0.0)
Subtract an interval from a datetime expression

TO_DAYS()
Return the date argument converted to days

UNIX_TIMESTAMP()
Return a UNIX timestamp

UTC_DATE()(v4.1.1)
Return the current UTC date

UTC_TIME()(v4.1.1)
Return the current UTC time

UTC_TIMESTAMP()(v4.1.1)
Return the current UTC date and time

WEEK()
Return the week number

WEEKDAY()
Return the weekday index

WEEKOFYEAR()(v4.1.1)
Return the calendar week of the date (0-53)

YEAR()
Return the year

YEARWEEK()
Return the year and week

More details on the date & time functions in MySQL

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html


SQL Server - Free tools/utilities to download

 

I am not at work today because I have a very sore back so I thought in the moments that I can sit I could find SQL Server Tools that you can download for free. As we all know you will probably have to register to get them free but who cares free tools are free tools and you never know when you really need one of these tools.

The tools are listed in no particular order:

Idera have 4 free tools available that you may want including a free tool (SQL Job Manager) that they use to sell for $249 USD - they say that it is only for a short period that it is free so get downloading if you want it.

IDERA

SQL JOB MANAGER

View and manage SQL Server jobs across multiple servers from a central point of control with SQL job manager! Quickly identify failed or runaway jobs. Drag and drop to rearrange or reschedule jobs. Or drag and drop to move jobs across servers. SQL job manager will save you hours of time and reduce the administrative headaches associated with cross-server job management. It’s FREE for a limited time only (a $249 Retail Value) so get your copy now!

SQLSAFE FREEWARE EDITION

Idera’s SQLsafe Freeware Edition is free SQL Server database backup and recovery tool based on our award-winning SQLsafe product. SQLsafe Freeware Edition gives you a really high-performance backup and recovery engine, a scriptable interface (command line and XSP) and the ability to backup and compress any size and any number of databases, all for free.

SQL CHECK

SQLcheck is a free performance monitoring tool for SQL Server databases. It provides basic performance information about one SQL Server and runs as a secure screensaver or on demand.

SQL PERMISSIONS

Idera’s SQLpermissions is a freeware tool for copying or moving logins and permissions settings across SQLservers. SQLpermissions automates the time consuming job of configuring logins and permissions on one server to match another by generating a customizable TSQL script to do this for you. SQLpermissions also offers the flexibility to move one login or a group of logins and apply permissions across all databases on the target server, or a single database.

TOP 10 TIPS TO SECURE YOUR SQL SERVER

A free white paper on securing your SQL SERVER

http://www.idera.com (registration required)

      

RED-GATE

SQL LOG RESCUE

SQL Log Rescue is an award-winning log analysis tool, allowing you to explore your logs to view information on database transactions. Additionally, SQL Log Rescue should be kept on hand in case of a potential disaster – if you have accidentally deleted, inserted or modified your database, SQL Log Rescue will help you get the data back.

NOTE: SQL Log Rescue is compatible with SQL Server 2000 and but not SQL Server 2005 & 2008

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm (registration required)

 

HOW TO BECOME AN EXCEPTIONAL DBA

You know what it's like to be a DBA. Your skills are in constant demand at work, so how do you set aside time each week to maintain and learn new skills? Brad McGehee, SQL Server expert and Director of DBA Education of Red Gate Software, offers you an eBook packed with hard-learned advice on how to achieve more during your working hours. Be an Exceptional DBA.

NOTE: Also includes DBA Best Practices e-book

http://www.red-gate.com/products/SQL_Backup/offers/brad_exceptional_dba_ebook5.htm  (no registration required)

 

      

QUEST

QUEST DISCOVERY WIZARD (BETA)

SQL Server databases are proliferating throughout organizations. This presents a challenge to all levels of the IT organization – how many SQL Servers are deployed, how many are running mission-critical versus non-critical applications, where are they located, are any candidates for consolidation?

SQL Server DBA

Quest Discovery Wizard allows you to detect SQL Server instances and monitor the current network for changes. It provides a number of discovery methods which help you to reveal even those SQL Server instances, which cannot be detected using the native tools.

Discovery Wizard allows you to:

  • Find how many databases there are and how much data is there
  • Determine databases that are good candidates for consolidation
  • See history of these databases including when created and installed
  • Learn what versions of SQL Server are deployed and what their service packs are
  • Understand server names, instance names and operating systems
  • See who the end users are

http://www.quest.com/landing/?ID=1305&AdCode=SQLDiscover_download_031208 (registration required)

 

QUEST SPOTLIGHT ON SQL SERVER

Quest use to offer a free version of their enterprise tool Spotlight on SQL Server - this seems to have been dropped - it was very useful but it appears to be no longer provided.

We  would love to see a new release or even the old version re-released...

      

SQL MANAGER.NET

EMS SQL Manager for SQL Server (light)


EMS SQL Manager for SQL Server is a high performance tool for Microsoft SQL Server development and administration. SQL Manager works with any MS SQL Server versions from 7 to the newest one and supports the latest SQL Server features including new data types (datetimeoffset, hierarchyid, geometry, geography and more), table types and table-valued parameters, logon triggers, backups with compression and other. It offers plenty of powerful SQL tools such as Visual Database Designer that allows creating SQL Server database in few clicks, Visual Query Builder and advanced SQL editor to build complicated SQL Server queries and many more useful features for efficient SQL Server administration. SQL Manager for SQL Server has a state-of-the-art graphical user interface with well-described wizard system, so clear in use that even a newbie will not be confused with it.

http://www.sqlmanager.net/en/products/mssql/manager (no registration required)


Microsoft has a SQL Server 2008 Jumpstart Training Site

This site contains presentations, recordings, hands-on labs and demonstrations for the SQL Server 2008 JumpStart technical training event.

There are 5 tracks in this event:

You must register online to get access to the content, which I have just done.

At this stage it is hard to know if it is going to be very useful, but any free training may help me and I am not going to say no.

See SQL Server 2008 Jump Start for more details.


A free icon editor for Visual Studio 2008 users

Axialis Software, in association with Microsoft Corporation, presents Axialis IconWorkshop Lite for Visual Studio 2008:

  • 100% Free for Visual Studio 2008 users;
  • Make icons for Windows up to 256x256 PNG-compressed icons for Windows Vista™ and include them in your software projects;
  • Use an advanced icon editor with various tools, filters and effects;
  • Work efficiently using a Plug-in for Visual Studio 2008;
  • Create icons from images or ready-to-use image objects;
  • Use a fully integrated workspace with librarian, built-in file explorer with thumbnail preview, image viewer and more...

 

File: IconWorkshop-Lite-6.3.1.0-Eng.exe

Language: English - Size: 22.0 MB

Visual Studio® 2008 is required*

For more details see : http://www.axialis.com/download/iwlite.html


"The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools."

You may find that when installing SQL Server 2008 you receive the follow error during installation.

"The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools."

A couple of things to do:

  1. Remove SQL Server 2005 Express Tools
  2. Remove Red-Gate SQL Prompt if installed.

 

UPDATE:

SQL Prompt v3.9 (available for download from www.red-gate.com) addresses this problem and no longer blocks the installation of SQL server 2008. Also, SQL Prompt v3.9 supports SQL Server 2008.

Tanya Joseph
Red Gate Software Ltd