How Do You Call an MSSQL System Function From ADO/C++?

Question

...specifically, the fn_listextendedproperty system function in MSSQL 2005.

I have added an Extended Property to my database object, named 'schemaVersion'. In my MSVC application, using ADO, I need to determine if that Extended Property exists and, if it does, return the string value out of it.

Here is the T-SQL code that does what I want. How do I write this in C++/ADO, or otherwise get the job done?

select value as schemaVer
from fn_listextendedproperty(default, default, default, default, default, default, default)
where name=N'schemaVersion'

Here's the code I tried at first. It failed with the error listed below the code:

_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = cnn;

cmd->PutCommandText("select value "
    "from fn_listextendedproperty(default, default, default, default, default, default, default) "
    "where name=N'schemaVersion'");
VARIANT varCount;
cmd->Execute(NULL, NULL, adCmdText);

...here are the errors I peeled out of the ADO errors collection. The output is from my little utility function which adds the extra text like the thread ID etc, so ignore that.

(Proc:0x1930, Thread:0x8A0) INFO : ===   1 Provider Error Messages : =======================
(Proc:0x1930, Thread:0x8A0) INFO : [  1]   (-2147217900) 'Incorrect syntax near the keyword 'default'.'
(Proc:0x1930, Thread:0x8A0) INFO :         (SQLState = '42000')
(Proc:0x1930, Thread:0x8A0) INFO :         (Source = 'Microsoft OLE DB Provider for SQL Server')
(Proc:0x1930, Thread:0x8A0) INFO :         (NativeError = 156)
(Proc:0x1930, Thread:0x8A0) INFO : ==========================================================

EDIT: Updated the call according to suggestions. Also changed "SELECT value AS schemaVer" to just "SELECT value".

EDIT: Changed the first parameter of Execute() to NULL per suggestion. This fixed my original problem, and I proceeded to the next. :)

Answer 1

Try specifying NULL rather than default for each parameter of fn_listextendedproperty. This should hopefully then execute without errors, just leaving you to retrieve the result as your next step.

Answer 2

I still have not figured out how to do this directly. To get on with my life, I wrote a stored procedure which called the function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[mh_getSchemaVersion]
@schemaVer VARCHAR(256) OUTPUT
AS
    select @schemaVer = CAST( (select value from fn_listextendedproperty(default, default, default, default, default, default, default) where name=N'schemaVersion') AS varchar(256) )
    return @@ROWCOUNT

...and then called thst sproc from my ADO/C++ code:

_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = cnn;
cmd->PutCommandText("mh_getSchemaVersion")_l

_variant_t schemaVar;
_ParameterPtr schemaVarParam = cmd->CreateParameter("@schemaVer", adVarChar, adParamOutput, 256);
cmd->GetParameters()->Append((IDispatch*)schemaVarParam);

cmd->Execute(NULL, NULL, adCmdStoredProc);

std::string v = (const char*)(_bstr_t)schemaVarParam->GetValue();

ver->hasVersion_ = true;

...which works, but I didn't want to have to deploy a new stored procedure.

So if anyone can come up with a solution to the original problem and show me how to call the system function directly from ADO/C++, I will accept that as the answer. Otherwise I'll just accept this.