Difference between revisions of "ODBC Connection"

Revision as of 02:31, 18 October 2010

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\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\%ODBC_DSN_NAME%" value="%ODBC_DRIVERNAME%" />
        
    <install cmd="xcopy /Y "%WPKGHOME%\ODBC\%ODBC_TEMPLATE_FILE%" "%LOCALDOWNLOADS%\"" />
    <install cmd="%tools%\TxtReplace\TxtReplace.exe -i "%LOCALDOWNLOADS%\%ODBC_TEMPLATE_FILE%" -r __ODBC_DSN_NAME__ "%ODBC_DSN_NAME%" -r __ODBC_DESCRIPTION__ "%ODBC_DESC%" -r __ODBC_DBSERVER__ "%ODBC_DBSERVER%" -r __ODBC_DBNAME__ "%ODBC_DBNAME%" " />
    <install cmd="%tools%\reg.exe import "%LOCALDOWNLOADS%\%ODBC_TEMPLATE_FILE%"" />

    <remove cmd="cmd /c %tools%\reg.exe DELETE "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" /v "%ODBC_DSN_NAME%" /f" />
    <remove cmd="cmd /c %tools%\reg.exe DELETE "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\%ODBC_DSN_NAME%" /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"