Error: 0xc0207015: Data source column names must u

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error: 0xc0207015: Data source column names must u

Author

Topic

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-30 : 08:55:29

Here is the query I'm running and it comes back with the error...

SELECT *
FROM dbo.Case_Basic cb WITH (NOLOCK)
JOIN dbo.Gov_Entity_Relationships gov WITH (NOLOCK)
ON cb.cubs_case_id = gov.cubs_case_id
JOIN dbo.Gov_Entity_Library gl WITH (NOLOCK)
ON gov.contact_id = gl.CONTACT_ID

How can I select ALL columns in ALL tables I want to JOIN?? I know I can manually put them in but there are over 300 of them....there's got to be an easier solution than that.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 08:58:00

The problem is that the column names are ambiguous since there are multiple columns with the same name in multiple tables (example, Case_Basic and Gov_Entity_Relationships both have a column called cubs_case_id). There is no way to exclude those columns other than manually typing in all the column names or building the column list using a dynamic query.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 09:04:03

or just use excel to arrive at the string of column names.

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-30 : 09:10:09

quote:
Originally posted by sakets_2000

or just use excel to arrive at the string of column names.

Explain further please.....

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 09:12:02

If you drag and drop the table from Studio Manager into a new query window, it will automatically generate a column list. You can also use the information_schema table to get the column list. Then you can manually just removed the columns you don't need.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 09:12:40

sp_help 'tablename' will return you all column names. Paste that in an excel file. Add commas to the next cell and drag it down for all rows. Likewise add prefixes etc. Copy and paste this to another file after transposing.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-30 : 09:40:50

Or you can do this


SELECT
	', [' + [table_name] + '].[' + [column_name] + ']'
FROM
	information_schema.columns
WHERE
	[table_name] IN (
		--<Your List of Tables here>
	)
ORDER BY
	[table_name]
	, [ordinal_Position]

Just remember to remove the first leading comma

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources