Showing posts with label system. Show all posts
Showing posts with label system. Show all posts

Friday, March 30, 2012

If my cube don't have a measure how do I design cube ?

suggest I will try to design customer complaint system

to keep information about customer's complaint it will store ex. department , date , complainant , complaint message

I can't figure out which column will be measure

Anyone can guide me about cube that don't have a measure

Thanks for kindness

You can create a "count" measure which simply counts fact table rows, so no particular fact table column is selected for such a measure, For example, if you select "Count of rows" for Usage in the dialog box below, and select the Source table, then Source column selection will be disabled.

http://msdn2.microsoft.com/en-us/library/ms187698.aspx

>>

SQL Server 2005 Books Online

New Measure Dialog Box (SSAS)

...

Usage

Select the aggregation function to be used by the new measure.

Source table

Select the table from which the new measure is to be created.

Source column

Select the column in the table selected in Source table on which the new measure is to be based.

>>

Monday, March 26, 2012

If anybody like know...

i do not understand.

I have this code:

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")

...

_ConnectionPtr Conn;

...

CoInitialize(NULL);

HRESULT hr = Conn.CreateInstance(__uuidof(Connection));

Conn->Provider = "Microsoft.Jet.OLEDB.4.0";

Conn->ConnectionString="Data Source=c:\\mydb.mdb;";

hr = Conn->Open( "","Admin","", adConnectUnspecified);

Whith My account this code work good, but when i try to start my program whith another user login, i have this error:

hr=0x800a0e7a -> Provider cannot be found. It may not be properly installed.

Why by my login program run normal, but by another login failed? OS Xpsp2 whith standart MDAC 2.8 installed.

Tthis is bug: sum keys in registry have no permissions for another users in system.

Wednesday, March 21, 2012

identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".
> Thanks,
> Subbu.
>

identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".

> Thanks,
> Subbu.
>

Friday, March 9, 2012

identity in system tables

does anyone know how sql 2k stores whether a column is an identity column? is it in any system tables or information_schema?

i've read that i can't find the next value of the identity, which i find weird. I just want to know if the column is an identity or not.

Thanks in advance.SELECT COLUMNPROPERTY( OBJECT_ID('table_name'),'column_name','IsIdentity' )|||Originally posted by fattyacid
does anyone know how sql 2k stores whether a column is an identity column? is it in any system tables or information_schema?

i've read that i can't find the next value of the identity, which i find weird. I just want to know if the column is an identity or not.

Thanks in advance.

syscolumns:
status

Bitmap used to describe a property of the column or the parameter:
0x08 = Column allows null values.
0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
0x40 = Parameter is an OUTPUT parameter.
0x80 = Column is an identity column.|||Originally posted by Enigma
SELECT COLUMNPROPERTY( OBJECT_ID('table_name'),'column_name','IsIdentity' )

would you happen to know where sql 2k stores this information?|||Syscolumns

See post by snail above|||thank you

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA

identity field and IDENTITY_INSERT is Off

ok, so i get this error,
Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'TBL_LAPTOP_BOOKINGS' when IDENTITY_INSERT is set to OFF.

i ran SET INDENTITY_INSERT TBL_LAPTOP_BOOKINGS ON before i ran my script... and i also checked, we are running SQL 2000 with service pack four, because of the cursor error...

so im just wondering, is there anyway to make identity_insert to always be on, or any other way to have a unique id in my booking_id field??

Thanks Guys, JustinJust to clarify: did you SET IDENTITY_INSERT YourTable ON in the same connection as the one where you attempted the insert?

I'm not certain that you always want IDENTITY_INSERT set to ON.

Regards,

hmscott

Friday, February 24, 2012

Identity Column Fix

Hello, I am new to SQL and inhereted a system with a corrupt table. About 1000 records have an invalid barcode field which is an identity column and does not allow me to update once set. I need to fix these 1000 records by changing the field to 14 digits instead of the incorrect 13. What are some of my options? I was thinking about making a copy of the DB using the Wizzard, then turning the identity feature OFF and doing the fix using command SQL, then turning it back on and copying the DB back to production.

Any suggestions ? Thx

Copying the entire database seems like overkill for just 1000 rows. I've briefly put together an example below that shows how you can copy the data to a staging table, modify the data, then copy it back into your original table. Hope you find this useful.

Be aware that Foreign Key constraints could prevent you deleting rows from the main table. You'd have to drop the constraints then cascade the changes through to any referencing tables before re-applying the constraints. You'll have to do this regardless of the correction method chosen.

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||

Identity Column Fix

Hello, I am new to SQL and inhereted a system with a corrupt table. About 1000 records have an invalid barcode field which is an identity column and does not allow me to update once set. I need to fix these 1000 records by changing the field to 14 digits instead of the incorrect 13. What are some of my options? I was thinking about making a copy of the DB using the Wizzard, then turning the identity feature OFF and doing the fix using command SQL, then turning it back on and copying the DB back to production.

Any suggestions ? Thx

Copying the entire database seems like overkill for just 1000 rows. I've briefly put together an example below that shows how you can copy the data to a staging table, modify the data, then copy it back into your original table. Hope you find this useful.

Be aware that Foreign Key constraints could prevent you deleting rows from the main table. You'd have to drop the constraints then cascade the changes through to any referencing tables before re-applying the constraints. You'll have to do this regardless of the correction method chosen.

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||