ServiceDesk

Hi Guys,

As above I am upgrading from 7.0 to 7.1 SD, I currently have a Service Desk Server with a seperate SQL server (2003 Server). I built 3 new 2008 R2 servers for the new environment, 1, Service Desk, 2, NS Box, 3, SQL Server.

I can succesfuly install ITMS7.1 and the Service Desk on its own server, the migration of the CMDB is fine however not so with the Incident/Workflow migration.

I copy the Ensemble DB from the old Environment and place it on my new SQL Server then point the Service Desk install at it and allow it to perform the upgrade. All goes well and the install finishes but when trying to open the service desk portal I am faced with thousands of 'open' incidents that should be 'closed'. If I open one of the incidents they do have a closed status.

Has anyone else managed to perform their SD migration, did you run in to any issues, I would really appreciate any help

Thanks

For what its worth, in 7.0 you couldn't copy the DB around or it messes things up. Server name was hard coded into the DB somewhere, and even if you chose use existing it didn't 'fix' it.

You would hope that was fixed in 7.1 but i wouldn't bet on it at this point.

Agree with above poster... there are several columns of tables in the database that have a URL in them, and changing machine names would complicate things.

All is not lost, though. You can simply backup the database, and try updating the bad vals. The sorta-hard part is finding all the data.

Below is a "sweet data finder" procedure I uncovered on the web somewhere. You could run this to create the stored proc, then call the procedure from a query window to help find the data to clean

--SWEET DATA FINDER
CREATE PROC SearchAllTables
(
    @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
END

--SWEET DATA FINDER
DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #TMP
   (Clmn VARCHAR(500),
   CNT INT)

SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM '
        + TABLE_SCHEMA + '.[' + TABLE_NAME +
       '] WHERE [' + COLUMN_NAME + '] LIKE ''%bike%'' ;'  AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS
   JOIN sysobjects B
   ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
WHERE COLUMN_NAME LIKE '%Name%' AND xtype = 'U'
   AND DATA_TYPE NOT IN ('datetime')

PRINT @SQL
EXEC(@SQL)

SELECT * FROM #TMP WHERE CNT > 0

DROP TABLE #TMP

Thanks for this thats excellent I will give it a go and see how it works out, I'll let you know the results.