Microsoft SQL Reporting - Repeating headers on new pages

This is something I've battled for years.

I finally figured it out this week.

You need to select your tablix, go to the right of column groups, select the down arrow, check "Advanced Mode", select the static member from "Row Groups", if you have more than one, look for the one that selects the first cell in your header row ("WO #" in my case), make the properties window (hotkey F4) shows "KeepWithGroup" as "After".  That means you have the proper object selected.  Now, change "RepeatOnNewPage" to "True" and do the same for "FixedData". 

That should do it.  Seems simple once you know, but man is it had to find out.  I hope this helps.

IIS Enable 32 bit applications "HTTP Error 500.19 - Internal Server Error"

http://forums.iis.net/t/1149768.aspx

"You cannot get rid of it for that site - compression schemes are registered globally - to get rid of the scheme altogether, run below
%windir%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpCompression /-[name='xpress']"

Restore Database and "Single User" mode

I recently restored a database and it gave an error after restore about indexes and rowguid columns, but the database was there, so I'm going with it.

But it showed up as "databasename (single user)" and I couldn't use it because "there is already a user logged into this database".

so I had to change it, this link was very helpful:
http://remidian.com/mssql/remove-sql-server-database-from-single-user-mode.html

SELECT spid
FROM sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
WHERE d.name = 'DatabaseName';

KILL <spid returned above>

EXEC sp_dboption 'DatabaseName', 'single user', 'FALSE';

Sending Email With SQLServer 2008

It is beneficial to use the new "Database Mail" section in SQL Server Management Studio, you can setup profiles etc.

--Start the mail watcher, looks for mail then sends it when it has time.
EXEC msdb.dbo.sysmail_start_sp;

--Queue the Message.
EXEC   msdb.dbo.sp_send_dbmail
     @profile_name = 'ProfileName'
     ,@recipients       = 'Address@EmailAddress.com'
     ,@subject          = 'Subject'
     ,@body              = 'Body'
     ,@body_format        = 'HTML' --Or Text
     --,@query                = @UpdateMessageQuery
     --@file_attachments = @file_attachments       

Processing QBXML In SQL Server

I've been working with QuickBooks again, and this time I need to parse some QBXML in the database so I can do some data syncronization on a schedule.

SQL Server 2005+ has some nice XML tools that make things a lot easier.  I was having trouble getting things to show up, but eventually found some pretty basic examples.

I'm parsing an Invoice Query response.

Here is what I came up with:

-- variable to hold document reference
DECLARE @docHandle int

-- variable to hold actual XML
DECLARE @xmlDocument nvarchar(max) -- or xml type

-- Fill XML variable
SELECT TOP 1 @xmlDocument = QBXML FROM QBBridge ORDER BY QBBridgeID DESC

-- Use built in Procedure to process the XML into Data
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

    DECLARE @ParentID int
    DECLARE qbXMLCursor CURSOR FOR
        -- Get All InvoiceRet Objects.
        SELECT ID FROM OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') WHERE NodeType = 1 AND LocalName = 'InvoiceRet'

    -- cycle through InvoiceRets and compile Sub Data.
    OPEN qbXMLCursor
    FETCH NEXT FROM qbXMLCursor INTO @ParentID

   
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Use Common Table Expression to compile values to names of parent nodes
        -- I named my table columns <ParentName>.<ParentName>.<Name> so I'm sitting pretty for an insert from this.
        WITH InvoiceValues ([LocalName], [Value], [Level], [ID]) AS
        (
            SELECT    LocalName,
                    [text],
                    1 AS [Level],
                    [ID]
            FROM    OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs')
            WHERE ParentID = @ParentID
            UNION ALL
            SELECT    CASE WHEN SubValues.LocalName = '#text' THEN
                        InvoiceValues.LocalName
                    ELSE
                     InvoiceValues.LocalName + '.' + SubValues.LocalName
                     END As LocalName,
                    [text],
                    InvoiceValues.[Level] + 1 AS [Level],
                    SubValues.[ID]
            FROM    InvoiceValues
                    INNER JOIN OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') As SubValues
                    ON InvoiceValues.ID = SubValues.ParentID
        )
        SELECT    [LocalName], [Value]
        FROM    InvoiceValues
        WHERE Value IS NOT NULL
           
        FETCH NEXT FROM qbXMLCursor INTO @ParentID
    END
    CLOSE qbXMLCursor
    DEALLOCATE qbXMLCursor

