Columns IDENTITY property

Hi all.
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:

> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>

ColumnName Property on an Attribute

I have a Matter dimension with an OrgUnit attribute, the ColumnKey is Matter.OrgUnitKey and the ColumnName is OrgUnit.Name:

This is the select generated when processing the cube is:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1],[Foundation].[Person] AS [Foundation_Person]

WHERE (([Foundation_Person].[PrimaryOrgUnit1Key] = [Foundation_OrgUnit1].[Key])

AND([Foundation_Matter].[BillingPersonKey]= [Foundation_Person].[Key]))

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

2

Firm 2

2

Firm 3

3

Firm 1

3

Firm 2

3

Firm 3

4

Firm 1

4

Firm 2

4

Firm 3

This is more or less the select I was expecting:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1]

WHERE ([Foundation_Matter].[OrgUnit1Key] = [Foundation_OrgUnit1].[Key])

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

3

Firm 2

4

Firm 3

The question is how is the SQL statement build?

I have defined the following relationships:

Matter.OrgUnitKey = OrgUnit.Key

Matter.BillingPersonKey = Person.Key

Peson.OrgUnitKey = OrgUnit.Key

I can define a select in the DSV to get the Name with the Key but like to avoid it if I can,

Please Help

It looks like you have a loop in your relationships and SSAS has chosen to go through the person table to get to the OrgUnit. Do you have any attributes in your Matter dimension that reference the Person table? If not, you might need to create a named query in the DSV for the OrgUnit table so that there are two instances of it. In this way you could break the realtionship loop into 2 pieces so SSAS would not be able to choose the wrong path.

|||

There is a relationship Matter to Person and Person to OrgUnit.I was trying to avoid going to the DVS, but it seems unavoidable.There is more that one Dimension that will have this problem.

Thank you for you quick response.

Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I am incrementing "User Code" with "column identity" property of MS SQL Server 2K. I have different companies and those companies have different users. When I increment User Code one by one, of course it doesnt consider whether it is the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing column formula or not?Question 1: How can I satisfy this condition below?
EX:
company user
1---1
1---2
2---3
2---4
3---5
What I want is
company user
1---1
1---2
1---3
2---1
2---2
3---1
Question 2: I want to know that whether it is possible to do that by writing column formula or not?

A1 One approach in supporting such a business requirement: one may implement a "key assignment" table that privately tracks and assigns user ID numbers for each company.

A2 It is not exactly clear what is meant by a "column formula"? However, the built in MS Sql Server 2k identity column support / functionality likely won't help much in implementing a typical "key assignment" table. (A "key assignment" table approach, as described in A1, would likely be implemented primarily using stored procedures / triggers, and / or user functions).|||/*
create table companyuser (
"user" int identity(1,1) primary key
,company int not null
)
*/

--ad 1
select
company
,newusernum=(select count(*) from companyuser cu2 where cu1.company=cu2.company and cu1."user"<=cu2."user")
,origusernum="user"
from companyuser cu1

--OR on large table
create table companyuserTMP (
"id" int identity(1,1) primary key
,origusernum int null
,company int not null
)
insert companyuserTMP(company,origusernum)
select company,"user"
from companyuser
order by company,"user"
select
tmp.company
,newusernum=tmp."id"-XXX."id"+1
,origusernum
from companyuserTMP tmp
join (
select "id"=min("id"),company
from companyuserTMP
group by company
) XXX on tmp.company=XXX.company
drop table companyuserTMP

--ad 2-- computed columns can use one row information only, use TR

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I

am incrementing "User Code" with "column identity" property of MS SQL Server

2K. I have different companies and those companies have different users. When

I increment User Code one by one, of course it doesnt consider whether it is

the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing

column formula or not?You may want to add a third column for that information, so that you can have a column with PK for joining other tables to. Are you asking whether a script can be written to modify the user codes after they're entered, or as they're being entered?|||it's ok now, thanx for help

column

Hey,
Is it possible to change a column's Allow Nulls property after the table is created?
Thanks,Yes - using alter table. But you have to fill in null values with something if you are going from NULL to NOT NULL.|||FYI - If you have access to bol (Books Online), most of your questions could be answered - including the one you had earlier on primary key constraints.|||What's this books online?|||Oh yeah, and how would I do it using Alter Table??
thanks,|||If you have access to bol (Books Online)... everybody with an internet connection does, it's, um, online

http://msdn.microsoft.com/library/en-us/dnanchor/html/sqlserver2000.asp

for those without a 24/7 internet connection, you can also download BOL in case you want to look stuff up without dialing up...

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

rudy|||Originally posted by vbgladiator
Oh yeah, and how would I do it using Alter Table??
thanks,

http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_3ied.asp|||Rudy - Thanks for posting those links.

vbgladiator - BOL is an online technical reference utility for SQL Server. I would download it - that gives you guaranteed access. You will find that it is an indispensable reference for your SQL Server questions.|||already did :)

Thanks a lot guys.

Color Property Formatting not working correctly

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

I wish I could help you, however I cannot.

I can however state with confidence that posting 7 times in roughly 1 hour about the same thing is not the best way to get the attention of those who can.

Just my

|||Yea I can't help you either. . .|||

I agree with the sentiment in the above posts - spamming a forum is not the best way to get an answer.

What would also help is if you posted the expression you are using - without it we are just guessing.

Color Property Formatting not working

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

Thanks in advance

If I am not wrong, matrix conditional formatting is applied by default at the details. You may need to do scope it at the right level, e.g.; =Iif(SUM(<field name, "group name")>0, "Red", "Blue"). Also, download your deployed report definition from the Report Manager (General report properties) and make sure the expression is there.