dilemma with getting sql data

[JohnE]

Greetings. I am seeking feedback from the group on the a project handed to
me. What I have is a situation in which there is an xml file updating a table
in SQL Server 2005 4 times per minute. What I am faced with is getting the
info out of sql following the update and placing the information into one or
more excel worksheets, after each update. I have been asked to use a third
party add-in developer inside visual studio 2005. But I am finding that it
might be more work then it is worth and some difficulty in getting my mind
around it. My thought is Excel could handle the task directly without any
intervention from a third party. Excel is even capable of creating an
add-in. The extraction of the data from sql can be done thru stored
procedures, which may have or may have not parameters.

So, am I way off base here regarding excel, sql, and third party add-in
developer, and excel can handle it on its own?

Let me know your thoughts on this.

Thanks.

.... John

 

[John Bundy]

Its hard to answer without knowing all of the details, but my first thought
is that yes you can do it, and no it is not easy. My first question is
obviously why, if the xml is updating the table, why not have it dump
directly from there? You know getting this data from every update into excel
will create 5760 workbooks or sheets per day! and with Excel hitting a
presumably large file and downloading it almost constantly you will basically
have to dedicate a machine to it. Visual Studios is a much simpler option,
but the way that would make the most since to me is to have the code update
another table as well, and doing just inserts instead of updates. that way
all of the data is in a place much easier to work with than 5k+ worksheets,
that and it is done with a machine that is already performing that function.
Just add a timestamp column or increment it at each update and store it all
directly in SQL. I can't fathom a logical, or perhaps illogical reason for
keeping that much data stored in excel sheets. That said, it doesn't mean we
won't help whatever you decide

 

[Tim Zych]

info out of sql following the update and placing the information into one

or
more excel worksheets, after each update.

Unless XL can constantly poll the database looking for updates, I'm not sure
how XL would figure out how to know when to execute the stored proc. This is
a server side "push" into XL after a SQL data event, which sounds like a SQL
Job and/or using SSIS. This is probably a better question for a SQL Server
newsgroup.

 

[JohnE]

Thanks for the info. I will try to explain it better. There is an xml file
that is getting refreshed 4 times per minute with stock quotes. Originally,
the xml was to update a series of worksheets in excel for the users to view.
But it was decided there could be some conflicts occuring so it was decided
to put the info into a sql table and then have series of excel worksheets get
it from there. The excel worksheets would grab the new data from sql after
the sql table was refreshed with data. The third party add-in maker for vis
studio 2005 does not have very good documentation. The syntax for it is
different but yet similar to vba that I've seen. How would I go about using
vis studio for developing an excel add-in? Is a third party program for
vs2005 needed? Or can vs2005 do it on its own? Are there samples/examples
that are available to review?
Any assistance on this is appreciated. I (for some reason) am having a
brain freeze on this project. Must be the end of the year blahs.
Thank you.
.... John

 

[John Bundy]

As Tim mentioned, the data would be pushed from the server to Excel and would
therefore be on the server side of things. That said, from the looks of how
you will be using it, you could set the Excel document through Data->Import
external data, to grab info from the database only when the user wants it,
you can set it up easily as a manual refresh or set it to refresh every x
seconds/minutes. Do some searching around on setting up SQL as a data source
for an Excel import.
First search result i found came up with this, looks exactly like what you
want to do.
http://www.dbforums.com/archive/index.php/t-907427.html
notice the refresh section.