Do you have a consultant id that you an use instead as the data value in your drop down?

The convert is working, it is just the display using "print" that is truncating it.

IF you print LEN(CONVERT(VARCHAR(MAX),xmltypevariable)) you will see the conversion has worked.

What are you hoping to do...

And just to show how it can be done with the first query:

EXECUTE sp_msforeachdb 'use ?;select DB_NAME(),s.*, p.*

from sys.sql_modules s

inner join sys.procedures p

on s.object_id = p.object_id'

It does work, you just missed the "?"

EXECUTE sp_msforeachdb 'select specific_catalog, specific_name, routine_definition from ?.information_schema.routines'

you could build a table from this list : http://msdn.microsoft.com/en-us/library/0h88fahh(VS.85).aspx

Try having a read of Jacob's article here : http://beyondrelational.com/blogs/jacob/archive/2010/05/12/for-xml-workshop-for-xml-explicit-and-cdata-xml-and-xmltext-attributes.aspx

It explains how to do this with FOR XML EXPLICIT

ScottPletcher (12/7/2010)

Quite true. A valid point.

3 of them are exactly the same function. Unfortunately,...

I will take a crack at it...

First get all the pilot names

Now get all the plane names

Now check for each combination of pilot name and plane name is there a...

Unless you can guarantee the data will be in that format, I would personally be looking for a valid web site address.

This is not a complete solution as I have...

Another "ugly" method...just for the sake of it...

select *,

CASE WHEN 32*Work0110+16*Work0210+8*Work0310+4*Work0410+2*Work0510+Work0610 NOT IN (0,1,2,3,4,6,7,8,12,14,15,24,28,30,31,32,48,56,60,62,63) THEN 'Y' ELSE 'N' END

from #Employee

No problem. It is ugly code, so by rights should have something wrong!

HI Craig, can you explain why? It produces the correct answer for me...

Probably not the most elegant answer you will receive....

select *,

CASE WHEN

...

Brandie, I found your response to be curious - I was merely pointing out that you had not answered the question as asked - and I did not have time...

GSquared (11/30/2010)

... the part that says:

1 duplicate record occurs ONLY in p table and none of the duplicate records have a reference to it in pic table:

- all records, except...