Microsoft SQL Select Date Range

Select an arbitrary range of dates in Microsoft SQL, which can be joined against data with date gaps using a LEFT OUTER JOIN on the date range table.

DECLARE @DateStart datetime = '01-01-2013';
DECLARE @DateEnd   datetime = DATEADD(day, 29, @DateStart);

WITH DateRange(Day) AS (
    SELECT    @DateStart AS [Day]
    UNION ALL
    SELECT    DateRange.Day + 1
    FROM      DateRange
    WHERE     DateRange.Day < @DateEnd
)
SELECT    CONVERT(date, DateRange.Day) AS [Date]
FROM      DateRange

Microsoft SQL Failover Partner

Connection String
The SQL Native Client data providers introduced a new connection string keyword to support some of the new features in SQL Server 2005. The failover partner keyword is used to specify the second partner of the database mirroring session in the connection string. SQL Native Client connects to whichever server is the principal at the time the connection is made.

Following is an example connection string:

Data Source=SQLA\INST1;Failover Partner=SQLB\INST1;Initial Catalog=DBMTest;Integrated Security=True

For more information on connection attributes when using SQL Native Client with ADO.NET, OLE DB, and ODBC, see Using Connection String Keywords with SQL Native Client in SQL Server 2005 Books Online.

The syntax of the failover partner keyword is slightly different from one connection method to another:

• OLE DB: FailoverPartner (no space between “Failover” and “Partner”)
• ODBC: Failover_Partner (underscore between “Failover” and “Partner”)
• ADO.NET: Failover Partner (one space between “Failover” and “Partner”)
• JDBC: failoverPartner (no space between “failover” and “Partner”)

Note: Keywords are not case sensitive. Make sure there is no white space before or after the keyword, before or after the semi-colons, and before or after the equal to operator in the connection string.

Source: http://technet.microsoft.com/en-us/library/cc917713.aspx#EDAA

Microsoft SQL Insert Identity

Insert an identity into a table:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

Example:

-- Create products table.
CREATE TABLE products
(id int IDENTITY PRIMARY KEY, product varchar(40))
GO

-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product)
VALUES (3, 'garden shovel')
GO

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product)
VALUES (3, 'garden shovel')
GO

SELECT *
FROM products
GO

-- Drop products table.
DROP TABLE products
GO

Source: Microsoft Developer Network

Microsoft SQL MD5 Support

Microsoft SQL includes support for the MD5 hashing algorithm:

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HASHBYTES('MD5', 'String to MD5')), 3, 32);

New Microsoft Outlook Social Connectors Released

Microsoft has released Outlook Social Connectors for Facebook and Windows Live Messenger, both social connectors include 32 bit and 64 bit versions. Additionally LinkedIn and MySpace recently updated their social connectors; however, they have not yet released 64 bit versions.

Outlook Social Connectors bring all of your social networks into Microsoft Outlook.

Microsoft Outlook Social Connector

Outlook Social Connector 32 bit (Beta) is now available for download:

Stay up-to-the-minute with the people in your networks by accessing everything from e-mail threads to status updates in one single, centralized view.

Download Outlook Social Connector 32 bit (Beta)

RPGDX Side-Scrolling Mini-RPG Summer 2009 Competition

For anyone who is interested, RPGDX is holding a 48 hour Side-Scrolling Mini-RPG Summer 2009 Competition, which begins this Saturday, August 15, 2009. Anyone is welcome to participate.

Warhammer 40K – Let the Galaxy Burn

I am currently reading Let the Galaxy Burn, a collection of short stories set in the Warhammer 40K universe.

Amazon describes the book as follows:

By popular demand, we've gathered up the best science fiction short stories ever written for the Black Library into one massive volume, and added some brand new tales! Warhammer 40,000 fans will be keen to get their hands on classic stories that have been unavailable for a while, and all readers will enjoy the range and variety on offer in this high-value volume.

The stories I have read so far have been very well written, action-packed, and highly descriptive. I strongly recommend this book for anyone who likes science fiction or Warhammer 40K.

Sprint Commercial

I generally record shows using Windows Media Center in Windows Vista Ultimate and skip the commercials during playback, but this Sprint advertisement caught my eye:

Walmart has lost my business and my respect

Last week Melissa and I purchased a piece of furniture and a DVD from Walmart. When we unpacked the furniture we could see it had been repackaged and had a large scratch on the front, which was disappointing since we believed we were buying a new, pristine piece of furniture. We then tried to play the DVD in three different DVD players, but were unsuccessful (I double checked the DVD to make sure the region was correct and tried other DVDs, which all played properly).

