Products
Webtrends Analytics 8.5
Webtrends Analytics 8.7
Webtrends Analytics 9.x
Cause
Somewhere in the wtMaster or wt_sched databases is a reference to an old server name or other information that is causing problems with the current installation or attempts at upgrading, but the table and field name for this information cannot be located.
Resolution
Make a backup of the database that to be searched and restore it into a non-Webtrends MS SQL installation. Create a new query and copy the attached stored procedure text into the new query. Change the “[DatabaseName]” to “[wtMaster]” or “[wt_sched”, depending on the database used, and then execute the query.
Next, create a new query and enter the following where “” is the name of the database being searched and “” is the data being searched for:
USE
execute searchalltables ”
GO
Click on “Execute”. All of the tables with associated fields that match the search criteria will be listed in the results.
More Information
/***** usage – execute searchalltables ” *****/
/***** for “DatabaseName” below, insert the name of the database to create the stored procedure in *****/
USE [DatabaseName]
GO
/****** Object: StoredProcedure [dbo].[SearchAllTables] Script Date: 09/25/2008 04:55:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[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