Tuesday 11 November 2008

Print What You Like

Ever print a web page only to find your printout is full of ads, empty space and other junk you don't want? PrintWhatYouLike is a free web page editor that gives you control of how web pages look when printed.

 

How it works ? quite simple

1.       Go to the page you want to print

2.       Copy and paste in the address bar the script below and hit return

 

javascript:(function(){window.open('http://www.printwhatyoulike.com/print?url='+encodeURIComponent(window.location.href));})()

 

Go to http://www.printwhatyoulike.com/ for more info.

 

A great resource on SQL and XML

This is really a great site. It has a lot of tutorials and code that works fine !

A simple search that uses google and really find what’s useful.

It could not be done better.

Really many thanks to Jacob Sebastian.

 

http://www.sqlserverandxml.com/

 

Thursday 16 October 2008

How to cleanup SQL Server Service Broker

If you need to cleanup or reset Service Broker, you may find useful this short script that shuts down all open conversations and cleans up the Service Broker queue.

 

declare @u uniqueidentifier

select top 1 @u=conversation_handle from sys.conversation_endpoints

while @u is not null begin

  END CONVERSATION @u WITH CLEANUP

  set @u = null

  select top 1 @u=conversation_handle from sys.conversation_endpoints

end

 

Hope that helps.

Monday 13 October 2008

Reset SQL Schema during your coding sessions

I think that anyone that writes SQL code has coded like the following:

 

if OBJECT_ID('???') IS NOT NULL DROP PROCEDURE ???

GO

 

CREATE PROCEDURE ???

AS

    ...

GO

 

This is quite common for me during the testing phase, since I find myself comfortable in writing a single script that drops & recreates all objects under development… but it is also quite frustrating to copy & paste always the same code only to change the object name.

So I’ve decided to look a way to avoid repeating the same code again and again… the solution I’ve find is to query INFORMATION_SCHEMA to list object I need and then to build dynamic queries to drop those objects.

 

Here follows a short script that drops a certain number of  tables all prefixed by 'wf_%' string, the script drops the constraints first and then the tables:

 

declare @spname sysname

declare spCur cursor

    read_only for

    select 'ALTER TABLE ' + foreign_table + ' DROP CONSTRAINT [' + fk_name + ']'

      from foreign_keys

      where ((primary_table like 'wf_%') or (foreign_table like 'wf_%'))

    UNION

    select  case when table_type = 'view' then 'DROP VIEW ' + table_schema + '.[' + table_name + ']'

                 else 'DROP TABLE ' + table_schema + '.[' + table_name + ']'

            end

        from INFORMATION_SCHEMA.TABLES

        where (table_name like 'wf_%') and table_catalog = db_name()

open spcur

FETCH NEXT FROM spcur INTO @spname

WHILE (@@fetch_status = 0) BEGIN

    exec (@spname)

       FETCH NEXT FROM spcur INTO @spname

END

CLOSE spcur

DEALLOCATE spcur

GO

 

You can also drop any other type of object by referring the right schema tables, for example this is how to drop stored procedures and functions

 

    select 'DROP ' + ROUTINE_TYPE + ' ' + routine_schema + '.[' + routine_name + ']'

        from information_schema.routines

        where (routine_name like 'wf_%') and routine_catalog = db_name()

 

Just to be complete… here the script that creates the foreign_keys view

 

CREATE  view dbo.foreign_keys as

   select cast(f.name  as varchar(255)) as fk_name

    , r.keycnt

    , cast(ft.name as  varchar(255)) as foreign_table

    , cast(f1.name as varchar(255))  as foreign_col1

    , cast(f2.name as varchar(255))  as foreign_col2

    , cast(pt.name as varchar(255))  as primary_table

    , cast(p1.name as varchar(255))  as primary_col1

    , cast(p2.name as varchar(255))  as primary_col2

    from sysobjects f

    inner join sysobjects ft on  f.parent_obj = ft.id

    inner join sysreferences r on f.id =  r.constid

    inner join sysobjects pt on r.rkeyid = pt.id

    inner  join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid

    inner  join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid

    left join  syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid

    left join  syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid

    where f.type =  'F'

GO

 

Hope that helps

 

 

 

Saturday 4 October 2008

CLR Via C# Second Edition (by Jeffrey Richter)

This is absolutely the best book I've read on programming CLR. It is focused on CLR features and capabilities and C# is only a way to access them, so you will find a lot of real-world tips&tricks that will route you on the right way, so avoiding a lot of common errors. The best chapter, IMO, is the one that explain how multithreading works, what to do and what not to do, and how to avoid CLR bugs... what yoy want more ? buy this book, have a quick read, and keep it on your desk.
If you still are not convinced, go to www.amazon.com and read reviews...