How to Fix Embedded Spira Image URLs When You Change Server URLs

Overview

When you install SpiraTest, SpiraTeam or SpiraPlan on a server, you can either install it on the root of the webserver (in which case it will be http://server), or you can install it under a virtual director (e.g. http://server/SpiraTest or http://server/SpiraTeam). Similar for cloud customers, when you have a demo instance, your URL will be https://demo-us.spiraservice.net/myname and in production you would have https://myname.spiraservice.net).

Normally this doesn't matter. However if you paste images / screenshots into the rich text editors inside Spira, you will be embedding a relative URL that looks something like:

<img src="/Support/Attachment/179.aspx">

this will be a problem if you change the URL of your server from SpiraTeam (in this example) to SpiraPlan. All the URLs will no longer work. Similar if you move from a demo cloud instance to a production once, you may get the same issue.

Solutions

One obvious solution is to just edit the IMG tags and fix the URLs. That can be time consuming and laborious, plus you may easily forget to fix certain ones manually. The good news is that there is another solution.

We have put together the following SQL that can be run to fix these kinds of issue.

Fixing Test Step's broken image URL in a SAMPLE Data rich text field

In this example we are going to fix the SAMPLE DATA rich text column of the TEST STEPs list:

but it will work in the same way for the requirements, test cases, task, descriptions, etc:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT TEST_STEP_ID, PATINDEX('%<img src="/SpiraTeam/%', SAMPLE_DATA) AS n
        FROM TST_TEST_STEP)
    UPDATE TST_TEST_STEP
    SET SAMPLE_DATA = STUFF(SAMPLE_DATA, q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_TEST_STEP.TEST_STEP_ID = q.TEST_STEP_ID AND q.n != 0;
    IF @@ROWCOUNT = 0 BREAK;
END;

Note that in this example, we are changing SpiraTeam to SpiraPlan.

The equivalent code to switch SpiraTest to SpiraTeam would be:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT TEST_STEP_ID, PATINDEX('%<img src="/SpiraTest/%', SAMPLE_DATA) AS n
        FROM TST_TEST_STEP)
    UPDATE TST_TEST_STEP
    SET SAMPLE_DATA = STUFF(SAMPLE_DATA, q.n, LEN('<img src="/SpiraTest/'), '<img src="/SpiraTeam/')
    FROM q
    WHERE TST_TEST_STEP.TEST_STEP_ID = q.TEST_STEP_ID AND q.n != 0;
    IF @@ROWCOUNT = 0 BREAK;
END;

Fixing Test Step's broken image URL in a EXPECTED Result rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT TEST_STEP_ID, PATINDEX('%<img src="/SpiraTeam/%', EXPECTED_RESULT) AS n
        FROM TST_TEST_STEP)
    UPDATE TST_TEST_STEP
    SET EXPECTED_RESULT = STUFF(EXPECTED_RESULT, q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_TEST_STEP.TEST_STEP_ID = q.TEST_STEP_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Note, that while typing a Rich Text field name you should have that field name listed as a prompt in SQL Management Studio query window:

Fixing Requirement's broken image URL in a DESCRIPTION rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT REQUIREMENT_ID, PATINDEX('%<img src="/SpiraTeam/%', [DESCRIPTION]) AS n
        FROM TST_REQUIREMENT)
    UPDATE TST_REQUIREMENT
    SET [DESCRIPTION] = STUFF([DESCRIPTION], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_REQUIREMENT.REQUIREMENT_ID = q.REQUIREMENT_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Fixing Requirement's broken image URL in a COMMENT rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT [DISCUSSION_ID], PATINDEX('%<img src="/SpiraTeam/%', [TEXT]) AS n
		FROM [dbo].[TST_REQUIREMENT_DISCUSSION])
    UPDATE [dbo].[TST_REQUIREMENT_DISCUSSION]
    SET [TEXT] = STUFF([TEXT], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE [dbo].[TST_REQUIREMENT_DISCUSSION].[DISCUSSION_ID] = q.[DISCUSSION_ID] AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Fixing Incident's broken image URL in a DESCRIPTION rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT INCIDENT_ID, PATINDEX('%<img src="/SpiraTeam/%', [DESCRIPTION]) AS n
        FROM TST_INCIDENT)
    UPDATE TST_INCIDENT
    SET [DESCRIPTION] = STUFF([DESCRIPTION], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_INCIDENT.INCIDENT_ID = q.INCIDENT_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Fixing Incident's broken image URL in a COMMENT rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT [INCIDENT_RESOLUTION_ID], PATINDEX('%<img src="/SpiraTeam/%',[RESOLUTION]) AS n
		FROM [dbo].[TST_INCIDENT_RESOLUTION])
    UPDATE [dbo].[TST_INCIDENT_RESOLUTION]
    SET [RESOLUTION] = STUFF([RESOLUTION], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE [dbo].[TST_INCIDENT_RESOLUTION].[INCIDENT_RESOLUTION_ID] = q.[INCIDENT_RESOLUTION_ID] AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

IMPORTANT NOTE:
all the other artifact comment tables end in _DISCUSSION; only the Incident's is ends as _RESOLUTION.

Fixing Test Case's broken image URL in a DESCRIPTION rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT TEST_CASE_ID, PATINDEX('%<img src="/SpiraTeam/%', [DESCRIPTION]) AS n
        FROM TST_TEST_CASE)
    UPDATE TST_TEST_CASE
    SET [DESCRIPTION] = STUFF([DESCRIPTION], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_TEST_CASE.TEST_CASE_ID = q.TEST_CASE_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Fixing Test Case's broken image URL in a COMMENT rich text field

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT ARTIFACT_ID, PATINDEX('%<img src="/SpiraTeam/%', [TEXT]) AS n
		FROM [dbo].[TST_TEST_CASE_DISCUSSION])
    UPDATE TST_TEST_CASE_DISCUSSION
    SET [TEXT] = STUFF([TEXT], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE TST_TEST_CASE_DISCUSSION.ARTIFACT_ID = q.ARTIFACT_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

Fixing broken image URL in a CUSTOM PROPERTY rich text field - applies to ALL artifacts

Changing <img src="/SpiraTeam /Support/Attachment/179.aspx"> to SpiraPlan:

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT ARTIFACT_ID, PATINDEX('%<img src="/SpiraTeam/%', [CUST_02]) AS n
		FROM [dbo].[TST_ARTIFACT_CUSTOM_PROPERTY])
    UPDATE [dbo].[TST_ARTIFACT_CUSTOM_PROPERTY]
    SET [CUST_02] = STUFF([CUST_02], q.n, LEN('<img src="/SpiraTeam/'), '<img src="/SpiraPlan/')
    FROM q
    WHERE [dbo].[TST_ARTIFACT_CUSTOM_PROPERTY].ARTIFACT_ID = q.ARTIFACT_ID AND q.n != 0;
	IF @@ROWCOUNT = 0 BREAK;
END;

If you are cloud hosted, we can run these scripts for you to fix the data in the same way.