LP on .NET

March 9, 2013

OPENXML and Leading/Trailing Spaces

Filed under: SQL Server — Larry Parker @ 6:15 pm

SQL Server’s XML support can be a handy way to extract data out of a database and insert it into another database.  But there’s one thing you need to watch out for.  OPENXML “fixes” up your data by trimming leading and trailing spaces.  I suppose this could be viewed as helpful (e.g. an email address was mistakenly entered with a leading space), but it could have nasty side effects when your customer’s data really needs those spaces.

For example, suppose we have a table containing messages that will be displayed on a signboard outside a restaurant and we need to move the data from one database to another.  Also, assume that our application uses FOR XML to extract the data from the source database, and then uses OPENXML to insert it into the target database.

   1: DECLARE @messageData xml

   2:  

   3: -- This data can come from anywhere, but say it's extracted out of a database using FOR XML RAW:

   4: --      SELECT MessageGroupId, MessageId, MessageText

   5: --      FROM MessageLine

   6: --      FOR XML RAW, ROOT('Table')

   7: SET @messageData = '<Table Name="dbo.Message">

   8:   <row MessageGroupId="100" MessageId="1" MessageText="Welcome to Joe''s Restaurant!" />

   9:   <row MessageGroupId="100" MessageId="2" MessageText="     *** Early bird specials every night ***" />

  10:   <row MessageGroupId="100" MessageId="3" MessageText="Specials include:" />

  11:   <row MessageGroupId="100" MessageId="4" MessageText="     Meatloaf" />

  12:   <row MessageGroupId="100" MessageId="5" MessageText="     Sirloin Steak" />

  13:   <row MessageGroupId="100" MessageId="6" MessageText="     Baked Chicken" />

  14: </Table>

  15: '

  16:  

  17: DECLARE @docHandle int

  18:  

  19: EXEC sp_xml_preparedocument @docHandle OUTPUT, @messageData

  20:  

  21: -- And now we want to insert the message data into another database, for example:

  22: INSERT INTO MessageLine

  23: (MessageGroupId, MessageId, MessageText)

  24: SELECT MessageGroupId, MessageId, MessageText

  25: FROM OPENXML(@docHandle, N'/Table/row')

  26: WITH (MessageGroupId Int, MessageId Int, MessageText NVarChar(100)) AS x

  27:  

  28: EXEC sp_xml_removedocument @docHandle; 

The messages get inserted into the target database, but they look like this:

image

When displayed on the sign outside our restaurant, the meatloaf, sirloin steak and baked chicken all run over to the left.  Not good for business.  Smile

A solution is to use SQL Server’s nodes() method instead of OPENXML.

   1: -- Insert using nodes() method instead of OPENXML

   2: INSERT INTO MessageLine

   3: (MessageGroupId, MessageId, MessageText)

   4: SELECT T.m.value('@MessageGroupId', 'Int') AS MessageGroupId,

   5:        T.m.value('@MessageId', 'Int') AS MessageId,

   6:        T.m.value('@MessageText', 'NVarChar(100)') AS MessageText

   7: FROM @messageData.nodes('/Table/row') AS T(m)

Now our sign looks just the way the customer wants:

image

The nodes() method preserves the leading and trailing spaces, and is a bit easier to use than OPENXML as well because you don’t have to mess with preparing and removing the @docHandle variable.

There are a couple of caveats to this.  In the value() method, be sure to map the TEXT data type to VARCHAR(MAX), NTEXT to NVARCHAR(MAX), and IMAGE to VARBINARY(MAX).  Also, when copying XML columns, you need to use the query() method instead of value().

So a little extra work in that respect, but the nodes() method will do a better job of copying your data verbatim than OPENXML.

Hope this helps.

January 31, 2012

Obtaining SQL Identity Information

Filed under: Software Development,SQL Server — Larry Parker @ 7:05 pm

I found a great query today on this blog for obtaining information about SQL identity columns.  Here’s my updated version to handle SQL schemas:

SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Current_Identity,
    TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableHasIdentity') = 1
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME

This allows you to easily find out every table in your database that has an identity column, and what the seed, increment, and current values are.

Super helpful!

October 19, 2010

Removing a Specific Instance of SQL Server 2008

Filed under: .NET,SQL Server — Larry Parker @ 9:16 am

I normally work with a couple of instances of SQL Server 2008.  This allows me to better isolate work I’m doing for different projects, and also lets me run different SQL editions on a single machine (e.g. Express, Developer).

Recently I wanted to delete an instance I no longer needed, but could not figure out how to do this.  The SQL Server setup menus apparently do not have a deletion option.

