Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

SELECT 1 AS [dbo_FactInternetSalesReason0_0],[SalesReasonKey] AS [dbo_FactInternetSalesReasonSalesReasonKey0_1]
FROM [dbo].[FactInternetSalesReason]
)
AS [dbo_FactInternetSalesReason]
Processing Mining Structure 'Customer Mining'.
Start time 5/6/2007 1:45:40 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/6/2007 1:45:40 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application. Certificate is used to encrypt the data transmission. Certificates are stored locally for the users on the computer

...
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

From the end of the SQL Server 2005 Express Reporting Services install, I would get the following message. (I am not sure about the SQL Server 2005 Developer Edition Reporting Services install. I did not keep notes.)

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

"
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

Nontheless, this is the 'best lead' on this mystery so far. I will see if SQL Server 2005 has some 'install logs' on both the 'computer that the 'Data Mining' processing does not work, and the computer that the 'Data Mining' processsing does work.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

--

Note 2, about the possible 'no digital certificate problem,' I seem to have one and only one digital cerificate in my Certificates (Local Computer) \ Personal \ Certicates Store. This is untrusted. This has all the required extended attributes. This digital certificate shows up in the Digital Certificate for Protocols for <instance> Properties. However, this selection is CLEAR. This digital certificate is NOT SELECTED.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:36:08 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Processing Mining Structure 'Customer Mining'.
Start time 5/26/2007 3:58:33 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/26/2007 3:58:33 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM (HANGS HERE INDEFINITELY)

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Processing Cube 'Customer Clusters ~MC' failed.
Start time: 5/26/2007 4:16:25 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:02
Processing Measure Group '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Processing Measure Group 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors in the OLAP storage engine: An error occurred while processing the 'Internet ~1 ~MG' partition of the 'Internet ~1 ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

Error 1 Internal error: The operation terminated unsuccessfully. 0 0
Error 2 Internal error: The operation terminated unsuccessfully. 0 0
Error 3 OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'.. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0
Error 5 Internal error: The operation terminated unsuccessfully. 0 0

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Is it possible, that I may need a better build of SQL Server 2005 Developer's Edition? I am using amazon.com: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2)

On another machine where Adventure Works DW will process ...I have the following: SQL Server 2005 Enterprise Components + MSDN: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2).

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

I think we've reached the point where a support analyst would really need to drill deeper. Could you contact MS product support with this issue? They will talk you the process of providing a minidump and other information that would help to identify the issue here.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

SELECT 1 AS [dbo_FactInternetSalesReason0_0],[SalesReasonKey] AS [dbo_FactInternetSalesReasonSalesReasonKey0_1]
FROM [dbo].[FactInternetSalesReason]
)
AS [dbo_FactInternetSalesReason]
Processing Mining Structure 'Customer Mining'.
Start time 5/6/2007 1:45:40 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/6/2007 1:45:40 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application. Certificate is used to encrypt the data transmission. Certificates are stored locally for the users on the computer

...
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

From the end of the SQL Server 2005 Express Reporting Services install, I would get the following message. (I am not sure about the SQL Server 2005 Developer Edition Reporting Services install. I did not keep notes.)

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

"
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

Nontheless, this is the 'best lead' on this mystery so far. I will see if SQL Server 2005 has some 'install logs' on both the 'computer that the 'Data Mining' processing does not work, and the computer that the 'Data Mining' processsing does work.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

--

Note 2, about the possible 'no digital certificate problem,' I seem to have one and only one digital cerificate in my Certificates (Local Computer) \ Personal \ Certicates Store. This is untrusted. This has all the required extended attributes. This digital certificate shows up in the Digital Certificate for Protocols for <instance> Properties. However, this selection is CLEAR. This digital certificate is NOT SELECTED.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:36:08 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Processing Mining Structure 'Customer Mining'.
Start time 5/26/2007 3:58:33 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/26/2007 3:58:33 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM (HANGS HERE INDEFINITELY)

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Processing Cube 'Customer Clusters ~MC' failed.
Start time: 5/26/2007 4:16:25 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:02
Processing Measure Group '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Processing Measure Group 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors in the OLAP storage engine: An error occurred while processing the 'Internet ~1 ~MG' partition of the 'Internet ~1 ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

Error 1 Internal error: The operation terminated unsuccessfully. 0 0
Error 2 Internal error: The operation terminated unsuccessfully. 0 0
Error 3 OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'.. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0
Error 5 Internal error: The operation terminated unsuccessfully. 0 0

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Is it possible, that I may need a better build of SQL Server 2005 Developer's Edition? I am using amazon.com: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2)

