Why aren’t my ASA job results showing up in Azure SQL Server table? How do I debug?


Often we receive questions from users of Azure Stream Analytics which help us improve our documentation or error handling. These Q&A’s also help other users. Here is one such case of figuring out why ASA results may not be appearing in Azure SQL server table.

Question:

I’m having trouble outputting my ASA job results into an Azure SQL server table. The schema of the ASA output and the SQL server table matches perfectly with datatypes and field names as per the instructions I’ve read . I’m still however getting an error when trying to start the job. Can someone point out what I’m doing wrong? Or Is there a step by step walkthrough or instructions somewhere on how to use SQL server as an output correctly?

Error: Stream Analytics job has validation errors: The output output used in the query was not defined. Activity Id: 'activity id string'.

Here is my ASA query:

Select
CAST(Text As nvarchar(max)) as text,
Cast (CreatedAt as datetime) as createdat,
(case Cast(sentimentscore as nvarchar(max))
when 2 then 0
when 0 then -10
when 4 then 10 else sentimentscore
end) as sentimentscore,
 
(case CAST(topic as nvarchar(max))
when 'XBox' then 'Xbox'
else topic end) as topic,
 
(case CAST(sentimentscore AS nvarchar(max))
when '0' then 'Bad :('
when '2' then 'Neutral'
when '4' then 'Good :)' else sentimentscore end) as sentiment
 from TwitterSteam

 

Solution by our team member Zhong Chen:

You may have a message in your ops log similar to the following message. Often we have noticed people have trouble locating relevant logs when job fails. We are working on improving that experience. Meanwhile, please carefully sift through the Error logs in the relevant time range.
{"Message Time":"2016-02-02 22:12:00Z","Error":"Comparison is not allowed for operands of type 'nvarchar(max)' and 'bigint' in expression 'case Cast ( sentimentscore as nvarchar ( max ) ) when 2 then 0 when 0 then - 10 when 4 then 10 else sentimentscore end'.\u000d\u000a","Message":"Runtime exception occurred while processing events, Comparison is not allowed for operands of type 'nvarchar(max)' and 'bigint' in expression 'case Cast ( sentimentscore as nvarchar ( max ) ) when 2 then 0 when 0 then - 10 when 4 then 10 else sentimentscore end'.\u000d\u000a, : OutputSourceAlias:twitter2;","Type":"SqlRuntimeError","Correlation ID":"connection id string"}

 

If you have questions like this one, please reach out to us on twitter : @AzureStreaming         

 

 

Comments (1)

  1. Kent Weare says:

    Along the same lines, but related to Service Bus Topics.  In the event ASA cannot communicate with Service Bus, what sort of 'protection' exists?  Can I set up an alert to be notified of this?  What would happen to the event(s) that may be in-flight?

Skip to main content