need to call sqlfetch twice to get one set of records

[kobosh]

I have legacy mfc project i am building in vs 2010; I have a stored proc which I am assuming returns one set of records: there is only one select block that is actually executed; but when i run sqlfetch on the sql statement it seems there are two sets of records

the first one returns -1 and only when i do a second fetch after calling sqlmoreresults do i get the records i wanted as shown below

int CDBProcessHandler::NextRow()
{
int rtn = SQLFetch(m_statementHandles[m_currentStatementHandle].GetStatementHandle());

while(rtn ==-1)// !=SQL_SUCCESS )//|| rtn != SQL_SUCCESS_WITH_INFO)// if(rtn==-1)
{



rtn=SQLMoreResults(m_statementHandles[m_currentStatementHandle].GetStatementHandle());
rtn = SQLFetch(m_statementHandles[m_currentStatementHandle].GetStatementHandle());
}
HandleErrors("NextRow() - SQLFetch()", rtn,
m_statementHandles[m_currentStatementHandle].GetStatementHandle());

if (rtn == SQL_SUCCESS || rtn == SQL_SUCCESS_WITH_INFO)
{

rtn = REG_ROW;
CCharBindData* charBindData = m_statementHandles[m_currentStatementHandle].GetCharBindData();
if (charBindData->GetNextIndex() != 0)
{
// we have some single character binds to deal with
// move the data from the temporary storage to final destination
for (int i = 0; i < charBindData->GetNextIndex(); i++)
{
CCharBindRow* charBindRowPtr = charBindData->GetCharBindRow(i);
charBindRowPtr->SetDestinationValue();
}
}
}
else if (rtn == SQL_NO_DATA_FOUND)
rtn = NO_MORE_ROWS;
else
rtn = 0;

return rtn;
}


The legacy code does not call sqlmoreresults functon and it is getting the records

the stored proc which runs on a server that i cannot access looks like this

/****** Object: Stored Procedure dbo.GetTignThreadList Script Date: 2/21/2005 10:52:04 PM ******/
CREATE PROC GetTignThreadList
@DEBUG int = 0
AS




ja7121 memid 1480092330
sbbykz 202 591 3046
2000 n loop west #100


/************************************************************************
* Declare And Initialize Local Variables
************************************************************************/
DECLARE @Err int

SELECT @Err = 0


IF (@DEBUG>0)
BEGIN
SELECT "***Entering GetTignThreadList", convert(char,getdate(),109)
END

SELECT DISTINCT s.LocaleNo, s.DBNo, l.Locale, SPACE(20) "DBServerName"
INTO #jtemp
FROM t_switch_db_servers s, t_switch_locales l, t_switch_services ss
WHERE s.SwitchType = TIGN
AND s.LocaleNo = l.LocaleNo
AND s.LocaleNo = ss.LocaleNo
AND s.DBNo = ss.DBNo

SELECT @Err = @@ERROR
IF (@Err != 0) GOTO ErrorLabel

UPDATE #jtemp
SET DBServerName = s.DBServerName
FROM t_switch_db_servers s, #jtemp j
WHERE j.LocaleNo = s.LocaleNo
AND j.DBNo = s.DBNo

SELECT @Err = @@ERROR
IF (@Err != 0) GOTO ErrorLabel

SELECT LocaleNo, DBNo, DBServerName, Locale
FROM #jtemp

DROP TABLE #jtemp

SELECT @Err = @@ERROR
IF (@Err != 0) GOTO ErrorLabel

/************************************************************************
* Error Condition
************************************************************************/
/* If no error has occured, so far then we are fine. */
GOTO OkLabel
ErrorLabel:

/* For TG defined error messages we raise the error. SQL errors are
* simply returned.
*/
IF (@Err > 50000)
BEGIN
DECLARE @ErrMsg varchar(255)
SELECT @ErrMsg = (SELECT ErrorMsg FROM t_error_codes WHERE ErrorCode = @Err)
RAISERROR @Err @ErrMsg
END

IF (@DEBUG>0)
SELECT "???Exiting GetTignThreadList with error", convert(char,getdate(),109)

RETURN (@Err)

/************************************************************************
* Finish Line
************************************************************************/
OkLabel:

IF (@DEBUG>0)
BEGIN
SELECT "---Exiting GetTignThreadList", convert(char,getdate(),109)
END



Can any one please find why this stored proc seems to pull two records?

Is there any way I can keep the legacy code as is and get the records without need to call sqlmoreresuls and a second fetch?

After i did this change and later into the project I am getting run time memory overrun error could this change i made had caused a memory overrun

Continue reading...