- SQL Server 2017 Administrator's Guide
- Marek Chmel Vladimír Mu?n?
- 751字
- 2025-04-04 17:46:08
Testing connection to a fresh SQL Server
As mentioned earlier, SQL Server does not contain a client management toolset in its installation. It's a good idea to install SQL Server Management Studio directly on the server where SQL Server service is already running because a lot of administrator's tasks will be done directly on the server, but for a quick check whether SQL Server is accessible to clients, the command prompt can be used. Its name is sqlcmd and it's the only client tool installed with SQL Server directly. This tool is very useful in some scenarios:
- When SQL Server Management Studio is not present or cannot be used (for example, when restoring the master database)
- When the express edition of SQL Server was installed and SQL Server Agent cannot be used (when planning regular tasks, it can be done by PowerShell or by sqlcmd in conjunction with Windows Task Scheduler)
The most simple way to use sqlcmd is as follows:
sqlcmd
When running sqlcmd as shown in the preceding code example, it tries to connect the local default instance of SQL Server using the current user's Windows account. When successfully connected, rows in the command prompt window start to be numbered.
A better approach is to call sqlcmd with parameters precisely set:
sqlcmd -E -S localhost
In domain user context or with SQL login context:
sqlcmd -U <user name> -P <password> -S localhost
The E parameter (beware that all parameters of all command line tools provided by SQL Server are case-sensitive) says to the connection that Windows login context of the user currently logged in the desktop will be used; U and P parameters are used when user wants to connect via mixed authentication mode of SQL Server. Then user and password created on the SQL Server are used, not the Windows identity.
The S parameters is used for the name of the server. If connected locally on a default instance of SQL Server, shortcuts such as . or (localhost) could be used.
All the preceding examples start the sqlcmd tool in interactive mode. When successfully connected, rows start numbering and the user can start to write queries. Every query must be followed by the GO keyword. This keyword (sometimes called batch terminator) causes the text written to the console to be sent to SQL Server and then processed with some result.
Results returned back to the console are not so readable in many cases, so the sqlcmd could be started with the command parameter, o, followed by the path to the output file. The output file is just a text file catching all results from all queries sent by the user in the session.
When the user wants to run sqlcmd in unattended mode, the i parameter followed by the path to the input file may also be very useful. A complete example may look the following. The first piece of code shows the correctly created input file (for example, demo.sql):
-- content of demo.sql file
use master
go
select @@version as VersionOfMySQL
go
The first line (use master) establishes the correct database context in the connection and it is highly recommended to never commit this row because very often, the database context is not the default database context set for login.
The third line is just an example of doing something meaningful.
When the administrator wants to run a script file like this, he can add the following command to the command prompt:
sqlcmd -E -S (localhost) -i "c:\demo.sql" -o "c:\demo_output.txt"
The command will run and it will save all results (even if error will occur) to the file called demo_output.txt.
There are more useful command parameters for sqlcmd but this set, especially the first three examples, are sufficient to test an instance's accessibility locally.
For remote testing of accessibility, very common way is to use SQL Server Management Studio. Common issues (followed by error No. 40 - Network Related Error) are as follows:
- SQL Server instance is not running: In Sql Server Configuration Manager, this error is seen if service is running or not. When it's not running, we can try to start it up manually and diagnose additional errors.
- TCP/IP protocol is disabled: This issue may be corrected by Sql Server Configuration Manager (requires restart after reconfiguring).
- Other than default TCP port number is used: It can be corrected on user's side by adding the port number after server name (for example, MYSQLSERVER:12345).
- Firewall rules are not set: It must be resolved on firewall's side by enabling certain ports for communication.