SQL Excel

First of all, thanks again for creating this wonderful tool! I have two suggestions that I think would hugely enhance usability.

1) Remove restrictions on TSQL statements. Security should be handled in the database, not by the application. If the connected account doesn't have rights to execute a statement, they'll be an error regardless of whether the application passes the statement through or not. Restricting TSQL statements (especially inserts into TEMPDB #temptables, which is standard in many non-trivial queries) really reduces functionality.

2) Add the ability to pass parameters from Excel; for stored procedures, and optionally scripts. This would really open up SQL Excel as a fully functional user interface for running database queries. I'm not an Excel expert, so perhaps another member might have suggestions for the best interface. My suggestion would be to have some convention where the application provides an input prompt based on symbols in the query.

So a query like: EXEC myproc @parm1, @parm2 - would prompt for 'Enter @parm1 value', 'Enter @parm2 value'.

Once again, thanks for providing this great add-in!

Hi Jon

Thanks a million for the feedback.. It is great to get feedback and I appreciate it alot.

For your points:

For (1), will work on getting this down within the addin. I have been a bit wary of this as the addin is primarily designed for retrieving data but as you say, temp tables are often necessary for this. So will look into this and will be back with some points and what I can come up with. Will be back only the weekend though and need a bit of time to think about implemention.. Also, there are other items that are underway and these will get done first however it will be very good to get a plan of action in and maybe it wont be too much work to get a basic version out.

For (2), for passing the parameters from Excel cells or ranges. This probably wont get done too fast as it will take some hours - but it is on the list. You could pass the parameters from the main form but I dont think that is useful to you. The good news is that I am pretty sure you can do this from a different addin. Please look here when you get time http://dbaddin.sourceforge.net/
Roland built this addin and he is here on the site and am pretty sure he will be happy to help you out if you have any questions on it..

For the SQL Excel addin, I think it is best to concentrate on the main form for the time being and once this is working great then move more towards Excel ranges. There is one functionality coming up (VBA code generation) that will enable you to make your own functions though easily from SQL Excel and this in a way will pretty much take care of this type of thing I think.. So with this, you will hit a paste function button and it will paste the ado/vba equivalent code for the sql - will give you much more control that there is at the moment with the addin as you can change the vba to do exactly what you want (pass in ranges etc as parameters etc)

Hope all that makes sense. Thanks again for the feedback and please feel free to post more up. It is very useful and keeps the addin moving forward.
Bye for now,
Al