How to save Load Test reports in SQL Server

Hey Guys, recently I was working on Load Test automation using VS. By default VS stores all your Load Test report in SQLExpress but I wanted to have them stored on SQL Server. I digged hard and found out that following some simple steps it can be attained. I thought of sharing the same idea with you guys so that you dont have to dig in that hard.

So here we go..

Open the VS command prompt and type

cd n:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE.

dont forget to replace n: with the drive where your Visual Studio folder resides.

Once you are into that folder you need to type

SQLCMD /S localhost -i loadtestresultsrepository.sql

“loadtestresultsrepository.sql” contains all the sql scripts that create the “LoadTest” DB and another 24 tables in your sql server.

You also need to give credentials to connect to your DBserver( which in this case is localhost), you can give it using the following syntax

SQLCMD /S localhost -U <user name> -P <password> -i loadtestresultsrepository.sql

Once done with the above steps Click “Administer Test Controllers” under the “Test” Menu.

Click the browse button(…) to select the “LoadTest” DB.

You are now ready to run your Load Test and use your SQLServer Reporting Services to generate custom report.

Happy Testing

Danish Khan

Ramp Technology

Keyword search in SQL Server

Hello guys,

For those who ever felt the need to search for a keyword in the entire DataBase, I have this following script. Run this PROC on the DataBase you wish to make your search on.

It accepts a search string as input parameter, goes and searches all the user created tables (System tables are excluded).

CREATE PROC SearchDB
(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ”
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM     INFORMATION_SCHEMA.TABLES
WHERE         TABLE_TYPE = ‘BASE TABLE’
AND    QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND    OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
AND    TABLE_NAME    = PARSENAME(@TableName, 1)
AND    DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
‘SELECT ”’ + @TableName + ‘.’ + @ColumnName +”’, LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
ENDUpon running this script you can execute it:

EXEC SearchDB ‘<keyword>’

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column

Danish Khan

Ramp Technology