Fuzzy Lookup error - Multiple Identity Columns?

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.

Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.

It's like it's trying to add it's own Id to the temp table created for the tokens

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

I have found an interesting solution to this problem, but I'm still left wondering if this is by design or error. The reference table must be in the dbo schema to work. I began with a reference table in a schema owned by me. I finally moved to another DB on the same server and it worked. The difference I then noticed was that the table was now in dbo. I went back to the original development DB and changed ownership on my schema to dbo. I ran the package and got the same error. So I then moved the table to dbo and it worked just fine. If anybody knows why this might be, I'd really like to know. If you'd like to see where I started out, get a copy of Hands-On SQL Server 2005 Integration Services by Ashwani Nanda. In Chapter 10 there is a good section on removing duplicates.

Unaswered questions

Is it a permission issue, a DB confituration issue or the design of the Fuzzy Lookup or an error.

It is interesting that if I remove the ID column (which uses identity(1,1))from my reference table in my schema it will work, but its sort of the point to be able to go back to the duplicate records for updates, inserts or deletes etc.

Fuzzy Lookup

How is it possible to get the Unmatched records on the error path of lookup so that they may be passed to a fuzzy lookup. I'm trying to duplicate the 1st example on the Data Cleansing Webcast.

All I'm getting is a null on the title out of the lookup component.

TIA,
GUYCheck out method 2: http://www.sqlis.com/default.aspx?311|||Can you post a link to the SSIS data cleansing webcast?

|||Here you go.. http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20050512SQLServerDF/manifest.xml

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.