Dynamic list of years in T-SQL

I have many SQL Server 2005 Reporting Services reports that include a year value as a parameter. Recently I was asked to make this more dynamic, allowing for a rolling list of years from which the user can select.

Here is the table function I created, which I think should only work in SQL Server 2005 and up because of the parametrized TOP value:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.udtf_GetDynamicYearList
(
    @StartYear INT -- The first year you want in the list
    , @NumberofYears INT -- The total number of years you want (from the Start Year)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        TOP (@NumberofYears) *
    FROM
    (
        SELECT
            @StartYear + n1.num AS [Year]
        FROM
        (
            SELECT 0 AS num UNION ALL
            SELECT 1 UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4 UNION ALL
            SELECT 5 UNION ALL
            SELECT 6 UNION ALL
            SELECT 7 UNION ALL
            SELECT 8 UNION ALL
            SELECT 9
        ) n1
    ) GenCalendar
    ORDER BY 1
)
GO

Simple! Now you can call it like so:

SELECT * FROM dbo.udtf_GetDynamicYearList(2006, 10)

If you want it to be more dynamic (say, to start from last year), you can do:

SELECT * FROM dbo.udtf_GetDynamicYearList(YEAR(GETDATE())-1, 10)

ODBC Connection to Informix 7.2 Server

I recently had to figure out a way to connect remotely to an Informix 7.2 server running on AIX 4. After struggling a bit, I finally got it working. I thought a checklist might be useful on the off chance that someone else has to do something similar.

Prepare the Informix instance for remote connectivity

Step 1: Ensure that an entry for the network-enabled version of the Informix instance exists in $INFORMIXDIR/etc/sqlhosts. Mine reads:

tcpipserver onsoctcp rs6000 on_socket

Entries in this file are in the following format :

[Database instance alias] [Protocol name] [Host name] [TCP service name]

Step 2: Add the database instance alias name to the list of server names in your onconfig file. This is found in the $INFORMIXDIR/etc directory as well - its extension is often the name of your database. Find the DBSERVERALIASES line, and (in our example) set its value to tcpipserver. If this key already contains a value, you can add tcpipserver to the list (and up to 32 total) by separating the aliases with commas.

Step 3: Edit your /etc/services file to include a service name and port for remote connections. The most common port to use is 1526/tcp. In our example, we'd add:

on_socket 1526/tcp #Informix TCP/IP Socket

Step 4: Restart the Informix database service.

Get an ODBC Driver

Step 5: Download the IBM Informix Client SDK 3.50 from IBM's download page and install.

Step 6: Set up the Informix connectivity parameters using the "Setnet32". You can launch this utility either via the shortcut in the "IBM Informix Client-SDK 3.50" folder in All Programs, or by launching it directly from C:\Program Files\IBM\Informix\Client-SDK\bin\setnet32.exe (if you didn't change the default install path).

On the "Environment" tab, click the line that reads "INFORMIXSERVER=", enter the value "tcpipserver" (without quotes) in the box that appears in the "Edit Environment Variable" section, then click the "Set" button.

On the "Server Information" tab, enter the following info:

IBM Informix Server: tcpipserver
HostName: ip address of server
Protocolname: onsoctcp
Service Name: 1526

Click the Make Default Server" button.

On the "Host Information" tab, enter the following info:

Current Host: ip address of server
User Name: enter a valid AIX username with access to the Informix database
Select "Password" in the "Password Option" dropdown list.
Password: enter the password associated with "User Name" above

Click the "OK" button.

Connect to your Informix database server

You can now either set up an ODBC connection (using the "Data Sources (ODBC)" applet in Administrative Tools - use IBM INFORMIX ODBC DRIVER as the driver for the data source), or you can set up the Informix database server as a linked server in Microsoft SQL Server. In 2005, you can use the following script to set up a linked server named "RS6000" (change the values of [DatabaseName], [AIXUser], [AIXUserPassword], and [LocalSQLServerLogin] to appropriate values):

EXEC master.dbo.sp_addlinkedserver
@server = N'RS6000'
, @srvproduct=N'Ifxoledbc'
, @provider=N'Ifxoledbc'
, @datasrc=N'[DatabaseName]@tcpipserver'
, @provstr=N'Provider=Ifxoledbc;Data Source=[DatabaseName]@tcpipserver;User ID=[AIXUser];Password=[AIXUserPassword];'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RS6000'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=NULL
,@rmtpassword=NULL
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'RS6000'
,@useself=N'False'
,@locallogin=N'[LocalSQLServerLogin]'
,@rmtuser=N'[AIXUser]'
,@rmtpassword='[AIXUserPassword]'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'collation compatible'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'dist'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'pub'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'rpc'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'rpc out'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'sub'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'connect timeout'
, @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'collation name'
, @optvalue=null
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'lazy schema validation'
, @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'query timeout'
, @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption
@server=N'RS6000'
, @optname=N'use remote collation'
, @optvalue=N'true'
GO

You can now access this linked server from a query window with a statement like:

SELECT * FROM OPENQUERY(RS6000, 'SELECT * FROM SomeInformixTable')

Happy Hunting!