Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Thursday, March 22, 2012

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
Bala
Don't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:
>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>sql

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Monday, March 19, 2012

Errors in the OLAP storage engine: The attribute key cannot be found

I'm somewhat new to using SQL server analaysis services so bear with me on this post.

I am getting the following error when trying ot process a simple cube I built:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: Invoice_x0020_Date, Value: 3/7/2004 10:00:00 AM.

This cube use an Invoice table as the fact table and then a "geography" table as a dimensions table. I am using the server built time dimension which seems to be the problem. It seems to not like the "Invoice Date" field which is a datetime field. This appears to happen on imported data from a legacy application where that application didn't store time so we defaulted the time to 10:00 AM. My guess is that the Invoice Date field has to be unique? If that is the case, any idea on how to get aroudn that?

Thanks for your time,

Marc

Hi,

The error means that the '3/7/2004 10:00:00 AM' value from the 'dbo_vwInvoice' fact table cannot be found in the time dimension table. You mentioned that the time dimension is the server generated one. The server time dimensions only go down to the day level (not hour, minute and second), thus the value will indeed not be found.

If you do not need to do analysis on the hour/minute/second level (in other words, if you do not use the time part of the 'Invoice Date' from from the 'dbo_vwInvoice' fact table), then you can do this:

- create a calculated column (in the DataSourceView, you don't need to alter the actual relational table) in the 'dbo_vwInvoice' fact table to only keep the date part of the 'Invoice Date' column

- then join the fact table (in the 'Dimension Usage' tab of the cube editor) with the time dimension table by that calculated column

Adrian Dumitrascu

|||

You know, I was kind of thinking that but I couldn't find any documentation stating that. Thanks for the response. I would have thought it would inheritly figure out that the field is a datetime field and to just extract the date portion. Easy enough to fix though.

Thanks again,

Marc

|||

Well I spoke too soon. I'm still getting the error. I did a convert(varchar(10),invoice date,101) as the calculated field and still came up with this errror:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: InvoiceDateOnly, Value: 3/29/1998. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Date of Dimension: TimeDimension from Database: WinSTIS Analysis Services Project, Cube: Winstis, Measure Group: Vw Invoice, Partition: Vw Invoice, Record: 11.

Any ideas?

Thanks,

Marc

|||

Sorry for the confusion. I figured it out. It was because I didn't have a large enough date range on the time dimension...

Marc

Sunday, March 11, 2012

Errors in the encryption library Error

I'm getting the following error:

Error 74 Errors in the encryption library: Failed to encrypt sensitive data. Possibly the encryption key is inaccessible because of improper service account change. 0 0

I've tried deleting the database and directory from the data directory. This doesn't fix it. I still get the same error. However if I change the name of the target and deploy the problem is resolved.

I'd like to retain the original name for the database if I can. Could somebody explain why it's got this problem with the original name and how I go about fixing it. Thanks.

Mark

Try backing up all rest of the databases on your server. Then stop the service and delete everything from the data directory.
You can see the location of your data directory in DataDir server property.

Start the service, restore rest of your databases and at this point you should be able to depoy your database under original name.

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

Errors in OLAP storage Engine: The attribute Key cannot be found: URGENT

Hi,

Okay, I am stuck here with this error and other related errors. before anything I will describe the scenario. Please do tell me What is wrong, How it happened and how to correct it.

Initially I had 3 relational tables in SQL server.
1. Dim_A (col1a, col2a, col3a)
2. Dim_B (col1b, col2b)
3. Fact_data (col1a,Col1b)

There are no Primary keys, foreign keys and relationships defined in the SQL relational database between these tables.

I created a OLAP database by:
1. creating the datasource
2. creating the datasource view and setting the logical primary keys and relationships as follows:
a. Fact_data.col1a references -> Dim_A.col1a
b. Fact_data.col1b references -> Dim_B.col1b
3. Next, I created a Cube, without automatically creating attributes or hierarchies (no auto build)
4. A Measure Group was automatically created, with a measure called Fact_data count (which i think is the count of the number of rows and was equal to 145). I checked the relational table "Fact_data" to find that the fact table had indeed 145 rows.
5. Next I built the project with not errors, then deployed and processed it.
6. I had no errors and I was able to browse the cube in the SQL BI studio.

After all this, I had created a asp.net page to issue the "Process" command to the complete OLAP database. (I had used the Process Script XMLA from the BI studio). This was also running perfectly.

Now the problem, suddeny next day when i ran the Process from my Asp.Net page, it gave me errors saying that there are Errors in the OLAP Storage engine. The attribute Key cannot be found, with the Table name, Column name and the value. It also gave other errors saying: The record was skipped because the attribute key was not found. it gave the info of the attribure, Dimension, database, cube, measure group and Partition.

I checked the SQL relational tables to find a change in the "Fact_data" table structure. Here is the changed structure of the Fact_data table:

Fact_data (col1a,Col1b,colNewC)

A new column "colNewC" was added to the table. Actually this was a new table with the same name as the old one with a new column. the old table was renamed and probably changed by some other person.
so i thought that the change in structure was the problem.

I created a new Analysis services project in the SQL BI studio, repeated the steps stated above to create a OLAP database. Then I built the project with no erros.
Next while trying to deploy and process the project. It is giving me the same errors. Attribute Key not found. I am neither able to deploy only or deploy and process.

Next I changed the error handling settings and set it to convert the not found keys to unknown. now it gave me esactly 145 errors, with the No attribute keys found. but with deployment and processing complete.
Now trying to browse the cube gives no records at all.

I analyzed the tables and its records, I felt that this was because the records present in the Fact_data table had no matching records in the Dim_A table. or specifically there were no matching "col1a" columns between them. thus all the 145 rows of the Fact_data table were shown as errors.


My question is:
1. So is this only a data in-consistency issue?
2. When somebody changed the Fact_data table structure, they also changed the data which had no matching columns with the dim_A table? is this correct? and hence the errors.
3. The only possible way to correct this is to populate the correct records, is this right?

Sorry for the lengthy post, but i thought it would describe the scenario better. Please do give a detailed answer for this and also how to correct it.

Thanks and regards

Try defining primary key-foreign key relationships in your SQL database.

This should help you to deal with referential integrity errors you are seeing.

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

|||

When running the XMLA Script run the default refresh of the dimesions first then process the cube for a full refresh..

The Error means that the Fact tables foriegn key doest match with the Primary key of the Dim Table AKA the lookup table (DIM) doest have a value to match with the DIM Table.Hope this Explains.

If there are lotsa invalidated data in the fact table change the Error procession configuration to notify and continue.

|||

I got exactly the same error message as your post here when I tried to process and deploy the cube in BIDS, did the answer posted here help you solve the problem above?

I want to knwo if that helped solve the problem then I may try this solution.

With best regards,

Yours sincerely,

Errors in OLAP storage Engine: The attribute Key cannot be found: URGENT

Hi,

Okay, I am stuck here with this error and other related errors. before anything I will describe the scenario. Please do tell me What is wrong, How it happened and how to correct it.

Initially I had 3 relational tables in SQL server.
1. Dim_A (col1a, col2a, col3a)
2. Dim_B (col1b, col2b)
3. Fact_data (col1a,Col1b)

There are no Primary keys, foreign keys and relationships defined in the SQL relational database between these tables.

I created a OLAP database by:
1. creating the datasource
2. creating the datasource view and setting the logical primary keys and relationships as follows:
a. Fact_data.col1a references -> Dim_A.col1a
b. Fact_data.col1b references -> Dim_B.col1b
3. Next, I created a Cube, without automatically creating attributes or hierarchies (no auto build)
4. A Measure Group was automatically created, with a measure called Fact_data count (which i think is the count of the number of rows and was equal to 145). I checked the relational table "Fact_data" to find that the fact table had indeed 145 rows.
5. Next I built the project with not errors, then deployed and processed it.
6. I had no errors and I was able to browse the cube in the SQL BI studio.

After all this, I had created a asp.net page to issue the "Process" command to the complete OLAP database. (I had used the Process Script XMLA from the BI studio). This was also running perfectly.

Now the problem, suddeny next day when i ran the Process from my Asp.Net page, it gave me errors saying that there are Errors in the OLAP Storage engine. The attribute Key cannot be found, with the Table name, Column name and the value. It also gave other errors saying: The record was skipped because the attribute key was not found. it gave the info of the attribure, Dimension, database, cube, measure group and Partition.

I checked the SQL relational tables to find a change in the "Fact_data" table structure. Here is the changed structure of the Fact_data table:

Fact_data (col1a,Col1b,colNewC)

A new column "colNewC" was added to the table. Actually this was a new table with the same name as the old one with a new column. the old table was renamed and probably changed by some other person.
so i thought that the change in structure was the problem.

I created a new Analysis services project in the SQL BI studio, repeated the steps stated above to create a OLAP database. Then I built the project with no erros.
Next while trying to deploy and process the project. It is giving me the same errors. Attribute Key not found. I am neither able to deploy only or deploy and process.

Next I changed the error handling settings and set it to convert the not found keys to unknown. now it gave me esactly 145 errors, with the No attribute keys found. but with deployment and processing complete.
Now trying to browse the cube gives no records at all.

I analyzed the tables and its records, I felt that this was because the records present in the Fact_data table had no matching records in the Dim_A table. or specifically there were no matching "col1a" columns between them. thus all the 145 rows of the Fact_data table were shown as errors.


My question is:
1. So is this only a data in-consistency issue?
2. When somebody changed the Fact_data table structure, they also changed the data which had no matching columns with the dim_A table? is this correct? and hence the errors.
3. The only possible way to correct this is to populate the correct records, is this right?

Sorry for the lengthy post, but i thought it would describe the scenario better. Please do give a detailed answer for this and also how to correct it.

Thanks and regards

Try defining primary key-foreign key relationships in your SQL database.

This should help you to deal with referential integrity errors you are seeing.

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

|||

When running the XMLA Script run the default refresh of the dimesions first then process the cube for a full refresh..

The Error means that the Fact tables foriegn key doest match with the Primary key of the Dim Table AKA the lookup table (DIM) doest have a value to match with the DIM Table.Hope this Explains.

If there are lotsa invalidated data in the fact table change the Error procession configuration to notify and continue.

|||

I got exactly the same error message as your post here when I tried to process and deploy the cube in BIDS, did the answer posted here help you solve the problem above?

I want to knwo if that helped solve the problem then I may try this solution.

With best regards,

Yours sincerely,

Friday, March 9, 2012

Errors adding a column in 64-bit version

I have run into an interesting problem. I have some code that adds a column to an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL 2005 it works fine. It fails on the 64-bit version of 2005 intermittently with a "could not create unique index because duplicate values were found". Kind of odd, considering as an identity field it's creating the values. I was able to recreate the problem with the following schema:

Create table test1

(col1 varchar(20),

col2 varchar(20),

col3 uniqueidentifier default newid())

-- insert data

Declare @.counter int

set @.counter=1

While @.counter < 1000000

BEGIN

insert into test1

values ('Joe','Smith',default)

Set @.counter=@.counter+1

END

-- add column

Alter table test1 add col4 int constraint PK_test1 primary key clustered identity

After Running this, I get this error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28).

