ODBC Connection

A WPKG Package to create an ODBC Connection

The Installer Package

This uses one of 8 template files, all based on registry exports, to produce entries using a mix of:-

• trusted connection
• SQL authentication
• named Database
• database not specified

- as needed. For example:-

1. ODBC_template_SQL2K5_int_auth_db.reg
2. ODBC_template_SQL2K5_int_auth_no_db.reg
3. ODBC_template_SQL2K5_sql_auth_db.reg
4. ODBC_template_SQL2K5_sql_auth_no_db.reg
5. ODBC_template_SQL2K8_int_auth_db.reg
6. ODBC_template_SQL2K8_int_auth_no_db.reg
7. ODBC_template_SQL2K8_sql_auth_db.reg
8. ODBC_template_SQL2K8_sql_auth_no_db.reg

<package id="ODBC_CONN_X"
           name="Create ODBC Connect to DB XYZ on SQL 2008 Server ABC..."
           revision="1.1"
           reboot="false"
           execute="always">

    <depends package-id="SETUP" />
    <depends package-id="SETUPTOOLS" /> <!-- TxtReplace.exe -->
    <depends package-id="SQL_SERVER_NATIVECLIENT_10.0" />
    
    <variable name="LOCALDOWNLOADS" value="C:\downloads\provisioning" />
    <variable name="WPKGHOME"       value="\\enterprise\development\infrastructure\provisioning\wpkg-1.1.2\" />
    <variable name="tools"          value="c:\tools" />

    <variable name="ODBC_DSN_NAME"      value="ODBC_CONN_X" />
    <variable name="ODBC_DBSERVER"      value="SQLDEV-2005-A" />
    <variable name="ODBC_DBNAME"        value="AutomationTest" />
    <variable name="ODBC_DESC"          value="A test of blah for blah" />
    <variable name="ODBC_DRIVERNAME"    value="SQL Server Native Client 10.0" />
    <variable name="ODBC_TEMPLATE_FILE" value="ODBC_template_SQL2K8_int_auth_db.reg" />

    <check type="registry" condition="equals" path="HKEY_LOCAL_MACHINE&#92;SOFTWARE&#92;ODBC&#92;ODBC.INI&#92;ODBC Data Sources&#92;%ODBC_DSN_NAME%" value="%ODBC_DRIVERNAME%" />
        
    <install cmd="xcopy /Y &quot;%WPKGHOME%\ODBC\%ODBC_TEMPLATE_FILE%&quot; &quot;%LOCALDOWNLOADS%\&quot;" />
    <install cmd="%tools%\TxtReplace\TxtReplace.exe -i &quot;%LOCALDOWNLOADS%\%ODBC_TEMPLATE_FILE%&quot; -r __ODBC_DSN_NAME__ &quot;%ODBC_DSN_NAME%&quot; -r __ODBC_DESCRIPTION__ &quot;%ODBC_DESC%&quot; -r __ODBC_DBSERVER__ &quot;%ODBC_DBSERVER%&quot; -r __ODBC_DBNAME__ &quot;%ODBC_DBNAME%&quot; " />
    <install cmd="%tools%\reg.exe import &quot;%LOCALDOWNLOADS%\%ODBC_TEMPLATE_FILE%&quot;" />

    <remove cmd="cmd /c %tools%\reg.exe DELETE &quot;HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources&quot; /v &quot;%ODBC_DSN_NAME%&quot; /f" />
    <remove cmd="cmd /c %tools%\reg.exe DELETE &quot;HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI&#92;%ODBC_DSN_NAME%&quot; /f" />
    
  </package>

This <depend>s on package SQL Server 2008 ODBC Driver.

The Registry Template

This requires "TxtReplace.exe" substitution utility, and "reg.exe" from the Windows 2000 resource kit (on the W2K CD)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"__ODBC_DSN_NAME__"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\__ODBC_DSN_NAME__]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="__ODBC_DESCRIPTION__"
"Server"="__ODBC_DBSERVER__"
"Trusted_Connection"="Yes"