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