SQL Server Performance

How do I Insert records from within sql 2000 extended stored procedures?

Discussion in 'T-SQL Performance Tuning for Developers' started by Tom Adams, Dec 21, 2007.

  1. Tom Adams New Member

    I am trying to find out how to insert records into tables from within a sql 2000 extended stored procedure. If you know how please let me know.
  2. ndinakar Member

    Can you explain what you mean by sql 2000 extended procs? Which one are you talking about? Can you show some code?
  3. Tom Adams New Member

    The following is the visual studio 6 C++ function that is linked to the extended stored procedure that I created. What I want it to do is write to tables in the db also. I have no idea how to do that as I've seen no examples.
    RETCODE __declspec(dllexport) xp_ITP_AD_AuthenticateUserTest(SRV_PROC *srvproc)
    {
    long retVal = 0;
    long returnCode = 0;
    char acct[MAX_PATH];
    char passwd[MAX_PATH];
    char act[MAX_PATH];
    char pwd[MAX_PATH];
    char data[MAX_PATH];
    HANDLE hand;
    PHANDLE phand = &hand;
    IADsContainer * pContainer = NULL;
    IADsContainer * * ppContainer = &pContainer;
    wchar_t fqdn[MAX_PATH];
    wchar_t domain[MAX_PATH];
    wchar_t user[MAX_PATH];
    wchar_t * index = NULL;
    wchar_t gc[] = L"LDAP://";
    wchar_t wacct[MAX_PATH];
    wchar_t wpasswd[MAX_PATH];
    DBCHAR msg[MAX_PATH];
    int intType;
    unsigned long actLen;
    int decryptResult = 0;
    long int pwd_len = 0;
    long int act_len = 0;
    BOOL ret = FALSE;
    DWORD lastError;
    memset(act, '', MAX_PATH);
    memset(pwd, '', MAX_PATH);
    memset(acct, '', MAX_PATH);
    memset(passwd, '', MAX_PATH);
    memset(domain, '', MAX_PATH * sizeof(wchar_t));
    memset(user, '', MAX_PATH * sizeof(wchar_t));
    memset(msg, '', MAX_PATH);
    srv_describe(srvproc, 1, "ReturnCode", SRV_NULLTERM, SRVINT4, sizeof(DBINT), SRVINT4, sizeof(DBINT), NULL);
    // Make sure that we have 4 arguments. If not exit.
    if(srv_rpcparams(srvproc) != 4)
    {
    _snprintf(msg, MAX_PATH, "SYNTAX: exec xp_ITP_AD_AuthenticateUser <itp encoded fquid>, <fquid pre-enc-len>, <itp encoded pwd>, <pwd pre-enc-len>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: 4 arguments must be passed to this procedure.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -2;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    // Make sure that this is a char variable type.
    intType = srv_paramtype(srvproc, 1);
    actLen = srv_paramlen(srvproc, 1);
    if((intType != SRVCHAR && intType != SRVVARCHAR) || actLen >= MAX_PATH)
    {
    _snprintf(msg, MAX_PATH, "SYNTAX: exec xp_ITP_AD_AuthenticateUser <itp encoded fquid>, <fquid pre-enc-len>, <itp encoded pwd>, <pwd pre-enc-len>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: <itp encoded fquid> must be a domain\userid encrypted by itp.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -2;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    strncpy(act, (char *)srv_paramdata(srvproc, 1), actLen);
    // Make sure that this is a char variable type.
    intType = srv_paramtype(srvproc, 3);
    actLen = srv_paramlen(srvproc, 3);
    if((intType != SRVCHAR && intType != SRVVARCHAR) || actLen >= MAX_PATH)
    {
    _snprintf(msg, MAX_PATH, "SYNTAX: exec xp_ITP_AD_AuthenticateUser <itp encoded fquid>, <fquid pre-enc-len>, <itp encoded pwd>, <pwd pre-enc-len>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: <itp encoded pwd> must be a password encrypted by itp.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -2;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    strncpy(pwd, (char *) srv_paramdata(srvproc, 3), actLen);
    // Make sure that this is an integer type.
    intType = srv_paramtype(srvproc, 2);
    if(intType == SRVINT4 || intType == SRVINTN)
    {
    memcpy(&act_len, srv_paramdata(srvproc, 2), sizeof(long int));
    }
    if(act_len <= 0)
    {
    _snprintf(msg, MAX_PATH, "SYNTAX: exec xp_ITP_AD_AuthenticateUser <itp encoded fquid>, <fquid pre-enc-len>, <itp encoded pwd>, <pwd pre-enc-len>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: <act pre-enc-len> must be a positive integer.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -2;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    // Make sure that this is an integer type.
    intType = srv_paramtype(srvproc, 4);
    if(intType == SRVINT4 || intType == SRVINTN)
    {
    memcpy(&pwd_len, srv_paramdata(srvproc, 4), sizeof(long int));
    }
    if(pwd_len <= 0)
    {
    _snprintf(msg, MAX_PATH, "SYNTAX: exec xp_ITP_AD_AuthenticateUser <itp encoded fquid>, <fquid pre-enc-len>, <itp encoded pwd>, <pwd pre-enc-len>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: <pwd pre-enc-len> must be a positive integer.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -2;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    // Here we decrypt the encrypted domainusername and password fields.
    memset((void*)data,'',MAX_PATH);
    decryptResult = DecryptData((void *)act, strlen(act), (void*)data, act_len);
    if(decryptResult != SUCCESS)
    {
    _snprintf(msg, MAX_PATH, "ERROR: Decryption error while decrypting <itp encoded fquid>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: decryptResult: %d", decryptResult);
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -3;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    memcpy(acct, data, act_len);
    memset((void*)data,'',MAX_PATH);
    decryptResult = DecryptData((void *)pwd, strlen(pwd), (void*)data, pwd_len);
    if(decryptResult != SUCCESS)
    {
    _snprintf(msg, MAX_PATH, "ERROR: Decryption error while decrypting <itp encoded pwd>.");
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    _snprintf(msg, MAX_PATH, "ERROR: decryptResult: %d", decryptResult);
    srv_sendmsg(srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msg,SRV_NULLTERM);
    returnCode = -4;
    srv_setcoldata(srvproc, 1, &returnCode);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)1);
    return XP_ERROR;
    }
    memcpy(passwd, data, pwd_len);
    memset(fqdn, '', sizeof(wchar_t) * MAX_PATH);
    wcscat(fqdn, gc);
    wsprintfW(wacct, L"%S", acct);
    wsprintfW(wpasswd, L"%S", passwd);
    index = wcschr(wacct, '\');
    if(index != NULL && index != wacct)
    {
    wcsncpy(domain, wacct, index - wacct);
    wcscpy(user, index + 1);
    try
    {
    ret = LogonUserW(user, domain, wpasswd, LOGON32_LOGON_NETWORK, LOGON32_PROVIDER_DEFAULT, phand);
    ZeroMemory(wpasswd, sizeof(wpasswd));
    if(ret == FALSE)
    {
    lastError = GetLastError();
    if(lastError == LOGON_FAILURE)
    {
    retVal = (long)2147943726;
    }
    else
    {
    retVal = (long)lastError;
    }
    }
    else
    {
    retVal = 0;
    }
    CloseHandle(hand);
    }
    catch(...)
    {
    // Do Nothing here...
    }
    }
    else
    {
    retVal = USER_PASSED_DOMAIN_PROBLEMS;
    }
    srv_setcoldata(srvproc, 1, &retVal);
    srv_sendrow(srvproc);
    srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)1);
    return NO_ERROR;
    }

Share This Page