Today after work, Melissa and I went to Walmart to return the defective items. When we arrived, we loaded the furniture and DVD in a shopping cart and proceeded to the customer service  counter where after waiting in line, we had the pleasure of speaking with Nina, an extremely rude and unprofessional “customer service” representative. She informed us that we didn't have the necessary “yellow stickers” and could not return the items. In a condescending tone, she demanded we return to the entrance to get the stickers. We informed her there was no one at the entrance when we arrived, to which she replied in an extremely sarcastic tone, "There is always someone there!". She then mumbled something that sounded inappropriate and walked away, which left us standing at the customer service counter wondering if she was coming back.

After a few minutes I took the items back to the entrance and waited for the person who was supposed to be "watching" the door to finish flirting with a woman in the Subway restaurant (near the entrance). When Richard finally came over he said, "You must have stolen this because the box isn't cold." By this time, I was rather upset having just been accused of stealing the items in the cart (even though I had the receipt in my hand and several people had seen Melissa and I enter the store). I told him I was going to call the police and we could review the security tapes together if he didn't believe me. After several minutes of discussion another employee came over and told him to give me the stickers.

I took the stickers and returned to the customer service desk where a quality customer service representative politely assisted us with the return of the items.

As a direct result of being treated so poorly, Melissa asked to speak with a manager. When the manager arrived, two strangers who were also waiting in the long customer service line took the opportunity to tell the manager about the situation and how poorly Melissa and I had been treated. When they finished telling him what they observed, the manager asked us to tell him what happened. During our entire conversation with the manager he did not make eye contact with Melissa or me, nor did he apologize for the employees’ inexcusable behavior or attempt to rectify the situation.

As a result of this debacle, Walmart has officially lost my business and more importantly, my respect.

Microsoft Excel Workbook Links

Opening certain Excel or Comma Separated Value (CSV) files in Excel may (correctly or incorrectly) produce the following error message:

This workbook contains one or more links that cannot be updated.

Depending on how the file was opened (either through the GUI or programmatically), the following message box may appear:

Clicking the "Edit Links..." button opens the following window:

Selecting just about any option continues loading the file; however, if the files was accessed programmatically, user intervention is required through the GUI (even when the application has set Application.DisplayAlerts = false).

To disable the automatic update of Workbook links in Microsoft Excel 2007, which suppresses the prompt:

• Open Excel
• Click on the Microsoft Office button (in the upper left corner of the application)
• Click "Excel Options" near the bottom of the menu
• Select "Trust Center" tab
• Click the "Trust Center Settings..." button
• Select "External Content" tab
• Select "Disable automatic update of Workbook Links"
• Click "OK" button to close the "Trust Center" window
• Click "OK" button to close the "Excel Options" window

Addition information pertaining the issue is available through Microsoft's support website:

• http://support.microsoft.com/kb/813977

Visualizing Numerical Data

I was looking into visualizing numerical data and came across the following website:

• http://www.visitmix.com/labs/descry/theobesityepidemic/

Overall, MIX Online does an impressive job of allowing an average user to quickly and easily understand complex three dimensional data: time, space, and value.

Based on the visualization, it was interesting to see that the number of obese people in most U.S. states has approximately doubled since 1994. It was also interesting that the research does not take into account people who are underweight.

Bookmarklet - View Rendered HTML Source Code

Developing DHTML applications can be challenging, often the rendered HTML source code is dynamically assembled via JavaScript or AJAX and is dramatically different than the original source code of the Web page.

To view the raw rendered source code of an HTML page, save the following link as a bookmark, then navigate to the HTML page and click the bookmark.

javascript:document.write('<pre>' + document.body.innerHTML.replace(/</g,'&lt;').replace(/>/g,'&gt;') + '</pre>');

Change Remote Desktop Port

To change the default port for Windows Remote Desktop:

1. Start Registry Editor ("RegEdit.exe")
2. Locate and the following registry key:
   
   HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TerminalServer\WinStations\RDP-Tcp\PortNumber
   
   
3. On the Edit menu, click Modify, and then click Decimal
   
4. Type the new port number, and then click OK
   
5. Exit Registry Editor

See Microsoft Knowledge Base Article 306759 for additional details.

Classic ASP Permanent Redirect

In Classic ASP, the Response.Redirect method causes the server to send a temporary redirect code (302) in the response header.

To send a permanent redirect code (301) in the response header, use the following code instead:

Response.Status = "301 Moved Permanently"
Response.AddHeader "Location", "/new-page.asp"
Response.End

Replace "/new-page.asp" with the absolute or relative URL of the destination page.