• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

Get data from net

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.

0
samprg
Asked:
samprg
1 Solution
 
Alpesh PatelAssistant ConsultantCommented:
Please elaborate your need.
0
 
malikirfan28Commented:
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.
0
 
AmmarRCommented:
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
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

http://www.webservicex.net/ws/WSDetails.aspx?WSID=9&CATID=2
0
 
samprgAuthor Commented:
Hi Ammar,

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

Thank you so much
0
 
samprgAuthor 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.
0
 
AmmarRCommented:
To get Stock prices for free you have Yahoo & Google APIs

http://stackoverflow.com/questions/281263/where-can-i-get-free-real-time-stock-data

http://stackoverflow.com/questions/527703/google-financial-api-how-get-stock-quotes

how to use Yahoo
http://www.suite101.com/content/using-yahoo-financial-a71884
http://jarloo.com/tutorials/get-yahoo-finance-api-data-via-yql/    (using C#)
http://www.gummy-stuff.org/Yahoo-data.htm

how to use Google
http://code.google.com/apis/finance/docs/finance-gadgets.html

But it wont be real time.

Now to automate the process you have many options.

either use a .net application to
1. Download the file & insert in SQL and then use a scheduled task to run it daily
or use SSIS, in SSIS you have the ability to download the file. and direclty insert it in SQL
and then a sql job will help you run it on scheduled basis


download a file with SSIS
http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

import an Excel file into SQL using SSIS
http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm


hope it helps
0
 
samprgAuthor 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.
0
 
samprgAuthor Commented:
Thanks a lot
0
 
AmmarRCommented:
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

0
 
AmmarRCommented:
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/
0
 
samprgAuthor 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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now