Frustration with T-SQL 2000
We finally launched a very important web site yesterday.
The web application logs all of it errors in a sql server 2000 database, which is neat as we can review the logs and see what script produced the error, what specific query string was sent to that page, the exception it caused and the end user only sees a nice looking message of "We're sorry, please try again" kind of thing.
I've been trying to isolate a pretty random error, trying to nail down the cause. I reviewed the error log table and need to filter an nvarchar field.
My T-sql sentence looks like this:
SELECT *
FROM dbo.ErrorLog
WHERE
(datestamp = '9/19/2005') AND (error LIKE 'Object reference %')
Reviewing the help, I found there are very few wildcards and zero support for regular expressions.
So, here's my formal complain, how are we going to do decent querying without regular expressions. I know MSSQL 2005 will include the .NET Framework inside and that means it will include the Regex namespace, but, wouldn't it be more efficient to have reg exps on the T-sql level, instead of having the overload of creating objects just for using regexps?
I've been told we should avoid using the Framework on our SQL 2005 sprocs whenever we can accomplish the same task with T-SQL...
Regexps, regexps!
The web application logs all of it errors in a sql server 2000 database, which is neat as we can review the logs and see what script produced the error, what specific query string was sent to that page, the exception it caused and the end user only sees a nice looking message of "We're sorry, please try again" kind of thing.
I've been trying to isolate a pretty random error, trying to nail down the cause. I reviewed the error log table and need to filter an nvarchar field.
My T-sql sentence looks like this:
SELECT *
FROM dbo.ErrorLog
WHERE
(datestamp = '9/19/2005') AND (error LIKE 'Object reference %')
Reviewing the help, I found there are very few wildcards and zero support for regular expressions.
So, here's my formal complain, how are we going to do decent querying without regular expressions. I know MSSQL 2005 will include the .NET Framework inside and that means it will include the Regex namespace, but, wouldn't it be more efficient to have reg exps on the T-sql level, instead of having the overload of creating objects just for using regexps?
I've been told we should avoid using the Framework on our SQL 2005 sprocs whenever we can accomplish the same task with T-SQL...
Regexps, regexps!
posted by Lizet Pena de Sola at
11:03 AM
2 Comments:
It's true you should avoid CLRSQL when writing a sproc in T-SQL will do the job, but MS is giving a very clear message you should got the .NET way whenever doing something in T-SQL is not possible, regexps being usually the example whenener the topic is discussed (you should also check these two articles) Still there are choices, you could either buy a package like SQLRegEx that adds regexp capabilities to SQL Server 2000, or you could use VBScript.RegExp if you have Windows Scripting Host installed. See variations of this approach here and here
Hope it helps
Computer nerdets rule!
Post a Comment
<< Home