Tim Mackey's Weblog

Data truncated to 255 characters with Excel Jet/ODBC driver: http://support.microsoft.com/kb/189897

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Set to 0 to avoid the truncation problem!

alter table TABLE1 alter column COL1 NVarChar(MAX)

SET @df = 
 (SELECT OBJECT_NAME(cdefault) 
  FROM SYSCOLUMNS 
  WHERE id = OBJECT_ID('dbo.TABLE1') 
   AND name = 'COL1') 
IF @df IS NOT NULL 
 BEGIN 
  EXEC sp_rename @df, 'df_to_drop', 'OBJECT' 
  ALTER TABLE dbo.TABLE1 DROP CONSTRAINT df_to_drop 
 END

CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    DECLARE @ID int

    SELECT @INDEX = 1, @ID = 1
    WHILE @INDEX !=0

        BEGIN
         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
         ELSE
          SELECT @SLICE = @STRING
         -- PUT THE ITEM INTO THE RESULTS SET
         INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
         SELECT @ID = @ID + 1
         -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
         -- BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
        END
    RETURN 

select Items from dbo.Split(@List, ',')

SELECT TOP 10 [t1].[Name], [t1].[Address], [t1].[Tel1], [t1].[Tel2], [t1].[Email], [t1].[DateCommenced], [t1].[Comments], [t1].[Active], [t1].[Fax]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]) AS [ROW_NUMBER], [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]
    FROM [dbo].[Table1] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [50]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

it's not immediately obvious why they use the subquery like this, but i'm sure they have been very thorough in optimising LINQ. 

select * from table order by @OrderBy

select * from table order by case 
	when @OrderBy = 'Column1' then Column1
	when @OrderBy = 'Column2' then Column2
	end

select * from table order by 
	case when @OrderBy = 'Column1' then Column1 end,
	case when @OrderBy = 'Column2' then Column2 end

ALTER TRIGGER [dbo].[Table1CascadeDeleteTable2]
   ON  [dbo].[Table1]
   FOR DELETE
AS 
BEGIN
	declare @Ref int;
	SELECT @Ref = Ref FROM Deleted;
	delete from Table2 where Ref = @Ref;
END

CONVERT(Numeric(10,2), (Count(ID) * 100. / @Total)) as Percentage

the above will display "23.54" (for example), including rounding, as a number.

Procedure dbo.Select_Totals_For_Year
(
   @Name NvarChar(200),
   @Year int
)
AS
BEGIN
select Name, Count(ID) AS [Total]
FROM Table1
WHERE  Name = @Name AND
                 Year = (CASE WHEN @Year is null THEN Year ELSE @Year END)
group by Name
order by Name
END

Right(FullName, PatIndex('% %', Reverse(FullName))) + ', ' + Left(FullName, Len(FullName) - PatIndex('% %', Reverse(FullName)))    

CONVERT(char(7), GetDate(), 121)

this gives a date like "2007-03".  it works because SQL formats the dates like YYYY-MM-DD etc., so you can just take the first X characters to take as much of the date as you want.

another handy one i use is to get DD/MM/YYYY format:

Convert(NVarChar(10), GetDate(), 103)

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Net" %>

