Dynamic prompts associated with a SAS Stored Process might occasionally fail to load parameter values. In some cases, errors are generated in various log files, which can indicate any of the following:

• the user has insufficient permissions to access the dynamic prompt data source
• the prompt data source cannot be found
• or the data source does not exist.

For example, this error might be found in a web application log file:

ERROR [user-ID]  com.sas.svcs.formvr.server.impl.ColumnValueRetriever - Unable to find 
this data source: /Metadata/Path/To/Datasource/Tablename(Table).

The SAS Pooled Workspace Server, which is often used to run the SQL query to populate the dynamic prompt from the data source, might report one of the following when you try to read the prompt data:

If the data source for the prompt points to an external database, the error might be similar to this Oracle example:

Often, these errors are valid. Permissions for the connecting user are the first thing to check when an "insufficient authorization," "access denied," "logon denied," or "unable to find data source" error is consistently received.

The following resources contain additional information regarding database permissions and considerations when working with SAS Stored Processes:

• Considerations for SAS Stored Process and SAS Pooled Workspace Servers
• SAS Note 36979: SAS^® 9.2 Stored Processes use client accounts for database access

However, these errors (and others) might occur intermittently even if the dynamic prompt usually works for the same user running the same SAS Stored Process in the same client application. The random nature of the problem suggests that the user who submitted the SAS Stored Process has the necessary access permissions to read the data source and load the prompt values at least some of the time.

One possible cause for intermittent permission-based errors in cases where user permissions appear to be correct is that cached credentials might be incorrectly used to query the data source for the dynamic prompt. This problem is most likely to occur in stored processes with dynamic prompts that are executed in SAS client applications that use a specific method to load dynamic prompts. The list of affected SAS clients includes (but is not limited to) the following:

• SAS^® Customer Intelligence Studio
• SAS^® Information Delivery Portal
• SAS^® Forecast Studio
• SAS^® Enterprise Guide^®
• SAS^® Add-In for Microsoft Office.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Important: To avoid the problem of dynamic prompts failing due to cached or incorrect credentials being used for the data source query, all sites that use dynamic prompts with stored processes should apply this ALERT level hot fix.

Operating System and Release Information

A fix for this issue for SAS Middle Tier 9.4_M2 is available at:

A fix for this issue for SAS Middle Tier 9.4_M1 is available at:

A fix for this issue for SAS Middle Tier 9.4 is available at:

A fix for this issue for SAS Management Console 9.3_M2 is available at:

A fix for this issue for SAS Middle Tier 9.3_M2 is available at:

A fix for this issue for SAS Middle Tier 9.3_M1 is available at:

A fix for this issue for SAS Management Console 9.3_M1 is available at:

A fix for this issue for SAS MIddle Tier 9.3 is available at: