For anybody troubleshooting possible NULL values in the table while using the Basic authentication authHeader, change the setRequestHeader value from 'Authentication' to 'Authorization'. Also, adjust the DECLARE value for authHeader NVARCHAR from (64) to something bigger. Only realized that from testing in Postman and it worked for me.
@ProgenysEnd
4 жыл бұрын
Absolute lifesaver.
@joestocker660
Жыл бұрын
Awesome! I was looking for something to update sales tax info, from a state API. Had no idea I could contain it all in SQL Server. I can parse what I need from the results. Thanks so much!
@patiurco
Жыл бұрын
Dudee what a great explanation, simply awesome thank you so much for sharing this at this detailed level. Greetings from México and sorry for my english
@houstonfirefox
4 жыл бұрын
@Sigma Coding Great video. Concise and very well explained. Was able to modify your examples to build out a set of OAuth 2.0 stored procedures to interact with 3rd party vendors outside of our organization. With everything moving to the 'cloud', this seems to be the future with regard to data interchanges. I've noticed more and more vendors moving from older methods to OAuth 2.0. Keep up the great work!
@GP3_907
2 жыл бұрын
Was that easy for you
@houstonfirefox
2 жыл бұрын
@@GP3_907 Dealing with APIs is never easy getting started, especially when dealing with 'token timeouts' and all that mess. Our shop gets around that by simply requesting a new token when the job starts regardless of how old the 'old' token was. It simplified us having to worry about if a token is about to expire or not since a new one will always last Way beyond however long a job will take. Once you get the hang of it then it's pretty easy to deal with. Parsing the resulting JSON from many different vendors on the other hand can be a pain in the behind deciphering their layouts.
@MarijnTF
Жыл бұрын
@@houstonfirefox Hi there, I'm struggling to set up an OAuth2.0 connection. Could you by any chance share some of your wisdom? Would really help me out!
@houstonfirefox
Жыл бұрын
@@MarijnTF The process usually follows the same steps. Using either a username or password, or a token given to you from the provider, authenticate with the API and get a bearer token to use during any subsequent calls. If you are new to this then I recommend practicing with Python or some other language until you are comfortable, then bringing that knowledge over to SQL Server and replicating it in a stored procedure. Every API provider is different so you'll have to weave and bob around the way the provider chose to implement it. I would start off locating the API documentation they should have available and following the examples they hopefully provided.
@CurtTreece
3 жыл бұрын
FYI, you don't need to select the text in order to execute the entire script. Just make sure nothing is selected and click the "Execute" button. It will run the entire script.
@Juan-Hdez
Жыл бұрын
Very useful. Thank you!
@karlbasson6695
2 жыл бұрын
Thank you for the help! Good video.
@ernestmedina2448
7 ай бұрын
Greate Video only thing is that you probably should make the @apiKey bigger nvarchar(32) may not be enough. Also the @json is going to be blank or NULL because where it says: EXEC @ret = sp_OACreate 'MSXML.XMLHTTP' , @token OUT; should be: EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP' , @token OUT; the 2 is missing.
@seangillespie4601
2 жыл бұрын
Awesome video man, I kept returning NULL and spinning my wheels, your workaround changed the game for me! Do you have any videos on doing this with CSV instead of JSON?
@werneralonzo1230
3 жыл бұрын
greate tutorial dude!! thanks..
@danielrobertson9747
4 жыл бұрын
Really good video, thank you
@ReverendHogwash
3 жыл бұрын
Great for a one time import of API data, but what if the data in that API is updated multiple times per day - how does one make sure the date in SQL is syncd with the latest data?
@nmarks
2 жыл бұрын
You would need to schedule a job: docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?redirectedfrom=MSDN&view=sql-server-ver15
@maethebae8202
3 жыл бұрын
I get null values. When I exclude the insert to json part, the result is unable to open http connection.
@busiyatdah
2 жыл бұрын
thanks bro
@nandinisharma2340
3 жыл бұрын
wow! thanks!
@karangupta4518
3 жыл бұрын
Thank sir
@karangupta4518
3 жыл бұрын
Thanku sir
@premkumardr
3 жыл бұрын
Thank you. How do send a response to the request. For example, customer_id request response will be first name, last name and other in JSON format.
@marcosoliveira8731
4 жыл бұрын
Learned new stuff here! 100th like
@TechandArt
4 жыл бұрын
Grt learning
@SigmaCoding
4 жыл бұрын
Glad to hear that
@sanjoyroy
4 жыл бұрын
Would you kindly share the previous video link? I just came to page first. Thanks.
@skerdilajdshtylla7263
2 жыл бұрын
Hello! I want to update multiple rows with where clause! The data that i want to update, i get it from excel. How to get a lot of o data from excel? Is there any method i can copy them all at the same time in sql server and not getting them one by one with copy command??
@dmikester1
2 жыл бұрын
Are the two 'sp_configure' calls at the top temporary that only happen in the context of this query? Or does that change the database going forward? Would I need to revert them back after I am done?
@evemarenghi690
4 жыл бұрын
Thanks for the video! Super helpful. I followed your instructions exactly, I'm not getting any errors but when I select from @json it's empty. I checked and my URL is functional. Any recommendations for how to troubleshoot?
@ryanmorse5470
3 жыл бұрын
Same thing happened to me. Did you figure this one out?
@user-jk6jo1pu3j
3 жыл бұрын
not from this video but I was calling the Open Weather API specifically so I used this - maybe it can help you. github.com/JosephMagiya/Weather-Data-and-Forecasts-from-Open-Weather-API/blob/master/Weather_API%20(Current%20%2B%20Forecast).ipynb
@rachelsurendra
4 жыл бұрын
Thanks for the video.. I have to access an API from SQL server, this has three header values. I get an error ' Invalid number of parameters.' Do i have to concatenate it to one and set the request header?
@madhavareddy3488
Жыл бұрын
I built the request successfully, on 'Send' I have below error. Please help to fix! Let me pls know in case of addl info. A certificate is required to complete client authentication
@user-jh2yv2jz5s
Жыл бұрын
can we make SSIS package of this script. if yes then how?
@bobsnijders4182
4 жыл бұрын
Playback speed 1.25 :)
@ajitheshgupta3017
3 жыл бұрын
Can somone point to create a post request in SP
@cristianmene
3 жыл бұрын
How could I do it with the cURL command?
@patolobos8266
4 жыл бұрын
Can you create a store procedure or a SSIS package so it can run every night? I did not work for me, the code and parsing work perfect, can insert, select a perfect table, but it fails in SSIS or the SQL agent...
@houstonfirefox
4 жыл бұрын
@pato lobos Consider placing your code in a stored procedure and scheduling an agent job to call it every night. Make sure that the SQL Server Agent service is set to startup automatically and is indeed running.
@alpinestone1
4 жыл бұрын
Hello, It does not work with bearer token, please advice
@houstonfirefox
4 жыл бұрын
@Rahul Girl Ensure your variable holding the token is long enough to hold the entire token. I have seen cases where the variable was declared too small and only part of the token was being sent.
@herrieeffendi7748
4 жыл бұрын
Hi, I've tried to follow your script, but I still failed to put existing file with json file, Can I see your result script for SELECT api_key FROM [SigmaCodingDatabase].[dbo].[API_Services] WHERE service_name = 'Alpha Vantage' ? Thanks
@SigmaCoding
4 жыл бұрын
That just returns my API key, so I can't share the actual result with you.
@riddhipatel3104
4 жыл бұрын
I created loop as I need multiple json extract based on key. after 500 Calls I am getting Unable to open HTTP connection. Looks like I am getting error after exact 512 calls. How can I fix that?
@houstonfirefox
4 жыл бұрын
@riddhi patel it sounds like your API service provider has a "cap" on the number of calls within a certain time period. Read the API documentation or get in contact with them to determine if this is the case. Many data providers will put a cap in place based on subscription level or to prevent their systems being monopolized by a single customer.
@riddhipatel3104
4 жыл бұрын
I don't think its restriction issue source as as soon as I disconnect session and restart new session I am again able to call 512 records. Is there a way disconnect session automatically after 512 calls?
@houstonfirefox
4 жыл бұрын
@@riddhipatel3104 Another possibility is 'pagination' where the API provider limits a single request to a fixed number of records. They do this to prevent people from requesting multi-million row recordsets in one call and overwhelming the resource server. Think of it as providing recordsets in 'chunks' of 512 records, then requesting the next set of 512 records. How the resource provider does this is usually covered in their documentation. Let us know what you find.
@mallikarjuna1235
2 жыл бұрын
Can you please share me theoretical documents
@patolobos8266
4 жыл бұрын
I have a problem parsing, I just can't get it ... I have post it the issue here... any tips? stackoverflow.com/questions/62223028/parse-json-in-microsoft-sql
@patolobos8266
4 жыл бұрын
Solved! Thanks and to @kane from Stackoverflow!
@ponkyzw
4 жыл бұрын
I have an API that uses Basic Authentication and a body to POST to the API , how do I do the connection for this?
@ponkyzw
4 жыл бұрын
Sorry I need to read the data from API to SQL server. I need to use basic Authentication with body content.
@paulstanitzek
4 жыл бұрын
@@ponkyzw I've got the same problem have you found a solution already? Thank you in advance!
Пікірлер: 56