none
Issue with SQL Notification and WCF-SQL Adapter RRS feed

  • Question

  • Hi, today we run into an issue and I don't have an idea, what we've missed. We tried to configure a SQL notification receive location. But the receive location can't be enabled and stops with the following error:

    The Messaging Engine failed to add a receive location "<location>" with URL "<server>?InboundId=StepApproved" to the adapter "WCF-Custom". Reason: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: The notification callback returned an error. Info=Invalid. Source=Statement. Type=Subscribe.
       at Microsoft.Adapters.Sql.SqlAdapterInboundHandler.StartListener(String[] actions, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)

       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

       at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)".

     

    I know, it's similar to http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/thread/1fbc551f-0b94-44ad-b4ae-ccb07537f084 but that thread was no help to us. We don't get a response.

    Any ideas how to solve or how to debug the problem?

    Rgds Mrks

    Monday, June 7, 2010 11:39 AM

Answers

  • From the documentation at http://msdn.microsoft.com/en-us/library/ms181122.aspx:

    Query notifications are supported for SELECT statements that meet the following requirements:

    • The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

    • The statement may not use the asterisk (*) or table_name.* syntax to specify columns.

    • The statement may not use unnamed columns or duplicate column names.

    • The statement must reference a base table.

    • The statement must not reference tables with computed columns.

    • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

    • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.

    • The statement must not include PIVOT or UNPIVOT operators.

    • The statement must not include the UNION, INTERSECT, or EXCEPT operators.

    • The statement must not reference a view.

    • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.

    • The statement must not reference server global variables (@@variable_name).

    • The statement must not reference derived tables, temporary tables, or table variables.

    • The statement must not reference tables or views from other databases or servers.

    • The statement must not contain subqueries, outer joins, or self-joins.

    • The statement must not reference the large object types: text, ntext, and image.

    • The statement must not use the CONTAINS or FREETEXT full-text predicates.

    • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.

    • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.

    • The statement must not use any nondeterministic functions, including ranking and windowing functions.

    • The statement must not contain user-defined aggregates.

    • The statement must not reference system tables or views, including catalog views and dynamic management views.

    • The statement must not include FOR BROWSE information.

    • The statement must not reference a queue.

    • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

    • The statement can not specify READPAST locking hint.

    • The statement must not reference any Service Broker QUEUE.

    • The statement must not reference synonyms.

    • The statement must not have comparison or expression based on double/real data types.

    • The statement must not use the TOP expression.


    http://geekswithblogs.net/paulp/
    • Marked as answer by Mrks83 Friday, June 11, 2010 6:55 AM
    Monday, June 7, 2010 3:59 PM
  • What does SQL statement look like? Error message indicates that server failed to register a subscription because the SELECT statement didn't meet the requirements for query notification.


    http://geekswithblogs.net/paulp/
    • Marked as answer by Mrks83 Friday, June 11, 2010 6:55 AM
    Monday, June 7, 2010 3:51 PM

All replies

  • What does SQL statement look like? Error message indicates that server failed to register a subscription because the SELECT statement didn't meet the requirements for query notification.


    http://geekswithblogs.net/paulp/
    • Marked as answer by Mrks83 Friday, June 11, 2010 6:55 AM
    Monday, June 7, 2010 3:51 PM
  • From the documentation at http://msdn.microsoft.com/en-us/library/ms181122.aspx:

    Query notifications are supported for SELECT statements that meet the following requirements:

    • The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

    • The statement may not use the asterisk (*) or table_name.* syntax to specify columns.

    • The statement may not use unnamed columns or duplicate column names.

    • The statement must reference a base table.

    • The statement must not reference tables with computed columns.

    • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

    • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.

    • The statement must not include PIVOT or UNPIVOT operators.

    • The statement must not include the UNION, INTERSECT, or EXCEPT operators.

    • The statement must not reference a view.

    • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.

    • The statement must not reference server global variables (@@variable_name).

    • The statement must not reference derived tables, temporary tables, or table variables.

    • The statement must not reference tables or views from other databases or servers.

    • The statement must not contain subqueries, outer joins, or self-joins.

    • The statement must not reference the large object types: text, ntext, and image.

    • The statement must not use the CONTAINS or FREETEXT full-text predicates.

    • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.

    • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.

    • The statement must not use any nondeterministic functions, including ranking and windowing functions.

    • The statement must not contain user-defined aggregates.

    • The statement must not reference system tables or views, including catalog views and dynamic management views.

    • The statement must not include FOR BROWSE information.

    • The statement must not reference a queue.

    • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

    • The statement can not specify READPAST locking hint.

    • The statement must not reference any Service Broker QUEUE.

    • The statement must not reference synonyms.

    • The statement must not have comparison or expression based on double/real data types.

    • The statement must not use the TOP expression.


    http://geekswithblogs.net/paulp/
    • Marked as answer by Mrks83 Friday, June 11, 2010 6:55 AM
    Monday, June 7, 2010 3:59 PM
  • Thanks that worked for me.
    Thursday, January 13, 2011 4:51 PM
  • Hi Paul,

     

    I'm using a simple SQL Query which I have used for the Query Notfication is

    "SELECT MessageTT FROM dbo.ESBMessageQueue WHERE IsStatus = 'PROCESSED'"

    This statement is not working with SQL-WCF adapter and it is coming up with the following error message

     

    "The Messaging Engine failed to add a receive location "WcfReceiveLocation_SqlAdapterBinding_NotificationOperation_Custom" with URL "mssql://localhost//ESBServiceBroker?" to the adapter "WCF-Custom". Reason: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: The notification callback returned an error. Info=Invalid. Source=Statement. Type=Subscribe.
       at Microsoft.Adapters.Sql.SqlAdapterInboundHandler.StartListener(String[] actions, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)"."

     

    Can you please advice me where I'm going wrong?

    Many thanks,

    Kishore

     

     


    Kishore

    Monday, May 9, 2011 10:23 AM