Best way to retrieve Oracle data into SS2000 near real-time?

I have no idea where to post this kind of question, so here it is!

I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )

I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.

Any suggestions?

thanks!

In a bank I worked a while back we had a DTS package that runs five hours a day five days a week taking DB2 AS400 deposits to the web first 7.0 then 2000 almost real time. The package runs as a job with the Agent using xp_cmdshell and it works like a charm. Hope this helps.

Best way to generate Excel from SQL Server?

I have a requirement to generate a moderately complex spreadsheet containing
multiple worksheets and formulas from within SQL Server. I'm interested in
what is the recommended way of doing this. I will need to organize the data
within a stored procedure, generate the excel output and save it in a file,
which the application, which is web-based, will ship to the client's browser
to hopefully pop up into Excel. I've looked into generating HTML and XML
output, which can be loaded into Excel and functions as a spreadsheet, but i
t
would be better if I could generate an xls file in native Excel format. That
is because the end user should be able to work with these files over time,
and I would not want to have to deal with incompatibility issues.
Other options I'm considering:
1. Analysis Services - I need to research this more
2. Reporting Services - Ditto
3. Writing a procedure in C# to take the logic outside of SQLServer and
thereby having better access to Office API.
Thanks for any recommendations.Hi Steve,
Without thinking over so much this kind of task seems very affordable
by means of DTS along with some custom tasks done with VbScript (or
something like that)
Let me know your doubts or concerns with this.
Regards,
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||I've had a project with similar requirements.
What I did was encapsulate my complex queries in stored procedures.
I called these stored procedures via classic asp and rendered the output in
excel format.
I then created an excel macro for the user to do any requisite formatting.
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||there are lot way ways to get report into Excel.. the simple way is just
create the query to create view then open excel sheet then import the view
into excel by using Data Menu then Getexternal Data.
hope this will help u.
Regards
S Kaliyan
"Steve Elliott" <SteveElliott@.discussions.microsoft.com> wrote in message
news:C2E9EF12-A3F4-477B-A882-113412E03845@.microsoft.com...
> I have a requirement to generate a moderately complex spreadsheet
containing
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the
data
> within a stored procedure, generate the excel output and save it in a
file,
> which the application, which is web-based, will ship to the client's
browser
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format.
That
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.