Extended Stored Procedure - ODBC Loopback Connection Problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anil Saharan (aks_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 09:31:03 -0800

Hi,

I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here??

The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//

#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h>

#define XP_NOERROR 0
#define XP_ERROR 1

#define SEND_ERROR(szMessage, pServerProc) \
 { \
         srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
                        NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
                srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
 }

// typedef const char* (_MakeODBCConnection)(void);

static const char* _szMessage = "ODBC Working out....";

void
_MakeODBCConnection(void)
{
        char szConnOut[1024];
        SQLSMALLINT nOut = 0;
        const char* szDSNName = "TestOdbc";
        const char* szUsername = "test";
        const char* szPassword = "test";
        SQLHANDLE hEnvironment = NULL;
        SQLHANDLE hDBConnection = NULL;

        if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
                _szMessage = "Failed to create the environment handle";
                return;
        }

        SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
        if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
                SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
                _szMessage = "Failed to create the database connection";
                return;
        }

        /*----------------- This is where it fails -------------------*/
        /* Tried both the with/Without database name */
        if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,
                        (SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)) {
                SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
                SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
                _szMessage = "Failed to connect to the database";
                return;
        }

        /*
        if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName, SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
                SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
                SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
                _szMessage = "Failed to connect to the database";
                return;
        }
        */

        SQLFreeConnect(hDBConnection);
        SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
        _szMessage = "ODBC Connection cycle completed successfully";
}

BOOL APIENTRY DllMain( HANDLE hModule,
                       DWORD ul_reason_for_call,
                       LPVOID lpReserved
                                         )
{
        switch (ul_reason_for_call)
        {
        case DLL_PROCESS_ATTACH:
                _MakeODBCConnection();
                break;

        case DLL_THREAD_ATTACH:
                break;

        case DLL_THREAD_DETACH:
                break;

        case DLL_PROCESS_DETACH:
                break;
        }
    return TRUE;
}

static void
_CheckODBCConnection(void)
{
// _MakeODBCConnection pFunction = NULL;
// _szMessage = pFunction();
}

extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
        //_szMessage = _MakeODBCConnection();
        if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
                return XP_ERROR;
        }
        return XP_NOERROR;
}

Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )

Relevant Pages

  • Lookback connection inside an Extended Proc using ADO
    ... I am trying to do a loopback connection to Sql Server from inside an ... Extended Stored Procedure that uses the TAdo... ... is executed after the call to the extended proc. ...
    (borland.public.delphi.database.ado)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... Well, it would be nice to access the call stack from anywhere in SQL Server, ... procedure finds that there is an open transaction, ... that performs a loopback connection and writes to this table outside the ...
    (microsoft.public.sqlserver.programming)