This is a migrated thread and some comments may be shown as answers.

Stored Procedures as coded step

17 Answers 293 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

Amardeep asked on 10 Jan 2014, 10:22 AM

Hi,

I want use the stored procedure as a Setup coded step where I want to attach database from server location to machine where I am running the test and upgrade it. After that I want to run my recorded steps.

How can I implement that?

Regards
A

using System.Data.SqlClient;

CREATE PROCEDURE SP_UpgradeDatabaseTest @RegressionRunID int

AS

BEGIN

DECLARE @CMDSQL varchar(1000),

@DatabaseID int,

@DatabaseName varchar(250),

@CurrentLocation varchar(250),

@TargetLocation varchar(250),

@Result int

SET @TargetLocation = 'C:\UpgradeDatabaseTest\'

-- Tidy up previous run... detach databases

DECLARE Database_cursor CURSOR

FOR

SELECT name FROM sys.databases

JOIN DatabasesForUpgrading ON name = DatabaseName

OPEN Database_cursor

FETCH NEXT FROM Database_cursor

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC sp_detach_db @DatabaseName

FETCH NEXT FROM Database_cursor INTO @DatabaseName

END

CLOSE Database_cursor

DEALLOCATE Database_cursor

EXEC master..xp_cmdshell 'Del C:\UpgradeDatabaseTest\*.mdf'

EXEC master..xp_cmdshell 'Del C:\UpgradeDatabaseTest\*.ldf'

-- Get list of databases to upgrade, copy, attach, and upgrade

DECLARE Database_cursor CURSOR FOR SELECT DatabaseID, DatabaseName, Location FROM DatabasesForUpgrading WHERE Upgrade =1

OPEN Database_cursor

FETCH NEXT FROM Database_cursor

INTO @DatabaseID,

@DatabaseName,

@CurrentLocation

WHILE @@FETCH_STATUS = 0

BEGIN

-- copy database to expected location

DECLARE @PassedVariable VARCHAR(100)

SET @CurrentLocation = @CurrentLocation + RTRIM(@DatabaseName) + '*.*'

SET @CMDSQL = 'C:\NR_UpgradeDatabaseDBRepository\CopyFiles.bat ' + @CurrentLocation + ' ' + @TargetLocation

EXEC master..xp_cmdshell @CMDSQL

-- Attach Database to Server

SELECT @CMDSQL = 'CREATE DATABASE ' + @DatabaseName +

' ON (FILENAME = ''' + @TargetLocation + @DatabaseName + '.mdf''' + '),

(FILENAME = ''' + @TargetLocation + @DatabaseName + '_Log.ldf'')

FOR ATTACH'

EXECUTE (@CMDSQL)

-- Run Upgrade Database

SET @CMDSQL = '"c:\program files (x86)\Athene\Data Management\Upgrade Database" DW-E5530,' + @DatabaseName

EXEC master..xp_cmdshell @CMDSQL

--Check Result of upgrade

SET @CMDSQL = 'findstr /c:"The database has been updated" c:\programdata\metron\logs\*' + @DatabaseName + '*.log'

EXEC @Result = master..xp_cmdshell @CMDSQL

IF @Result = 0

-- Success

INSERT RR_UpgradeDatabase (RegressionRunID, DatabaseID, Result)

VALUES (@RegressionRunID, @DatabaseID, 1)

ELSE

-- Failure

INSERT RR_UpgradeDatabase (RegressionRunID, DatabaseID, Result)

VALUES (@RegressionRunID, @DatabaseID, 0)

FETCH NEXT FROM Database_cursor

INTO @DatabaseID,

@DatabaseName,

@CurrentLocation

END

CLOSE Database_cursor

DEALLOCATE Database_cursor

END

17 Answers, 1 is accepted

answered on 20 Jan 2015, 11:07 PM

Boyan,

I tried using the coded step as you suggested, but it times out before the stored procedure finishes running and forcefully closes the connection. Using the timeout in the connection string also had no effect.

////Define a new SQL connection with a connection string.

////The connection string will be different depending on your environment and the name of the database, table, etc.

////See http://www.connectionstrings.com for connection string examples.

SqlConnection thisConnection = new SqlConnection("Server=st_db;Database=QualityAssurance;"

+ " User Id=xxxxxxxxx; Password=xxxxxxxxx;"

+ " Connection Timeout=600");

thisConnection.Open();

////Write the name of the database to the log.

//Log.WriteLine(thisConnection.Database);

//Create a SQL command to insert a new value into the "City" column.

SqlCommand thisCommand = thisConnection.CreateCommand();

thisCommand.CommandText = "EXECUTE [QualityAssurance].[dbo].[CreateWPIData] "

+ " @ApplicationName = 'Just' "

+ " ,@ApplicationVersion = 'Delete' "

+ " ,@TestPlanName = 'It' "

+ " ,@TestSetName = 'All' "

+ " ,@DeleteIntegrationRecords = 'Y' "

+ " ,@DeleteWrExpertDataClaims = 'Y' "

+ " ,@ResetIntegrationCompany = 'Y' "

+ " ; ";

thisCommand.ExecuteNonQuery();

answered on 02 Feb 2015, 05:29 PM

applicationlog_sptimeout.zip

Boyan,

I have included both the application log and the test log in the attached zip file.
Additionally, I created a new procedure that intentionally takes 5 minutes to complete to duplicate the issue in any database you desire. The code for this stored procedure is below.

-- =======================================

-- Author: Bryan Boutwell

-- Create date: 2015-02-02

-- Description: Sample stored procedure that intentionally takes

-- 5 minutes to complete to demonstrate Telerik

-- Test Studio issue with executing long running

-- stored procedures.

-- =======================================

CREATE PROCEDURE [dbo].TELERIK_TIMEOUT

AS

BEGIN

WAITFOR DELAY '0:5:00';

END

GO

answered on 10 Feb 2015, 03:17 PM

codedstepsample.zip

Boyan,

To clarify, the error is actually NOT being thrown by SQL. The connection is being forcibly closed by Telerik. My assumption is that there is a timeout configuration in Telerik that is causing the connection to be forcibly closed even though the stored procedure is still running.

A sample project is attached. You will need to perform the following database steps on your end to make it run.
1 - create the stored procedure "TELERIK_TIMEOUT" in the database you want to run the test against.

-- =======================================

-- Author: Bryan Boutwell

-- Create date: 2015-02-02

-- Description: Sample stored procedure that intentionally takes

-- 5 minutes to complete to demonstrate Telerik

-- Test Studio issue with executing long running

-- stored procedures.

-- =======================================

CREATE PROCEDURE [dbo].TELERIK_TIMEOUT

AS

BEGIN

WAITFOR DELAY '0:5:00';

END

GO

2 - update the connection string in the coded step to connect to the correct database.