I assume you're familiar with shredding those data into a relational (temp) table using XQuery. If not, please post back and we'll show you an example how to do it.

Once...

Lynn Pettis (5/3/2010)

---

... and that's exactly the case...

Would you mind to share an example to support your argument?

The following code would solve the example you provided, but as Lynn already suggested, posting your code would probably help us help you with the "real issue"...;-)

SELECT 'Header Record'

UNION ALL...

It seems like you add the extra CR-LF within your trailer record statement. Hard to tell without actually seeing the code...

Probably something like

-- some code

+'

< trailer record > '

...

black.clouds.999 (5/3/2010)

---

Thanks Imu !

Here is what I got from BOL :

CONTAINSTABLE ranking uses the following algorithm:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )

Rank = min( MaxQueryRank, HitCount...

It's called CroosTab or Pivot.

For a CrossTab solution please see the related link in my signature. It will shortly describe the PIVOT syntax as well.

You actually search for an exact match of either the word "mountain" or "mounting". Therefore, you'll get two different result sets. You might want to search for "mount*".

For details, please...

You're very welcome! 😀

Assume the following scenario:

You have a new room that's available since the hotel decided to refurniture the clothing store.

This room will never be available since it doesn't show up in...

Change the SELECT clause to

SELECT COUNT(DISTINCT t.room_id)

FROM @tbl t

LEFT OUTER JOIN cte_blocked b ON t.room_id=b.room_id

WHERE b.room_id IS NULL

First I would try to find the blocked rooms and second display the remaining:

Side note: please notice how I posted the sample data o make it easier to work on......

What would be your expected result?

I would use the "quirky update" (or 3-part-update):

DECLARE @tbl TABLE

(

Region CHAR(10),Col1 CHAR(1), Col2 CHAR(1),Col3 CHAR(1)

)

INSERT INTO @tbl

SELECT 'SOUTH','N','N','Y' UNION ALL

SELECT 'SOUTH','Y','N','N'

DECLARE @n CHAR(1)

SET @n='0'

UPDATE @tbl

SET

@n=col3=CASE WHEN @n='0' AND col3='N'...

CirquedeSQLeil (4/30/2010)

---

Odd question just occurred to me.

Has anybody here ever tried setting their sa account password to contain emoticons? Or any sql logon password for that matter.

sa password =...