Andrew Kernodle (6/30/2014)
Ah, gotcha! That changes things up a good bit :-).Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.
If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.
There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).
Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.
Can you go into a little more detail? I'm a little muddy on this.
Are you saying that the users would say what they wanted in the report and then the DBA would actually write the SQL for it? It's unfortunately not really an option since I think part of what has driven this is indeed to lighten the load of the DBA to work on other matters. If the other departments could order these queries themselves that would save time for the DBAs who spend a lot of time doing this for them. There are a fairly large number of people in the company.
Let me know if I misinterpreted you since I'm still a little rusty on this.
As for stored procedures and views, can I also get a bit more detail into that? Would that be basically selecting pre-made queries or would there be some flexibility with this?
I'm hoping Access is workable in some way as it seems like the easiest solution, but I want to again emphasize that some of the tables are VERY large. For example a table of transactions has millions of entries. Even just querying that table alone by itself takes two minutes to finish selecting all the rows. So when you're now joining that table with other tables or not efficiently filtering your results, you run into major performance problems. And I know that the columns you select, the indexes, what order you filter in, and how you do your joins all can make major major differences in performance.