How to import executed queries from SSMS Tools Pack to ApexSQL Complete

Applies to

ApexSQL Complete

Summary

This article explains how to import executed queries from another SSMS Tools Pack into ApexSQL Complete, as it is not compatible with other third party applications/add-ins, and therefore importing executed queries need to be done manually.

Description

For this purpose, we have executed a simple query against AdventureWorks2014 database, on a local server named WIN-055NEUA6K39:

SELECT * FROM Person.Address

The above query has been logged as an executed in both add-ins, and here is the structure for both logging:

SSMS Tools Pack add-in logged this as a txt document:

<ActionLogEntry>

<ComputerName><![CDATA[WIN-055NEUA6K39]]></ComputerName>

<CommandExecutedBy><![CDATA[WIN-055NEUA6K39\Marko]]></CommandExecutedBy>

<DocumentCaption><![CDATA[SQLQuery1.sql – WIN-055NEUA6K39.AdventureWorks2014 (WIN-055NEUA6K39\Marko(55))]]></DocumentCaption>

<CommandExecutedOn><![CDATA[2015-02-21 15:51:08.714]]></CommandExecutedOn>

<QueryText><![CDATA[BEGIN TRAN

SELECT * FROM Person.Address a

ROLLBACK]]></QueryText>

</ActionLogEntry>

In ApexSQL Complete, the same query is logged as an xml document:

<ArrayOfExecutedQuery xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

<ExecutedQuery>

<ServerName>WIN-055NEUA6K39</ServerName>

<Connection>Data Source=WIN-055NEUA6K39;Initial Catalog=AdventureWorks2014;Integrated Security=True;MultipleActiveResultSets=False</Connection>

<ExecBy>WIN-055NEUA6K39\Marko</ExecBy>

<ExecDate>2015-02-21T15:51:10.1175623+01:00</ExecDate>

<QueryDuration>00:00:01.1282336</QueryDuration>

<ExecStatus>Query executed successfully</ExecStatus>

<ExecAgainst>AdventureWorks2014</ExecAgainst>

<QueryText><![CDATA[BEGIN TRAN

SELECT * FROM Person.Address a

ROLLBACK]]></QueryText>

</ExecutedQuery>

</ArrayOfExecutedQuery>

To use the run queries from another add-in, specific information needs to be added to an xml document. For each of the executed query, create a folder in:

C:\Users\<user_name>\AppData\Local\ApexSQL\ApexSQLComplete\ExecutedQueries

The name of the folder should be a date of the query execution (mm-dd-yyyy), as shown in the above image. In each folder, there is an xml file called ExecutedQueries that contains all queries executed on specific date:

The above image represents the way data should be transferred from the SSMS Tools Pack to ApexSQL Complete.

  • Server name, which is under the <ComputerName> tag should be transferred under the <ServerName> tag, and as a value for Data Source in the <Connection> tag
  • Username from the <CommandExecutedBy> tag should be transferred under the <ExecBy> tag
  • Database name from the <DocumentCaption> tag should be transferred as a value for the Initial Catalog
  • Date and time of the execution should be transferred from the <CommandExecutedOn> tag to the <ExecDate> tag
  • SQL code should be transferred from the <QueryText> tag to the same <QueryText> tag

When all the information needed is properly transferred, view the executed query by initiating the Query history feature from the ApexSQL Complete menu:

ApexSQL Complete main menu

In the Query history window, the imported query will be properly shown:

Query history window