Retrieving varbinary output from a query in sql server into classic ASP

Question

Hi,

I'm trying to retrieve a varbinary output value from a query running on SQL Server 2005 into Classic ASP. The ASP execution just fails when it comes to part of code that is simply taking a varbinary output into a string. So I guess we gotta handle it some other way.

Actually, I'm trying to set (sp_setapprole) and unset (sp_unsetapprole) application roles for a database connection. First I'd set the approle, then I'd run my required queries and finally unset the approle. During unsetting is when I need the cookie (varbinary) value in my ASP code so that I can create a query like 'exec sp_unsetapprole @cookie'. Well at this stage, I don't have the cookie (varbinary) value.

The reason I'm doing this is I used to get 'sp_setapprole was not invoked correctly' error when trying to set app roles. I've disabled pooling by appending 'OLE DB Services = -2;Pooling=False' into my connection string.

I know pooling helps performance wise but here I'm facing big problems.

Please help me out to retrieve a varbinary value into an classic ASP file or suggest a way to set & unset app roles. Either way solutions are appreciated.

Thanks, Nandagopal

Answer 1

Maybe you could try casting your VARBINARY datatype to IMAGE, which is old enough that it might be able to be used by "Classic" ASP. Just a thought. Could be wrong.

Answer 2

The easiest way is to store the varbinary value from sp_setapprole in an ADO recordset and keep that value around, not worrying about the actual type (you should be able to store the value in an array of bytes, though).

Then, when you need to place that varbinary value in your final sp_setapprole, you can simply use the recordset value. Do this by invoking sp_setapprole using a ADODB.Command object:

'--untested, syntax could be slightly off'
Set myCommand = Server.CreateObject("ADODB.Command")
With myCommand
    .ActiveConnection = myConnection
    .CommandType = adCmdStoredProc
    .CommandText = "sp_unsetapprole"
    .Parameters.Append _
        .CreateParameter("@Cookie", adLongVarBinary, adParamInput, fileSize)
    .Parameters(str_ParamName).Attributes = adFldLong
    .Parameters(str_ParamName).AppendChunk myRecordset.Fields("Cookie").Value
    .Execute
End With

Perhaps you can also get some insights from this Stackoverflow question.