Home Dashboard Directory Help
Search

Schema Compare to detect cross-database dependencies by tbedwards


Status: 

Active


1
0
Sign in
to vote
Type: Suggestion
ID: 795117
Opened: 7/26/2013 8:47:12 AM
Access Restriction: Public
0
Workaround(s)
view

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:
Details
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
Sign in to post a workaround.