help what is wrong w/ my syntax

[equus2]

SELECT 'Name', 'Rows', 'Reserved', 'Data', 'IndexSize', 'Unused'
UNION
SELECT Name, Rows, Reserved, Data, IndexSize, Unused FROM ##TableSpaceUsed ORDER BY CAST(Rows AS INT)

Error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Rows'.

TRYING TO OUTPUT COLUMN NAME + RESULT SET.

Thanks!

 

[jbenson001]

Not sure what you are tyring to do here, what do you want your final output to look like?

 

[equus2]

Like:

Name Rows Reserved Data Indexsize Unused
ABC 100 1K 1K 1K 1K

I'm bcp-ing this to a file, there's no column headers. I'm trying to add the column headers to my output so they'll show up on the file.

The "select" stmts work fine on their own. Don't like the union I think.

I have "show column headers" selected in my editor but it makes no difference in my file.

thanks

 

[jbenson001]

I just tried what you are doing on a table here at work. It works fine and there is only a problem if the column types don't match.

 

[equus2]

hmm

anyways, found workaround with osql

 

[djj55]

jbenson001 is correct because you have numeric and character data going into the same column of a union is the problem.

One work around is a temp table that you insert the headers into then insert the data, another way is to convert all data to character.

Glad you found a workaround.
djj

 

[Qik3Coder]

What did you do with osql?

That error rather than a conversion error looked like the column name "Rows" didn't exist.

Anytime you ask a question, the solution should be posted or identified to help others with problems when they search for things they are having trouble with.

-Sometimes the answer to your question is the hack that works

 

[SQLBill]

I know it's late, but his first SELECT doesn't have a FROM. It almost looks like he is trying to use the first SELECT to create the column headers. Notice that everythng in the first SELECT has single quotes so they become strings.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Qik3Coder]

SQLBill, that is correct, and that was his intention. That was his problem, he had integer values in the second which were colliding with string values in the first.

his initial select syntax is valid

-Sometimes the answer to your question is the hack that works