GerixSoft

I am astonished of FOR XML feature in MSSQL. Oracle, despite claiming they had XML support first, has nothing like that; and XML support in PgSQL and MySQL is just ridiculuos...

The key feature of FOR XML is that is allows fetching multiple tables in just one query.

For example, below SELECT fetches Customer-Order-Shipper-OrderDetail-Product-Supplier-Category relationship:

SELECT *, (
    SELECT *, (
        SELECT *, (
            SELECT *, (
                SELECT Suppliers.SupplierID, Suppliers.CompanyName
                FROM Suppliers
                WHERE Suppliers.SupplierID = Products.SupplierID
                FOR XML PATH('Supplier'), TYPE
            ), (
                SELECT Categories.CategoryID, Categories.CategoryName,
                    Categories.Description
                FROM Categories
                WHERE Categories.CategoryID = Products.CategoryID
                FOR XML PATH('Category'), TYPE
            )
            FROM Products
            WHERE Products.ProductID = OrderDetails.ProductID
            FOR XML PATH('Product'), TYPE
        )
        FROM "Order Details" OrderDetails
        WHERE OrderDetails.OrderID = Orders.OrderID
        FOR XML PATH('OrderDetail'), TYPE
    ), (
        SELECT Shippers.ShipperID, Shippers.CompanyName
        FROM Shippers
        WHERE Shippers.ShipperID = Orders.ShipVia
        FOR XML PATH('Shipper'), TYPE
    )
    FROM Orders
    WHERE Orders.CustomerID = Customers.CustomerID
    FOR XML PATH('Order'), TYPE
)
FROM Customers
FOR XML PATH('Customer'), ROOT('xml'), TYPE

The question is not about receiving tables in XML, but about receiving all tables in just one call, w/ proper master-detail grouping (see attachment for result of the query). It is hard to imagine code doing the same w/ JOINs or cursors or on client-side. This makes FOR XML a perfect use-case in report generation services.

XSLT application servers would also greatly from FOR XML support: bespoke multiple tables can be fetched in one call, FOR XML produces results in XML format — just perfect for subsequent XSLT processing.

Summary

This article describes the course of actions you should take

1. if your database somehow got "suspect mode" or "emergency mode";
2. if you receive the error 1813 on attaching database

The History Behind 

I've been developing under SQL 7 for a while. Sometimes transaction log grew too large. My usual fix for that problem was:

1. stop the server (on the development machine we do not need any logs), 
2. delete transaction log
3. start the server again
4. after that the server was creating empty 1MB transaction log from scratch

I had been using this technique a lot of times under SQL 7, until sometime I have migrated to MSSQL 2000...

After my transaction log grew too large, I stopped the server, deleted the log file and started the server once again. However this time it did not worked: my database got "suspect". I was not able to read or write from the database. A week of work seemed to be lost... Ouch!...

I searched the newsgroups - a lot of people were facing the same problem and nobody knew the solution. All I found was the article: http://support.microsoft.com/support/kb/articles/q251/6/28.asp. I have tried that and revealed information in the article applies only to SQL 7 and does not to SQL 2000...

Did I solved the problem? Read on!

"Suspect Mode" --> "Emergency Mode"

Open Enterprise Manager,

1. Right click onto your server node, - an SQL Server Properties dialog will popup,
2. Enable "Allow modifications to be made directly to the system catalogs" option
   
3. Now proceed to the sysdatabases table in the master database, locate your database row and put 32768 into its status column
   
4. Restart SQL Server and refresh Enterprise Manager

Open Enterprise Manager - the database will be marked as "Emergency Mode"

These articles explain the trick behind suspect and emergency mode:
http://support.microsoft.com/support/kb/articles/Q165/9/18.ASP
http://www.swynk.com/friends/knight/unmarksuspect.asp
http://support.microsoft.com/support/kb/articles/Q180/5/00.asp

"Emergency Mode" --> "Normal Mode"

Now your database is in "emergency mode". Disregard Enterprise Manager does not show tables, views and procedures - use Query Analyzer's Object Browser instead.

"Emergency mode" means database is locked against any modifications, it is readonly forever. There is no way to bring "emergency" database back to normal state. All we can do is copy all data from it into new database.

1. Create new database (pubz)
   
2. Create new DTS package
3. Use "Copy SQL Objects" task to transfer data from old database to new database (pubs->pubz)
   
4. Execute the DTS job & enjoy!

Note that there might be complex dependencies between database objects, so Copy SQL Objects task might not work properly. For example: view A is dependent on view Z; Copy SQL Objects task will try to create view A first, -- that will fail, because view Z does not exist yet.

"Error 1813" --> "Suspect Mode"

If you receive "Error 1813", this probably means that you are trying to attach database you have improperly detached, or haven't detached at all. Only successfully detached databases can be successfully attached back.

1. Create new database using the same name and file location as database that is failing to attach
2. Stop SQL Server
3. Copy non-attachable data files over the new ones
4. Delete log files
5. Start SQL Server

Open Enterprise Manager - the database will be marked as "suspect"

Once your database is "suspect", use instructions outlined above...

Note that database will be marked "suspect" only if transaction log contained active sections. To illustrate the problem for 100%, run a few inserts against the database before deleting transaction log.