Cosmos DB Stored procedure did not execute

Question

I'm new to Cosmos DB. I was trying to execute a stored procedure which simply fetch all the documents based on id and update one property of each documents. While executing it failed to execute.

What's wrong with this Stored Procedure?

string database = ConfigurationManager.AppSettings["database"];
string collection = ConfigurationManager.AppSettings["collection"];
client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);

DocumentCollection obj = await DocumentClientHelper.GetOrCreateCollectionAsync(client, database, collection);
string scriptFileName = @"E:\Satyaray\NoSql\NosqlDemoConsole\NosqlDemoConsole\Updatevalue.js";
string scriptId = Path.GetFileNameWithoutExtension(scriptFileName);

var sproc = new StoredProcedure {
   Id = scriptId,
   Body = File.ReadAllText(scriptFileName)
};

await DocumentClientHelper.TryDeleteStoredProcedure(client, obj, sproc.Id);

sproc = await client.CreateStoredProcedureAsync(obj.SelfLink, sproc);

var response = await client.ExecuteStoredProcedureAsync < string > (sproc.SelfLink, new RequestOptions {
PartitionKey = new PartitionKey("XMS-0001")
}, "XMS-001-FE24C");

Stored procedure

function simple(id) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();

    getAndUpdatedata();

    function getAndUpdatedata() {

        var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r where r.id=' + id,
        function (err, feed, options) {
            if (err) throw err;

            for (var i = 0; i < feed.length; i++) {
                var metaDoc = feed[i];

                metaDoc.readingTime = new Date();
                var isAccepted = collection.replaceDocument(metaDoc._self, metaDoc, function (err) {
                    if (err) throw err;

                });
                if (!isAccepted) throw new Error("The call replaceDocument(metaDoc) returned false.");
            }


        });

        if (!isAccepted) throw new Error("The call queryDocuments for metaDoc returned false.");
    }
}

Error Message

Message: {"Errors":["Encountered exception while executing function. Exception = Error: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":32,\"end\":35},\"code\":\"SC2001\",\"message\":\"Identifier 'XMS' could not be resolved.\"},{\"severity\":\"Error\",\"location\":{\"start\":40,\"end\":45},\"code\":\"SC2001\",\"message\":\"Identifier 'FE24C' could not be resolved.\"}]}\r\nStack trace: Error: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":32,\"end\":35},\"code\":\"SC2001\",\"message\":\"Identifier 'XMS' could not be resolved.\"},{\"severity\":\"Error\",\"location\":{"]}
ActivityId: 62e49cf4-1259-4d36-a196-7f752ceeba53, Request URI: /apps/DocDbApp/services/DocDbServer22/partitions/a4cb4962-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, RequestStats: , SDK: Microsoft.Azure.Documents.Common/1.20.108.4, documentdb-dotnet-sdk/1.20.2 Host/32-bit MicrosoftWindowsNT/6.2.9200.0

Is that all your code? I tried to validate your JS code and it would fail... have it checked here for example codebeautify.org/jsvalidate . Or use any linter to validate it. — Maxime Rouiller
Your JS code doesn't validate as valid Javascript. Are you missing code/closing brackets? — Maxime Rouiller

Jay Gong Jay Gong · Accepted Answer · 2018-03-16T02:48:57

I used console.log to print your sql and I found it looks like :

SELECT * FROM root r where r.id= XMS-001-FE24C

Actually , it should be like:

SELECT * FROM root r where r.id= 'XMS-001-FE24C'

So, please modify the SQL in your Stored Procedure a little bit and it works fine:

var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    "SELECT * FROM r where r.id = '" + id + "'",
    ......

BTW, id is user-defined unique name of the resource which is mentioned here. So i think the result of your sql should contain only one piece of data , no need to loop the feed array.

Hope it helps you.

Cosmos DB Stored procedure did not execute

Stored procedure

Error Message

1 Answers