Error during Full-text population

I'm using Full-text in various databases on my servers (SQL2005 on W2K3). On a few databases the Full-text population ends with the error:

'Error '0x80030050' occurred during full-text index population for table or indexed view '[database].[dbo].[table]' (table or indexed view ID '1714105147', database ID '9'), full-text key value 0x00015EE1. Failed to index the row.'

The next log-line lets me know the name of the dll that caused the problem:

The component 'offfilt.dll' reported error while indexing. Component path 'C:\WINDOWS\system32\offfilt.dll'.

There's one solution I read about, but that one is not the case here. That sollution states that this problem occurs when de datatype is not the same as the filetype (e.g. datatype is pdf, documenttype is doc).

What can be the problem here?

Thanks.

I'm getting the same error in a multitude of environments (XP Pro (development/test environment), Windows Server 2003). SQL Server 2005 SP1 has been applied. Also getting on XP Pro system with SP2 applied. However, the OFFFILT.DLL appears to be part of the OS and not SQL Server. The version on my XP Pro is 2003.5.28.0 and is dated 2004-08-04.

I think the problem is with Word (.doc) files as I don't appear to have any problems indexing PowerPoint (.ppt) files.

|||

It sounds like the Microsoft Office Indexing filter DLL is choking on an unrecognised file format. Might you have some very old Word documents in the table, or conversely if there are new Word 2007 documents in there might these be too new?

|||In my case (not the original poster) the files that it is choking on are valid Microsoft Word documents. The documents can be opened in Office 2003. We are avoiding Office 2007 for now. Not every document has a problem. So it is something (style, font, property, ?) in the document that is tripping it up.|||

Ok, it took some time, but here it is:

I had some word-documents with embedded object that were unkown to Word. So I deleted them from the documents. All messages with the error in the original post are gone now. There is however a 'known bug' in Full Text Catalogue (I got the information from Microsoft) which will be patched in SP3. If the Full Text Index is being used very frequent (I'm not sure if that is 500 times a second or 5000000 times...) it gets corrupted.

A few tips here:

Make sure the dll's concerning FTC are in place. You can use this query to see which dll's FTE is using:

select * from sys.fulltext_document_types

Make sure there are no unkown objects in your documents. The error produced, if so, contains a HEX-number which represents the id-column value of the table. You can transform it to a more usable number with this query:

select * from [table] where [id-column] in (cast([HEX-number] as int))

If all this is ok, it's getting more likely that you have the 'known-bug' issue at hand.

Error during Full-text population

I'm using Full-text in various databases on my servers (SQL2005 on W2K3). On a few databases the Full-text population ends with the error:

'Error '0x80030050' occurred during full-text index population for table or indexed view '[database].[dbo].[table]' (table or indexed view ID '1714105147', database ID '9'), full-text key value 0x00015EE1. Failed to index the row.'

The next log-line lets me know the name of the dll that caused the problem:

The component 'offfilt.dll' reported error while indexing. Component path 'C:\WINDOWS\system32\offfilt.dll'.

There's one solution I read about, but that one is not the case here. That sollution states that this problem occurs when de datatype is not the same as the filetype (e.g. datatype is pdf, documenttype is doc).

What can be the problem here?

Thanks.

I'm getting the same error in a multitude of environments (XP Pro (development/test environment), Windows Server 2003). SQL Server 2005 SP1 has been applied. Also getting on XP Pro system with SP2 applied. However, the OFFFILT.DLL appears to be part of the OS and not SQL Server. The version on my XP Pro is 2003.5.28.0 and is dated 2004-08-04.

I think the problem is with Word (.doc) files as I don't appear to have any problems indexing PowerPoint (.ppt) files.

|||

It sounds like the Microsoft Office Indexing filter DLL is choking on an unrecognised file format. Might you have some very old Word documents in the table, or conversely if there are new Word 2007 documents in there might these be too new?

|||In my case (not the original poster) the files that it is choking on are valid Microsoft Word documents. The documents can be opened in Office 2003. We are avoiding Office 2007 for now. Not every document has a problem. So it is something (style, font, property, ?) in the document that is tripping it up.|||

Ok, it took some time, but here it is:

I had some word-documents with embedded object that were unkown to Word. So I deleted them from the documents. All messages with the error in the original post are gone now. There is however a 'known bug' in Full Text Catalogue (I got the information from Microsoft) which will be patched in SP3. If the Full Text Index is being used very frequent (I'm not sure if that is 500 times a second or 5000000 times...) it gets corrupted.

A few tips here:

Make sure the dll's concerning FTC are in place. You can use this query to see which dll's FTE is using:

select * from sys.fulltext_document_types

Make sure there are no unkown objects in your documents. The error produced, if so, contains a HEX-number which represents the id-column value of the table. You can transform it to a more usable number with this query:

select * from [table] where [id-column] in (cast([HEX-number] as int))

If all this is ok, it's getting more likely that you have the 'known-bug' issue at hand.

Error during Full-text population

I'm using Full-text in various databases on my servers (SQL2005 on W2K3). On a few databases the Full-text population ends with the error:

'Error '0x80030050' occurred during full-text index population for table or indexed view '[database].[dbo].[table]' (table or indexed view ID '1714105147', database ID '9'), full-text key value 0x00015EE1. Failed to index the row.'

The next log-line lets me know the name of the dll that caused the problem:

The component 'offfilt.dll' reported error while indexing. Component path 'C:\WINDOWS\system32\offfilt.dll'.

There's one solution I read about, but that one is not the case here. That sollution states that this problem occurs when de datatype is not the same as the filetype (e.g. datatype is pdf, documenttype is doc).

What can be the problem here?

Thanks.

I'm getting the same error in a multitude of environments (XP Pro (development/test environment), Windows Server 2003). SQL Server 2005 SP1 has been applied. Also getting on XP Pro system with SP2 applied. However, the OFFFILT.DLL appears to be part of the OS and not SQL Server. The version on my XP Pro is 2003.5.28.0 and is dated 2004-08-04.

I think the problem is with Word (.doc) files as I don't appear to have any problems indexing PowerPoint (.ppt) files.

|||

It sounds like the Microsoft Office Indexing filter DLL is choking on an unrecognised file format. Might you have some very old Word documents in the table, or conversely if there are new Word 2007 documents in there might these be too new?

|||In my case (not the original poster) the files that it is choking on are valid Microsoft Word documents. The documents can be opened in Office 2003. We are avoiding Office 2007 for now. Not every document has a problem. So it is something (style, font, property, ?) in the document that is tripping it up.|||

Ok, it took some time, but here it is:

I had some word-documents with embedded object that were unkown to Word. So I deleted them from the documents. All messages with the error in the original post are gone now. There is however a 'known bug' in Full Text Catalogue (I got the information from Microsoft) which will be patched in SP3. If the Full Text Index is being used very frequent (I'm not sure if that is 500 times a second or 5000000 times...) it gets corrupted.

A few tips here:

Make sure the dll's concerning FTC are in place. You can use this query to see which dll's FTE is using:

select * from sys.fulltext_document_types

Make sure there are no unkown objects in your documents. The error produced, if so, contains a HEX-number which represents the id-column value of the table. You can transform it to a more usable number with this query:

select * from [table] where [id-column] in (cast([HEX-number] as int))

If all this is ok, it's getting more likely that you have the 'known-bug' issue at hand.

Error Connecting To SQL 2005 through Webservice using SQL Authentication

Running a SQL 2005 Database under a Default instance. i.e. (SERVERNAME) and not (SERVERNAME/SQL2005)

I have a SQL Login sa password.

When I connect to SQL 2005 from anywhere other then the webservice it works fine.

In my webservice i am using System.Data.SqlClient.

SqlConnection, SqlCommand, SqlDataAdapter are being used to called a stored procedure and return the data into a System.Data.DataSet.

I get the following error when calling DataAdapter.Fill(dataSet);

{"TCP Provider: An existing connection was forcibly closed by the remote host.\r\n\r\nLogin failed for user 'sa'."}

I have no resolutions as of yet. Any help would be greatly appreciated.

Did you mean you can connect to the SQL2005 from anywhere with 'sa' login, except webservice? You can check the status of 'sa' login in management studio under Security->Logins, also make sure the Authentication Mode is SQL Server and Windows.|||

I have checked the status of the login for user "sa" and it has access rights and grant levels to everything it needs to.

I have also created a new account specifically for that database which does not work either. Same error.

And yes my SQL 2005 Instance is configured to use both SQL Authentication and Windows Authentication. (Mixed Mode).

Any Ideas?

|||

If you encountered this issue when you're trying to establish encrypted connection , you may take a look at this article:

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Error Code 2259 applying SQL2005 service pack

I have SQL 2005 Express installed. When I try to apply service pack 1 to this by going through the upgrade process, I get an error ' THe installer has encountered an unexpected error. The error code is 2259. Table(s) update failed'

Any ideas? Help!

Ranjit Charles

Ranjit, are you trying to run the Express SP1 refresh package or the full SP1 installer?

Thanks,
Sam Lester (MSFT)

|||

Sam,

I was using the full SP1 installer. I couldn't really find the SP1 refresh ..

Ranjit

|||

Ah, OK. That should be the issue. If you go to the SP1 page at http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en

It has a link midway down the page for the Express upgrades. Here's a snippet...

Overview

Service Pack 1 for Microsoft SQL Server 2005 is now available. You can use these packages to upgrade any of the following SQL Server 2005 editions:

Enterprise Enterprise Evaluation Developer Standard Workgroup

Error catching on data duplication in a sql2005 db

Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum.

I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site.

The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected.

How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address?

Thank you for all your help.

Antonio

you could do something like this in a proc:

declare a parameter @.res of type OUTPUT.

IF NOT EXISTS ( SELET * FROM table WHERE Email = @.EMAIL)

BEGIN

--do the insert

SET @.res = 1

END

ELSE

BEGIN

SET @.res = 2

END

and use the value in @.res in your front end and displaty an appropriate message.

|||

Thank you dinakar. The SQL is working fine. I need help with the front-end. This issue was originally posted on the ASP.NET forum.

Antonio

|||

hi

here's sample code

SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Response.Write(cmd.Parameters["@.ProductName"].Value);
Response.Write(cmd.Parameters["@.Quantity"].Value.ToString());

thanks,

satish

|||You could also use a TRY/CATCH block to catch any exceptions.

Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.

Regards,

Jan

Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.

Regards,

Jan