The solution was to go to Control Panel –> Add or Remove Programs, select Microsoft SQL Server 2008, click Change / Remove, and then select Remove from the next dialog.

From a user perspective this just doesn’t feel right because it seems that you’ll end up uninstalling the entire SQL Server product!  Fortunately, the SQL Server setup program is run and you’re prompted to remove a particular instance.

Not the greatest user experience, but it works nonetheless.

Here’s an excellent blog post on the topic.

Hope this helps.

April 15, 2010

SQL Server 2008 Management Studio Express Installation Problems

Filed under: .NET,Microsoft,Software Development,SQL Server — Larry Parker @ 8:50 pm

Boy was this a day to remember.  Granted it was April 15th, but it had nothing to do with taxes.

I needed to apply a short SQL script to a database, so I fired up SQL Server 2005 Management Studio and tried to connect to my local SQL Server 2005 Express instance.  But surprisingly, I got this error:

image

[This version of Microsoft SQL Server Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers]

It turns out that my local SQL Server instance was no longer SQL Server 2005 Express but was instead SQL Server 2008 Express.  I recently installed Visual Studio 2010 on my development machine and it must have upgraded this.  Fair enough.

So I proceeded to run SQL Server 2008 Management Studio Express instead so it could talk to my SQL Server 2008 Express instance.  The only problem was that it wasn’t under the Microsoft SQL Server 2008 program group.  I guess VS 2010 did not install this but instead just installed the SQL engine itself.  I cannot understand why Microsoft would not include the GUI tool for SQL 2008, but surely they have a good reason.

So I found the SQL Server 2008 Management Studio Express link and proceeded to install that.

When I got to the Installation Type screen, I selected “Add features to an existing instance of SQL Server 2008”.

image

This seemed like the logical choice since SQL 2008 was already installed by the Visual Studio 2010 install.

But logic did not prevail in this case.  When I got to the Feature Selection screen, there were no features to select:

image

After searching around on some blogs and forums, it turns out that I should have selected “Perform a new installation of SQL Server 2008”:

image

Not very intuitive, but at least now I could select the Management Tools feature:

image

After successfully clicking through the next couple of wizard screens , I ran into another snag – this time on the Installation Rules screen:

image

When I clicked on the Failed hyperlink next to the “Previous releases of Microsoft Visual Studio 2008” entry, I got the following error:

image

[A previous release of Microsoft Visual Studio 2008 is installed on this computer.  Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008]

So I brought up Visual Studio 2008 to check if I had SP1 installed, and it turned out that I did.  Huh??  At this point, I felt like I was on Candid Camera (ok, I’m showing my age – I should have said Punk’d) and probably uttered a few expletives.

After looking around for Allen Funt, I mean Ashton Kutcher, I tried repairing Visual Studio 2008 by going to Control Panel and bringing up this screen:

image

Then I uninstalled SQL 2008 Express and reinstalled it again, but got the same problem on the Installation Rules screen where it was saying I needed VS 2008 SP1.

Note: make sure you install SQL Server 2008 Express with Tools!  At first I just installed SQL Server 2008 Express and had to waste more time figuring out why Management Tools was not available on the Feature Selection screen.

Back to the VS 2008 SP1 problem…  After checking around on a few blogs and forums, the general consensus was to uninstall Visual Studio 2008 SP1 and reinstall it.

Instead of suffering through completely uninstalling and reinstalling Visual Studio,  I tried out a shortcut and just reinstalled the service pack from this link.  I didn’t encounter any problems with this, but I highly recommend that you back up your Visual Studio 2008 settings first (under Tools | Import and Export Settings…).

So then I went back to the SQL Server 2008 Express with Tools installation and this time the Installation Rules passed the check.  Progress at last!  🙂

image

From there it was just a matter of clicking through the remaining wizard screens (and setting up some security) and it finally went through!

image

Wow.  That was a full day of work.  I finally got to run my SQL script at the end of the day, which took all of 30 seconds to complete.  Gotta roll with the punches, I guess.

Anyway, I sincerely hope this helps others out who have run into this same nonsense.  If you would like some more information on this problem, you can read more here and here.

June 17, 2009

LINQ Queries the Easy Way

Filed under: .NET,LINQ,SQL Server — Larry Parker @ 1:14 pm

A colleague recently approached me about a LINQ query he wrote that was a bit challenging since it traversed about a half dozen tables in the database.  He had it working but it was very long (several dozen lines) and he was curious if there was a better way.

The query he wrote used the LINQ query syntax instead of the method syntax (sometimes called lambda syntax) .  Query syntax tends to be a bit verbose, while method syntax can be a bit cryptic at times (see here for a comparison of the two styles).  I can’t really say one is “better” than the other, and I find that I use both in many cases, sometimes within the same query.

