[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

Automation of Testing SQL queries - QTP? Other?

I've got a large buch of SQL queries just saved as text files. They are generally record counts, compating tables, looking for data anomolies, validating transformations that kind of thing. Been tasked with finding out if its possible to automate these scripts easily and if so what kind of tools are available to use. I've used QTP before and know that its possible to use database checkpoints and write some vb code to pass arguments etc, but I think this is mainly designed a UI tool rather than being able to run a batch of SQL scripts. Also there may be a licencing contstraint, so if I do suggest this tool, then has to be sold argument. Are there other easier/free tools out there? Is QTP gonna be the best, if so, why? cheers
2 Solutions
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. You need to have a VB application or some application calling these set of queries and including your queries to this application to test it out..

And if you are looking for free tools then this might be of interest to you..


Else you can look at some other cheaper alternatives here:

We have our queries externalized as well.  The queries however are stored in XML files and we have a custom application we wrote to exercise them.  For your situation it would be easy.  

1.  Create a console application (or forms application)
2.  Add a reader class to the application to read the text files in (you can set this to recurse using the .net Get Directories section
3.  Create a custom Test object that has a query, bool pass / fail, and a string error result
4  Once you have all the paths to the files - then read the files and parse each query into a test object and then add the test object to a collection
5.  Create a test manager class to manage the execution of the class
6.  Run a foreach loop to execute each test object and set the values in the test object based on the result (pass /fail, note any errors, etc)
7.  Once complete you could export the test object collection to an xml file or send the results to the screen.

Hope this helps.  

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now