Overview (FAQ6) - SQL Topics

Continuing


The following article describes how to configure a Microsoft SQL Server instance to be remotely accessible over TCP/IP.  This configuration change may be required when a Sophos product is required to access a remote SQL instance as part of a distributed installation.

Known to apply to the following Sophos product(s) and version(s)

Not product specific

What To Do

  1. On the SQL Server, open 'SQL Server Configuration Manager'.  This can typically be found linked from the 'Start' menu.  For example: Start | All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.

  2. Expand 'SQL Server Network Configuration' and highlight the 'Protocols for [InstanceName]' option.

  3. In the right-hand window, if 'TCP/IP' currently has the 'status' of 'Disabled', right click on 'TCP/IP' and select 'Enable'. 

    Note: 
    You will be requested to restart the SQL Server service to complete the configuration change. 

  4. To restart the service, you can use the same Microsoft Management Console (MMC) window.  To do so, highlight the 'SQL Server Services' option at the top of the tree. In the right-hand window, you can then right click on the 'SQL Server [Instance]' entry and choose 'Restart'.
The SQL instance has now been configured to accept TCP/IP connections from remote computers.
To enable remote connections on the instance of SQL Server 2005 and to turn on the SQL Server Browser service, use the SQL Server 2005 Surface Area Configuration tool. The Surface Area Configuration tool is installed when you install SQL Server 2005.
Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

Enable the SQL Server Browser service
If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you have to enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You only have to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the SQL Server Browser service, follow these steps.

Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.
  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click 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, click Automatic forStartup type, and then click Apply

    Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
  4. Click Start, and then click OK.
Note When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:
  • SQL Server Browser Service
  • Connecting to the SQL Server Database Engine
  • Client Network Configuration

SQL: If Exists Update Else Insert

This is a pretty common situation that comes up when performing database operations.  A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach.  This will do a table/index scan for both the SELECT statement and the UPDATE statement.  In most standard approaches, the following statement will likely provide better performance.  It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.  Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take.  Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way. 


 


INSERT INTO table (name, rowid, modifiedDate) VALUES ('Vacation Name', NEWID(), GETDATE() )


sp_helpdb 'master'
select @@VERSION
select * from sys.databases where name = 'master'
select @@SERVERNAME
select * from sys.configurations



view the contents of a system function -> sp_helptext 'sys.fn_isrolemember'



select * from [sys].[all_parameters]
select * from [sys].[all_columns]



Creating a timestamp field in an SQL table, to be auto-created when a record is INSERTED (download .sql)

USE [NVZN11]
GO

ALTER TABLE [dbo].[HTTP_LOG] DROP CONSTRAINT [HTTP_LOG.Timestamp]
GO

/****** Object: Table [dbo].[HTTP_LOG] Script Date: 24/04/2013 8:49:44 AM ******/
DROP TABLE [dbo].[HTTP_LOG]
GO

/****** Object: Table [dbo].[HTTP_LOG] Script Date: 24/04/2013 8:49:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[HTTP_LOG](
[url] [nvarchar](max) NOT NULL,
[headers] [nvarchar](max) NULL,
[timestamp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[HTTP_LOG] ADD CONSTRAINT [HTTP_LOG.Timestamp] DEFAULT (getdate()) FOR [timestamp]
GO


Testing SQL connectivity from command line

osql -Usa -Ppassword -Slaptop-xps

1> select getdate();
2> go

-----------------------
2013-06-21 13:19:50.977

(1 row affected)

 

Enable Dedicated Administrator Connection in SQL Server 2008 Using TSQL


1> Use master
2> GO
1> /* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
2> sp_configure 'remote admin connections', 1
3> GO
Configuration option 'remote admin connections' changed from 0 to 1. Run the RECONFIGURE statement to install.
1> RECONFIGURE
2> GO
1>


SQL Server GETDATE() Function

SQL Server Date Functions
Definition and Usage
The GETDATE() function returns the current date and time from the SQL Server.

Syntax

GETDATE()

Example

The following SELECT statement:

SELECT GETDATE() AS CurrentDateTime
will result in something like this:

CurrentDateTime
2008-11-11 12:45:34.243
Note: The time part above goes all the way to milliseconds.

Example

The following SQL creates an "Orders" table with a datetime column (OrderDate):

CREATE TABLE Orders
(
OrderId int NOT NULL PRIMARY KEY,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT GETDATE()
)
Notice that the OrderDate column specifies GETDATE() as the default value. As a result, when you insert a row into the table, the current date and time are automatically inserted into the column.

Now we want to insert a record into the "Orders" table:

INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')
The "Orders" table will now look something like this:

OrderId ProductName OrderDate
1 Jarlsberg Cheese 2008-11-11 13:23:44.657