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

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.