Population of @SOURCE_DATABASENAME and @DESTINATION_DATABASENAME

[Imran-imtiaz48]

You are assigning a query to @SOURCE_SQL_DBNAME and then running sp_executesql to insert into a table variable:

SELECT @SOURCE_SQL_DBNAME = 'select name from ' + @SOURCEDBSERVER + '.master.sys.databases where database_id>4'
INSERT INTO @SOURCE_DATABASENAME EXEC sp_executesql @SOURCE_SQL_DBNAME
Issue is :
o sp_executesql cannot insert directly into a table variable from a dynamic string like this unless the schema matches exactly, and it can get tricky.
o If the dynamic SQL returns more than one column, or if there’s a typo, this will fail.