But more importantly, he was doing joins the “old” way — i.e. his LINQ code looked very much like SQL code.  When I started writing LINQ queries, I took the same approach.  SQL was familiar to me so being able to write strongly-typed SQL code in LINQ was at least a step up.  But writing queries this way is not thinking in LINQ.

I will demonstrate a query that is along the lines of how we ended up translating his original query into something very simple that flowed very nicely.  (His new query ended up being four lines of code instead of the original several dozen.)

Using the ever awesome LINQPad utility as our development environment, and coding against the Northwind database, we have the following challenge:

Retrieve the customer ID and company name for all customers who have ever placed an order that contained a product in the Condiments category.

LINQPad queries execute within the context of the database context, so I won’t be prefixing anything with “db”, “dc”, “ctx”, etc.

The first step is to just get the customer ID and company name:

Customers.Select(o => new {o.CustomerID, o.CompanyName})

This returns 91 rows and generates the following SQL:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]

Next, we refine the query to only get customers who have placed an order:

Customers.Where(o => o.Orders.Any())
    .Select(o => new {o.CustomerID, o.CompanyName})

This returns 88 rows and generates the following SQL:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
    )

Notice how the LINQ Any operator was translated into a SQL “WHERE EXISTS”.

Now things get a bit more complicated because we only want orders that contain condiments, but the query still flows very nicely:

Customers.Where(o => o.Orders.Any(o2 => o2.OrderDetails
    .Any(o3 => o3.Product.Category.CategoryName == "Condiments")))
    .Select(o => new {o.CustomerID, o.CompanyName})

The final result set is 68 customers, with the SQL looking like this:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [OrderDetails] AS [t2]
        INNER JOIN [Products] AS [t3] ON [t3].[ProductID] = [t2].[ProductID]
        LEFT OUTER JOIN [Categories] AS [t4] ON [t4].[CategoryID] = [t3].[CategoryID]
        WHERE ([t4].[CategoryName] = @p0) AND ([t2].[OrderID] = [t1].[OrderID])
        )) AND ([t1].[CustomerID] = [t0].[CustomerID])
    )

Note how LINQ generates the JOINs in the SQL for us.  There was no need to explicitly do a join in the LINQ code.  Instead, we used the navigation properties that are created for us (provided your database has foreign keys!).

So the final result was a fairly straightforward LINQ query using method syntax that got translated into some pretty nice SQL (pretty much what I would have written myself).

There are always many ways to write LINQ queries, and I recommend experimenting directly in LINQPad to see what makes the most sense for you.  You’ll find that as you start thinking more in LINQ, as opposed to SQL, that the queries become much simpler.

Hope this helps.

May 27, 2009

Installing BIDS after SQL Server 2005 SP2

Filed under: .NET,SQL Server — Larry Parker @ 10:27 am

Last Fall I set up a new machine with my usual development tools, but I forgot to install SQL Server 2005’s Business Intelligence Development Studio (BIDS) on top of Visual Studio 2005.  I had since moved on to other projects using Visual Studio 2008 that were keeping me plenty busy.

Yesterday, I needed to revisit an older project that used SQL Server 2005 Reporting Services and Analysis Services.  I opened up my solution in VS 2005 but got an error that it didn’t recognize the project.  This made sense because I never installed BIDS.

So I broke out my SQL Server 2005 CD’s and tried to install it.  Unfortunately, I had recently upgraded my client tools to SQL Server 2005 SP2, and the original install was blocking adding anything since a newer service pack had already been applied.

I figured I would need to uninstall SQL 2005 from my machine to undo the service pack, reinstall it with BIDS this time, and then apply SP2 after that.  But I found this post that made things much easier.

At the end of the thread (dated April 22, 2009) there’s a post by ubster that solves it quite nicely:

Situation: Installed SQL Server 2005 ages ago using an installer predating any of the service packs. I have since installed service pack 2. I went back to using the initial installer to add BIDS but of [course] the silly installer tells me that it’s already installed and won’t budge. I also happen to be running Vista for some masochistic reason.

Solution: Open Control Panel -> add/remove programs. Find Microsoft SQL Server 2005 and hit Change. Select Workstation Components found under SQL Server 2005 common components and hit next. Select Change Installed Components when you arrive at the option and choose to add Client Components -> Business Intelligence Development Studio. Continue through the rest of the installation and you will find a shiny new icon under Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio. This solution does not require anything to first be uninstalled.

Problem solved!  Now I can open my reporting project under VS 2005, and I still have SQL Server 2005 SP2 without having had to uninstall and reinstall.

Hope this helps.

Blog at WordPress.com.