We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Get data from net

Medium Priority
1,018 Views
Last Modified: 2012-05-11
Hi,

I need to get data (stock price, etc) from a web site, then save it in MS sql table.

Any help will be appreciated.
Thank you.

Comment
Watch Question

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please elaborate your need.
From which service provider you need to get data of stock price. Kindly note that I have used Xignite for live stock prices in my application. They have provided an API for C# and by using their API I gave a call to their web services and get back data.

So you can check your service provider that does they have given any API for it. If they have given it then you can use their API for getting data. Once you get data then you can use ADO.Net to insert data into database.
Top Expert 2010

Commented:
hi

check out these websites

http://www.stockpricedownloads.com/services.asp

http://www.eoddata.com/products/default.aspx

you can download the data and then import it into SQL, or if you want to automate the process you can use SSIS to create a package and then use a sql job to run it on scheduled basis.

Regards
Top Expert 2010

Commented:
there are ready webservices that you can use as well.

http://www.webservicex.net/ws/WSDetails.aspx?WSID=9&CATID=2

Author

Commented:
Hi Ammar,

Yes,I want to automate the process, can you give me more detail

Thank you so much

Author

Commented:
Hi Ammar,

Yes,I want to automate the process, can you give me more detail
I want to download the data but free

Thanks & regards.
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Ammar,

Thanks a lot,
I have other way, I can pull the data by Excell,
now I need to open Excell from SQL, I have already code for that but dosent work,
can you tel my why dosent work?!

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

declare @xlApp integer
declare @rs integer
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'

declare @xlWorkbooks integer
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT

declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'd:\M1.xlsx'

declare @xlWorkSheet integer
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT

---- Give me this error
Msg 10019, Level 16, State 0, Line 0
sp_OACreate has not yet been called successfully for this command batch.

Author

Commented:
Thanks a lot
Top Expert 2010

Commented:
hi

you need to add the following to your script

declare @xlApp integer, @rs integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT

check your code below after i added the line, it works now.
-----
check out this article it will help you alot with your Excel
http://www.sqlservercentral.com/articles/Excel/64838/
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

declare @xlApp integer, @rs integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT

execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'

declare @xlWorkbooks integer
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT

declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'd:\M1.xlsx'

declare @xlWorkSheet integer
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT

Open in new window

Top Expert 2010

Commented:
by the way here is another article that compares Writing to a File Using the sp_OACreate Stored Procedure and OSQL

check it for better performance

http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/

Author

Commented:
Thanks Ammar,

Last question, I need to take some rows from excell, do you have command for that in sql or excell?

Thanks & regards
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.