Highlighted
Absent Member.
Absent Member.
5885 views

Running SQL Query from within Excel using OTA?

All,

 

I have some monitoring SQL queries to run against SiteAdmin and/or individual ALM project databases. Management is now asking for a way to run these queries from within Excel. Looked around and noticed everyone using OTA APIs for reporting when it comes to Excel but my problem is different as it has to be SQL query so I am looking for direction how to run SQL query from within Excel. It will be like user having TDAdmin privileges clicking on a button for SQL to run and pull data in same or new spreadsheet

 

PS: I'll be using TDAdmin account to run queries as I dont have a DB account so direct connectivity to DB via ODBC is a no go unfortunately. Any sample code or high level view is highly appreciated. All I need to know is how to execute SQL and save results in Excel assuming TDConnection is used for connection.

 

Thanks!

Tags (1)
0 Likes
5 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

You need to access the Command object of the OTA API to run a SQL query.

 

Sample code is available in the OTA API reference document.

 

Carefully review the Remarks given for the Command object. You will have to set a Site Configuration parameter to enable use of the Command object, and once enabled it is accessible to all users. It can't be restricted on a per user basis.

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Highlighted
Absent Member.
Absent Member.

Thanks Trudy! So it is same approach as if I am running SQL inside workflow code. I see. The confusion now is spitting data out to Excel. Do I need to loop through query results view and write to Excel sheet cell by cell or there is something like query table I can write all at once to an excel spreadsheet?

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

I can't help you with that part. Sorry!

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
0 Likes
Highlighted
Absent Member.
Absent Member.

No worries; thought to post back as FYI that I finally found a thread talking about this issue on SQA Forums. Credit goes to Massimo for the code I am tweaking ... looks like I must do cell-by-cell write using nested loops (which is not too bad). Thanks again!

 

http://www.sqaforums.com/forums/hp-alm-mercury-quality-center/149551-running-sql-query-outside-qc-download-reports-graphs.html

0 Likes
Highlighted

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.