Меню

Sqlcmd ошибка microsoft odbc driver 11 for sql server

Hi everybody

I have a SQL 2014 EXPRESS server. I want to schedule automatic backups using the SQL Server Maintenance Solution by Ola Hallengren. In order to do this I have created a CMD script which is to be used by Task Scheduler. However, I am having some trouble connecting
to the SQL server using the command sqlcmd in the command prompt. 

For starters I’m just trying to directly connect through the prompt using the command 

sqlcmd -S 12.0.2269

Where 12.0.2269 is the IP of my server. I have also tried connecting using the name of the server, and I have also tried connecting using specific ports, which have been opened in the firewall (both inbound and outbound), e.g.

sqlcmd -S 12.0.2269,1433

In addition, I have ticked off the field that says «Allow remote connections to this server» in the SQL Server Management Studio. The server I am trying to connect to is «located» on the machine I am working on, so I don’t quite get what
is wrong. I hope you can help. When I connect using the command

sqlcmd -S 12.0.2269,1433

I get the following error messages

C:UsersThomas>sqlcmd -S 12.0.2269,1433Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: 
Et forsøg på at oprette forbindelse mislykkedes, fordi den part, der havde oprettet forbindelse, 
ikke svarede korrekt efter en periode, eller en oprettet forbindelse blev afbrudt, fordi værten ikke svarede.
.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired
.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error 
has occurred while establishing a connection to SQL Server. Server is not found or not accessible. 





  



                       
  





Check if instance name is correct and if SQL Server is configured to allow remote connections. For more 
information see SQL Server Books Online..
C:UsersThomas>

When connecting using the server name, these are the errors I am getting

C:UsersThomas>sqlcmd -S ./MUUTO270813
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [3]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

C:UsersThomas>

I hope you can help 🙂

Источник
  • Remove From My Forums

  • Question

  • I tried run SQLCMD from the command line:

    sqlcmd -E -S MSSQLSERVER

    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: 
    Could not open a connection to SQL Server [67]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server :
    A network-related or instance-specific error has occurred 
    while establishing a connection to SQL Server. Server is not found or not accessible. 
Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online..

Answers

  • Hello,

    Is SQL Server running local on the machine? The replace MSSQLSERVER with a dot (if it’s a Default instance)

    sqlcmd -E -S .

    Optional you can use the Parameter -L (large L) to list local instances

    sqlcmd -L

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by

      Friday, January 8, 2016 10:48 AM

    • Marked as answer by
      Morgan2015
      Tuesday, January 12, 2016 6:27 PM

  • If you connecting remote server then

    server property —>connection —>remote connection-> should enabled Allow remote connection.

    syntax:-

    sqlcmd -S Servername -U username -P password

    Please click Mark As Answer if my post helped.

    • Edited by
      AV111
      Friday, January 8, 2016 4:28 AM
    • Marked as answer by
      Morgan2015
      Tuesday, January 12, 2016 6:27 PM

Источник

SQL Server 2014 Enterprise Cluster
Windows 2012 R2 Servers

On the Node1 SQL Cluster server I am running SQLCMD scripts to backup the SQL databases.

PS C:Util> sqlcmd -d master -Q «EXECUTE dbo.DatabaseBackup @databases = ‘Master’, @Directory = ‘F:SQL-BackupData’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 672» -b >>$LogFolder$LogFile

sqlcmd : Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .

At line:1 char:1

+ sqlcmd -d master -Q «EXECUTE dbo.DatabaseBackup @databases = ‘Mast …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (Sqlcmd: Error: …L Server [2]. .:String) [], RemoteException

+ FullyQualifiedErrorId : NativeCommandError

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server

is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books

Online..

Just built this Node1 server not sure why sqlcmd does not run on the server itself

Any ideas ??

Thank you,

Tom

Sue_H

SSC Guru

Points: 90860

trgrassijr — Sunday, April 22, 2018 2:44 PM

SQL Server 2014 Enterprise Cluster
Windows 2012 R2 Servers

On the Node1 SQL Cluster server I am running SQLCMD scripts to backup the SQL databases.

PS C:Util> sqlcmd -d master -Q «EXECUTE dbo.DatabaseBackup @databases = ‘Master’, @Directory = ‘F:SQL-BackupData’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 672» -b >>$LogFolder$LogFile

sqlcmd : Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .

At line:1 char:1

+ sqlcmd -d master -Q «EXECUTE dbo.DatabaseBackup @databases = ‘Mast …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (Sqlcmd: Error: …L Server [2]. .:String) [], RemoteException

+ FullyQualifiedErrorId : NativeCommandError

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server

is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books

Online..

Just built this Node1 server not sure why sqlcmd does not run on the server itself

Any ideas ??

Thank you,

Tom

Is this a named instance or a default instance?
What protocols are enabled?
Do you only get the errors running from sqlcmd while on the server itself?
Do you get the error when you specify the server name?

Sue

trgrassijr

Right there with Babe

Points: 729

Hi Sue

Thank you for replying.

This is the default instance  but during the install I gave the SQL Server Network name this TGCS014-N1-SLC

All protocols are enabled

I get this from running the command on the server itself

I was able to get it to work using this command
sqlcmd -S tgcs014-n1-sqlc -E -d master -Q «EXECUTE dbo.DatabaseBackup @databases  =  ‘Master’,  @Directory  =  ‘F:SQL-BackupData’,  @BackupType  =  ‘FULL’,  @Verify = ‘Y’,  @CleanupTime  =  672» -b >>$LogFolder$LogFile

Strange that from the server I get this I have the exact same sqlcmd on my other SQL servers one SQL 2012 and another SQL 2014 server.

Any ideas why I would need the server name when running from the server?

Thank you
Tom

Sue_H

SSC Guru

Points: 90860

trgrassijr — Tuesday, April 24, 2018 6:09 PM

Hi Sue

Thank you for replying.

This is the default instance  but during the install I gave the SQL Server Network name this TGCS014-N1-SLC

All protocols are enabled

I get this from running the command on the server itself

I was able to get it to work using this command
sqlcmd -S tgcs014-n1-sqlc -E -d master -Q «EXECUTE dbo.DatabaseBackup @databases  =  ‘Master’,  @Directory  =  ‘F:SQL-BackupData’,  @BackupType  =  ‘FULL’,  @Verify = ‘Y’,  @CleanupTime  =  672» -b >>$LogFolder$LogFile

Strange that from the server I get this I have the exact same sqlcmd on my other SQL servers one SQL 2012 and another SQL 2014 server.

Any ideas why I would need the server name when running from the server?

Thank you
Tom

It looks for the default instance MSSQLSERVER when you don’t specify the name.

Sue

trgrassijr

Right there with Babe

Points: 729

Sue

Ok that makes sense now to me

Источник

Вопрос:

Я пытаюсь выполнить одну хранимую процедуру, используя пользователя ‘sa’ через Perl скрипт

сохранить эту строку в файле perl и выполнить этот файл perl

system("start /wait sqlcmd.exe -S serverName -U sa -P test@123 -d JSONdb -Q "EXECUTE UPDATE_SP" -o C:\Temp\log.txt");

================================================== ===========================

-S serverName :: sql сервер, к которому я хочу подключиться.

-U sa -P test @123 :: учетные данные для входа

-d JSONdb :: имя базы данных.

”EXECUTE update_JSONTable ” :: команда для выполнения хранимой процедуры [update_JSONTable]

-o C:Templog.txt “:: сохранение логов

Лучший ответ:

Причин может быть три:

  1. SQL Server (или смешанная) аутентификация отключена
  2. Пароль, используемый в команде, неверен
  3. База данных [JSONdb] недоступна

Другая причина может быть связана с выходом специальных символов. Ваш пароль содержит @, поэтому этот аргумент также должен быть скорректирован:

 -P test@123 =>  -P test@123
Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Sql сервер обнаружил логическую ошибку ввода вывода
  • Sql при выполнении текущей команды возникла серьезная ошибка

    • ➤