BookmarkSubscribeRSS Feed
Ihsan-Mahdi
Quartz | Level 8

Hello,

 

I'm trying to access an SQL server using the  ODBC engine. A library is created successfully using different lines of code, however the library has no contents! Any suggestions? Thank you!

Code I used:

LIBNAME SQL ODBC noprompt= "DSN=servername;Description=server description;Trusted_Connection=Yes;APP=SAS 9.4 for
Windows;WSID=serverID ;DATABASE=mydata";

And
LIBNAME SQL ODBC DSN=servername user=myuser pw=pwd schema=dwems;

 

Both lines of code above seem to work with no errors/warnings.

11 REPLIES 11
SASKiwi
PROC Star

How did you check the library contents? Here is one way

proc contents data = sql._all_;
run;

If the contents are still empty then most likely your database and schema settings are incorrect. I find checking this in MS SQL Server Management Studio helpful.

Ihsan-Mahdi
Quartz | Level 8

Thanks for replying! The way I realized there were no contents in the library was looking at it through explorer and running a proc contents on one of the sets that are supposed to be there.

I ran the code you sent and this is the result:

Directory
Libref SQL
Engine ODBC
Physical Name SERVERNAME
Schema/Owner MYUSER
Server version: 15.00.4280
SQL Driver version: 10.00.19041
ODBC Driver name: SQLSRV32.DLL

 

SASKiwi
PROC Star

So no tables listed? 

Ihsan-Mahdi
Quartz | Level 8

No! For some reason, I'm not gaining access to the actual data base with all the tables. My DSN connection is there, The ODBC engine is creating the path but it's leading nowhere!

Ksharp
Super User
/*
You have to specify schema= option in libname.
Check your schema by the following code:
*/
proc sql;
connect to odbc(dsn= user= pw=);

select * from connection to ODBC(ODBC::SQLTables);
quit;


/*after get schema ,also try assign it by uppercase ,Like:*/
LIBNAME X ODBC DSN=servername user=myuser pw=pwd schema=MYUSER ;
Ihsan-Mahdi
Quartz | Level 8

Thanks for responding! I have been trying different options with the schema option as the data base I'm trying to access has tables with different schemas. I was only successful in accessing tables with one schema (dbo.) , yet when I select the schema I want I get nothing!

SASKiwi
PROC Star

If you are only seeing one schema then most likely this is a database permission problem. Ask your database adminstrator or help desk to check your permissions.

Ihsan-Mahdi
Quartz | Level 8

I will check thank you. Just to add clarity, I am seeing a few tables just not the ones I want, and I am able to access the tables using a different machine. Would this make a difference?

 

SASKiwi
PROC Star

Are any of the tables you are not seeing have names longer than 32 characters? That's a SAS restriction without a workaround. You can still read them using SQL Passthrough though.

Ihsan-Mahdi
Quartz | Level 8

Yes, many tables have names with more than 32 characters, but I am able to access them using a different machine. using SQL Passthrough still won't show the tables I need.

 

These are the tables shown using SQL Passthrough.

The SAS System

TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
masterdboMSreplication_optionsTABLE 
masterdbospt_fallback_dbTABLE 
masterdbospt_fallback_devTABLE 
masterdbospt_fallback_usgTABLE 
masterdbospt_monitorTABLE 
mastersystrace_xe_action_mapTABLE 
mastersystrace_xe_event_mapTABLE 
masterdbospt_valuesVIEW 
masterINFORMATION_SCHEMACHECK_CONSTRAINTSVIEW 
masterINFORMATION_SCHEMACOLUMN_DOMAIN_USAGEVIEW 
masterINFORMATION_SCHEMACOLUMN_PRIVILEGESVIEW 
masterINFORMATION_SCHEMACOLUMNSVIEW 
masterINFORMATION_SCHEMACONSTRAINT_COLUMN_USAGEVIEW 
masterINFORMATION_SCHEMACONSTRAINT_TABLE_USAGEVIEW 
masterINFORMATION_SCHEMADOMAIN_CONSTRAINTSVIEW 
masterINFORMATION_SCHEMADOMAINSVIEW 
masterINFORMATION_SCHEMAKEY_COLUMN_USAGEVIEW 
masterINFORMATION_SCHEMAPARAMETERSVIEW 
masterINFORMATION_SCHEMAREFERENTIAL_CONSTRAINTSVIEW 
masterINFORMATION_SCHEMAROUTINE_COLUMNSVIEW 
masterINFORMATION_SCHEMAROUTINESVIEW 
masterINFORMATION_SCHEMASCHEMATAVIEW 
masterINFORMATION_SCHEMASEQUENCESVIEW 
masterINFORMATION_SCHEMATABLE_CONSTRAINTSVIEW 
masterINFORMATION_SCHEMATABLE_PRIVILEGESVIEW 
masterINFORMATION_SCHEMATABLESVIEW 
masterINFORMATION_SCHEMAVIEW_COLUMN_USAGEVIEW 
masterINFORMATION_SCHEMAVIEW_TABLE_USAGEVIEW 
masterINFORMATION_SCHEMAVIEWSVIEW 
mastersysall_columnsVIEW 
mastersysall_objectsVIEW 
mastersysall_parametersVIEW 
mastersysall_sql_modulesVIEW 
mastersysall_viewsVIEW 
mastersysallocation_unitsVIEW 
mastersysassembliesVIEW 
mastersysassembly_filesVIEW 
mastersysassembly_modulesVIEW 
mastersysassembly_referencesVIEW 
mastersysassembly_typesVIEW 
mastersysasymmetric_keysVIEW 
mastersysavailability_databases_clusterVIEW 
mastersysavailability_group_listener_ip_addressesVIEW 
mastersysavailability_group_listenersVIEW 
mastersysavailability_groupsVIEW 
mastersysavailability_groups_clusterVIEW 
mastersysavailability_read_only_routing_listsVIEW 
mastersysavailability_replicasVIEW 
mastersysbackup_devicesVIEW 
mastersyscertificatesVIEW 
mastersyschange_tracking_databasesVIEW 
mastersyschange_tracking_tablesVIEW 
mastersyscheck_constraintsVIEW 
mastersyscolumn_encryption_key_valuesVIEW 
mastersyscolumn_encryption_keysVIEW 
mastersyscolumn_master_keysVIEW 
mastersyscolumn_store_dictionariesVIEW 
mastersyscolumn_store_row_groupsVIEW 
mastersyscolumn_store_segmentsVIEW 
mastersyscolumn_type_usagesVIEW 
mastersyscolumn_xml_schema_collection_usagesVIEW 
mastersyscolumnsVIEW 
mastersyscomputed_columnsVIEW 
mastersysconfigurationsVIEW 
mastersysconversation_endpointsVIEW 
mastersysconversation_groupsVIEW 
mastersysconversation_prioritiesVIEW 
mastersyscredentialsVIEW 
mastersyscrypt_propertiesVIEW 
mastersyscryptographic_providersVIEW 
mastersysdata_spacesVIEW 
mastersysdatabase_audit_specification_detailsVIEW 
mastersysdatabase_audit_specificationsVIEW 
mastersysdatabase_automatic_tuning_modeVIEW 
mastersysdatabase_automatic_tuning_optionsVIEW 
mastersysdatabase_credentialsVIEW 
mastersysdatabase_filesVIEW 
mastersysdatabase_filestream_optionsVIEW 
mastersysdatabase_mirroringVIEW 
mastersysdatabase_mirroring_endpointsVIEW 
mastersysdatabase_mirroring_witnessesVIEW 
mastersysdatabase_permissionsVIEW 
mastersysdatabase_principalsVIEW 
mastersysdatabase_query_store_optionsVIEW 
mastersysdatabase_recovery_statusVIEW 
mastersysdatabase_role_membersVIEW 
mastersysdatabase_scoped_configurationsVIEW 
mastersysdatabase_scoped_credentialsVIEW 
mastersysdatabasesVIEW 
mastersysdefault_constraintsVIEW 
mastersysdestination_data_spacesVIEW 
mastersysdm_audit_actionsVIEW 
mastersysdm_audit_class_type_mapVIEW 
mastersysdm_broker_activated_tasksVIEW 
mastersysdm_broker_connectionsVIEW 
mastersysdm_broker_forwarded_messagesVIEW 
mastersysdm_broker_queue_monitorsVIEW 
mastersysdm_cache_hit_statsVIEW 
mastersysdm_cache_sizeVIEW 
mastersysdm_cache_statsVIEW 
mastersysdm_cdc_errorsVIEW 
mastersysdm_cdc_log_scan_sessionsVIEW 
mastersysdm_clr_appdomainsVIEW 
mastersysdm_clr_loaded_assembliesVIEW 
mastersysdm_clr_propertiesVIEW 
mastersysdm_clr_tasksVIEW 
mastersysdm_cluster_endpointsVIEW 
mastersysdm_column_encryption_enclaveVIEW 
mastersysdm_column_encryption_enclave_operation_statsVIEW 
mastersysdm_column_store_object_poolVIEW 
mastersysdm_cryptographic_provider_propertiesVIEW 
mastersysdm_database_encryption_keysVIEW 
mastersysdm_db_column_store_row_group_operational_statsVIEW 
mastersysdm_db_column_store_row_group_physical_statsVIEW 
mastersysdm_db_data_pool_nodesVIEW 
mastersysdm_db_data_poolsVIEW 
mastersysdm_db_external_language_statsVIEW 
mastersysdm_db_external_script_execution_statsVIEW 
mastersysdm_db_file_space_usageVIEW 
mastersysdm_db_fts_index_physical_statsVIEW 
mastersysdm_db_index_usage_statsVIEW 
mastersysdm_db_log_space_usageVIEW 
mastersysdm_db_mirroring_auto_page_repairVIEW 
mastersysdm_db_mirroring_connectionsVIEW 
mastersysdm_db_mirroring_past_actionsVIEW 
mastersysdm_db_missing_index_detailsVIEW 
mastersysdm_db_missing_index_group_statsVIEW 
mastersysdm_db_missing_index_group_stats_queryVIEW 
mastersysdm_db_missing_index_groupsVIEW 
mastersysdm_db_partition_statsVIEW 
mastersysdm_db_persisted_sku_featuresVIEW 
mastersysdm_db_rda_migration_statusVIEW 
mastersysdm_db_rda_schema_update_statusVIEW 
mastersysdm_db_script_levelVIEW 
mastersysdm_db_session_space_usageVIEW 
mastersysdm_db_storage_pool_nodesVIEW 
mastersysdm_db_storage_poolsVIEW 
mastersysdm_db_task_space_usageVIEW 
mastersysdm_db_tuning_recommendationsVIEW 
mastersysdm_db_uncontained_entitiesVIEW 
mastersysdm_db_xtp_checkpoint_filesVIEW 
mastersysdm_db_xtp_checkpoint_internalsVIEW 
mastersysdm_db_xtp_checkpoint_statsVIEW 
mastersysdm_db_xtp_gc_cycle_statsVIEW 
mastersysdm_db_xtp_hash_index_statsVIEW 
mastersysdm_db_xtp_index_statsVIEW 
mastersysdm_db_xtp_memory_consumersVIEW 
mastersysdm_db_xtp_nonclustered_index_statsVIEW 
mastersysdm_db_xtp_object_statsVIEW 
mastersysdm_db_xtp_table_memory_statsVIEW 
mastersysdm_db_xtp_transactionsVIEW 
mastersysdm_distributed_exchange_statsVIEW 
mastersysdm_exec_background_job_queueVIEW 
mastersysdm_exec_background_job_queue_statsVIEW 
mastersysdm_exec_cached_plansVIEW 
mastersysdm_exec_compute_node_errorsVIEW 
mastersysdm_exec_compute_node_statusVIEW 
mastersysdm_exec_compute_nodesVIEW 
mastersysdm_exec_compute_poolsVIEW 
mastersysdm_exec_connectionsVIEW 
mastersysdm_exec_distributed_request_stepsVIEW 
mastersysdm_exec_distributed_requestsVIEW 
mastersysdm_exec_distributed_sql_requestsVIEW 
mastersysdm_exec_dms_servicesVIEW 
mastersysdm_exec_dms_workersVIEW 
mastersysdm_exec_external_operationsVIEW 
mastersysdm_exec_external_workVIEW 
mastersysdm_exec_function_statsVIEW 
mastersysdm_exec_procedure_statsVIEW 
mastersysdm_exec_query_memory_grantsVIEW 
mastersysdm_exec_query_optimizer_infoVIEW 
mastersysdm_exec_query_optimizer_memory_gatewaysVIEW 
mastersysdm_exec_query_parallel_workersVIEW 
mastersysdm_exec_query_profilesVIEW 
mastersysdm_exec_query_resource_semaphoresVIEW 
mastersysdm_exec_query_statsVIEW 
mastersysdm_exec_query_transformation_statsVIEW 
mastersysdm_exec_requestsVIEW 
mastersysdm_exec_session_wait_statsVIEW 
mastersysdm_exec_sessionsVIEW 
mastersysdm_exec_trigger_statsVIEW 
mastersysdm_exec_valid_use_hintsVIEW 
mastersysdm_external_authenticationVIEW 
mastersysdm_external_script_execution_statsVIEW 
mastersysdm_external_script_requestsVIEW 
mastersysdm_external_script_resource_usage_statsVIEW 
mastersysdm_filestream_file_io_handlesVIEW 
mastersysdm_filestream_file_io_requestsVIEW 
mastersysdm_filestream_non_transacted_handlesVIEW 
mastersysdm_fts_active_catalogsVIEW 
mastersysdm_fts_fdhostsVIEW 
mastersysdm_fts_index_populationVIEW 
mastersysdm_fts_memory_buffersVIEW 
mastersysdm_fts_memory_poolsVIEW 
mastersysdm_fts_outstanding_batchesVIEW 
mastersysdm_fts_population_rangesVIEW 
mastersysdm_fts_semantic_similarity_populationVIEW 
mastersysdm_hadr_ag_threadsVIEW 
mastersysdm_hadr_auto_page_repairVIEW 
mastersysdm_hadr_automatic_seedingVIEW 
mastersysdm_hadr_availability_group_statesVIEW 
mastersysdm_hadr_availability_replica_cluster_nodesVIEW 
mastersysdm_hadr_availability_replica_cluster_statesVIEW 
mastersysdm_hadr_availability_replica_statesVIEW 
mastersysdm_hadr_cached_database_replica_statesVIEW 
mastersysdm_hadr_cached_replica_statesVIEW 
mastersysdm_hadr_clusterVIEW 
mastersysdm_hadr_cluster_membersVIEW 
mastersysdm_hadr_cluster_networksVIEW 
mastersysdm_hadr_database_replica_cluster_statesVIEW 
mastersysdm_hadr_database_replica_statesVIEW 
mastersysdm_hadr_db_threadsVIEW 
mastersysdm_hadr_instance_node_mapVIEW 
mastersysdm_hadr_name_id_mapVIEW 
mastersysdm_hadr_physical_seeding_statsVIEW 
mastersysdm_hpc_device_statsVIEW 
mastersysdm_hpc_thread_proxy_statsVIEW 
mastersysdm_io_backup_tapesVIEW 
mastersysdm_io_cluster_shared_drivesVIEW 
mastersysdm_io_cluster_valid_path_namesVIEW 
mastersysdm_io_pending_io_requestsVIEW 
mastersysdm_logpool_hashentriesVIEW 
mastersysdm_logpool_statsVIEW 
mastersysdm_os_buffer_descriptorsVIEW 
mastersysdm_os_buffer_pool_extension_configurationVIEW 
mastersysdm_os_child_instancesVIEW 
mastersysdm_os_cluster_nodesVIEW 
mastersysdm_os_cluster_propertiesVIEW 
mastersysdm_os_dispatcher_poolsVIEW 
mastersysdm_os_dispatchersVIEW 
mastersysdm_os_enumerate_fixed_drivesVIEW 
mastersysdm_os_host_infoVIEW 
mastersysdm_os_hostsVIEW 
mastersysdm_os_job_objectVIEW 
mastersysdm_os_latch_statsVIEW 
mastersysdm_os_loaded_modulesVIEW 
mastersysdm_os_memory_allocationsVIEW 
mastersysdm_os_memory_broker_clerksVIEW 
mastersysdm_os_memory_brokersVIEW 
mastersysdm_os_memory_cache_clock_handsVIEW 
mastersysdm_os_memory_cache_countersVIEW 
mastersysdm_os_memory_cache_entriesVIEW 
mastersysdm_os_memory_cache_hash_tablesVIEW 
mastersysdm_os_memory_clerksVIEW 
mastersysdm_os_memory_node_access_statsVIEW 
mastersysdm_os_memory_nodesVIEW 
mastersysdm_os_memory_objectsVIEW 
mastersysdm_os_memory_poolsVIEW 
mastersysdm_os_nodesVIEW 
mastersysdm_os_performance_countersVIEW 
mastersysdm_os_process_memoryVIEW 
mastersysdm_os_ring_buffersVIEW 
mastersysdm_os_schedulersVIEW 
mastersysdm_os_server_diagnostics_log_configurationsVIEW 
mastersysdm_os_spinlock_statsVIEW 
mastersysdm_os_stacksVIEW 
mastersysdm_os_sublatchesVIEW 
mastersysdm_os_sys_infoVIEW 
mastersysdm_os_sys_memoryVIEW 
mastersysdm_os_tasksVIEW 
mastersysdm_os_threadsVIEW 
mastersysdm_os_virtual_address_dumpVIEW 
mastersysdm_os_wait_statsVIEW 
mastersysdm_os_waiting_tasksVIEW 
mastersysdm_os_windows_infoVIEW 
mastersysdm_os_worker_local_storageVIEW 
mastersysdm_os_workersVIEW 
mastersysdm_pal_cpu_statsVIEW 
mastersysdm_pal_disk_statsVIEW 
mastersysdm_pal_net_statsVIEW 
mastersysdm_pal_processesVIEW 
mastersysdm_pal_spinlock_statsVIEW 
mastersysdm_pal_vm_statsVIEW 
mastersysdm_pal_wait_statsVIEW 
mastersysdm_qn_subscriptionsVIEW 
mastersysdm_repl_articlesVIEW 
mastersysdm_repl_schemasVIEW 
mastersysdm_repl_tranhashVIEW 
mastersysdm_repl_traninfoVIEW 
mastersysdm_resource_governor_configurationVIEW 
mastersysdm_resource_governor_external_resource_pool_affinityVIEW 
mastersysdm_resource_governor_external_resource_poolsVIEW 
mastersysdm_resource_governor_resource_pool_affinityVIEW 
mastersysdm_resource_governor_resource_pool_volumesVIEW 
mastersysdm_resource_governor_resource_poolsVIEW 
mastersysdm_resource_governor_workload_groupsVIEW 
mastersysdm_server_audit_statusVIEW 
mastersysdm_server_memory_dumpsVIEW 
mastersysdm_server_registryVIEW 
mastersysdm_server_servicesVIEW 
mastersysdm_tcp_listener_statesVIEW 
mastersysdm_tran_aborted_transactionsVIEW 
mastersysdm_tran_active_snapshot_database_transactionsVIEW 
mastersysdm_tran_active_transactionsVIEW 
mastersysdm_tran_commit_tableVIEW 
mastersysdm_tran_current_snapshotVIEW 
mastersysdm_tran_current_transactionVIEW 
mastersysdm_tran_database_transactionsVIEW 
mastersysdm_tran_global_recovery_transactionsVIEW 
mastersysdm_tran_global_transactionsVIEW 
mastersysdm_tran_global_transactions_enlistmentsVIEW 
mastersysdm_tran_global_transactions_logVIEW 
mastersysdm_tran_locksVIEW 
mastersysdm_tran_persistent_version_storeVIEW 
mastersysdm_tran_persistent_version_store_statsVIEW 
mastersysdm_tran_session_transactionsVIEW 
mastersysdm_tran_top_version_generatorsVIEW 
mastersysdm_tran_transactions_snapshotVIEW 
mastersysdm_tran_version_storeVIEW 
mastersysdm_tran_version_store_space_usageVIEW 
mastersysdm_xe_map_valuesVIEW 
mastersysdm_xe_object_columnsVIEW 
mastersysdm_xe_objectsVIEW 
mastersysdm_xe_packagesVIEW 
mastersysdm_xe_session_event_actionsVIEW 
mastersysdm_xe_session_eventsVIEW 
mastersysdm_xe_session_object_columnsVIEW 
mastersysdm_xe_session_targetsVIEW 
mastersysdm_xe_sessionsVIEW 
mastersysdm_xtp_gc_queue_statsVIEW 
mastersysdm_xtp_gc_statsVIEW 
mastersysdm_xtp_system_memory_consumersVIEW 
mastersysdm_xtp_threadsVIEW 
mastersysdm_xtp_transaction_recent_rowsVIEW 
mastersysdm_xtp_transaction_statsVIEW 
mastersysedge_constraint_clausesVIEW 
mastersysedge_constraintsVIEW 
mastersysendpoint_webmethodsVIEW 
mastersysendpointsVIEW 
mastersysevent_notification_event_typesVIEW 
mastersysevent_notificationsVIEW 
mastersyseventsVIEW 
mastersysextended_proceduresVIEW 
mastersysextended_propertiesVIEW 
mastersysexternal_data_sourcesVIEW 
mastersysexternal_file_formatsVIEW 
mastersysexternal_language_filesVIEW 
mastersysexternal_languagesVIEW 
mastersysexternal_librariesVIEW 
mastersysexternal_libraries_installedVIEW 
mastersysexternal_library_filesVIEW 
mastersysexternal_library_setup_errorsVIEW 
mastersysexternal_table_columnsVIEW 
mastersysexternal_tablesVIEW 
mastersysfilegroupsVIEW 
mastersysfiletable_system_defined_objectsVIEW 
mastersysfiletablesVIEW 
mastersysforeign_key_columnsVIEW 
mastersysforeign_keysVIEW 
mastersysfulltext_catalogsVIEW 
mastersysfulltext_document_typesVIEW 
mastersysfulltext_index_catalog_usagesVIEW 
mastersysfulltext_index_columnsVIEW 
mastersysfulltext_index_fragmentsVIEW 
mastersysfulltext_indexesVIEW 
mastersysfulltext_languagesVIEW 
mastersysfulltext_semantic_language_statistics_databaseVIEW 
mastersysfulltext_semantic_languagesVIEW 
mastersysfulltext_stoplistsVIEW 
mastersysfulltext_stopwordsVIEW 
mastersysfulltext_system_stopwordsVIEW 
mastersysfunction_order_columnsVIEW 
mastersyshash_indexesVIEW 
mastersyshttp_endpointsVIEW 
mastersysidentity_columnsVIEW 
mastersysindex_columnsVIEW 
mastersysindex_resumable_operationsVIEW 
mastersysindexesVIEW 
mastersysinternal_partitionsVIEW 
mastersysinternal_tablesVIEW 
mastersyskey_constraintsVIEW 
mastersyskey_encryptionsVIEW 
mastersyslinked_loginsVIEW 
mastersyslogin_tokenVIEW 
mastersysmasked_columnsVIEW 
mastersysmaster_filesVIEW 
mastersysmaster_key_passwordsVIEW 
mastersysmemory_optimized_tables_internal_attributesVIEW 
mastersysmessage_type_xml_schema_collection_usagesVIEW 
mastersysmessagesVIEW 
mastersysmodule_assembly_usagesVIEW 
mastersysnumbered_procedure_parametersVIEW 
mastersysnumbered_proceduresVIEW 
mastersysobjectsVIEW 
mastersysopenkeysVIEW 
mastersysparameter_type_usagesVIEW 
mastersysparameter_xml_schema_collection_usagesVIEW 
mastersysparametersVIEW 
mastersyspartition_functionsVIEW 
mastersyspartition_parametersVIEW 
mastersyspartition_range_valuesVIEW 
mastersyspartition_schemesVIEW 
mastersyspartitionsVIEW 
mastersysperiodsVIEW 
mastersysplan_guidesVIEW 
mastersysproceduresVIEW 
mastersysquery_context_settingsVIEW 
mastersysquery_store_planVIEW 
mastersysquery_store_queryVIEW 
mastersysquery_store_query_textVIEW 
mastersysquery_store_runtime_statsVIEW 
mastersysquery_store_runtime_stats_intervalVIEW 
mastersysquery_store_wait_statsVIEW 
mastersysregistered_search_propertiesVIEW 
mastersysregistered_search_property_listsVIEW 
mastersysremote_data_archive_databasesVIEW 
mastersysremote_data_archive_tablesVIEW 
mastersysremote_loginsVIEW 
mastersysremote_service_bindingsVIEW 
mastersysresource_governor_configurationVIEW 
mastersysresource_governor_external_resource_pool_affinityVIEW 
mastersysresource_governor_external_resource_poolsVIEW 
mastersysresource_governor_resource_pool_affinityVIEW 
mastersysresource_governor_resource_poolsVIEW 
mastersysresource_governor_workload_groupsVIEW 
mastersysroutesVIEW 
mastersysschemasVIEW 
mastersyssecurable_classesVIEW 
mastersyssecurity_policiesVIEW 
mastersyssecurity_predicatesVIEW 
mastersysselective_xml_index_namespacesVIEW 
mastersysselective_xml_index_pathsVIEW 
mastersyssensitivity_classificationsVIEW 
mastersyssequencesVIEW 
mastersysserver_assembly_modulesVIEW 
mastersysserver_audit_specification_detailsVIEW 
mastersysserver_audit_specificationsVIEW 
mastersysserver_auditsVIEW 
mastersysserver_event_notificationsVIEW 
mastersysserver_event_session_actionsVIEW 
mastersysserver_event_session_eventsVIEW 
mastersysserver_event_session_fieldsVIEW 
mastersysserver_event_session_targetsVIEW 
mastersysserver_event_sessionsVIEW 
mastersysserver_eventsVIEW 
mastersysserver_file_auditsVIEW 
mastersysserver_memory_optimized_hybrid_buffer_pool_configurationVIEW 
mastersysserver_permissionsVIEW 
mastersysserver_principal_credentialsVIEW 
mastersysserver_principalsVIEW 
mastersysserver_role_membersVIEW 
mastersysserver_sql_modulesVIEW 
mastersysserver_trigger_eventsVIEW 
mastersysserver_triggersVIEW 
mastersysserversVIEW 
mastersysservice_broker_endpointsVIEW 
mastersysservice_contract_message_usagesVIEW 
mastersysservice_contract_usagesVIEW 
mastersysservice_contractsVIEW 
mastersysservice_message_typesVIEW 
mastersysservice_queue_usagesVIEW 
mastersysservice_queuesVIEW 
mastersysservicesVIEW 
mastersyssoap_endpointsVIEW 
mastersysspatial_index_tessellationsVIEW 
mastersysspatial_indexesVIEW 
mastersysspatial_reference_systemsVIEW 
mastersyssql_dependenciesVIEW 
mastersyssql_expression_dependenciesVIEW 
mastersyssql_loginsVIEW 
mastersyssql_modulesVIEW 
mastersysstatsVIEW 
mastersysstats_columnsVIEW 
mastersyssymmetric_keysVIEW 
mastersyssynonymsVIEW 
mastersyssysaltfilesVIEW 
mastersyssyscacheobjectsVIEW 
mastersyssyscharsetsVIEW 
mastersyssyscolumnsVIEW 
mastersyssyscommentsVIEW 
mastersyssysconfiguresVIEW 
mastersyssysconstraintsVIEW 
mastersyssyscscontainersVIEW 
mastersyssyscurconfigsVIEW 
mastersyssyscursorcolumnsVIEW 
mastersyssyscursorrefsVIEW 
mastersyssyscursorsVIEW 
mastersyssyscursortablesVIEW 
mastersyssysdatabasesVIEW 
mastersyssysdependsVIEW 
mastersyssysdevicesVIEW 
mastersyssysfilegroupsVIEW 
mastersyssysfilesVIEW 
mastersyssysforeignkeysVIEW 
mastersyssysfulltextcatalogsVIEW 
mastersyssysindexesVIEW 
mastersyssysindexkeysVIEW 
mastersyssyslanguagesVIEW 
mastersyssyslockinfoVIEW 
mastersyssysloginsVIEW 
mastersyssysmembersVIEW 
mastersyssysmessagesVIEW 
mastersyssysobjectsVIEW 
mastersyssysoledbusersVIEW 
mastersyssysopentapesVIEW 
mastersyssysperfinfoVIEW 
mastersyssyspermissionsVIEW 
mastersyssysprocessesVIEW 
mastersyssysprotectsVIEW 
mastersyssysreferencesVIEW 
mastersyssysremoteloginsVIEW 
mastersyssysserversVIEW 
mastersyssystem_columnsVIEW 
mastersyssystem_components_surface_area_configurationVIEW 
mastersyssystem_internals_allocation_unitsVIEW 
mastersyssystem_internals_partition_columnsVIEW 
mastersyssystem_internals_partitionsVIEW 
mastersyssystem_objectsVIEW 
mastersyssystem_parametersVIEW 
mastersyssystem_sql_modulesVIEW 
mastersyssystem_viewsVIEW 
mastersyssystypesVIEW 
mastersyssysusersVIEW 
mastersystable_typesVIEW 
mastersystablesVIEW 
mastersystcp_endpointsVIEW 
mastersystime_zone_infoVIEW 
mastersystrace_categoriesVIEW 
mastersystrace_columnsVIEW 
mastersystrace_event_bindingsVIEW 
mastersystrace_eventsVIEW 
mastersystrace_subclass_valuesVIEW 
mastersystracesVIEW 
mastersystransmission_queueVIEW 
mastersystrigger_event_typesVIEW 
mastersystrigger_eventsVIEW 
mastersystriggersVIEW 
mastersystrusted_assembliesVIEW 
mastersystype_assembly_usagesVIEW 
mastersystypesVIEW 
mastersysuser_tokenVIEW 
mastersysvia_endpointsVIEW 
mastersysviewsVIEW 
mastersysxml_indexesVIEW 
mastersysxml_schema_attributesVIEW 
mastersysxml_schema_collectionsVIEW 
mastersysxml_schema_component_placementsVIEW 
mastersysxml_schema_componentsVIEW 
mastersysxml_schema_elementsVIEW 
mastersysxml_schema_facetsVIEW 
mastersysxml_schema_model_groupsVIEW 
mastersysxml_schema_namespacesVIEW 
mastersysxml_schema_typesVIEW 
mastersysxml_schema_wildcard_namespacesVIEW 
mastersysxml_schema_wildcardsVIEW 

The tables I need have the schema DwEms

SASKiwi
PROC Star

Can you post the complete code that produced this please, including any LIBNAME or CONNECT statements? As you said only schema DBO is listed.

 

EDIT: Using your original LIBNAME, try this adding your correct database name (mydata looks suspiciously wrong to me):

LIBNAME SQL ODBC noprompt= "DSN=servername;Description=server description;Trusted_Connection=Yes;APP=SAS 9.4 for
Windows;WSID=serverID" database = ????? schema = dwems; 

  

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 2016 views
  • 3 likes
  • 3 in conversation