Sem Dendoncker

Hello,

To receive the number of connections on a database you can use the following query:

SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame

Cheers,
Sem (aka Mayiko)

When you install SQL 2005/2008 you probably won’t be able to connect to it remotely.
This is because the default settings in the installation won’t allow any remote access to the sql server.

If you try to connect to it you can expect errors like these:

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

or,

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

This is a quick guide on how to enable remote connections to your sql server. I assume that the installation has been done correctly.

Firstly, enable and turn on the local and remote connections setting (or check if they are turned on):

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
3. On the “Surface Area Configuration for Services and Connections” page, expand Database Engine tree, click Remote Connections.
4. Select Local and remote connections, or Local connections only which applicable only if there is no remote system tries to connect to the SQL Server, useful when you just trying to connect and authenticate with the server after installing.
5. Select the appropriate protocol to enable to local and/or remote connections environment. To ensure maximum compatibility, select Using both TCP/IP and named pipes.
6. Click Apply button when done.
7. Click OK button when prompted with the message saying that “Changes to Connection Settings will not take effect until you restart the Database Engine service.”
8. On the “Surface Area Configuration for Services and Connections” page, expand Database Engine, then click Service.
9. Click Stop button to stop the SQL Server service.
10. Wait until the MSSQLSERVER service stops, and then click Start button to restart the MSSQLSERVER service.

Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
3. On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
4. Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
5. Click Apply button.
6. Click on Start button to start the service immediately.
7. Click OK button.

Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
3. On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
4. Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
5. Click Apply button.
6. Click on Start button to start the service immediately.
7. Click OK button.

Finally, if remote computer needs to connect and access SQL Server, an exceptions in Windows Firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista needs to be created. If you’re using third-party firewall system, the exception rules also needed to be created to allow external remote connections to the SQL Server 2005 and SQL Server Browser Service to communicate through the firewall, else connections will be blocked. Consult the firewall manual for more details. Each instance of SQL Server 2005 must have its own exception, together with an exclusion for SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Configuration Manager.
2. In “SQL Server Configuration Manager”, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the “SQL Server Browser Properties” page, click the Advanced tab, locate the instance ID in the property list.
4. Click OK button.

Then create an exception for SQL Server 2005 in Windows Firewall.

1. Click on Start button, the click on Run and type firewall.cpl, and then click OK. For Windows Vista, type firewall.cpl in Start Search box and press Enter key, then click on Allow a program through Windows Firewall link on left tasks pane.
2. In “Windows Firewall”, click the Exceptions tab, and then click Add Program.
3. In the “Add a Program” window, click Browse button.
4. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open button. MSSQL.1 with is a placeholder for the instance ID that is obtained from previous procedure. Note that the path may be different depending on where SQL Server 2005 is installed.
5. Click OK button.
6. Repeat steps 1 through 5 for each instance of SQL Server 2005 that needs an exception.
7. For SQL Server Browser service, locate the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, and click Open button.
8. Click OK button.

Original article

Hi,

Here’s an easy solution how to select random rows from a table.
By using “ORDER BY NEWID()” the list will be scrambled.

Ex:

We have a table Users (containing an Id, FirstName and LastName).
We would like to select 10 random users from this table.
You can select all rows and then take 10 of them randomly (with code) OR you can just execute the following query:

SELECT TOP 10 Id, FirstName, LastName
FROM Users
ORDER BY NEWID();

This code will select 10 random users from the table.

You can also use this approach in more complex queries.

Cheers,
Sem