Schema Compare to detect cross-database dependencies - by tbedwards

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 795117 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/26/2013 8:47:12 AM
Access Restriction Public

Description

We are constantly getting bit with an error each time we use a CTE (Common Table Expression) that relies on a SELECT *.  We need all of the columns because that is the result of the function in question and is subsequently mapped to a C# class.  Whenever we add something to the underlying table to add a column, the CTE’s compiled knowledge of the table columns becomes out of sync with the actual table and SQL Server throws an exception.  

I should mention that the function is accessing the changing table in another database, so Schema Compare is not helping us to detect the cross-DB dependency in its graph analysis.  It would be great if Schema Compare could detect these dependencies across databases.

Here is the function for review.  I would also be interested if Microsoft has plans to help with that kind of dependency in any future Schema Compare enhancements.

ALTER FUNCTION [dbo].[fnGetAssociateSubordinates]
(     
      @managerGuid uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
      WITH MgrCTE
      AS
      ( 
        -- Anchor Member (AM)
        SELECT 
          *
        FROM 
          Security.dbo.AdUsers
    WHERE 
      guid = @managerGuid
        
        UNION ALL
        
        -- Recursive Member (RM)
        SELECT 
          A.*
        FROM 
          Security.dbo.AdUsers AS A
    INNER JOIN
      Security.dbo.AdUsersManagerIDs I
    ON  A.guid = I.adusers_id
            JOIN MgrCTE AS M
              ON I.managerguid = M.guid
            where  
                  A.managerguid != A.guid
            and
                  A.Status = 'A'
            AND
                  A.IsServiceAccount = 'N'
      )
  SELECT
    *
      FROM 
            MgrCTE m
)

Here is the error:

System.Exception: Error in CustomerAccounts.aspx ERROR DESCRIPTION: Error in CustomerAccounts.aspx ERROR DESCRIPTION: Cannot execute GetRawInternal The application has encountered a data access layer exception. CALL STACK: at FSA.Data.DAL.Layer.Entity.GetRawInternal(SqlParameter[] sqlParameterArray, String commandText, Int32 commandTimeout) at FSA.Data.DAL.Layer.UserConfiguration.GetByAssociateFilters(String company, String division, String userKeyString, String customerNumber, String customerName, String customerGroupCode, Int32 topN) at Popup_Pages_CustomerAccounts.LookupAccount(Boolean isBasicSearch, Boolean isPageLoadEntry) INNER EXCEPTION: System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at FSA.Data.DAL.Layer.Entity.GetRawInternal(SqlParameter[] sqlParameterArray, String commandText, Int32 commandTimeout) CALL STACK: at Popup_Pages_CustomerAccounts.LookupAccount(Boolean isBasicSearch, Boolean isPageLoadEntry) at Popup_Pages_CustomerAccounts.btnAccountLookup_Click(Object sender, EventArgs e) INNER EXCEPTION: 
Sign in to post a comment.
Posted by Microsoft on 12/18/2013 at 3:15 PM
Thank you for the request. We agree this would be helpful and is a good suggestion, but at this point we are not going to be able to make this change. We will keep this in our list for upcoming releases.

Thanks,
Jill