Anybody else run into this? Why would this be happening, and is there any way to fix it? I'm running the 64-bit version of Windows 2003 and the the 64-bit version of SQL Server 2005.

Thanks in advance,

Mark

Could you please file a bug with these details / repro steps at the MSDN Product Feedback Center? I do not have a 64-bit m/c to try this out. Also, please mention the editions of Windows and SQL Server (x64, IA, EMT). Thanks.

Sunday, February 26, 2012

error:an item with the same key has already been added

hi.
i just moved to the april ctp beta 2 version of dts(i know it's already late to do that). i created a new project and moved the packages i did on beta 1 into it. after building it, and when i run the dtsintall.exe, it got this error : an item with the same key has already been added (mscorlib).
what i did is excluded all the packages from the project and ran it for one package it worked..gradually i added a package after the other and build it and it worked..then suddenly the error appeared again and i can't get rid of it even when i go back to my initial state of 2 packages.. i can't understand why it's giving it.

i'm sure someone must have faced this problem. please help!

Thanks
ChristinaHi Christina,

One of the problems could have been that the packages use XML configurations that have the same file name.

This has been addressed for CTP16.

regards,
ash|||Hi Ash. Thanks for your reply.
Is it the file name or the configuration name?
If i change the configuration type will it work?

Thanks
Christina

Friday, February 24, 2012

error: when insert into table

Violation of PRIMARY KEY
constraint 'PK__tablename__182C9B23'.
Cannot insert duplicate key in object 'tablename'.
The statement has been terminated.
Thanks reply
The error that you are getting is related to the violation of "PRIMARY KEY"
constraint. To know what primary key is defined on the table run the
following statment.
sp_pkeys <table name>
Make sure that you are inserting non duplicate values in the primary key.
ex:
create table t(idd int primary key)
insert into t values (1)
insert into t values (2)
insert into t values (3)
insert into t values (3) --error
in above case 4th insert into will fail because of duplication of primary
key value.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com