SQL 2005 Error Msg 4101 Mutli-Bound Error

[Merkaba]

I just upgraded to SQL Server 2005 Standard Edition for my home Dev work.

When I SELECT a column from a table and JOIN with that same column I get a weird error message. I've been Googling it for about 20 minutes and found people with the same problem, but no answer was given.

Oddly enough, I searched this site and it seems I'm the first to come across it here.

Here is the error msg:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sa.svr_area_id" could not be bound.

I've been able to get around it on a few queries but this one won't display any data with the previous workarounds:

INSERT  INTO base.area_attributes
(
   app_attribute_id,
   svr_area_id,
   user_id_create,
   create_date,
   user_id_update,
   update_date
)
SELECT	DISTINCT
	aa.app_attribute_id,
	sa.svr_area_id,
	1,
	getDate(),
	1,
	getDate()
FROM	base.svr_areas sa,
	base.app_attributes aa
JOIN	base.base_type_svr_areas btsa
ON	sa.svr_area_id = btsa.svr_area_id
JOIN	base.base_types bt
ON	btsa.base_type_id = bt.type_id
JOIN	mode.defaults def
ON	bt.default_id = def.default_id
JOIN	base.app_attribute_types aat
ON	aat.[name] = 'Data Type'
WHERE	(def.business_name = 'Some Data'
	 AND (aa.[name] = 'Some different data'
	      OR  aa.[name] = 'Some more different data'))
	      OR  (def.business_name = 'More Data'
		   AND (aa.[name] = 'Some more different data'))
OR 	 (def.business_name = 'Some Data #2'
	  AND (aa.[name] = 'Some more different data'))

I had to change the table and column names as I'm a consultant and my company doesn't take kindly to posting their "proprietary" quieries on the web, but I was careful in my changes (I think) so it's exactly the same structure, just different names.

Why does a query like this error out in SQL 2005? I've done one right before it exactly the same

- with 2 tables in the FROM and a bunch of JOINS with a WHERE defining the rough conditions that would require too many more JOINs on the same table -

and it worked fine. The only difference was I was SELECTing the sa.svr_area_id and using it in the JOIN this time, and if I comment that first JOIN out it will run, just not correctly. I don't think doing that ever bombed out on me before ????

Any ideas?

 

[SimonSellick]

I haven't come across this error before, but the query looks odd. Does the SELECT work without the INSERT? Your starting point appears to be a cartesian join of two unrelated tables; is that really what you want? Can you give some hints as to the data content (relationships) in the tables?

 

[Merkaba]

The SELECT doesn't run, but this one works fine:

INSERT INTO base.svr_areas
(
	site_id,
	[name],
	srv_area_type_id,
	create_date,	
	create_user_id,
	update_date,
	update_user_id
)
SELECT	s.site_id,
		def.[business_name] + ' & ' + at.[name] + ' Default',
		sat.service_area_type_id,
		getDate(),
		1,
		getDate(),
		1
FROM	mode.sites s,
	base.srv_area_types sat,
	base.app_types at
JOIN	mode.default def
ON	at.[id] = def.[id]
WHERE	sat.[name] = 'Default Application'

As for the relationships, well, I'm migrating from a old DB to a completely new DB and have to insert the metadata for the new tables based on the conditions of the old DB. Oddly, the queries importing from the old DB were much easier than the metadata filler #$%^ I have to figure out. I did not design the either DB, so I have no control over the table structure and I had to rename them all in the posts here or I could get in trouble for posting "proprietary" data; but here is a run down of the relationships between my first posted query:

The DB incorporates multiple programs; that's why there are different owners, but the App I have to write mainly uses the ones I coded with an owner [base]

base.svr_areas is the central table, roughly the hub of the DB; it had a FK to base.srv_area_types to define the type of svr_area

base.base_types holds a FK to mode.default

mode.defaults is a global table used by almost every App the DB supports as a backend.

base.base_type_svr_areas is a relationship table between base.base_types and base.svr_areas

base.app_attributes are configured attributes set in the configuration of the App and each attrib has a type linked by a FK to base.app_attribute_types;

these attribs link to the base.area_attributes for each svr_area that is based on the conditions that have 'Some Data' and such.

These conditions were defined in the old DB easily, but in the new data model, they didn't take much consideration for the App they assigned me to do; the reason for the odd query. I have to mimic the old UI's needs using the new data model.

From what else I've seen researching this, it seems to be a SQL Server 2005 problem, as I've NEVER seen that error before, and I've written hairier queries in the past in SQL 2000 using multiple unrelationed tables that I really can't join with out double to triple joining the table in question.

If you have SQL Server 2005 try writing a similar query. No matter how I re-position the FROM clause it fails...

Hopefully when I get to work tomorrow (ugh, today now) MSDE 2000 will accept the query. I should just install a seperate instance of MSDE on my home machine and check so I can actually get some sleep, as my Deadline for the data migration is Friday.

Thanks,

Merk

 

[Merkaba]

BTW, I'm trying to build the realtionship table between base.svr_areas and base.app_attributes, which is why they are currently "unrelated"

Merk

 

[Merkaba]

Well thanks for the help, but I figured it out. I had to take the base.app_attributes out of the FROM and JOIN it with another table for it to work properly. It's weird that one worked with multiple unrelated tables in the FROM, but not the first one. Oh well, Thanks again, anyways... Also, the Spreadsheet my boss gave me had incorrect [name] values so that broke it too, lol.

Merk