EXEC sp_xml_removedocument @docHandle

Tricks & Tips

New shortcut I found in Firefox & IE8.  When in the browser search box press Alt-Enter to automatically open a new tab for your search.

Schedule SyncToy

Found a good article on automating SyncToy Jobs.

http://www.mydigitallife.info/2008/01/01/schedule-synctoy-to-run-and-automatically-and-repetitively/

Decided to use the task scheduler to start up some other software too, should prove to be useful.

RE: Quickbooks QBFC 80040154 "Co-Create error"?

Previously I had problems getting a desktop application to integrate with QuickBooks on a 64 bit Windows Server 2008 operating system running IIS 7.0.  Recently I've moved my development Website to the same 64 bit machine, and I was experience same problems with getting my .Net website to be able to use the QuickBooks QBFC8 Library.  Thanks to some experience from before I knew I should see if this was a problem going from 32 Bit to 64 Bit.  It was.  I had to "Enable 32-bit Applications" in the Advanced Settings on the Application Pool.

Windows 7 64 bit and SQL Server cliconfg

I recently did a wipe and Windows 7 install.  Installed all my dev tools, then tried connecting to some SQL Servers, local and remote, local worked fine.  I added some remote servers to my cliconfg by running (windows run dialog) 'cliconfg' it brought up the familiar server alias configuration tool, so I added my servers.  Then I tried logging into some of them, no luck.  Totally confused.

Turns out there are two cliconfg dialogs, a 32 bit and a 64 bit one, I had been getting to the 64 bit one, but my sql server management studio had been using the values from the 32 bit one.  Found the 32 bit one in "C:\Windows\SysWOW64\cliconfg.exe" started using it, and everything worked, still pretty annoying, I wish they would at least warn you, "oh hey, by the way, there are actually two cliconfgs"

Kind of silly.

*Edited, I had my cliconfg locations mixed up, I had assumed the one found in the folder referencing the number 64 would contain the 64 bit version.  This is wrong, though I remain confused because my brain refuses to believe the System32 folder on 64 bit windows would contain the x64 exe and the SysWOW64 directory would contain the x86 exe.  Thanks to Niels Grove-Rasmussen for the catch.

SQL Server FTS Thesaurus tsENU.xml Names

Here is what I am using for my FTS Thesaurus tsENU.xml.  It is mostly names, but I put in some numbers too (though these are useless if they are in your noise file as is default), and the html encoded ampersand '&amp;' with 'and' but I think that is useless too.

<XML ID="Microsoft Search Thesaurus">

    <!--  Commented out

    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            _{Internet Explorer}
            _IE
            _IE5
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            _{Windows 2000}
        </replacement>
        <expansion>
            _run
            _jog
        </expansion>
    </thesaurus>
