CODEWITHSUNDEEP
azurestored-proceduresazure-cosmosdb
Edmond Sesay
Edmond Sesay

Reputation: 99

Azure stored procedure in Azure portal not working although SQL query works

The following stored procedure failed to produce any result even though the SQL query used in it produced results when tested on the Azure portal.

function checktemp() {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    let query = `SELECT DISTINCT {"Elevator": t.connectiondeviceid,
        "Vibration": t["vibration"],
        "Temperature": t["temperature"]} 

    FROM t 
    WHERE t["temperature"] > 75
    AND t.EventEnqueuedUtcTime > "2019-08-03T20:30:51.905Z"
    ORDER BY t["temperature"] DESC`

    // Query documents and take 1st item.
    var isAccepted = container.queryDocuments(
        container.getSelfLink(), query,
    function (err, feed, options) {
        if (err) throw err;

        // Check the feed and if empty, set the body to 'no docs found', 
        // else take 1st element from feed
        if (!feed || !feed.length) {
            response.setBody('no docs found');
        }
        else {
            var body = { moststrain: feed[0] };
            response.setBody(JSON.stringify(body));
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

I expect to have items returned, but I always get 'no docs found'. My partition key is /ConnectionDeviceId.

Upvotes: 1

Views: 472

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Tested your sample document with your stored procedure code,it works for me.Your SP structure should be fine.

enter image description here

Some mistake with the spell of property(ConnectionDeviceId) you provide,it should be ConnectionDeviceId in sql:t.ConnectionDeviceId.

enter image description here

To solve such issue like something works in the portal, no results in SP, i suggest you removing query statements partially step by step to locate which part of SQL causes no results.

Anyway,the issue is related to partition key. When you query data in the portal, it scans all the partitions. However,if you execute SP,it only scans specific partition.

Since the data is partitioned on 'connectiondeviceid', I was supposed to provide a value of it during execution of the stored procedure.

Upvotes: 2

Related Questions