Getting a binding error in SQL Server on INSERT using Output

Gurus,

I am getting the common error:

Msg 4104, Level 16, State 1, Procedure ImportLandscapeETLToLandscape, Line 106 [Batch Start Line 5]
The multi-part identifier "stgdb.MSDatabaseId" could not be bound.

Usually this is a typo which I can quickly discern and correct, however, in this case I haven't been able to do so.  What is different is that I am using the Output Inserted feature. When I execute the SELECT portion of the INSERT statement as a standalone, the statement returns data.

Here's the code

	DECLARE @identityDatabaseTable table (DatabaseId int, MSDatabaseId int, ServerName varchar(100))

		  INSERT INTO EnterpriseDataLandscape.Landscape.[Database]
		   ( MSDatabaseId,
		     DatabaseName,
		     Active,
		     LastScanDatetime 
		   )
		Output Inserted.DatabaseId, stgdb.MSDatabaseId, convert(varchar(100),@@SERVERNAME) into @identityDatabaseTable(DatabaseId,MSDatabaseId,ServerName)
		   SELECT DISTINCT
		          stgdb.MSDatabaseId,
		          stgdb.DatabaseName,
				  1 as Active,
				  getdate() as LastScanDatetime
			FROM [EnterpriseDataLandscapeETL].[stagingIR].[DBInstanceMetadata] stgdb

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Does it work when you replace stgdb.MSDatabaseId on your Output clause with  Inserted.DatabaseId, if not, can you ensure that you are not inserting on a remote database ?  Also  arent these the same Inserted.DatabaseId, stgdb.MSDatabaseId ?
0
Paula DiTalloIntegration developerAuthor Commented:
Aneesh,
THanks for responding--stgdb is the reference to the column I am attempting to store in the variable table. No, replacing stgdb.MSDatabaseId with MSDatabaseId or Inserted.MSDatabaseId does not work.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Does it show the same error when you replace it with the inserted.MSdatabaseID ? in that case make sure that you are not referencing any remote tables ? also ensure that your sql server version supports OUTPUT clause.
refer this document for the exceptions
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms177564(v=sql.110)#remarks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
so what was the culprit
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.