topic Re: Accessing an SQL server using the ODBC engine in New SAS User

Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Tue, 21 Mar 2023 16:57:10 GMT

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.

Re: Accessing an SQL server using the ODBC engine

SASKiwi — Tue, 21 Mar 2023 19:26:05 GMT

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.

Re: Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Tue, 21 Mar 2023 20:14:41 GMT

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

Re: Accessing an SQL server using the ODBC engine

SASKiwi — Tue, 21 Mar 2023 21:05:47 GMT

So no tables listed?

Re: Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Tue, 21 Mar 2023 21:33:33 GMT

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!

Re: Accessing an SQL server using the ODBC engine

Ksharp — Wed, 22 Mar 2023 11:39:41 GMT

/*
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 ;

Re: Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Mon, 27 Mar 2023 17:00:42 GMT

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!

Re: Accessing an SQL server using the ODBC engine

SASKiwi — Mon, 27 Mar 2023 19:34:48 GMT

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.

Re: Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Mon, 27 Mar 2023 19:48:44 GMT

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?

Re: Accessing an SQL server using the ODBC engine

SASKiwi — Mon, 27 Mar 2023 20:11:56 GMT

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.

Re: Accessing an SQL server using the ODBC engine

Ihsan-Mahdi — Tue, 28 Mar 2023 00:18:48 GMT

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

master	dbo	MSreplication_options	TABLE
master	dbo	spt_fallback_db	TABLE
master	dbo	spt_fallback_dev	TABLE
master	dbo	spt_fallback_usg	TABLE
master	dbo	spt_monitor	TABLE
master	sys	trace_xe_action_map	TABLE
master	sys	trace_xe_event_map	TABLE
master	dbo	spt_values	VIEW
master	INFORMATION_SCHEMA	CHECK_CONSTRAINTS	VIEW
master	INFORMATION_SCHEMA	COLUMN_DOMAIN_USAGE	VIEW
master	INFORMATION_SCHEMA	COLUMN_PRIVILEGES	VIEW
master	INFORMATION_SCHEMA	COLUMNS	VIEW
master	INFORMATION_SCHEMA	CONSTRAINT_COLUMN_USAGE	VIEW
master	INFORMATION_SCHEMA	CONSTRAINT_TABLE_USAGE	VIEW
master	INFORMATION_SCHEMA	DOMAIN_CONSTRAINTS	VIEW
master	INFORMATION_SCHEMA	DOMAINS	VIEW
master	INFORMATION_SCHEMA	KEY_COLUMN_USAGE	VIEW
master	INFORMATION_SCHEMA	PARAMETERS	VIEW
master	INFORMATION_SCHEMA	REFERENTIAL_CONSTRAINTS	VIEW
master	INFORMATION_SCHEMA	ROUTINE_COLUMNS	VIEW
master	INFORMATION_SCHEMA	ROUTINES	VIEW
master	INFORMATION_SCHEMA	SCHEMATA	VIEW
master	INFORMATION_SCHEMA	SEQUENCES	VIEW
master	INFORMATION_SCHEMA	TABLE_CONSTRAINTS	VIEW
master	INFORMATION_SCHEMA	TABLE_PRIVILEGES	VIEW
master	INFORMATION_SCHEMA	TABLES	VIEW
master	INFORMATION_SCHEMA	VIEW_COLUMN_USAGE	VIEW
master	INFORMATION_SCHEMA	VIEW_TABLE_USAGE	VIEW
master	INFORMATION_SCHEMA	VIEWS	VIEW
master	sys	all_columns	VIEW
master	sys	all_objects	VIEW
master	sys	all_parameters	VIEW
master	sys	all_sql_modules	VIEW
master	sys	all_views	VIEW
master	sys	allocation_units	VIEW
master	sys	assemblies	VIEW
master	sys	assembly_files	VIEW
master	sys	assembly_modules	VIEW
master	sys	assembly_references	VIEW
master	sys	assembly_types	VIEW
master	sys	asymmetric_keys	VIEW
master	sys	availability_databases_cluster	VIEW
master	sys	availability_group_listener_ip_addresses	VIEW
master	sys	availability_group_listeners	VIEW
master	sys	availability_groups	VIEW
master	sys	availability_groups_cluster	VIEW
master	sys	availability_read_only_routing_lists	VIEW
master	sys	availability_replicas	VIEW
master	sys	backup_devices	VIEW
master	sys	certificates	VIEW
master	sys	change_tracking_databases	VIEW
master	sys	change_tracking_tables	VIEW
master	sys	check_constraints	VIEW
master	sys	column_encryption_key_values	VIEW
master	sys	column_encryption_keys	VIEW
master	sys	column_master_keys	VIEW
master	sys	column_store_dictionaries	VIEW
master	sys	column_store_row_groups	VIEW
master	sys	column_store_segments	VIEW
master	sys	column_type_usages	VIEW
master	sys	column_xml_schema_collection_usages	VIEW
master	sys	columns	VIEW
master	sys	computed_columns	VIEW
master	sys	configurations	VIEW
master	sys	conversation_endpoints	VIEW
master	sys	conversation_groups	VIEW
master	sys	conversation_priorities	VIEW
master	sys	credentials	VIEW
master	sys	crypt_properties	VIEW
master	sys	cryptographic_providers	VIEW
master	sys	data_spaces	VIEW
master	sys	database_audit_specification_details	VIEW
master	sys	database_audit_specifications	VIEW
master	sys	database_automatic_tuning_mode	VIEW
master	sys	database_automatic_tuning_options	VIEW
master	sys	database_credentials	VIEW
master	sys	database_files	VIEW
master	sys	database_filestream_options	VIEW
master	sys	database_mirroring	VIEW
master	sys	database_mirroring_endpoints	VIEW
master	sys	database_mirroring_witnesses	VIEW
master	sys	database_permissions	VIEW
master	sys	database_principals	VIEW
master	sys	database_query_store_options	VIEW
master	sys	database_recovery_status	VIEW
master	sys	database_role_members	VIEW
master	sys	database_scoped_configurations	VIEW
master	sys	database_scoped_credentials	VIEW
master	sys	databases	VIEW
master	sys	default_constraints	VIEW
master	sys	destination_data_spaces	VIEW
master	sys	dm_audit_actions	VIEW
master	sys	dm_audit_class_type_map	VIEW
master	sys	dm_broker_activated_tasks	VIEW
master	sys	dm_broker_connections	VIEW
master	sys	dm_broker_forwarded_messages	VIEW
master	sys	dm_broker_queue_monitors	VIEW
master	sys	dm_cache_hit_stats	VIEW
master	sys	dm_cache_size	VIEW
master	sys	dm_cache_stats	VIEW
master	sys	dm_cdc_errors	VIEW
master	sys	dm_cdc_log_scan_sessions	VIEW
master	sys	dm_clr_appdomains	VIEW
master	sys	dm_clr_loaded_assemblies	VIEW
master	sys	dm_clr_properties	VIEW
master	sys	dm_clr_tasks	VIEW
master	sys	dm_cluster_endpoints	VIEW
master	sys	dm_column_encryption_enclave	VIEW
master	sys	dm_column_encryption_enclave_operation_stats	VIEW
master	sys	dm_column_store_object_pool	VIEW
master	sys	dm_cryptographic_provider_properties	VIEW
master	sys	dm_database_encryption_keys	VIEW
master	sys	dm_db_column_store_row_group_operational_stats	VIEW
master	sys	dm_db_column_store_row_group_physical_stats	VIEW
master	sys	dm_db_data_pool_nodes	VIEW
master	sys	dm_db_data_pools	VIEW
master	sys	dm_db_external_language_stats	VIEW
master	sys	dm_db_external_script_execution_stats	VIEW
master	sys	dm_db_file_space_usage	VIEW
master	sys	dm_db_fts_index_physical_stats	VIEW
master	sys	dm_db_index_usage_stats	VIEW
master	sys	dm_db_log_space_usage	VIEW
master	sys	dm_db_mirroring_auto_page_repair	VIEW
master	sys	dm_db_mirroring_connections	VIEW
master	sys	dm_db_mirroring_past_actions	VIEW
master	sys	dm_db_missing_index_details	VIEW
master	sys	dm_db_missing_index_group_stats	VIEW
master	sys	dm_db_missing_index_group_stats_query	VIEW
master	sys	dm_db_missing_index_groups	VIEW
master	sys	dm_db_partition_stats	VIEW
master	sys	dm_db_persisted_sku_features	VIEW
master	sys	dm_db_rda_migration_status	VIEW
master	sys	dm_db_rda_schema_update_status	VIEW
master	sys	dm_db_script_level	VIEW
master	sys	dm_db_session_space_usage	VIEW
master	sys	dm_db_storage_pool_nodes	VIEW
master	sys	dm_db_storage_pools	VIEW
master	sys	dm_db_task_space_usage	VIEW
master	sys	dm_db_tuning_recommendations	VIEW
master	sys	dm_db_uncontained_entities	VIEW
master	sys	dm_db_xtp_checkpoint_files	VIEW
master	sys	dm_db_xtp_checkpoint_internals	VIEW
master	sys	dm_db_xtp_checkpoint_stats	VIEW
master	sys	dm_db_xtp_gc_cycle_stats	VIEW
master	sys	dm_db_xtp_hash_index_stats	VIEW
master	sys	dm_db_xtp_index_stats	VIEW
master	sys	dm_db_xtp_memory_consumers	VIEW
master	sys	dm_db_xtp_nonclustered_index_stats	VIEW
master	sys	dm_db_xtp_object_stats	VIEW
master	sys	dm_db_xtp_table_memory_stats	VIEW
master	sys	dm_db_xtp_transactions	VIEW
master	sys	dm_distributed_exchange_stats	VIEW
master	sys	dm_exec_background_job_queue	VIEW
master	sys	dm_exec_background_job_queue_stats	VIEW
master	sys	dm_exec_cached_plans	VIEW
master	sys	dm_exec_compute_node_errors	VIEW
master	sys	dm_exec_compute_node_status	VIEW
master	sys	dm_exec_compute_nodes	VIEW
master	sys	dm_exec_compute_pools	VIEW
master	sys	dm_exec_connections	VIEW
master	sys	dm_exec_distributed_request_steps	VIEW
master	sys	dm_exec_distributed_requests	VIEW
master	sys	dm_exec_distributed_sql_requests	VIEW
master	sys	dm_exec_dms_services	VIEW
master	sys	dm_exec_dms_workers	VIEW
master	sys	dm_exec_external_operations	VIEW
master	sys	dm_exec_external_work	VIEW
master	sys	dm_exec_function_stats	VIEW
master	sys	dm_exec_procedure_stats	VIEW
master	sys	dm_exec_query_memory_grants	VIEW
master	sys	dm_exec_query_optimizer_info	VIEW
master	sys	dm_exec_query_optimizer_memory_gateways	VIEW
master	sys	dm_exec_query_parallel_workers	VIEW
master	sys	dm_exec_query_profiles	VIEW
master	sys	dm_exec_query_resource_semaphores	VIEW
master	sys	dm_exec_query_stats	VIEW
master	sys	dm_exec_query_transformation_stats	VIEW
master	sys	dm_exec_requests	VIEW
master	sys	dm_exec_session_wait_stats	VIEW
master	sys	dm_exec_sessions	VIEW
master	sys	dm_exec_trigger_stats	VIEW
master	sys	dm_exec_valid_use_hints	VIEW
master	sys	dm_external_authentication	VIEW
master	sys	dm_external_script_execution_stats	VIEW
master	sys	dm_external_script_requests	VIEW
master	sys	dm_external_script_resource_usage_stats	VIEW
master	sys	dm_filestream_file_io_handles	VIEW
master	sys	dm_filestream_file_io_requests	VIEW
master	sys	dm_filestream_non_transacted_handles	VIEW
master	sys	dm_fts_active_catalogs	VIEW
master	sys	dm_fts_fdhosts	VIEW
master	sys	dm_fts_index_population	VIEW
master	sys	dm_fts_memory_buffers	VIEW
master	sys	dm_fts_memory_pools	VIEW
master	sys	dm_fts_outstanding_batches	VIEW
master	sys	dm_fts_population_ranges	VIEW
master	sys	dm_fts_semantic_similarity_population	VIEW
master	sys	dm_hadr_ag_threads	VIEW
master	sys	dm_hadr_auto_page_repair	VIEW
master	sys	dm_hadr_automatic_seeding	VIEW
master	sys	dm_hadr_availability_group_states	VIEW
master	sys	dm_hadr_availability_replica_cluster_nodes	VIEW
master	sys	dm_hadr_availability_replica_cluster_states	VIEW
master	sys	dm_hadr_availability_replica_states	VIEW
master	sys	dm_hadr_cached_database_replica_states	VIEW
master	sys	dm_hadr_cached_replica_states	VIEW
master	sys	dm_hadr_cluster	VIEW
master	sys	dm_hadr_cluster_members	VIEW
master	sys	dm_hadr_cluster_networks	VIEW
master	sys	dm_hadr_database_replica_cluster_states	VIEW
master	sys	dm_hadr_database_replica_states	VIEW
master	sys	dm_hadr_db_threads	VIEW
master	sys	dm_hadr_instance_node_map	VIEW
master	sys	dm_hadr_name_id_map	VIEW
master	sys	dm_hadr_physical_seeding_stats	VIEW
master	sys	dm_hpc_device_stats	VIEW
master	sys	dm_hpc_thread_proxy_stats	VIEW
master	sys	dm_io_backup_tapes	VIEW
master	sys	dm_io_cluster_shared_drives	VIEW
master	sys	dm_io_cluster_valid_path_names	VIEW
master	sys	dm_io_pending_io_requests	VIEW
master	sys	dm_logpool_hashentries	VIEW
master	sys	dm_logpool_stats	VIEW
master	sys	dm_os_buffer_descriptors	VIEW
master	sys	dm_os_buffer_pool_extension_configuration	VIEW
master	sys	dm_os_child_instances	VIEW
master	sys	dm_os_cluster_nodes	VIEW
master	sys	dm_os_cluster_properties	VIEW
master	sys	dm_os_dispatcher_pools	VIEW
master	sys	dm_os_dispatchers	VIEW
master	sys	dm_os_enumerate_fixed_drives	VIEW
master	sys	dm_os_host_info	VIEW
master	sys	dm_os_hosts	VIEW
master	sys	dm_os_job_object	VIEW
master	sys	dm_os_latch_stats	VIEW
master	sys	dm_os_loaded_modules	VIEW
master	sys	dm_os_memory_allocations	VIEW
master	sys	dm_os_memory_broker_clerks	VIEW
master	sys	dm_os_memory_brokers	VIEW
master	sys	dm_os_memory_cache_clock_hands	VIEW
master	sys	dm_os_memory_cache_counters	VIEW
master	sys	dm_os_memory_cache_entries	VIEW
master	sys	dm_os_memory_cache_hash_tables	VIEW
master	sys	dm_os_memory_clerks	VIEW
master	sys	dm_os_memory_node_access_stats	VIEW
master	sys	dm_os_memory_nodes	VIEW
master	sys	dm_os_memory_objects	VIEW
master	sys	dm_os_memory_pools	VIEW
master	sys	dm_os_nodes	VIEW
master	sys	dm_os_performance_counters	VIEW
master	sys	dm_os_process_memory	VIEW
master	sys	dm_os_ring_buffers	VIEW
master	sys	dm_os_schedulers	VIEW
master	sys	dm_os_server_diagnostics_log_configurations	VIEW
master	sys	dm_os_spinlock_stats	VIEW
master	sys	dm_os_stacks	VIEW
master	sys	dm_os_sublatches	VIEW
master	sys	dm_os_sys_info	VIEW
master	sys	dm_os_sys_memory	VIEW
master	sys	dm_os_tasks	VIEW
master	sys	dm_os_threads	VIEW
master	sys	dm_os_virtual_address_dump	VIEW
master	sys	dm_os_wait_stats	VIEW
master	sys	dm_os_waiting_tasks	VIEW
master	sys	dm_os_windows_info	VIEW
master	sys	dm_os_worker_local_storage	VIEW
master	sys	dm_os_workers	VIEW
master	sys	dm_pal_cpu_stats	VIEW
master	sys	dm_pal_disk_stats	VIEW
master	sys	dm_pal_net_stats	VIEW
master	sys	dm_pal_processes	VIEW
master	sys	dm_pal_spinlock_stats	VIEW
master	sys	dm_pal_vm_stats	VIEW
master	sys	dm_pal_wait_stats	VIEW
master	sys	dm_qn_subscriptions	VIEW
master	sys	dm_repl_articles	VIEW
master	sys	dm_repl_schemas	VIEW
master	sys	dm_repl_tranhash	VIEW
master	sys	dm_repl_traninfo	VIEW
master	sys	dm_resource_governor_configuration	VIEW
master	sys	dm_resource_governor_external_resource_pool_affinity	VIEW
master	sys	dm_resource_governor_external_resource_pools	VIEW
master	sys	dm_resource_governor_resource_pool_affinity	VIEW
master	sys	dm_resource_governor_resource_pool_volumes	VIEW
master	sys	dm_resource_governor_resource_pools	VIEW
master	sys	dm_resource_governor_workload_groups	VIEW
master	sys	dm_server_audit_status	VIEW
master	sys	dm_server_memory_dumps	VIEW
master	sys	dm_server_registry	VIEW
master	sys	dm_server_services	VIEW
master	sys	dm_tcp_listener_states	VIEW
master	sys	dm_tran_aborted_transactions	VIEW
master	sys	dm_tran_active_snapshot_database_transactions	VIEW
master	sys	dm_tran_active_transactions	VIEW
master	sys	dm_tran_commit_table	VIEW
master	sys	dm_tran_current_snapshot	VIEW
master	sys	dm_tran_current_transaction	VIEW
master	sys	dm_tran_database_transactions	VIEW
master	sys	dm_tran_global_recovery_transactions	VIEW
master	sys	dm_tran_global_transactions	VIEW
master	sys	dm_tran_global_transactions_enlistments	VIEW
master	sys	dm_tran_global_transactions_log	VIEW
master	sys	dm_tran_locks	VIEW
master	sys	dm_tran_persistent_version_store	VIEW
master	sys	dm_tran_persistent_version_store_stats	VIEW
master	sys	dm_tran_session_transactions	VIEW
master	sys	dm_tran_top_version_generators	VIEW
master	sys	dm_tran_transactions_snapshot	VIEW
master	sys	dm_tran_version_store	VIEW
master	sys	dm_tran_version_store_space_usage	VIEW
master	sys	dm_xe_map_values	VIEW
master	sys	dm_xe_object_columns	VIEW
master	sys	dm_xe_objects	VIEW
master	sys	dm_xe_packages	VIEW
master	sys	dm_xe_session_event_actions	VIEW
master	sys	dm_xe_session_events	VIEW
master	sys	dm_xe_session_object_columns	VIEW
master	sys	dm_xe_session_targets	VIEW
master	sys	dm_xe_sessions	VIEW
master	sys	dm_xtp_gc_queue_stats	VIEW
master	sys	dm_xtp_gc_stats	VIEW
master	sys	dm_xtp_system_memory_consumers	VIEW
master	sys	dm_xtp_threads	VIEW
master	sys	dm_xtp_transaction_recent_rows	VIEW
master	sys	dm_xtp_transaction_stats	VIEW
master	sys	edge_constraint_clauses	VIEW
master	sys	edge_constraints	VIEW
master	sys	endpoint_webmethods	VIEW
master	sys	endpoints	VIEW
master	sys	event_notification_event_types	VIEW
master	sys	event_notifications	VIEW
master	sys	events	VIEW
master	sys	extended_procedures	VIEW
master	sys	extended_properties	VIEW
master	sys	external_data_sources	VIEW
master	sys	external_file_formats	VIEW
master	sys	external_language_files	VIEW
master	sys	external_languages	VIEW
master	sys	external_libraries	VIEW
master	sys	external_libraries_installed	VIEW
master	sys	external_library_files	VIEW
master	sys	external_library_setup_errors	VIEW
master	sys	external_table_columns	VIEW
master	sys	external_tables	VIEW
master	sys	filegroups	VIEW
master	sys	filetable_system_defined_objects	VIEW
master	sys	filetables	VIEW
master	sys	foreign_key_columns	VIEW
master	sys	foreign_keys	VIEW
master	sys	fulltext_catalogs	VIEW
master	sys	fulltext_document_types	VIEW
master	sys	fulltext_index_catalog_usages	VIEW
master	sys	fulltext_index_columns	VIEW
master	sys	fulltext_index_fragments	VIEW
master	sys	fulltext_indexes	VIEW
master	sys	fulltext_languages	VIEW
master	sys	fulltext_semantic_language_statistics_database	VIEW
master	sys	fulltext_semantic_languages	VIEW
master	sys	fulltext_stoplists	VIEW
master	sys	fulltext_stopwords	VIEW
master	sys	fulltext_system_stopwords	VIEW
master	sys	function_order_columns	VIEW
master	sys	hash_indexes	VIEW
master	sys	http_endpoints	VIEW
master	sys	identity_columns	VIEW
master	sys	index_columns	VIEW
master	sys	index_resumable_operations	VIEW
master	sys	indexes	VIEW
master	sys	internal_partitions	VIEW
master	sys	internal_tables	VIEW
master	sys	key_constraints	VIEW
master	sys	key_encryptions	VIEW
master	sys	linked_logins	VIEW
master	sys	login_token	VIEW
master	sys	masked_columns	VIEW
master	sys	master_files	VIEW
master	sys	master_key_passwords	VIEW
master	sys	memory_optimized_tables_internal_attributes	VIEW
master	sys	message_type_xml_schema_collection_usages	VIEW
master	sys	messages	VIEW
master	sys	module_assembly_usages	VIEW
master	sys	numbered_procedure_parameters	VIEW
master	sys	numbered_procedures	VIEW
master	sys	objects	VIEW
master	sys	openkeys	VIEW
master	sys	parameter_type_usages	VIEW
master	sys	parameter_xml_schema_collection_usages	VIEW
master	sys	parameters	VIEW
master	sys	partition_functions	VIEW
master	sys	partition_parameters	VIEW
master	sys	partition_range_values	VIEW
master	sys	partition_schemes	VIEW
master	sys	partitions	VIEW
master	sys	periods	VIEW
master	sys	plan_guides	VIEW
master	sys	procedures	VIEW
master	sys	query_context_settings	VIEW
master	sys	query_store_plan	VIEW
master	sys	query_store_query	VIEW
master	sys	query_store_query_text	VIEW
master	sys	query_store_runtime_stats	VIEW
master	sys	query_store_runtime_stats_interval	VIEW
master	sys	query_store_wait_stats	VIEW
master	sys	registered_search_properties	VIEW
master	sys	registered_search_property_lists	VIEW
master	sys	remote_data_archive_databases	VIEW
master	sys	remote_data_archive_tables	VIEW
master	sys	remote_logins	VIEW
master	sys	remote_service_bindings	VIEW
master	sys	resource_governor_configuration	VIEW
master	sys	resource_governor_external_resource_pool_affinity	VIEW
master	sys	resource_governor_external_resource_pools	VIEW
master	sys	resource_governor_resource_pool_affinity	VIEW
master	sys	resource_governor_resource_pools	VIEW
master	sys	resource_governor_workload_groups	VIEW
master	sys	routes	VIEW
master	sys	schemas	VIEW
master	sys	securable_classes	VIEW
master	sys	security_policies	VIEW
master	sys	security_predicates	VIEW
master	sys	selective_xml_index_namespaces	VIEW
master	sys	selective_xml_index_paths	VIEW
master	sys	sensitivity_classifications	VIEW
master	sys	sequences	VIEW
master	sys	server_assembly_modules	VIEW
master	sys	server_audit_specification_details	VIEW
master	sys	server_audit_specifications	VIEW
master	sys	server_audits	VIEW
master	sys	server_event_notifications	VIEW
master	sys	server_event_session_actions	VIEW
master	sys	server_event_session_events	VIEW
master	sys	server_event_session_fields	VIEW
master	sys	server_event_session_targets	VIEW
master	sys	server_event_sessions	VIEW
master	sys	server_events	VIEW
master	sys	server_file_audits	VIEW
master	sys	server_memory_optimized_hybrid_buffer_pool_configuration	VIEW
master	sys	server_permissions	VIEW
master	sys	server_principal_credentials	VIEW
master	sys	server_principals	VIEW
master	sys	server_role_members	VIEW
master	sys	server_sql_modules	VIEW
master	sys	server_trigger_events	VIEW
master	sys	server_triggers	VIEW
master	sys	servers	VIEW
master	sys	service_broker_endpoints	VIEW
master	sys	service_contract_message_usages	VIEW
master	sys	service_contract_usages	VIEW
master	sys	service_contracts	VIEW
master	sys	service_message_types	VIEW
master	sys	service_queue_usages	VIEW
master	sys	service_queues	VIEW
master	sys	services	VIEW
master	sys	soap_endpoints	VIEW
master	sys	spatial_index_tessellations	VIEW
master	sys	spatial_indexes	VIEW
master	sys	spatial_reference_systems	VIEW
master	sys	sql_dependencies	VIEW
master	sys	sql_expression_dependencies	VIEW
master	sys	sql_logins	VIEW
master	sys	sql_modules	VIEW
master	sys	stats	VIEW
master	sys	stats_columns	VIEW
master	sys	symmetric_keys	VIEW
master	sys	synonyms	VIEW
master	sys	sysaltfiles	VIEW
master	sys	syscacheobjects	VIEW
master	sys	syscharsets	VIEW
master	sys	syscolumns	VIEW
master	sys	syscomments	VIEW
master	sys	sysconfigures	VIEW
master	sys	sysconstraints	VIEW
master	sys	syscscontainers	VIEW
master	sys	syscurconfigs	VIEW
master	sys	syscursorcolumns	VIEW
master	sys	syscursorrefs	VIEW
master	sys	syscursors	VIEW
master	sys	syscursortables	VIEW
master	sys	sysdatabases	VIEW
master	sys	sysdepends	VIEW
master	sys	sysdevices	VIEW
master	sys	sysfilegroups	VIEW
master	sys	sysfiles	VIEW
master	sys	sysforeignkeys	VIEW
master	sys	sysfulltextcatalogs	VIEW
master	sys	sysindexes	VIEW
master	sys	sysindexkeys	VIEW
master	sys	syslanguages	VIEW
master	sys	syslockinfo	VIEW
master	sys	syslogins	VIEW
master	sys	sysmembers	VIEW
master	sys	sysmessages	VIEW
master	sys	sysobjects	VIEW
master	sys	sysoledbusers	VIEW
master	sys	sysopentapes	VIEW
master	sys	sysperfinfo	VIEW
master	sys	syspermissions	VIEW
master	sys	sysprocesses	VIEW
master	sys	sysprotects	VIEW
master	sys	sysreferences	VIEW
master	sys	sysremotelogins	VIEW
master	sys	sysservers	VIEW
master	sys	system_columns	VIEW
master	sys	system_components_surface_area_configuration	VIEW
master	sys	system_internals_allocation_units	VIEW
master	sys	system_internals_partition_columns	VIEW
master	sys	system_internals_partitions	VIEW
master	sys	system_objects	VIEW
master	sys	system_parameters	VIEW
master	sys	system_sql_modules	VIEW
master	sys	system_views	VIEW
master	sys	systypes	VIEW
master	sys	sysusers	VIEW
master	sys	table_types	VIEW
master	sys	tables	VIEW
master	sys	tcp_endpoints	VIEW
master	sys	time_zone_info	VIEW
master	sys	trace_categories	VIEW
master	sys	trace_columns	VIEW
master	sys	trace_event_bindings	VIEW
master	sys	trace_events	VIEW
master	sys	trace_subclass_values	VIEW
master	sys	traces	VIEW
master	sys	transmission_queue	VIEW
master	sys	trigger_event_types	VIEW
master	sys	trigger_events	VIEW
master	sys	triggers	VIEW
master	sys	trusted_assemblies	VIEW
master	sys	type_assembly_usages	VIEW
master	sys	types	VIEW
master	sys	user_token	VIEW
master	sys	via_endpoints	VIEW
master	sys	views	VIEW
master	sys	xml_indexes	VIEW
master	sys	xml_schema_attributes	VIEW
master	sys	xml_schema_collections	VIEW
master	sys	xml_schema_component_placements	VIEW
master	sys	xml_schema_components	VIEW
master	sys	xml_schema_elements	VIEW
master	sys	xml_schema_facets	VIEW
master	sys	xml_schema_model_groups	VIEW
master	sys	xml_schema_namespaces	VIEW
master	sys	xml_schema_types	VIEW
master	sys	xml_schema_wildcard_namespaces	VIEW
master	sys	xml_schema_wildcards	VIEW

The tables I need have the schema DwEms

Re: Accessing an SQL server using the ODBC engine

SASKiwi — Tue, 28 Mar 2023 00:49:05 GMT

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;