On another machine where Adventure Works DW will process ...I have the following: SQL Server 2005 Enterprise Components + MSDN: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2).

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

I think we've reached the point where a support analyst would really need to drill deeper. Could you contact MS product support with this issue? They will talk you the process of providing a minidump and other information that would help to identify the issue here.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

SELECT 1 AS [dbo_FactInternetSalesReason0_0],[SalesReasonKey] AS [dbo_FactInternetSalesReasonSalesReasonKey0_1]
FROM [dbo].[FactInternetSalesReason]
)
AS [dbo_FactInternetSalesReason]
Processing Mining Structure 'Customer Mining'.
Start time 5/6/2007 1:45:40 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/6/2007 1:45:40 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application. Certificate is used to encrypt the data transmission. Certificates are stored locally for the users on the computer

...
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

From the end of the SQL Server 2005 Express Reporting Services install, I would get the following message. (I am not sure about the SQL Server 2005 Developer Edition Reporting Services install. I did not keep notes.)

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

"
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

Nontheless, this is the 'best lead' on this mystery so far. I will see if SQL Server 2005 has some 'install logs' on both the 'computer that the 'Data Mining' processing does not work, and the computer that the 'Data Mining' processsing does work.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

--

Note 2, about the possible 'no digital certificate problem,' I seem to have one and only one digital cerificate in my Certificates (Local Computer) \ Personal \ Certicates Store. This is untrusted. This has all the required extended attributes. This digital certificate shows up in the Digital Certificate for Protocols for <instance> Properties. However, this selection is CLEAR. This digital certificate is NOT SELECTED.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:36:08 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Processing Mining Structure 'Customer Mining'.
Start time 5/26/2007 3:58:33 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/26/2007 3:58:33 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM (HANGS HERE INDEFINITELY)

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Processing Cube 'Customer Clusters ~MC' failed.
Start time: 5/26/2007 4:16:25 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:02
Processing Measure Group '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Processing Measure Group 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors in the OLAP storage engine: An error occurred while processing the 'Internet ~1 ~MG' partition of the 'Internet ~1 ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

Error 1 Internal error: The operation terminated unsuccessfully. 0 0
Error 2 Internal error: The operation terminated unsuccessfully. 0 0
Error 3 OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'.. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0
Error 5 Internal error: The operation terminated unsuccessfully. 0 0

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Is it possible, that I may need a better build of SQL Server 2005 Developer's Edition? I am using amazon.com: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2)

On another machine where Adventure Works DW will process ...I have the following: SQL Server 2005 Enterprise Components + MSDN: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2).

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

I think we've reached the point where a support analyst would really need to drill deeper. Could you contact MS product support with this issue? They will talk you the process of providing a minidump and other information that would help to identify the issue here.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families.
"Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers
.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNu
m1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's name
s
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped i
n
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advance SQL question

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
-----
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf
3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
--
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
...

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan|||Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>> [1 zitierte Zeile ausgeblendet]
> Yes, it is possible:
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> This queries work with up to 250 words in each row.
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>

Adult Content Filter

I want to create a message board designed for a church. Their concern was if people were to type in offensive words; they didn't want to display that particular post.

Does anyone have suggestions on the best way to run through a particular block of text, searching if an offensive word was present and then omitting the post?

Obviousily the front end would be a web app, but the back end dbase for this is M$ SQL Server 2000.

Any ideas or comments would be appreciated!U need a dictionary in your database, and a fulltext search engine to deal with the posted text, to find if the words in the dictionary appear in the text, and also the count of them. Then to decide if the text is clean.|||I would look to perl, ruby, or python for this solution. Perl handles strings very well and would be a natural for this task. You could do this on your web server and reject the post or give the author a chance to correct the indiscretion if there was a problem.|||Paul,
The webserver is running Win2K and IIS 5.0. Do you think some ASP code would work? What would be the most effiecient place for it to process? Client, Webserver or DBase side?

Thanks for the response.|||First of, I am NOT a WEB developer so I may not be the best person to answer this question.

The way I see this problem is you need to take a list of words and pass them over some text to see if you get a match. This does not sound like something a DB could do efficiently. I am 100% posative I could setup a DB solution in less than a day but I suspect that using Perl and Regulare Expresions would be a better answer. If you haven't used Perl or RegEx. both excel at string manipulation.

As for running the code on the client or web server, I personaly would choose the web server. It seems like the deployment and maintinance would be simplified.|||Thanks for the info. I will post with the solution I come up with. Then maybe some has some ideas on whether they can come up with something better!

Thanks again.