How to schedule a program using Windows Task Scheduler

If a program is required to be run on a recurring schedule, a simple way of achieving this is to provide an executable than runs the command from its Main method. The task schedule can be managed using the Windows Task Scheduler.

To create a scheduled task on Windows Vista:

1. Open the Task Scheduler (Control Panel > Administrative Tools > Task Scheduler).
2. Click Create Task.
3. In the General tab, set the name of the task, and optionally provide a description. Click Change User or Group and select the user account under which the program should run. (This account should have the appropriate privileges.) Select Run Whether User Is Logged On or Not.
4. On the Triggers tab, click New. In the Settings section set the schedule. Optionally select Stop Task if It Runs Longer than. Click OK.
5. On the Actions tab, click New. Click Browse and select the .exe in the location selected in Setup.
6. Optionally, if the program requires network access, on the Conditions tab, select Start Only if the Following Network Connection Is Available and select the network connection to the required network connection. This will prevent the program from running in the event of the network connection being unavailable.
7. On the Settings tab, select Run Task as Soon as Possible After a Scheduled Start Is Missed and If the Task Fails Restart Every options.
8. Click OK.

How to use a CTE to select the first n rows of a referenced table for every row

The Common Table Expression (CTE) feature introduced in SQL Server 2005 provides some very useful querying extensions (albeit these are obviously not ANSI-92 compliant). A CTE takes the following form:

WITH cte AS
(
SELECT col1
FROM dbo.tbl1
WHERE...
)
SELECT col1
FROM dbo.cte
WHERE...
GO

One useful adjunct to the CTE is the ROW_NUMBER function in combination with the PARTITION BY clause. This allows you to write a query returning one or more rows of a subquery without resorting to complicated correlated subqueries and performance killing TOP n predicates. For example, say you want to pull for every row in a parent table the first row in a child table. You can write this query using a CTE as follows:

WITH firstChild AS
(
SELECT ParentID, ChildID,
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY ChildID ASC) AS rn
FROM dbo.tblChildren c
)
SELECT p.ParentID, fc.ChildID
FROM dbo.tblParents p
INNER JOIN firstChild fc ON p.ParentID = fc.ParentID AND fc.rn = 1;
GO

The performance of this query is blindingly fast because ROW_NUMBER is being performed in memory and has no disk IO cost. You can also use this technique to generate high performance crosstab queries.

ADO/ODBC connection string for SQL Server 2008

If upgrading SQL Server from 2005 to 2008, one thing to note is that the provider name has changed for SQL Server 2008. The provider portion of the string in both ADO and ODBC connection strings should be either

Provider=SQLNCLI10

or

Provider=SQLNCLI10.1

depending on which version of SQL Native Client is installed.

http://blogs.msdn.com/b/mattm/archive/2007/06/07/sql-native-client-10-provider-name-change.aspx

http://connectionstringexamples.com/staticpages/index.php?page=20081229061112484

.NET applications will be unaffected as you do not need to provide the provider name in the connection string when using the .NET provider.

SQL Server error not found

Operating system errors are often returned by SQL Server with just a number and the description “(error not found)”. The most common operating system errors that can be returned by SQL Server are:

3	The system cannot find the path specified
5	Access denied

Searching computed column definitions

Computed columns are not ANSI-92 compliant (i.e. they are a proprietary extension) and are therefore not included in the INFORMATION_SCHEMA views. To search for a string in a computed column in SQL Server 2005 use the syscolumns and syscomments system tables. For example:

SELECT object_name(cl.id) AS [Object Name], name AS [Column Name],
text AS [Definition]
FROM syscolumns cl
INNER JOIN syscomments cm
ON cl.id = cm.id
AND cm.number = cl.colid
WHERE iscomputed = 1
AND text LIKE '%search_string%'


Error parsing T-SQL

If XACT_ABORT is on for the current connection, parsing a T-SQL query will generate the following error:

.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The SQL will still execute. To avoid this error being reported, simply set XACT_ABORT off.

SQL Server 2005 connection problems

If queries on a SQL Server 2005 instance suddenly run very slowly and/or time out, check that the TCP/IP protocol for the instance is enabled and Named Pipes is disabled. (In SQL Server Configuration Manager, select SQL Server 2005 Network Configuration.) The server instance will need to be restarted for the changes to take effect.