troubleshooting Question

Status: 422 (Unprocessable Entity)

Avatar of Stevie Zakhour
Stevie Zakhour asked on
Microsoft SQL ServerJSONRESTSQL
2 Comments1 Solution155 ViewsLast Modified:
Hi All

I have a MS SQL script that runs a stored procedure called [Freak_APITest2] - see script code below

DECLARE @LeaderboardName		varchar		(20)
DECLARE @LeaderboardNameFull	varchar		(20) 
DECLARE @PlayerCount			varchar		(20)
DECLARE @PlayerName				varchar		(20)
DECLARE @Score					nvarchar	(20)
DECLARE @ZombiesKilled			varchar		(15)
DECLARE @Accuracy				varchar		(50)
DECLARE @DamageTaken			varchar		(20)
DECLARE @SecondsInLevel			varchar		(30)
DECLARE @Deaths					varchar		(50)

SELECT top 1 @LeaderboardName        = LeaderboardName		from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1  @LeaderboardNameFull	 = LeaderboardNameFull	from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1 @PlayerCount	 = PlayerCount					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @PlayerName	 = PlayerName					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @Score	         = Score						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @ZombiesKilled	 = ZombiesKilled				from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @Accuracy	     = Accuracy						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'		
SELECT top 1 @DamageTaken	 = DamageTaken					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @SecondsInLevel = SecondsInLevel				from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1 @Deaths		 = Deaths						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	


EXEC [Freak_APITest2]  @LeaderboardName, @LeaderboardNameFull,@PlayerCount, @PlayerName, @Score,@ZombiesKilled,@Accuracy,@DamageTaken,@SecondsInLevel,@Deaths

When I run the above script I get an error message, see below


Status: 422 (Unprocessable Entity)
Response text: {"error":{"type":"INVALID_REQUEST_MISSING_FIELDS","message":"Could not find field \"fields\" in the request body"}}

The stored procedure script code for Freak_APITest2 is below

alter procedure [dbo].[Freak_APITest2]
(
 @LeaderboardName			varchar		(20)
, @LeaderboardNameFull		varchar		(20) 
, @PlayerCount				varchar		(20)
, @PlayerName				varchar		(20)
, @Score					nvarchar	(20)
, @ZombiesKilled			varchar		(15)
, @Accuracy				varchar		(50)
, @DamageTaken				varchar		(20)
, @SecondsInLevel			varchar		(30)
, @Deaths					varchar		(50)
 --,@Date					datetime	
)
as

--DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

DECLARE @key varchar(100) = 'keyxxxxxxxxx';DECLARE @authHeader varchar(64) = 'Bearer '+@key; 


SET @contentType = 'application/json';
SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  "' + @PlayerCount + '",
        "PlayerName": "' + @PlayerName + '",
        "Score": "' + @Score + '",
        "ZombiesKilled": "' + @ZombiesKilled + '",
        "Accuracy":  "' + @Accuracy + '",
        "DamageTaken":  "' + @DamageTaken + '",
        "Deaths":  "' + @Deaths + '"
      }
    }
  ]
}'

SET @url = 'https://api.airtable.com/v0/appvFNyUQi9WZWSFH/ScoreTestTable';



-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

In the stored procedure, I did add the fields, see below

SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  "' + @PlayerCount + '",
        "PlayerName": "' + @PlayerName + '",
        "Score": "' + @Score + '",
        "ZombiesKilled": "' + @ZombiesKilled + '",
        "Accuracy":  "' + @Accuracy + '",
        "DamageTaken":  "' + @DamageTaken + '",
        "Deaths":  "' + @Deaths + '"
      }
    }
  ]
}'

I don't know why it won't run when the json is formatted correctly. Any support will be greatly appreciated.
ASKER CERTIFIED SOLUTION
Stevie Zakhour

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros