April 24, 2014 at 10:09 am
Hi there,
I have a C# process that gets JSON output from an API. I then need to store this JSON output for every ID(primary key) in SQL server DB.
Create table temp(ID INT NOT NULL, JSONOUTPUT nVARCHAR(2000));
Insert into temp(1,"{ "results" : [{"components" : [{"long_name" : "Nablus", "short_name" : "Nablus", "types" : [ "locality", "political" ]}],"formatted_address" : "Nablus","geometry" : {
"location_type" : "APPROXIMATE" }}],"status" : "OK","error":"null"}");
I have observed that if the JSON output is more than 2000 characters, we(our team) have concluded that the output is "sometimes" not correct. We want to store it somehow so, it can be analysed by content team.
What's the best way to do this?
1. Should that JSON OUTPUT be stored in a text log file and then in database for that column, insert a -1 or "see log file" for that ID
2. Should I truncate JSONOUTPUT to 2000 characters and store actual output in a separate table with nvarchar(max)
Any other suggestions??
Thanks
Rash
April 24, 2014 at 2:35 pm
What do you mean with "not correct"?
Why don't you use (n)varchar(max) for the column?
Another option is to use the filestream feature.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 24, 2014 at 2:49 pm
"not correct" - API returns vague result.
April 24, 2014 at 3:06 pm
rash3554 (4/24/2014)
"not correct" - API returns vague result.
Similar to the answer provided to the question. 😉
Remember we can't see you screen, we don't know what your process is doing, we don't know what the output is, we don't even know at this point what the actual issue is. We can help you but you have to provide some details. The answer you provided above is like taking your car to the mechanic and when they ask you what the problem is you say "it makes a strange noise".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2014 at 3:26 pm
What I mean by vague is that I do not have control over third party API result. All we know by experience and analyzing records is that if the JSON output is over 2000 characters, we may or may not keep that record. It is sent for further analysis. I was looking at different options to store this kind of data,
Thanks
Rash
April 24, 2014 at 3:30 pm
rash3554 (4/24/2014)
What I mean by vague is that I do not have control over third party API result. All we know by experience and analyzing records is that if the JSON output is over 2000 characters, we may or may not keep that record. It is sent for further analysis. I was looking at different options to store this kind of data,Thanks
Rash
So what about changing the column to varchar(max) as Koen suggested?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply