Azure streaming from azure SQL datatbase failing

Question

The issue i am having is that i am working on streaming data from Azure SQL Database, & to do that i created a reference data input from azure sql db, blob input stream & output that pushes to power bi workspace, unfortunatley the streaming job is failing to insert the reference data from azure sql db to the blob input stream & returning "Error encountered while storing reference data snapshot into the storage account" error.

This is how i am doing it:

1. i created a streaming job.
2. i created a reference input for sql database & assigned a storage account.
3. i created stream input azure blob & created a container in the same storage account.
4. wrote a query that joins the reference input & the blob stream input but when i run it, it says the blob container has no data. Then i started the streaming job, which creates a new container & dumps data to it instead of to the container i created manually, so the output is "no result to show". am i doing it wrong? is there any better approach to do it?

Answer 1

So, the output is "no result to show."I created a sample table reference using this doc for SQL Database in an Azure Stream Analytics job.

You are facing null due to there being no data in the SQL database, or you have sent the data of the table in a Snapshot query.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    Category NVARCHAR(50),
    Price DECIMAL(10, 2),
    StockQuantity INT
);

Insert sample data into the Products table:

INSERT INTO Products (ProductName, Category, Price, StockQuantity)
VALUES
('Laptop', 'Electronics', 999.99, 10),
('Smartphone', 'Electronics', 699.99, 25),
('Tablet', 'Electronics', 299.99, 15),
('Headphones', 'Accessories', 49.99, 50),
('Monitor', 'Electronics', 199.99, 20),
('Keyboard', 'Accessories', 29.99, 40),
('Mouse', 'Accessories', 19.99, 35),
('Chair', 'Furniture', 149.99, 10),
('Desk', 'Furniture', 249.99, 5),
('Lamp', 'Furniture', 39.99, 30);

• Navigate to your Stream Analytics job and select Inputs under Job topology.
• Click Add reference input and choose SQL Database.
• List item

• Fill out the configuration form with your database name, server name, username, and password.
• To enable periodic refresh of your reference data, set the refresh option to "On" and specify the refresh rate in DD:HH:MM format.
• For large datasets, use the Delta query to track changes by retrieving rows inserted or deleted within a specified time frame.

Important Note:

Azure Stream Analytics retains snapshots within this storage account. When configuring the retention policy, it is imperative to ensure that the chosen timespan effectively encompasses the desired recovery duration for your Stream Analytics job.