Specifies whether to use the sp_tables stored procedure to check for table existence.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | YES when connecting to Synapse (as determined by SAS) |
| NO when connecting to another Microsoft SQL Server environment (as determined by SAS) | |
| Data source: | Microsoft SQL Server, ODBC |
| Note: | Support for this option was added in the June 2023 update for SAS/ACCESS on SAS 9.4M8. |
Table of Contents
specifies whether to use the sp_tables stored procedure to check for table existence. If you do not use the stored procedure, then a SELECT SQL statement is used to check for table existence.
When the ACCESS engine checks to see if a table exists on Microsoft SQL Server, it typically constructs a SQL statement of the form "SELECT * from <table name> where 0=1". However, when connected to an Azure Synapse instance, this SELECT statement does not always return the correct results due to caching issues on Azure Synapse. When USE_SP_TABLES is set to YES, the ACCESS engine instead uses the sp_tables stored procedure to check for table existence. The sp_tables stored procedure does not have the same issues with Azure Synapse caching and therefore returns more accurate results when connected to Azure Synapse.
ODBC: This option is used only when you use ODBC to connect to Microsoft SQL Server.