Handy script to export binary column into any file

from http://stackoverflow.com/a/4088007/218046

DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
        SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations

OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TIMESTAMP = 'd:\' + 

        replace(replace(replace(replace(

        convert(varchar,getdate(),121),'-',''),':','')

        ,'.',''),' ','') + '.bmp'

        PRINT @TIMESTAMP
        PRINT @SQLIMG

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
    END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

Retrieve unsaved SQL query Scripts

So, the worst has happened and your elaborate work was lost because you didn't press save and then your session was unexpectedly closed? or SSMS crashed and you cannot restore the script?

Well, there is a solution to retrieve unsaved SQL Scripts, if you have executed the script within the last 24 hours:

http://www.sql-programmers.com/retrieve-unsaved-sql-query-scripts.aspx

SELECT execquery.last_execution_time AS [Date Time],

execsql.text AS [Script] 

FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

NOT IN or IN ?

I have been having a hard time believing my eyes when I noticed that these queries give different results:

1. select * from TableA a where a.a_id not in (select b.a_id from TableB b)

2. select * from TableA a where not exists (select b.a_id from TableB b where a.a_id=b.a_id )

3. select a.a_id, b.a_id from TableA a
left join TableB b
on a.a_id = b.a_id

where b.a_id is null


1. returns nothing whereas 2. and 3. return the same result set

Isn't it that in theory 1., 2. and 3. must give exactly same result?

Why is that 1. is giving different result than 2. and 3.?

As it turns out TableB contained some entries with NULLs in a_id column, so when NOT IN was evaluated, the presence of NULL in the list of returned values broke the logic, and statement was incorrectly interpreted

Googling gave me the reference to a similar post: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

oh, devious NOT IN, how art thou misleading...

T-SQL formula for %

I have needed a formula that will get me a number if it is <=100 and 100 for anything greater than 100, because clients required that % cannot be greater than 100%

I wanted a formula without IF or CASE statements.

And, here it is!

DECLARE @rr FLOAT

SET @@rr = 120

@rr - 0.5*(ABS(@rr - 100) + (@rr - 100))