When trying to set the 'max server memory' of a database I use the stored procedure (SP_COFIGURE) as follows:
_bstr_t bstrCommandText = OLESTR("SP_CONFIGURE 'max server memory',");
bstrCommandText = bstrCommandText + bstrServerMemory; // where bstrServerMemory = {"128" (1)}
// looks like = {"SP_CONFIGURE 'max server memory',128" (1)}
iCmd->CommandText = bstrCommandText;
iCmd->Execute(NULL, NULL, adCmdText); // This line generate an error
iCmd->CommandText = L"RECONFIGURE";
iCmd->Execute(NULL, NULL, adCmdText);However that code generates an except (access violation) on the line that has the comment (above) - not knowing why I changed the code (shown below) to the following so I could "catch" the actual error message and see what was going on...
_bstr_t bstrCommandText = OLESTR("SP_CONFIGURE 'max server memory',");
bstrCommandText = bstrCommandText + bstrServerMemory;
iCmd->CommandText = bstrCommandText;
try
{
iCmd->Execute(NULL, NULL, adCmdText);
}
catch (_com_error cex)
{
int i; // just to put something
}
iCmd->CommandText = L"RECONFIGURE";
iCmd->Execute(NULL, NULL, adCmdText);
}Now this is much better but has me really confused ..??.. - the error caught (cex) is "DB_E_ERRORSINCOMMAND" - which is odd because it seems to work fine in SQL 2000 itself (Qeury analyzer) just not run via-code as such...
But that isn't the oddest part - if I continue after the exception and then check the server memory settings they have been changed successfully...
So the code WORKS but still throws an error "DB_E_ERRORSINCOMMAND" ... Why? If it works (because I can guarantee that it is correctly changing the max server memory as expected, tested this many times with different values) then why would it send back an error message?
What exactly does it mean? How can I get around this? I don't like my current fix (try/catch that just bypasses the error) - what if there is a REAL error one day? This is really an odd problem and I did some research on the net and couldn't find anything really similar - a few people mention using SET NOCOUNT ON (but I can't edit SP_CONFIGURE)...
Also - this only happens the FIRST TIME it is run, all subsequent times there is NO exception (cex) generated, no "DB_E_ERRORSINCOMMAND" ... this only happens the first time I try to make the changes...
Do I need to Initialize something first? I tried running a "RECONFIGURE" before and after - didn't make a difference...
And I haven't seen a "CONFIGURE" or "INITIALIZE" option - or whatnot like that ... I thought the problem could be that it was returning an empty row/column ... but that is just a theory...
Any help, comments, or hints would be greatly appreciated - I am really at a loss...
Thanks,