MSSQL: подключение из консоли Linux

Автор: | 13/07/2016
Нет комментариев
 

sql-logo-no-version-250Для доступа к серверу MSSQL из консоли Linux – можно использовать утилиту tsql из набора FreeTDS.

Устанавливаем FreeTDS:

$ sudo apt-get install freetds-bin

Пробуем подключиться:

$ tsql -S platformnew.database.windows.net -U username
Password: 
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=uk_UA.UTF-8;LC_TIME=uk_UA.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=uk_UA.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=uk_UA.UTF-8;LC_NAME=uk_UA.UTF-8;LC_ADDRESS=uk_UA.UTF-8;LC_TELEPHONE=uk_UA.UTF-8;LC_MEASUREMENT=uk_UA.UTF-8;LC_IDENTIFICATION=uk_UA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20004 (severity 9):
        Read from the server failed
        OS error 104, "Connection reset by peer"
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

Для просмотра параметров, с которыми собран tsql – используйте -C:

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

Что бы избежать ошибки “OS error 104” – указываем версию протокола TDS:

$ TDSVER=7.1 tsql -S platformnew.database.windows.net -U username
Password: 
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=uk_UA.UTF-8;LC_TIME=uk_UA.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=uk_UA.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=uk_UA.UTF-8;LC_NAME=uk_UA.UTF-8;LC_ADDRESS=uk_UA.UTF-8;LC_TELEPHONE=uk_UA.UTF-8;LC_MEASUREMENT=uk_UA.UTF-8;LC_IDENTIFICATION=uk_UA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

8.0 тоже подходит.

Что бы подключиться напрямую к базе – добавьте -D:

$ TDSVER=7.1 tsql -S platfromstage.database.windows.net -U usename -P password -D PlatformDB_stage1
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=uk_UA.UTF-8;LC_TIME=uk_UA.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=uk_UA.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=uk_UA.UTF-8;LC_NAME=uk_UA.UTF-8;LC_ADDRESS=uk_UA.UTF-8;LC_TELEPHONE=uk_UA.UTF-8;LC_MEASUREMENT=uk_UA.UTF-8;LC_IDENTIFICATION=uk_UA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Default database being set to PlatformDB_stage1
1>

Проверяем.

Получить список баз:

1> select * from sys.databases
2> go
name    database_id     source_database_id      owner_sid       create_date     compatibility_level     collation_name  user_access     user_access_desc        is_read_only    is_auto_close_on        is_auto_shrink_on       state   state_desc      is_in_standby   is_cleanly_shutdown    is_supplemental_logging_enabled snapshot_isolation_state        snapshot_isolation_state_desc   is_read_committed_snapshot_on   recovery_model  recovery_model_desc     page_verify_option      page_verify_option_desc is_auto_create_stats_on is_auto_create_stats_incremental_on    is_auto_update_stats_on is_auto_update_stats_async_on   is_ansi_null_default_on is_ansi_nulls_on        is_ansi_padding_on      is_ansi_warnings_on     is_arithabort_on        is_concat_null_yields_null_on   is_numeric_roundabort_on        is_quoted_identifier_on        is_recursive_triggers_on        is_cursor_close_on_commit_on    is_local_cursor_default is_fulltext_enabled     is_trustworthy_on       is_db_chaining_on       is_parameterization_forced      is_master_key_encrypted_by_server       is_query_store_on      is_published    is_subscribed   is_merge_published      is_distributor  is_sync_with_backup     service_broker_guid     is_broker_enabled       log_reuse_wait  log_reuse_wait_desc     is_date_correlation_on  is_cdc_enabled  is_encrypted    is_honor_broker_priority_on    replica_id      group_database_id       default_language_lcid   default_language_name   default_fulltext_language_lcid  default_fulltext_language_name  is_nested_triggers_on   is_transform_noise_words_on     two_digit_year_cutoff   containment     containment_desc       target_recovery_time_in_seconds is_federation_member
master  1       NULL    010600000000006480000000005886943cdfdc9b193d1a4d9689ea6b6717e40d        лип  5 2016 09:41       100     SQL_Latin1_General_CP1_CI_AS    0       MULTI_USER      0       0       0       0       ONLINE  0       0       0       1       ON      1     1FULL    2       CHECKSUM        1       0       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       00000000-0000-0000-0000-000000000000   0       0       NOTHING 0       0       0       0       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    0       NONE    0       0
PlatformDB_NEW_TEST        4       NULL    010600000000016480000000005886948cbe7f4b5fca2c4f9d8aceb99b36581e        лип  5 2016 09:42       100     SQL_Latin1_General_CP1_CI_AS    0       MULTI_USER      0       0       0       0       ONLINE  0       0     01       ON      1       1       FULL    2       CHECKSUM        1       0       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0     00000000-0000-0000-0000-000000000000     0       0       NOTHING 0       0       0       0       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    0       NONE    0       0
PlatformCMS_NEW_TEST       5       NULL    010600000000016480000000005886948cbe7f4b5fca2c4f9d8aceb99b36581e        лип  5 2016 09:57       100     SQL_Latin1_General_CP1_CI_AS    0       MULTI_USER      0       0       0       0       ONLINE  0       0     01       ON      1       1       FULL    2       CHECKSUM        1       0       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0     00000000-0000-0000-0000-000000000000     0       0       NOTHING 0       0       0       0       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    0       NONE    0       0
PlatformCMS_NEW_TEST_Copy  6       NULL    010600000000016480000000005886948cbe7f4b5fca2c4f9d8aceb99b36581e        лип  7 2016 08:26       100     SQL_Latin1_General_CP1_CI_AS    0       MULTI_USER      0       0       0       0       ONLINE  0       0     01       ON      1       1       FULL    2       CHECKSUM        1       0       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0     00000000-0000-0000-0000-000000000000     0       0       NOTHING 0       0       0       0       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    0       NONE    0       0
(4 rows affected)

И выборка таблиц из базы:

1> select *
2> from information_schema.columns
3> order by table_name, ordinal_position
4> go 3
TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        COLUMN_DEFAULT  IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_PRECISION_RADIX NUMERIC_SCALE   DATETIME_PRECISION     CHARACTER_SET_CATALOG   CHARACTER_SET_SCHEMA    CHARACTER_SET_NAME      COLLATION_CATALOG       COLLATION_SCHEMA        COLLATION_NAME  DOMAIN_CATALOG  DOMAIN_SCHEMA   DOMAIN_NAME                                                                                    
PlatformDB_stage1    dbo     __MigrationHistory      MigrationId     1       NULL    NO      nvarchar        255     510     NULL    NULL    NULL    NULL    NULL    NULL    UNICODE NULL    NULL    SQL_Latin1_General_CP1_CI_AS    NULL    NULL    NULL           
PlatformDB_stage1    dbo     __MigrationHistory      Model   2       NULL    NO      varbinary       -1      -1      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL                                           
PlatformDB_stage1    dbo     __MigrationHistory      ProductVersion  3       NULL    NO      nvarchar        32      64      NULL    NULL    NULL    NULL    NULL    NULL    UNICODE NULL    NULL    SQL_Latin1_General_CP1_CI_AS    NULL    NULL    NULL           
PlatformDB_stage1    dbo     ContentViewCounters     ContentViewCounterId    1       NULL    NO      int     NULL    NULL    10      10      0       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL                                   
...