SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Let the Excel Play


Let the Excel Play

Author
Message
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 93
And i guess the full support of VBA (data formatting API, [UN]limited UI) would also b there with "Get External Data", And i don't think i should've taught my users to use "Get External Data" using the sqlserver(server=srvrname,pswd=gift4u) , this i had to use with 50 users only.

i'd appreciate a list of -ve impacts a few lines of VBA code lines would produce (don't include "connection string is in plain text" every responsible developer will protect the macro with a password)

i'd also appreciate the list of benefits of "Teach Get External Data" approach for non-technical MS-OFFICE only kind of users who are dying for the word "Automation" & Data on single click feature..
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 93
Didn't get your point my friend, are u in favour of the VBA approach coz.. it is difficult to individually teach n number of user how to use Get External Data or ...........
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215853 Visits: 41981
Don't get me wrong... I'm not bad mouthing your code or your article... I actually gave it a good mark. BigGrin

There's only one item on a list of benefits that you asked for that I can think of...

1. Users become self supporting

Most users can "survive" in MS Excel. Most of them can write simple formulas to accomplish what they need to accomplish. "Single button automation" with VBA is a really cool thing, but most users can't even spell VBA never mind do something with it other than recording simple macros. The end result is that whenever the users want a change in the automation or want something new to be automated, they have to come to you. You become the very busy single point of failure because, as you already know, everybody likes to make it easy on themselves and your fine automation definitely makes it easy.

If you get those folks together for a "Lunch-n-learn" and show them how to both setup MS Query and use "Get External Data" through a View to do their jobs, your users will become self supporting (their pride in doing something really cool will see to that) and you can concentrate on your primary job as well as thinking of what to teach them next. If you limit what they can read to simple views, you also keep control of the queries so they don't crush the server performance wise.

About the "Lunch'n'Learns"... not everyone actually needs to show up for those, although they should all be invited. We had a huge problem with users writing some really ignorant queries where dates were concerned and they were not only taking a million years to run, the results were often incorrect because the didn't actually know the right way to use dates (and all it's abbreviated forms) as criteria. I created a nice little "How to" booklet and not only gave it to them as handouts, but published it on the company WIKI. Only about 30% of the people actually attended the course but 100% of the people learned how to do it correctly because the people who attended the course actually taught it to their peers.

You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient. And, I've found, that if you teach them, you also and very suddenly become very well known and indispensible as a source of knowledge. That helps a lot in today's job market. Wink Hmmm... so maybe there's other advantages on the list of benefits you wanted, eh? w00t

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16816 Visits: 3403
Does it work? Yes
Should it be used? Not if you want a robust scaleable solution.

In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.

We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.

However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.

There is nothing wrong with the article. It presents a solution that works.

LinkedIn Profile
www.simple-talk.com
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 93
I understand your concern Jeff, infact you made a valid point by saying that

"You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient".

Also, i'm not advocating excel+VBA as a new full scale reporting tool, but my case was altogether different, all the reports were already running (partially) fine in our web application where users would click on the "Download to excel" and use the excel sheet thus generated..

BUT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

After this they spent another 15 min for formatting it the way they wanted,
for example whenever a large number like 7856556645452 gets exported in excel it doesn't appear as it should rather it takes the exponential form 7.85e10 also it get rounded off and bla bla bla.......

I even solved it by modifying the stored proc which fetched this data as ' ' + 7856556645452 but after a couple of days later i found that the stored procedure was doing more on the part of formatting the data than on fetching it, hence finally pestered with the daily arising new issues i took the help of VBA.

Some people have commented that code written this way is not maintainable, i agree coz any change in the data requirement would require the query in the macro to be modified but again why would you write plain text queries for what can be achieved through a stored procedure.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215853 Visits: 41981
shashank (10/5/2008)
...why would you write plain text queries for what can be achieved through a stored procedure.


I never said I would. Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215853 Visits: 41981
David.Poole (10/5/2008)
Does it work? Yes
Should it be used? Not if you want a robust scaleable solution.

In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.

We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.

However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.

There is nothing wrong with the article. It presents a solution that works.


I agree... nothing wrong with the article.

Not sure why you think you'd end up with uncatalogued and unsupported apps, though. The method I recommended would be done through supported views (or procs). The Excel spreadsheet is just to make the output "pretty".

I do, very much, agree that this type of reporting should probably be done through a reporting database rather than the production database for the very reasons you mentioned.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 93
I know that, an SSChampion won't BigGrin
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 93
I agree with everyone i definitely can't run in defiance of the facts by SSChamps and gurus infact this article doesn't need this much attention , but i'd say only 1 thing in the end that if the credit card number 4324244355455656 starts appearing as 4.32e10 or 43242400000000 on your final excel sheet than whatever u do won't be related anywhere to make the excel "pretty"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215853 Visits: 41981
shashank (10/5/2008)
I know that, an SSChampion won't BigGrin


Heh... OK. If you're going to throw a conversation on the floor with stuff like that, I guess this conversation is over.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search