T-sql toggle bit data type

Even after ten years in sql-server world, you can still find something you didn't know..

I have a field in a table called IsTrue of the type bit. I just want to toggle it, if it's true, set to false and if it's false, set to true.

I was on my way to do a case statement when I realized there is at least to better ways of doing this.

the bitwise NOT operator way
update table set IsTrue = ~IsTrue

the bitwise  XOR operator way:
update table set IsTrue = IsTrue^1

found at:
http://johnnycoder.com/blog/2006/10/04/t-sql-tip-flip-bit/

Can't believe I didn't know you could do that in SQL Server... =)

RandomForest.se

Starting up new company: http://RandomForest.se

Combining consulting and innovation within the area of Business Intelligence.

Great fun!

sp_xml_preparedocument open handles

To see all open handles, regardless of session,  when using sp_xml_preparedocument use this command:

select * from sys.dm_exec_xml_handles(0)

http://msdn.microsoft.com/en-us/library/ms408299(v=sql.105).aspx

Slow Oracle inserts from SSIS

Struggled for a while, with terrible performance when inserting a couple of millions of rows. Took about 4,5 hours using ole-db or ado.net with different drivers from both Oracle and others.

Tried the Attunity driver, and was stunned. 3,5 minutes instead of 4,5 hours.

Maps with Ssrs

http://blogs.msdn.com/b/querysimon/archive/2012/06/17/bi-data-on-maps-in-sql-server-2012.aspx

Good post on maps with Reporting Services by Simon Lidberg, Microsoft.

One strong advantage using this technique, is that you can simplify the maps  by removing parts of the world that are of no interest. Picture below is a reporting services 2008 r2 report.

 

T-SQL dynamic xml names

The scenario:
You want to import a xml file into SQL Server, but have one xml-file for developing and testing that is easy to read, and one for production that is using really short node names to minimize the size of the file.

The development file could look something like this:
<Root>
  <Booking>
    <BookingId>1</BookingId>
    <BookingName>My own booking</BookingName>
    <BookingRow>
       <BookingRowId>1</BookingRowId>
    </BookingRow>
  </Booking>
</Root>

And the production file could look something like this:
<r>
<bo>
<boid>1</boid>
<bn>My own booking</bn>
<br>
<br>1</br>
</br>
</bo>
</r>

And you want to be able to use the same SQL-script to import the file, no matter wether is the production or development file. Try this then:

Declare @xmlfile xml
Declare @docHandle int
Declare @xmlPath varchar(50)

-- Step 1 - Set the base path based on something, filename, or have a startnode in the xml-file
if @fileName =  'C:\prodXmlFile.xml'
      @xmlPath = '/r/bo'
else
      @xmlPath = '/Root/Booking'

SELECT @xmlfile=InvoicesXML FROM (
SELECT * FROM OPENROWSET(BULK @FileName,
SINGLE_BLOB) AS Invoices ) AS Invoices(InvoicesXML)

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlfile
--begin try
SELECT * INTO Bookings
   FROM OPENXML(@docHandle, @xmlPath,2)
    WITH (
         bookingId int 'bookingId | boid',
         bookingName int 'bookingName | bn'  )
-- This is the trick, an xpath expression the takes bookingId if it there, and boid if it is there

-- end try begin catch end catch
EXEC sp_xml_removedocument @docHandle

Relational database clean

Sometimes when developing you want to start over with a clean database. But if you have relational integrity, and using the Identity feature in SQL server, it could be a bit tricky.

However, this does it for you:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 1)'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'