PRB: SP_SetAppRole in RDO Causes "Application Roles Can Only Be Activated at the Ad Hoc Level" Error Message (269620)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q269620

SYMPTOMS

When you run the sp_SetAppRole stored procedure in Remote Data Objects (RDO) to set the application role for the connection, Microsoft SQL Server returns the following error message:
40002
37000: [Microsoft][ODBC SQL Server Driver][SQL Server] Application roles can only be activated at the ad hoc level.

CAUSE

This error message occurs because a temporary stored procedure has been created to run the query.

RESOLUTION

If you use the rdExecDirect option, RDO uses the SQLExecDirect ODBC API function to run the query because no temporary stored procedure is created.

To resolve the problem, add rdExecDirect option to the Execute method call as follows: 
rdoConnection.Execute "sp_SetAppRole 'RoleName', 'Password'", rdExecDirect
Modification Type:MajorLast Reviewed:1/26/2001
Keywords:kbDSupport kbprb kbRDO KB269620
©2002 Microsoft Corporation. All rights reserved.