-->
<!--Original Name Source http://www.tngenweb.org/franklin/frannick.htm-->
    <thesaurus xmlns="x-schema:tsSchema.xml">
        <diacritics_sensitive>0</diacritics_sensitive>
        <!--Special Text-->
        <expansion>
            _&amp;
            _and
        </expansion>
        <expansion>
            ₀
            _Zero
        </expansion>
        <expansion>
            ₁
            _One
        </expansion>
        <expansion>
            ₂
            _Two
        </expansion>
        <expansion>
            ₃
            _Three
        </expansion>
        <expansion>
            ₄
            _Four
        </expansion>
        <expansion>
            ₅
            _Five
        </expansion>
        <expansion>
            ₆
            _Six
        </expansion>
        <expansion>
            ₇
            _Seven
        </expansion>
        <expansion>
            ₈
            _Eight
        </expansion>
        <expansion>
            ₉
            _Nine
        </expansion>
        <expansion>
            ₁₀
            _Ten
        </expansion>
        <!--End Special Text-->
        <!--Names-->
        <expansion>
            _Aaron
            _Ron
        </expansion>
        <expansion>
            _Abigail
            _Abie
            _Aby
            _Abbie
            _abby
            _Gail
            _Nabby
        </expansion>
        <expansion>
            _Abraham
            _Abe
            _Abram
            _Bram
        </expansion>
        <expansion>
            _Adelaide
            _Ada
            _Addie
            _Adela
            _Adeline
            _Della
            _Heide
            _Addy
        </expansion>
        <expansion>
            _Adolphus
            _Ado
            _Adolph
            _Dolph
        </expansion>
        <expansion>
            _Adrienne
            _Adrian
        </expansion>
        <expansion>
            _Agatha
            _Ag
            _Aggie
            _Agnes
            _Aggy
        </expansion>
        <expansion>
            _Alan
            _Alanson
        </expansion>
        <expansion>
            _Albert
            _Al
            _Bert
            _Bird
            _Alberta
            _Abertina
            _Albertine
            _Bertie
            _Adelbert
            _Del
            _Delbert
            _Bertram
            _Alfred
            _Alf
            _Alfie
            _Alfreda
            _Alfy
            _Frieda           
        </expansion>
        <expansion>
            _Alexander
            _Alec
            _Alex
            _Lex
            _Alexis
            _Alexandra
            _Lexa
            _Xan
            _Alexandria
            _Lexi
            _Alyx
            _Alix
        </expansion>
        <expansion>
            _Alice
            _Alcy
            _Alicia
        </expansion>
        <expansion>
            _Alison
            _Ali
            _Ally
            _Allie
            _Aly
        </expansion>
        <expansion>
            _Amanda
            _Manda
            _Mandy
            _Mandie
            _Mandi
        </expansion>
        <expansion>
            _Andrew
            _Andy
            _Ansey
            _Drew
            _Andrea
            _Andi
        </expansion>
        <expansion>
            _Angela
            _Angel
            _Angelica
            _Angelina
            _Angeline
            _Angie
        </expansion>
        <expansion>
            _Ann
            _Anna
            _Anne
            _Annie
            _Annette
        </expansion>
        <expansion>
            _Anthony
            _Tony
            _Antoinette
            _Antonia
            _Toni
            _Tonie
        </expansion>
        <expansion>
            _Barbara
            _Babs
            _Barb
            _Barbie
            _Barby
        </expansion>
        <expansion>
            _Barnard
            _Bernard
            _Barney
            _Bernie
        </expansion>
        <expansion>
            _Bartholomew
            _Bart
            _Bartel
            _Barth
            _Bat
            _Barticus
        </expansion>
        <expansion>
            _Benjamin
            _Ben
            _Benjie
            _Bennie
            _Benny
        </expansion>
        <expansion>
            _Beverly
            _Bev
        </expansion>
        <expansion>
            _Boe
            _Bo
        </expansion>
        <expansion>
            _Brian
            _Bryan
            _Bryant
        </expansion>
        <expansion>
            _Brittany
            _Britt
            _Brittney
        </expansion>
        <expansion>
            _Cameron
            _Cam
        </expansion>
        <expansion>
            _Candace
            _Candy
        </expansion>
        <expansion>
            _Carl
            _Karl
        </expansion>
        <expansion>
            _Charles
            _Charlie
            _Charly
            _Chaz
            _Chuck
        </expansion>
        <expansion>
            _Catherine
            _Cathy
            _Katharine
            _Katie
            _Kathleen
            _Kittie
            _Cathleen
            _Kat
            _Kate
            _Katy
            _Kitsey
            _Kitty
        </expansion>
        <expansion>
            _Cecilia
            _Celia
            _Cecil
        </expansion>
        <expansion>
            _Celinda
            _Linda
            _Lindy
            _Lynn
        </expansion>
        <expansion>
            _Christine
            _Christiana
            _Crissy
            _Christy
            _Tina
            _Kristine
        </expansion>
        <expansion>
            _Clara
            _Claire
            _Clarice
            _Clarissa
        </expansion>
        <expansion>
            _Cory
            _Corey
            _Cori
        </expansion>
        <expansion>
            _Crystal
            _Chrisetc
            _Christal
            _Chrystal
            _Cristal
        </expansion>
        <expansion>
            _Cynthia
            _Cindy
        </expansion>
        <expansion>
            _Daniel
            _Dan
            _Danny
        </expansion>
        <expansion>
            _Danielle
            _Dani
        </expansion>
        <expansion>
            _David
            _Davy
            _Davey
            _Dave
        </expansion>
        <expansion>
            _Deborah
            _Deb
            _Debi
            _Debbie
            _Debby
        </expansion>
        <expansion>
            _Denise
            _Dennis
        </expansion>
        <expansion>
            _Diane
            _Diana
            _Di
        </expansion>
        <expansion>
            _Dorothy
            _Dee
            _Dolly
            _Dorothea
            _Dot
            _Dotha
            _Dotty
        </expansion>
        <expansion>
            _Douglas
            _Doug
        </expansion>
        <expansion>
            _Edward
            _Ed
            _Eddie
            _Eddy
        </expansion>
        <expansion>
            _Elias
            _Eli
            _Elijah
            _Elisha
            _Ellis
        </expansion>
        <expansion>
            _Elisa
            _Lisa
        </expansion>
        <expansion>
            _Eliza
            _Elizabeth
            _Beth
            _Liz
            _Liza
            _Lizabeth
            _Lizzie
        </expansion>
        <expansion>
            _Emanuel
            _Manny
            _Manuel
        </expansion>
        <expansion>
            _Emeline
            _Emmer
            _Emily
            _Emmy
            _Emilie
        </expansion>
        <expansion>
            _Ernest
            _Earnest
            _Ernie
            _Ernestine
        </expansion>
        <expansion>
            _Estella
            _Stella
        </expansion>
        <expansion>
            _Eugene
            _Gene
            _Eugenia
            _Genie
        </expansion>
        <expansion>
            _Evelyn
            _Ev
            _Eve
            _Evelina
        </expansion>
        <expansion>
            _Ezekiel
            _Ez
        </expansion>
        <expansion>
            _Frances
            _Fan
            _Fanny
            _Fran
            _Franie
            _France
            _Franz
            _Francis
        </expansion>
        <expansion>
            _Franklin
            _Frank
            _Frankie
            _Franky
        </expansion>
        <expansion>
            _Frederick
            _Fred
            _Fredric
            _Freddie
        </expansion>
        <expansion>
            _Gabriel
            _Gabe
            _Gabby
            _Garbrielle
            _Gabbie
        </expansion>
        <expansion>
            _Geoffrey
            _Jeff
            _Geoff
            _Jeffrey
        </expansion>
        <expansion>
            _George
            _Jorge
            _Georgia
            _Georgiana
        </expansion>
        <expansion>
            _Gerald
            _Jerry
            _Geraldine
            _Gerry
            _Gerri
            _Gerrie
        </expansion>
        <expansion>
            _Gertrude
            _Gertie
            _Trudy
        </expansion>
        <expansion>
            _Gilbert
            _Gil
            _Gill
        </expansion>
        <expansion>
            _Gloria
            _Glory
        </expansion>
        <expansion>
            _Gregory
            _Greg
        </expansion>
        <expansion>
            _Gwendolyn
            _Gwenny
            _Gwen
            _Gwennie
        </expansion>
        <expansion>
            _Hannah
            _Hanna
            _Hanne
        </expansion>
        <expansion>
            _Harold
            _Hal
            _Harry
        </expansion>
        <expansion>
            _Henry
            _Hank
        </expansion>
        <expansion>
            _Herbert
            _Herb
        </expansion>
        <expansion>
            _Irvin
            _Irving
        </expansion>
        <expansion>
            _Irwin
            _Erwin
        </expansion>
        <expansion>
            _Isaac
            _Ike
            _Zeke
        </expansion>
        <expansion>
            _Isabelle
            _Isabella
            _Bella
            _Belle
        </expansion>
        <expansion>
            _Jacob
            _Jake
        </expansion>
        <expansion>
            _Jacqueline
            _Jackie
        </expansion>
        <expansion>
            _James
            _Jamie
            _Jim
        </expansion>
        <expansion>
            _Jane
            _Janet
            _Janette
            _Janie
            _Jan
        </expansion>
        <expansion>
            _Jeanette
            _Jeanne
        </expansion>
        <expansion>
            _Jennifer
            _Jen
            _Jenny
        </expansion>
        <expansion>
            _Jeremiah
            _Jeremy
        </expansion>
        <expansion>
            _Jessica
            _Jess
            _Jesse
            _Jessie
        </expansion>
        <expansion>
            _Joanna
            _Joan
            _Jody
            _Johanna
        </expansion>
        <expansion>
            _Jonathan
            _Johnathan
            _John
            _Jack
            _Jackson
            _Jehu
            _Johnny
            _Jon
        </expansion>
        <expansion>
            _Joseph
            _Joe
            _Joey
            _Josephine
            _Josepha
            _Jo
            _Josey
            _Josie
        </expansion>
        <expansion>
            _Joshua
            _Josh
        </expansion>
        <expansion>
            _Joyce
            _Joy
        </expansion>
        <expansion>
            _Judith
            _Juda
            _Jude
            _Judi
            _Judie
            _Judy
        </expansion>
        <expansion>
            _Julia
            _Julie
            _Juliet
            _Julian
            _Julius
            _Jule
        </expansion>
        <expansion>
            _Karen
            _Karon
            _Karin
        </expansion>
        <expansion>
            _Kristopher
            _Christopher
            _Chris
            _Topher
            _Toph
        </expansion>
        <expansion>
            _Lawrence
            _Larry
            _Laurence
            _Lawrie
            _Lon
            _Lorry
            _Laura
        </expansion>
        <expansion>
            _Leonard
            _Leo
            _Leon
            _Leona
        </expansion>
        <expansion>
            _LeRoy
            _Lee
            _Roy
        </expansion>
        <expansion>
            _Leslie
            _Leslea
        </expansion>
        <expansion>
            _Louis
            _Lewis
            _Lou
            _Louie
            _Louise
        </expansion>
        <expansion>
            _Marcus
            _Mark
            _Marc
        </expansion>
        <expansion>
            _Mariah
            _Maria
        </expansion>
        <expansion>
            _Mary
            _Mari
            _Marian
            _Marianna
            _Marion
        </expansion>
        <expansion>
            _Marjorie
            _Madge
            _Marge
            _Margie
        </expansion>
        <expansion>
            _Marcy
            _Marcie
        </expansion>
        <expansion>
            _Marvin
            _Marv
            _Merv
            _Mervyn
        </expansion>
        <expansion>
            _Matthew
            _Matt
            _Matthias
        </expansion>
        <expansion>
            _Maxine
            _Max
            _Maxwell
        </expansion>
        <expansion>
            _May
            _Mae
        </expansion>
        <expansion>
            _Michael
            _Mickey
            _Mike
        </expansion>
        <expansion>
            _Mitchell
            _Mitch
        </expansion>
        <expansion>
            _Montgomery
            _Monty
        </expansion>
        <expansion>
            _Mortimer
            _Mort
        </expansion>
        <expansion>
            _Natalie
            _Nat
        </expansion>
        <expansion>
            _Nicholas
            _Nick
            _Nicky
        </expansion>
        <expansion>
            _Nicole
            _Nikki
        </expansion>
        <expansion>
            _Patricia
            _Pattie
            _Patty
            _Patrick
            _Pat
        </expansion>
        <expansion>
            _Philip
            _Filip
            _Phil
            _Philippa
        </expansion>
        <expansion>
            _Rae
            _Ray
        </expansion>
        <expansion>
            _Randolph
            _Randall
            _Randy
        </expansion>
        <expansion>
            _Rebecca
            _Becca
            _Beckie
            _Becky
            _Reba
        </expansion>
        <expansion>
            _Reginald
            _Reg
            _Reggie
        </expansion>
        <expansion>
            _Richard
            _Dick
            _Rich
            _Richie
            _Rick
            _Ritchie
        </expansion>
        <expansion>
            _Robert
            _Dob
            _Dobbin
            _Bob
            _Bobby
            _Rob
            _Robin
            _Rupert
            _Roberta
            _Robbie
            _Robby
            _Bobbie
        </expansion>
        <expansion>
            _Roxane
            _Roxy
            _Roxie
        </expansion>
        <expansion>
            _Ryan
            _Ry
            _Rian
        </expansion>
        <expansion>
            _Samantha
            _Mantha
            _Sammy
            _Samson
            _Sampson
            _Sam
            _Samuel
        </expansion>
        <expansion>
            _Sandra
            _Sandy
        </expansion>
        <expansion>
            _Sarah
            _Cera
            _Sara
        </expansion>
        <expansion>
            _Scott
            _Sceeter
            _Squat
            _Scottie
            _Scotty
        </expansion>
        <expansion>
            _Simeon
            _Sim
            _Simon
        </expansion>
        <expansion>
            _Stan
            _Stanley
        </expansion>
        <expansion>
            _Stephanie
            _Steph
            _Stephie
        </expansion>
        <expansion>
            _Stephen
            _Steve
            _Steven
            _Stevie
        </expansion>
        <expansion>
            _Teresa
            _Terry
            _Tessie
        </expansion>
        <expansion>
            _Theodore
            _Ted
            _Theodora
            _Theo
            _Theophilus
        </expansion>
        <expansion>
            _Thomas
            _Thom
            _Tom
        </expansion>
        <expansion>
            _Tiffany
            _Tiff
            _Tiffy
        </expansion>
        <expansion>
            _Timothy
            _Tim
            _Timmy
        </expansion>
        <expansion>
            _Ty
            _Tie
            _Tye
        </expansion>
        <expansion>
            _Victor
            _Vic
            _Vick
        </expansion>
        <expansion>
            _Victoria
            _Toria
            _Torrie
            _Tory
            _Vicki
        </expansion>
        <expansion>
            _Vincent
            _Vin
            _Vince
            _Vinnie
            _Vinny
        </expansion>
        <expansion>
            _Wallace
            _Wallie
            _Wally
        </expansion>
        <expansion>
            _Walter
            _Walt
        </expansion>
        <expansion>
            _Wilhelmina
            _Helmie
            _Mina
            _Minnie
            _Willie
            _Wilma
            _Wilhelm
            _William
            _Bill
            _Bud
            _Will
            _Willis
            _Willy
            _Billie
        </expansion>
        <expansion>
            _Wilfred
            _Winifred
            _Winnie
            _Winnet
        </expansion>
        <expansion>
            _Woodrow
            _Woody
        </expansion>
        <expansion>
            _Zachariah
            _Zach
            _Zacharias
            _Zachary
            _Zak
            _Zac
        </expansion>
        <!--End Names-->
    </thesaurus>
</XML>

SQL 2005 FTS Thesaurus Doesn't Allow Duplicate Terms PROOF!

Was trying to find a way to use '&' and 'and' as expansions for my SQL 2005 FTS Thesaurus, and found this gem:

Because the thesaurus file already contains the term "Windows," the thesaurus file now contains duplicate terms. Therefore, the full-text thesaurus component does not load the thesaurus file.

From: http://support.microsoft.com/kb/923317

Again, would have been nice to know three Days ago.