Most development environments that I have worked in have a developer edition of Microsoft SQL Server on the local machine of the developer. Hence, both the client (SSMS) and the database server are often on the same machine.
What this means is that the default connection mechanism used for all connections will be “Shared Memory”. However, when working on features like auditing, the ability to simulate a TCP/IP connection becomes important. Today, I document a mechanism that you can use to initiate a TCP/IP connection even when both server and client (SSMS) are the on the same machine, provided the server has been configured to allow TCP/IP connections in the Configuration Manager.
SQL Server Configuration Manager – ensuring that TCP/IP connections to the server are enabled
When initiating a new connection using the SQL Server Management Studio, simply click on the “Options” button in the “Connect to Database Engine” window, and navigate to the “Connection properties” tab.
SQL Server Management Studio (SSMS) – The “Connect to Database Engine” window
SQL Server Management Studio (SSMS) – Using the Connection Properties tab to change the Network protocol
In the “Network” group, locate the “Network Protocol” drop-down. Change the value from “<default>” to “TCP/IP“.
Finally, verify the credentials in the “Login” tab and click “Connect“. That’s it!
Use the CONNECTIONPROPERTY() function described in my previous post to confirm that you are indeed connected via TCP/IP.
- Testing your connection strings using SSMS [Link]
- Using CONNECTIONPROPERTY to determine connection properties [Link]
Today’s post comes from a recent experience. I was trying to extract an XML from one of the logs that we maintain in the application when exchanging data over an interface. The XML was coming from a table and I had written a simple SELECT statement similar to the one shown below:
FROM dbo.XMLSSMSTest AS xt;
While SSMS displayed the XML in the results pane, clicking on the XML attempts to open the XML. With a very large XML, it results into the following error:
TITLE: Microsoft SQL Server Management Studio
Unable to show XML. The following error happened:
Unexpected end of file while parsing Name has occurred. Line 1, position 2097154.
One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.
The solution is actually indicated in the error message itself – increase the number of characters being retrieved from the server for XML data. To do this:
Go to Tools –> Options
Expand the “Query Results” node
Expand SQL Server –> Results to Grid options
Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
Click “OK” and close out of the Options window
Attempt to open the XML again
A note of caution
Changing the Maximum Characters Retrieved value may impact the SSMS performance depending upon the size of the XML being opened.
Until we meet next time,
Be courteous. Drive responsibly.