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?
 
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
 
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:
so what was the culprit
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.