Alex Pinsker

Tuesday, July 22, 2008

SQL Tips: Using MERGE SQL statement in SQL 2008

... And still one more from the same generous source ...

SQL Server 2008's new MERGE statement allows you to insert, update, or delete data based on certain join conditions in the same statement.

MERGE SalesArchive AS SA
USING (
    SELECT
        CustomerID,
        LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
        TotalSalesAmount = SUM(SaleAmount),
        TotalSalesCount = COUNT(*)
    FROM SalesFeed
    GROUP BY CustomerID
) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)
ON (SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate )

WHEN NOT MATCHED THEN
INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)
    VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())
WHEN MATCHED THEN
UPDATE
    SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,
            SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,
            SA.UpdatedDate = GETDATE();

Also, there is a new ability to pass a table variable to the stored procedures:

DECLARE @MyTable TABLE (Col1 INT, Col2 Varchar(100))
EXEC MySP @Par1 = @MyTable OUTPUT

Monday, July 14, 2008

SQL Tips: Using Regular Expressions in SQL Query

One more; courtesy of our greate DBA - K. Fridman:

DECLARE @RegExp VARCHAR(100)
SET @RegExp = '%[^a-zA-Z0-9._-]%'
SELECT *
FROM Clients
WHERE PATINDEX(@RegExp, UserName) > 0

SQL Tips: Search String In All SP's

This is nice one; courtesy of our great DBA - Kostya Fridman:

SELECT name, OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Search String%'

Monday, June 16, 2008

How to configure Excel to work with IIS/ASP.NET

Detailed instruction on how to configure Excel to work with IIS/ASP.NET:

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

Sunday, June 15, 2008

touch command for DOS/command line

Following command would simulate UNIX touch command in DOS:
copy /b test.txt +,,
(command was changed following to the comments).

ArgumentOutOfRangeException at HttpCachePolicy.UtcSetLastModified if assembly file with script control has future date relatively to the machine date

It seems that I discovered an annoying bug in the AJAX engine:
if you have a JavaScript file of script control, which is loaded through the WebResource.axd and the DLL module that embeds this JavaScript file has a file creation date, which is in the future relatively to the current machine date – you would get an unhandled ArgumentOutOfRangeException exception, raising from System.Web.HttpApplication.ExecuteStep:


System.Web.HttpCachePolicy.UtcSetLastModified(DateTime utcDate)
System.Web.HttpCachePolicy.SetLastModified(DateTime date)
System.Web.Handlers.AssemblyResourceLoader.System.Web.IHttpHandler.ProcessRequest(HttpContext context)
System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

This would cause JavaScript error "Type NNN is undefined or null" in your browser.
The simple resolution for this problem is to "touch" the affected DLL to update it's date to current machine date time:
copy /y nul tt.txt

Tuesday, June 10, 2008

Enable flash .flv video streaming in with IIS / ASP.NET

Enable flash .flv video streaming in with IIS / ASP.NET:
1) Adding .FLV MIME Type in IIS
2) FLV Flash video streaming with ASP.NET 2.0, IIS and HTTP handler