Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server Testing with Profiler, SQLCMD and SSIS


By:   |   Last Updated: 2007-11-14   |   Comments (5)   |   Related Tips: More > Testing

Problem
One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify.  During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set.  In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.

Solution
Conducting thorough testing of a database driven application or automated (database) process can easily be a full time job by itself.  Trying to automate the testing process typically results in an application of one type or another in order to streamline manual repetitive steps.  Typically the testing process follows these steps:

  • Identify the applicable code
  • Build test cases
  • Test an application build
  • Verify results
  • Report status to team

Based on this general process, let's see how some of the native SQL Server tools can help with some of these specific processes.

Identify the applicable code - Profiler

The best native tool for capturing the code issued by an application, user or host is Profiler.  SQL Server Profiler has been available for the last few versions of SQL Server and provides a mechanism to capture the T-SQL commands issued against specific SQL Server instance, then save the results to a table or file for further analysis.  In addition, Profiler has the means to filter the data being collected so that only specific users or host traffic is captured.  Here are some quick steps to setup a Profiler session:

Launch SQL Server 2005 Profiler

Navigate to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.

 

Start a New Trace

Navigate to File | New Trace... | Authenticate to the SQL Server you want to monitor

 

General Tab - Configurations

Specify the name and configure the results to be saved to a table.

 

Events Selection Tab - Configurations

In order to review all events, enable the check box for 'Show all events' and 'Show all columns' on the bottom right of the screen.

Depending on how the testing is being conducted, it might make sense to filter the Profiler session by a combination of the application name, host name, login name, spid, etc in order to capture only a specific set of code.

Once you have filtered the settings then select the Profiler events that are needed based on the type of code that is being captured.

Once you have made these configurations or others as desired, then press the 'Run' button to begin the Profiler session.

 

Testing and Profiler Results

Begin to test the application and the results in Profiler will be something like the following:

 

Be sure to review the code captured to ensure only the needed commands are included in your test cases.  It might be a good idea to cross check the code captured versus the steps taken by the user or application.  Then save out the code to separate files based on the logical progression in your application.

In addition, one item to keep in mind here is that the code being captured by Profiler is anything passed to SQL Server.  So this provides the opportunity to separate testing the front end application from the back end code and database.  Be sure to test the front end as well to ensure the front end behaves as expected and does not throw any errors when columns are added\removed, objects change names or security changes.  With the information captured from the Profiler session these scenarios will probably not be caught.

Test an application build - Option 1 - SQLCMD

Once you have the needed code as an input, then the next step is to be able to specify an input file with the code and an output file with the results.  This is where SQLCMD comes into play.  SQLCMD is a Windows command line interface into a SQL Server 2005 instance where you can specify a server name, instance name, database, user name, password, input file, output file, etc.  Let's take a look at an example set of code to call SQLCMD at a Windows command line:

SQL Server 2005 - SQLCMD Example

sqlcmd -S JTKLaptop -d CustomerDB -E -i C:\TestScripts\sqlcmd_input.txt
-o C:\TestScripts\sqlcmd_output.txt

Here is a brief explanation of the code parameters:

  • sqlcmd - Command
  • -S JTKLaptop - SQL Server name and instance (default instance in this example)
  • -d CustomerDB - Database name
  • -E - Trusted user connection
  • -i C:\TestScripts\sqlcmd_input.txt - Input file
  • -o C:\TestScripts\sqlcmd_output.txt - Output file
  • Listing of all of the SQLCMD command line options - sqlcmd Utility

SQLCMD is offers a great deal of functionality beyond the set of parameters listed in this example, but the greatest value may be the simplicity.  Once you have your code captured (input), the learning curve is small to be able to run SQLCMD and determine if the process was successful or not. 

Here are a few tricks with SQLCMD in the input file:

  • Add PRINT statements in order to determine the process
  • If possible, do not add SET NOCOUNT ON statements to your code because you will not be able to determine if rows were inserted, update or deleted
    • If you have SET NOCOUNT ON statements then you may want to issue SELECT statements before and after the code changes to be able to validate the process

Test an application build - Option 2 - SQL Server Integration Services

To turn up the functionality, SQL Server Integration Services (SSIS) is the next native tool available to assist with the automated testing.  SSIS is traditionally an extraction, transformation and loading tool (ETL), but does have the functionality to execute scripts and add conditional logic in a rich graphical interface.  In a nutshell, it is necessary to do the following with SSIS in order to setup a package:

  • Build connections
    • Input data (text file)
    • Output data (text file)
    • SQL Server database
  • Execute the needed scripts
    • The Execute SQL Task might be the best approach to house the T-SQL test cases.
  • Review the output
    • The results could be saved to a table, text file or reviewed as a log for the process.

Here is an example using SSIS package to execute automated testing built in the Business Intelligence Development Studio (BIDS):

SQL Server 2005 Integration Services Example

SSIS is a very powerful tool and has a great deal of functionality.  The explanation in this tip is just the beginning, so check out the SQL Server Integration Services tips category.  Depending on the testing that is needed, SSIS might fit the bill and improve the overall functionality or might be more tool for the task.  You be the judge and consider SQLCMD or SSIS to assist in the testing automation.

Next Steps

  • Depending on the database testing you need to accomplish, using Profiler, SQLCMD and SSIS might be a viable alternatives to meet your needs.
  • Although all of the tools are freely shipped with SQL Server, depending on your proficiency with the tools, you might need to invest some time learning them or work with your DBAs to capture the necessary code then work to automate the process.
  • If you are responsible for testing SQL Server based applications at your organization, we would love to hear about approaches you use to automate the testing.  Please add your comments to the forum via the URL below.
  • Check out these related resources:
  • Stay tuned for an upcoming tip on replaying a Profiler session.
  • Many thanks to everyone who attended the recent session by the Edgewood Solutions team at the Verify conference.  Hopefully we were able to share as much knowledge as we gained from learning about some of your testing challenges with SQL Server.  Hope to see you next year!

 



Last Updated: 2007-11-14


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 12, 2012 - 9:54:32 AM - Jeremy Kadlec Back To Top

Razia,

You would need to have an Execute SQL Task run this code:

To stop a trace sp_trace_setstatus traceid, 0 TraceId would be the value of the trace

To learn more about this, check out this tip - http://www.mssqltips.com/sqlservertip/1035/sql-server-performance-statistics-using-a-server-side-trace/.

Thank you,
Jeremy Kadlec
Co-Community Leader


Wednesday, December 12, 2012 - 5:16:15 AM - Razia Back To Top

Hi

Any idea if we can start/stop profile trace through SSIS?

Thanks


Sunday, July 15, 2012 - 1:09:58 PM - Jeremy Kadlec Back To Top

Dinesh,

I assume your question is related to SQLCMD not SSIS.  If that is the case, then in your input file for your SQLCMD file, why not use a INSERT...SELECT command or SELECT...INTO command to load the data into a new table.  Here are some URLs as a point of reference:

http://www.mssqltips.com/sqlservertutorial/2522/insert-into-sql-server-table-with-select-command/

http://www.mssqltips.com/sqlservertutorial/2523/insert-into-new-sql-table-with-select-into/

HTH.

Thank you,
Jeremy Kadlec


Thursday, July 12, 2012 - 8:42:16 AM - Dinesh Vishe Back To Top

I am getting data in csv format but I want to in Ecle or table format ...

Please help me..


Monday, July 09, 2012 - 3:52:18 AM - Dinesh Vishe Back To Top

If i want sql cmd result in mssql table then what to do ???

Please help me...


Learn more about SQL Server tools