Geek Answers Handbook

Handy Tools for Retrieving Data from a SQL Server Database

We have several SQL Server databases containing dimensions from the generators that we create. However, this useful data is available only to a few engineers, since most of them are not familiar with SQL (including me). Are there any tools that allow an engineer to retrieve selected subsets of data for analysis in Excel or in another environment? The perfect tool would be

  • protect the database from any accidental changes,
  • does not require SQL knowledge to retrieve data,
  • be very easy to use, for example, with a graphical interface for selecting fields and a selected time range,
  • allow you to export data values ​​to a file that can be read by Excel,
  • does not require participation / input from the database manager to perform the extraction task and
  • It will be easy to set up a newbie database manager.

Thanks for any recommendations or suggestions.

+4
source share
4 answers

First, I would never allow users to run their own queries on a production machine. They could run table scans or some other performance performer all day.

We have a similar situation, and we usually create custom stored procedures for users who "call" and only allow access to the backup server, which stores "almost live" data.

Our users are familiar with excel, so I create a stored procedure with sufficient parameters for filtering / tuning, and they can easily call it using something like:

EXEC YourProcedureName '01/01/2010','12/31/2010','Y',null,1234

I accurately document what the parameters do, and they usually go well from there.

To configure an excel request, you need to configure data sources on the user's PC (control panel - data sources - odbc), which will differ slightly depending on your version of windows.

In excel, you need to configure a “query”, which is just the EXEC command on top. Depending on the version of Excel, it should be something like this: menu - data - import of external data - a new query to the database. Then select a data source, connect, skip the table chart creator and enter the above SQL. In addition, do not try to do one procedure, do everything, do different ones based on what they do.

As soon as the data is on the excel sheet, our users pull it onto other sheets and manipulate it as they see fit.

Some users are a little advanced and “try” to write their own SQL, but this is a pain. I end up debugging and fixing their wrong requests. In addition, as soon as you correct the request, they always mess with it and break it again. using a stored procedure means that they cannot change it, and I can put it in our other ways in the source code repository.

+3
source

I would recommend you create your own in Excel. Excel can query your SQL Server database through an ODBC connection. If you do everything right, the end user should do a little more than click the "get data" button. They then have access to the full power of the Excel GUI to view data.

+1
source

Excel allows you to upload the output of stored procedures directly to a tab. IMO is the best way: users don’t need to know SQL, they just call the procedure, and there are no additional moving parts besides Excel and your database.

0
source

Depending on your version of SQL Server, I would consider some of the excellent self-service BI tools with later versions, such as Report Builder. It looks like a stripped-down version of a visual studio with all the complex bits cut out and just leftovers.

If you set up a shared data source that registers with a server with fairly low permissions, users can create reports but not edit anything.

I would respond to KM's comments that providing large unwashed launch requests in a production system can lead to some interesting results either using an incorrect query or using a massive scan of tables or Cartesian joins, etc.

0
source

More articles:


All Articles