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'

T-SQL Split function

Split function to find the Nth word in a string

--select dbo.Split('abc,def', ',',2) --returns def

Create FUNCTION [dbo].[Split] (@fullString varchar(200), @separator char(1),@wordNo tinyint)
RETURNS VARCHAR(200) -- return_data_type.
AS BEGIN
declare @wordPart varchar(200),
              @sepPos int=0,
              @wordCount int = 1

while @wordCount <= @wordNo
begin
      if @fullString = ''
            return null

      set @sepPos = CHARINDEX(@separator, @fullString, @sepPos) if @sepPos = 0set @sepPos = len(@fullString)
      set @wordPart = SUBSTRING(@fullString, 1, @sepPos)set @fullString = REPLACE(@fullString, @wordPart,'')
      set @wordCount = @wordCount +1
end

RETURN replace(@wordPart,',','')

END

Branding SSRS, and make it look like an Intranet

If you want to enhance the user experience and feeling of a group of Reporting Services  reports and don't have SharePoint? You don't have to use the standard report library. Below is a screenshot where I use a tabular navigation looking a lot like a web site using just SSRS 2008 R2 standalone. The tabs are just actions that points to another report using the same header subreport. The user can navigate within the reports without having to go the the report library.

You could also brand use the web service interface to find reports and create your own branded SSRS portal using a SSRS report.

Access domain resources from other domain

Need to connect to a database, olap cube or other do main resource, but your computer is not logged in to the domain. Try the RUNAS command when launching your program.

For Excel:
RUNAS /user:domain\user /netonly "C:\Program\Microsoft Office\Office14\Excel.exe"

Cisco VPN x64 replacer

Cisco VPN client for 64-bit does not yet exist as far as I know. Try the Shrew client, http://www.shrew.net instead. Also, it doesn't cut your internet access.

SSIS, Oracle and x64

Originally posted by 2009 me at another blog.

SSIS, Oracle and x64

I hope I can spare someone the time I just spent to get this working...

The scenario is as follows:
We have SSIS 2008 installed on a windows 2008 x64 server and want to connect to a Oracle database and the standard drivers doesn't work. Something with version 7.3.3 requiring another set of drivers. Anyway, here is what I did. (You don't have to download the complete 1,87 GB package)

I chose to go through ODBC.

1. Download Instant Client Package - Basic (~40Mb) from Oracle (11g) for both 32 and 64-bit.

x64 found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html
and 32-bits found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

2. At the same location, download Instant Client Package - ODBC (1Mb) for both 32 and 64-bits.

3. I started with the 64-bits install. Create a folder somewhere, i.e. C:\Oracle\InstantClient_64 and extract the files from the basic package (64-bit) to that folder. Extract the ODBC package (64-bit) to the same catalog. Open a commandprompt and run odbc_install. Be sure to start the prompt with administrator privileges.

4. Now we need som .ora files, sqlnet.ora and tnsnames.ora was enough for me. I copied them from another server. You will find lots of examples on the internet if you try searching google. I created a folder C:\Oracle\InstantClient_64\network\admin and placed them there.

5. Now we need to create some enviroment variables. Theese are shared between 32 and 64 bits enviroment. First add to the Path variable the directory you just created (C:\Oracle\InstantClient_64). Second create a new variable called TNS_ADMIN pointing to the location of you .ora files (C:\Oracle\instantclient_64\network\admin)

6. Run the ODBC-administrator with administrator privileges and try creating a system-dsn, it should work. The problem is that BIDS is a 32-bit application, so you can't see the 64-bits odbc sources from BIDS.

7. Create a folder somewhere, i.e. C:\Oracle\InstantClient_32 and extract the files from the basic package (32-bits) to that folder. Extract the ODBC package (32-bit) to the same catalog. Now here is the thing: open a commandprompt from C:\Windows\SysWOW64\cmd.exe and run odbc_install.exe. This installs this as a 32-bits driver.

8. Add the directory containing 32-bits package to the path variable (C:\Oracle\InstantClient_32)

9. Run 32-bits ODBC-administrator from: C:\Windows\SysWOW64\odbcad32.exe and verify that it works.

10. When you create the DSN, make sure you call them the same name in both 32 and 64 bits. Also, clear the username and make sure it's a system dsn.

11. Done.

Hope this will help someone, or me when this is long forgotten.

Import large XML file into SQL Server

How to import a large xml file into SQL Server 2008. After some struggling with poor performance using xQuery, I found this way for parsing a large xml file without having a schema file.

The file was approximately 200 MB, containing ~500 000 rows in total and took 25s to import.

The file was more or less in the format of
<dsCost ...>
<ttXXX>
</ttXXX>
<ttYYY>
</ttYYY>
<ttHotelCost>
<hotelCostId>1<hotelCostId>
<destination>Sweden</destination>
</ttHotelCost>
</dsCost>

Declare @xmlfile xml
Declare @docHandle int
SELECT   @xmlfile=InvoicesXML   FROM   (  
SELECT * FROM OPENROWSET(BULK 'C:\Documents and Settings\XPMUser\Skrivbord\cost_export.xml',
    SINGLE_BLOB) AS Invoices ) AS Invoices(InvoicesXML)
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlfile
SELECT  *  INTO Costs
FROM OPENXML(@docHandle, N'/dsCost/ttHotelCost',2)
  WITH (hotelCostId int, costTypeId int ,destination varchar(200))

EXEC sp_xml_removedocument @docHandle