The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include: TCP Ports Named Pipes Shared Memory VIA (Virtual Interface Adapater) – Deprecated When communicating, SQL Server can actually use a combination of all three of these protocols for different sessions depending on whether or not they are enabled. You can find more specifics regarding these protocols here. If that doesn’t make things complicated enough, within the TCP protocol, static or dynamic ports can be defined. If the communication is set to a static port (most common) then it is by default 1433. If it is set to dynamic, then it typically uses a range of ports. It is this range of ports that is not easy to determine, unless you use a command at the command line and correlate the associated process ID with that of the SQL Server instance you are investigating. The easy way to find the ports is to use the following script which will enumerate all ports that all SQL Server instances are using. (Once you download the script, rename it so SQLListeningPorts.vbs). The script is also shown below. All you need to do is cut and paste it into notepad, save it with a .vbs extension and double click it. (or right click and ‘open with’ Microsoft Script Host). Show All SQL Ports VB Script [cc lang=”vb”] […]
Continue reading ...
This neat and simple little trick will help to transform each row in a table or query into an XML row. The simplest way is to use the FOR XML clause. However if you want to return the XML as a separate column in the table, you need to perform a self-join on the table itself. [cc lang=”sql”] SELECT TOP 100 EmployeeID, EmployeeXML = ( SELECT EmpLastName = LastName ,e_xml.* FROM dbo.Employee e_xml WHERE e_xml.EmployeeID = emp.EmployeeID FOR XML PATH (”) ) FROM dbo.Employee emp [/cc] The EmpLastName assignment shows how you can assign your own custom XML tags based on each column.
Continue reading ...
XQuery in SQL Server was introduced in version 2005, along with so many major advancements. The ability to store and query XML has changed the game of SQL Server and has made it what it more of what it should be, a better workflow / storage engine. There are some things to consider prior to introducing XML into the database. The most plausible question you can ask yourself when using XML instead of a table is, “do these XML elements have any use as individual items, or do they only have meaning within the context of this XML document”. Weighing that question, along with the ease of XML portability will hopefully give you the answer you need. Now to use XQuery. SQL Server only supports a subset of XQuery so you don’t want to get too fancy with your needs. There are certain functions (like substring) that are not supported, however most of the missing functionality can be addressed using SQL Server functions. If you do need these added functionalities, then you also may want to consider whether the XML manipulation using SQL is the right approach. While XML is quite useful, and while SQL Server can (architecturally) be used as a workflow engine, when it comes to certain items like display functions, SQL Server is not the right choice. This following example is from a rules engine I am creating. The XML itself represents a rule, and the XQuery below parses the rule into a table. The beauty of […]
Continue reading ...
sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server. [cc lang=”sql”] EXEC sp_configure [/cc] These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options this procedure does not show us unless we change an additional configuration setting, named, ‘show advanced options’. Let’s do that now. [cc lang=”sql”] EXEC sp_configure ‘show advanced options’, 1 — Changing to 1 turns it on GO RECONFIGURE WITH OVERRIDE [/cc] Now that we have changed this option, let’s execute sp_configure again and look at the result. [cc lang=”sql”] EXEC sp_configure [/cc] Now we should see upwards of 70 configuration options we are able to set on SQL Server. In order to set them, we only need to copy the exact name listed in the ‘name’ column, and pass it as the first parameter for sp_configure, then for the second parameter, pass our desired value. [cc lang=”sql”] EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE WITH OVERRIDE [/cc]
Continue reading ...
The system stored procedure sp_addlinkedserver is used in order to link a server from the server in which you are executing the command. In order to connect to a remote SQL Server, you basically only need 3 parameters. [cc lang=”sql”] EXEC sp_addlinkedserver @server=N’HOGEN-PC’, — Remote Computer Name @srvproduct=N”, — Not Needed @provider=N’SQLNCLI’, — SQL Server Driver @datasrc=N’HOGEN-PCSQLEXPRESS’; — Server Name And Instance [/cc] If the remote SQL Server does not have an instance name, then the @datasrc parameter nee only contain the remote server name and not the instance.
Continue reading ...
