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.
can you please give me an example of calling the API with these values, this is for my stock brokerage to place orders. App Key App Secret Callback URL Access Token Refresh Token Id Token Below is the order { "orderType": "MARKET", "session": "NORMAL", "duration": "DAY", "orderStrategyType": "SINGLE", "orderLegCollection": [ { "instruction": "BUY", "quantity": 1, "instrument": { "symbol": "SPY", "assetType": "EQUITY" } } ] }
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!
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.
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
@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 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.
@@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!
@@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.
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?
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?
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?
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??
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.
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?
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
@Sigma Coding can you please give me an example of calling the API with these values, this is for my stock brokerage to place orders. App Key App Secret Callback URL Access Token Refresh Token Id Token Below is the order { "orderType": "MARKET", "session": "NORMAL", "duration": "DAY", "orderStrategyType": "SINGLE", "orderLegCollection": [ { "instruction": "BUY", "quantity": 1, "instrument": { "symbol": "SPY", "assetType": "EQUITY" } } ] }
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?
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
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...
@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.
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
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?
@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.
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?
@@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.
@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.
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
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.
Absolute lifesaver.
can you please give me an example of calling the API with these values, this is for my stock brokerage to place orders.
App Key
App Secret
Callback URL
Access Token
Refresh Token
Id Token
Below is the order
{
"orderType": "MARKET",
"session": "NORMAL",
"duration": "DAY",
"orderStrategyType": "SINGLE",
"orderLegCollection": [
{
"instruction": "BUY",
"quantity": 1,
"instrument": {
"symbol": "SPY",
"assetType": "EQUITY"
}
}
]
}
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!
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.
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
@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!
Was that easy for you
@@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.
@@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!
@@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.
Very useful. Thank you!
Thank you for the help! Good video.
I get null values. When I exclude the insert to json part, the result is unable to open http connection.
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?
You would need to schedule a job:
docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?redirectedfrom=MSDN&view=sql-server-ver15
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?
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?
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.
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??
greate tutorial dude!! thanks..
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.
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?
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
@Sigma Coding can you please give me an example of calling the API with these values, this is for my stock brokerage to place orders.
App Key
App Secret
Callback URL
Access Token
Refresh Token
Id Token
Below is the order
{
"orderType": "MARKET",
"session": "NORMAL",
"duration": "DAY",
"orderStrategyType": "SINGLE",
"orderLegCollection": [
{
"instruction": "BUY",
"quantity": 1,
"instrument": {
"symbol": "SPY",
"assetType": "EQUITY"
}
}
]
}
Would you kindly share the previous video link? I just came to page first. Thanks.
Really good video, thank you
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?
Same thing happened to me. Did you figure this one out?
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
wow! thanks!
Learned new stuff here!
100th like
Can somone point to create a post request in SP
can we make SSIS package of this script. if yes then how?
Playback speed 1.25 :)
How could I do it with the cURL command?
Grt learning
Glad to hear that
Thank sir
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...
@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.
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
That just returns my API key, so I can't share the actual result with you.
thanks bro
Thanku sir
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?
@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.
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?
@@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.
Hello, It does not work with bearer token, please advice
@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.
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
Solved! Thanks and to @kane from Stackoverflow!
Can you please share me theoretical documents
I have an API that uses Basic Authentication and a body to POST to the API , how do I do the connection for this?
Sorry I need to read the data from API to SQL server. I need to use basic Authentication with body content.
@@ponkyzw I've got the same problem have you found a solution already? Thank you in advance!