Different Ways to Connect to SQL Server Using sqlcmd
栏目分类：资料 发布日期：2017-10-31 浏览次数：次
本文为去找网小编(www.7zhao.net)为您推荐的Different Ways to Connect to SQL Server Using sqlcmd，希望对您有所帮助，谢谢！
You need to connect to a SQL Server instance using sqlcmd. At first it seems to be an easy task, but sometimes things can get complicated. To mention a few of these things: How do you connect to a SQL Server instance that does not listen on a TCP port? How do you connect to a SQL Server instance if your host machine cannot resolve the server DNS? What about named instances? Keep reading this tip, so you can answer these questions. 欢迎访问www.7zhao.net
Those of us that have been in the SQL Server world for many years often disregarded sqlcmd. Lets face it; we are used to graphic user interfaces, like (SSMS). But now that SQL Server has entered the Linux world we must adapt to use the command line, usually referred to as a shell amongst the Linux community. 去找(www.7zhao.net欢迎您
We need to get used to seeing more SQL Server instances on Linux in the coming years. Usually production Linux servers do not have a graphical interface and there is no Remote Desktop Connection, so everything is done within a SSH connection. 本文来自去找www.7zhao.net
SQL Server Connection Protocols
SQL Server allows three different connection protocols that I will describe later in this tip: 本文来自去找www.7zhao.net
- Named Pipes
- Shared Memory
When we connect to a SQL Server instance using SSMS, we just enter the server name in the log in screen, select the authentication method and then connect and then we can start using SSMS.
If we want to use a specific protocol when connecting with SSMS we just need to select the appropriate protocol from a combo box like on the image below. 欢迎访问www.7zhao.net
But when we use the console to connect and need to use a specific protocol, we must do this without a combo box.
Connecting with sqlcmd using a Specific Protocol
Most of the time when we connect to a SQL Server instance we dont specify a protocol and you may think that you are using the TCP/IP protocol. Well, let me tell you that if you dont specify the connection protocol then you dont have a guarantee that you are using TCP/IP. There is a protocol order that SSMS and sqlcmd have which is determined by the protocol order in SQL Server Configuration Manager.
On the following image I show you my protocol order. But there is something really important! Please realize that there are two Client Configurations. One is for 32 bit applications and the other is for 64 bit applications. This is especially important if you are using , because you may be using a 32 bit connector, but if the package runs on 64 bit version and you havent configured the 64 bit settings, the package will fail.
SQL Server TCP/IP Connection with sqlcmd
TCP/IP is well known to us. In order to connect to a SQL Server instance using the TCP/IP protocol you need the servers IP address or the host name and the listening port if your instance doesnt run on the default port. 内容来自www.7zhao.net
We just need to specify the server name or its IP address and in some cases the port number. The following is the general syntax to connect using TCP/IP: 去找(www.7zhao.net欢迎您
sqlcmd -S tcp:<computer name>,<port number> 本文来自去找www.7zhao.net
Notice that we add tcp: prior to the server address. I mean, if you just enter an IP address you dont have a guarantee that you will use the TCP/IP protocol. For example if you want to connect using the TCP/IP protocol to a server named SQL-A using SQL Servers default instance with Windows Authentication, the syntax will be as follows: 本文来自去找www.7zhao.net
sqlcmd S tcp:SQL-A 去找(www.7zhao.net欢迎您
If you want to use SQL Server authentication then you need to specify the user name and password as follows:
sqlcmd S tcp:SQL-A U sa P Pa$$w0rdcopyright www.7zhao.net
When you cannot resolve the host name of the server you should use the IP address instead of the host name. In such case, all the previous commands will look like this assuming 10.10.10.10 is the servers IP address: 本文来自去找www.7zhao.net
sqlcmd S tcp:10.10.10.10 sqlcmd S tcp:10.10.10.10 U sa P Pa$$w0rd
For a named instance (in this case an instance named TEST) the previous commands are as follows: www.7zhao.net
sqlcmd S tcp:SQL-A\TEST sqlcmd S tcp:SQL-A\TEST U sa P Pa$$w0rd 内容来自www.7zhao.net
Also, you may need to specify the listening port. In such case you must specify the port after a comma. For example, suppose that my default instance uses port 1433 and the TEST instance runs on port 51613, then your connection string will be as follows:
sqlcmd S tcp:SQL-A,1433 sqlcmd S tcp:SQL-A,1433 U sa P Pa$$w0rd sqlcmd S tcp:10.10.10.10,1433 sqlcmd S tcp:10.10.10.10,1433 U sa P Pa$$w0rd sqlcmd S tcp:SQL-A,51613 sqlcmd S tcp:SQL-A,51613 U sa P Pa$$w0rd sqlcmd S tcp:10.10.10.10,51613 sqlcmd S tcp:10.10.10.10,51613 U sa P Pa$$w0rd
SQL Server Named Pipes Connection with sqlcmd
A named pipe is a named, one-way or duplex pipe for communication amongst a server and a client. Internally, all instances of a named pipe have the same pipe name, but they keep their own buffers that allow message-based communication and client impersonation. Named Pipes relies on Inter Process Communication (IPC). There is something to note which is the fact that if you connect to a local instance using Named Pipes, that pipe will run in Kernel mode as a Local Procedure Call (LPC).
Usually this protocol is not used because when connecting to a remote SQL Server instance it is preferred to use the TCP/IP protocol. On the other hand, when connecting to a local instance Shared Memory is often the protocol of choice.
The syntax of a named pipe connection for the default instance is as follows:
\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query www.7zhao.net
For example, a named pipe to the default instance of server MYSERVER will be like this:
On the other hand, the syntax for a named instance looks as follows:
\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query
As an example, if we want to use a named pipe to the SQL Server instance TEST on server SQL-A, the syntax will be like this:
Connecting to a SQL Server instance by using the named pipes protocol is not too different than using TCP/IP. I can say that we must add np: prior to the server address, but it is incorrect or at least incomplete. When you connect using named pipes you dont connect to a server. Instead you connect to a pipe, thats why this protocol is called named pipes. 欢迎访问www.7zhao.net
In the previous section I explained how to build a pipe, now in the following piece of code I will show you the way to connect to a SQL Server default instance and to a named instance using both Windows and SQL Server authentication.
sqlcmd S np:\\SQL-A\pipe\sql\query sqlcmd S np:\\SQL-A\pipe\sql\query U sa P Pa$$w0rd sqlcmd S np:\\SQL-A\pipe\MSSQL$TEST\sql\query sqlcmd S np:\\SQL-A\pipe\MSSQL$TEST\sql\query U sa P Pa$$w0rd 内容来自www.7zhao.net
SQL Server Shared Memory Connection with sqlcmd
This protocol only can be used when the client connecting runs on the local server. Basically it is a Local Procedure Call (LPC) that runs in Kernel Mode. Also, if we think about the previous explanation of named pipes, we can easily realize that shared memory is a special case of named pipes.
Something to note is that if you are using MDAC 2.8 or earlier, you cannot use the shared memory protocol. If you try to use this protocol, sqlcmd will automatically switch to the named pipes protocol.
If we think about it, the fact that MDAC 2.8 automatically translates shared memory to named pipes makes sense with the idea that shared memory is a special case of a named pipe.
The prefix to use Shared Memory is lpc:. Remember I told you that Shared Memory is a Local Procedure Call. In this case we dont need to worry about if we are able to resolve the host name, shared memory only works locally and you will always resolve to the local server name. www.7zhao.net
sqlcmd S lpc:SQL-A sqlcmd S lpc:SQL-A U sa P Pa$$w0rd sqlcmd S lpc:SQL-A\TEST sqlcmd S lpc:SQL-A\TEST U sa P Pa$$w0rd
- If this was the first time you heard of sqlcmd, my previous tip will serve as an introduction: .
- Check out this tip where we covered the basics on how to connect to SQL Server with sqlcmd: .
- You can get more information about the connection protocols of SQL Server in this tip:
- If you are running Linux then this tip will guide you to install sqlcmd: .
- You can also use sqlcmd in SQL Server Management Studio by using the sqlcmd mode: .
- In case you need to audit the connection protocols allowed by each of your SQL Server instances, the following tip will serve as a starting point: .
Last Update: 2017-10-31
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.
以上为Different Ways to Connect to SQL Server Using sqlcmd文章的全部内容，若您也有好的文章，欢迎与我们分享！ 本文来自去找www.7zhao.net
- Different Ways to Connect to SQL Server Using sqlcmd
- How APIs work — An Analogy For Dummies
- R live class | Data Visualization and Dashboard with
- Issue 111 – 31st October 最新
- Bitcoin Cash Developers Set Date for November Hard Fo
- iOS 11更新后以及iPhone X推出后工程中遇到的