<script RunAt="server">
	
	/*	Server Monitoring Script:
	*		- test SQL databases by running an sql string against an SQL connection string
	*		- test Access databases by running an sql string against a JET connection string
	*		- test web sites by Regex matching a search string against the contents of a HttpWebRequest
	*/

	enum TestType {Sql_Server, Ms_Access, Http_Request }	// different types of supported requests
	
	/// <summary>
	/// Container class to represent a 'test' object for a resource on the server.
	/// </summary>
	class TestObject
	{
		public TestType Type;			// e.g. Sql_Server.
		public string TestString;		// e.g. connection string for a database. or URI for http request.
		public string TestParam;		// e.g. sql string for a database. or search string for a http request.
		
		public TestObject(TestType type, string testString, string testParam)
		{
			this.Type = type;  
			this.TestString = testString;
			this.TestParam = testParam;
		}
	}
	
	void Page_Load(object sender, EventArgs e)
	{
		List<TestObject> tests = new List<TestObject>();

		tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB1;Integrated Security=True", "select top 10 * from Table1"));
		tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB2;Integrated Security=True", "select top 10 * from Table1"));
		tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB3;Integrated Security=True", "select top 10 * from Table1"));
		tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB4;Integrated Security=True", "select top 10 * from Table1"));

		tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB5.mdb;Persist Security Info=True", "select top 10 * from Table1"));
		tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB6.mdb;Persist Security Info=True", "select top 10 * from Table1"));

		tests.Add(new TestObject(TestType.Http_Request, "http://mysite1.ie/", "Site 1"));
		tests.Add(new TestObject(TestType.Http_Request, "http://mysite2.ie/", "Site 2"));
		tests.Add(new TestObject(TestType.Http_Request, "https://mysite3.ie/", "Site 3"));
		tests.Add(new TestObject(TestType.Http_Request, "https://mysite4.ie/", "Site 4"));
		tests.Add(new TestObject(TestType.Http_Request, "https://mysite5.ie/", "Site 5"));
		
		int numCompleted = 0;
		int numFailed = 0;
		
		// write the HTML header. (a result is flushed to the client after each test finishes.)
		Flush(@"			
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>
<html>
<head>
<title>Server Test</title>
<meta name='ROBOTS' content='NOINDEX,NOFOLLOW'>
<link rel='stylesheet' type='text/css' href='ServerTestStyles.css' />
</head>
<body>");

		foreach(TestObject test in tests)
		{				
			try
			{
				switch(test.Type)
				{					
					case TestType.Sql_Server:
						runQuerySql(test.TestParam, test.TestString);
						break;
					case TestType.Ms_Access:
						runQueryOleDb(test.TestParam, test.TestString);
						break;
					case TestType.Http_Request:
						string pageContents = new WebClient().DownloadString(test.TestString);
						if(!Regex.IsMatch(pageContents, test.TestParam, RegexOptions.IgnoreCase))
							throw new Exception("Search string not found: " + test.TestParam);
						break;
					default:
						throw new Exception("Test type not handled " + test.Type);
				}
				Flush(String.Format("<span class='pass'>Pass</span> &nbsp; <span class='type'>{0}</span> &nbsp; {1} <hr />", test.Type, test.TestString));
				numCompleted++;
			}
			catch(Exception ex)
			{
				Flush(String.Format("<span class='fail'>Fail</span> &nbsp; {1} <span class='type'>{0}</span><BR><span class='error'>{2}</span><hr />", test.Type, test.TestString, ex.Message));
				numFailed++;
			}
		}
		if(numFailed > 0)
			Flush(String.Format("<h1>{0} errors occured</h1>", numFailed));
		else
			Flush(String.Format("<h1>All Good!</h1>", numFailed));	// if you use this page with an automated monitoring service, look for "All Good" in the page contents. otherwise an error occured
		Flush("</body></html>");
	}

	/// <summary>
	/// Method to run an sql string against an sql database
	/// </summary>
	public static DataSet runQuerySql(string sql, string connString)
	{
		SqlConnection conn = new SqlConnection(connString);
		DataSet ds = new DataSet();
		SqlDataAdapter dba = new SqlDataAdapter();
		SqlCommand cmd = new SqlCommand(sql, conn);

		try
		{
			dba.SelectCommand = cmd;
			dba.Fill(ds, "Table");
			return (ds);
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			cmd.Connection.Close();
			conn.Close();
		}
	}

	/// <summary>
	/// Method to run an sql string against an Access database
	/// </summary>
	public static DataSet runQueryOleDb(string sql, string connString)
	{
		OleDbConnection conn = new OleDbConnection(connString);
		DataSet ds = new DataSet();
		OleDbDataAdapter dba = new OleDbDataAdapter();
		OleDbCommand cmd = new OleDbCommand(sql, conn);

		try
		{
			dba.SelectCommand = cmd;
			dba.Fill(ds, "Table");
			return (ds);
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			cmd.Connection.Close();
			conn.Close();
		}
	}
	
	/// <summary>
	/// Flush output to the browser (useful to indicate which tests are causing any delay)
	/// </summary>
	/// <param name="output"></param>
	private void Flush(string output)
	{
		Response.Write(output);
		Response.Flush();
	}
	
</script>

ServerTestStyles.css:  (just to make the output more legible)

body
{
	font-size: 90%;
	font-family: Calibri, Helvetica, Sans-Serif;
	padding: .5em;
}

hr
{
	color: #87ceeb;
	background-color: #87ceeb;
	margin: .3em 0 .3em 0;
	padding: 0;
	height: 1px;
}

.pass
{
	color: Blue;
	font-weight: bold;
}
.fail
{
	color: Red;
	font-weight: bold;
}
.type
{
	color: purple;
	font-weight: bold;
}
.error
{
	color: Red;
	font-size: small;
}

I have configured the test in InternetVista to search for "All Good" in the url for the test page.  If this isn't present, i'll get an SMS/email alert and i can go and see what exactly is wrong.  It should be fairly easy to add other test types if you have different resources you need to check on.
Enjoy.

    update sometable set whatever = @whatever
    exec SP_History 'joe bloggs', 'order taken', '€500'

if @@ERROR <> 0 
    RETURN @@ERROR    -- bail out

CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS SmallDateTime)

• http://www.sqldbatips.com/showarticle.asp?ID=27

XmlSerializer xs = new XmlSerializer(typeof(PageCollection));
xs.Serialize(fs, pages); // 'fs' is a FileStream to my xml file, and 'pages' is a collection class of objects

XmlSerializer xs = new XmlSerializer(typeof(PageCollection));
pages = xs.Deserialize(fs) as PageCollection;

xmlns=''> was not expected

[XmlRootAttribute("CmsPages", Namespace = "http://www.whatever.com/Cms", IsNullable = false)]

I had a transaction comprised of about 5 commands, and in the middle of it, i needed to do a SELECT on a table to know what values to insert for one of the commands.  I encountered a timeout just after i called Fill on the DataAdapter.  it took me a while to figure out that it was because the transaction had already taken out a lock on the table i was selecting from.  makes sense now, but i spent ages on it!  just posting it here in case anyone else runs into the same problem.

This might sound really obvious, but i couldn't find a better way.  Normally i would use TOP in the SQL query to limit the number of records i want to retrieve, but in my case, this value is parameterised and Access won't allow me to parameterise that value.  I tried using a DataView but TOP isn't one of it's supported functions.  So i just loop through the dataset and keep removing rows until the right number of records is reached.

int maxItems = 5;
while(ds.Tables[0].Rows.Count > MaxItems)
    ds.Tables[0].Rows.RemoveAt(MaxItems);