COM+ won't accept Identity for Admin account

People haven't answered my post on 'dev.component', 'programming.component',
etc. I don't know where else to post this..
Hello.
Somehow my Component Services is all messed up on our productions site.
Normally, in test and all other environments I need to click "Identity" in
Com+ and put in "run as" Administrator of some other account with
privileges. It always works fine, except now on our production site it says
"The user account or password entered is not valid. If you entered a domain
account, make sure the name is prefixed with the domain name". Well, I am
positive that the account I am using is correct and has full privileges. I
even created a new account and made it an admin account- then tried that
both with and without the domain account (domain\user). No matter what I
do, it does not accept accounts that are absolutely valid. I've been
working on this problem for some time and have tried everything I can think
of.
Any ideas?Is the SQL server installed on the same box? If not, then that could
be a problem. Is the Windows account mapped to a SQLS login and have
permissions in the database?
-- Mary
MCW Technologies
http://www.mcwtech.com
On Mon, 15 Dec 2003 14:56:17 -0500, "Tom" <none@.none.com> wrote:

quote:

---

>People haven't answered my post on 'dev.component', 'programming.component'
,
>etc. I don't know where else to post this..
>
>Hello.
>Somehow my Component Services is all messed up on our productions site.
>Normally, in test and all other environments I need to click "Identity" in
>Com+ and put in "run as" Administrator of some other account with
>privileges. It always works fine, except now on our production site it say
s
>"The user account or password entered is not valid. If you entered a domai
n
>account, make sure the name is prefixed with the domain name". Well, I am
>positive that the account I am using is correct and has full privileges. I
>even created a new account and made it an admin account- then tried that
>both with and without the domain account (domain\user). No matter what I
>do, it does not accept accounts that are absolutely valid. I've been
>working on this problem for some time and have tried everything I can think
>of.
>Any ideas?
>

---

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
>

column-defulat-value as auto-increment?

Thank you very much for the reply!
well, there is no way to update an identity column,
but is there a way to define a default value to a column
that makes auto-increment?
i tried in the SQL-Server-Enterprize-Manager\Design-Table
to set the default value of a column to MAX(recid_)+1...
any more ideas?
Thanks again,
edo.> well, there is no way to update an identity column,
> but is there a way to define a default value to a column
> that makes auto-increment?
> i tried in the SQL-Server-Enterprize-Manager\Design-Table
> to set the default value of a column to MAX(recid_)+1...
> any more ideas?
You could do this in a trigger, but maybe it might make more sense to
explain why you need this behavior.|||i made this trigger:
CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
FOR INSERT
AS
declare @.i1 int
declare c1 cursor for select Max(recid) from jobs
open c1
fetch next from c1 into @.i1
close c1
deallocate c1
update T1 set recid_=@.i1+1 where recid is NULL
do you think it's the most effecient way?
i doubt because i wonder first, wether i must open a
cursor, and second wether there is no direct way to
update the current inserted record, ruther than
search "where recid is NULL"
thank,
edo.
>--Original Message--
>> well, there is no way to update an identity column,
>> but is there a way to define a default value to a
column
>> that makes auto-increment?
>> i tried in the SQL-Server-Enterprize-Manager\Design-
Table
>> to set the default value of a column to MAX(recid_)
+1...
>> any more ideas?
>You could do this in a trigger, but maybe it might make
more sense to
>explain why you need this behavior.
>
>.
>|||You don't need a cursor:
SET @.i = (SELECT MAX(recid) FROM jobs)
Also, you use the INSERTED table to get the modified row(s).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"edo" <ewilde@.nana.co.il> wrote in message news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
> i made this trigger:
> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
> FOR INSERT
> AS
> declare @.i1 int
> declare c1 cursor for select Max(recid) from jobs
> open c1
> fetch next from c1 into @.i1
> close c1
> deallocate c1
> update T1 set recid_=@.i1+1 where recid is NULL
>
> do you think it's the most effecient way?
> i doubt because i wonder first, wether i must open a
> cursor, and second wether there is no direct way to
> update the current inserted record, ruther than
> search "where recid is NULL"
> thank,
> edo.
> >--Original Message--
> >> well, there is no way to update an identity column,
> >> but is there a way to define a default value to a
> column
> >> that makes auto-increment?
> >>
> >> i tried in the SQL-Server-Enterprize-Manager\Design-
> Table
> >> to set the default value of a column to MAX(recid_)
> +1...
> >> any more ideas?
> >
> >You could do this in a trigger, but maybe it might make
> more sense to
> >explain why you need this behavior.
> >
> >
> >.
> >|||Thanks for your helped,
i implemented your suggestion about the
SET @.i = (SELECT MAX(recid) FROM T1)
but i tried somthing like:
update inserted set recid=1
and got an error:
"the logical tables INSERTED and DELETED can not be
updated."
?
thanks agian,
edo.
>--Original Message--
>You don't need a cursor:
>SET @.i = (SELECT MAX(recid) FROM jobs)
>Also, you use the INSERTED table to get the modified row
(s).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"edo" <ewilde@.nana.co.il> wrote in message
news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
>> i made this trigger:
>> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
>> FOR INSERT
>> AS
>> declare @.i1 int
>> declare c1 cursor for select Max(recid) from jobs
>> open c1
>> fetch next from c1 into @.i1
>> close c1
>> deallocate c1
>> update T1 set recid_=@.i1+1 where recid is NULL
>>
>> do you think it's the most effecient way?
>> i doubt because i wonder first, wether i must open a
>> cursor, and second wether there is no direct way to
>> update the current inserted record, ruther than
>> search "where recid is NULL"
>> thank,
>> edo.
>> >--Original Message--
>> >> well, there is no way to update an identity column,
>> >> but is there a way to define a default value to a
>> column
>> >> that makes auto-increment?
>> >>
>> >> i tried in the SQL-Server-Enterprize-Manager\Design-
>> Table
>> >> to set the default value of a column to MAX(recid_)
>> +1...
>> >> any more ideas?
>> >
>> >You could do this in a trigger, but maybe it might
make
>> more sense to
>> >explain why you need this behavior.
>> >
>> >
>> >.
>> >
>
>.
>|||> update inserted set recid=1
You can't update the inserted / deleted tables.
Maybe you could show your table structure, sample data, and the results you
are trying to achieve, rather than have us reverse engineer your existing
trigger code. It might be that a trigger isn't even necessary for this, or
it might be that you could approach the trigger in a completely different
way. Your narrative a few posts back is difficult to follow, but might be
easier to understand if you show us your actual schema design. There might
be a much more efficient approach to whatever it is you mean by "cloning"...

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 Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.You can create a trigger to set the GroupID to the UserID value.
CREATE TABLE Foo (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
GroupID INT,
datacol CHAR(1));
GO
CREATE TRIGGER SetGroupID
ON Foo
AFTER INSERT
AS
UPDATE Foo
SET GroupID = I.UserID
FROM Foo AS F
JOIN Inserted AS I
ON F.UserID = I.UserID
AND I.GroupID IS NULL;
GO
INSERT INTO Foo (datacol) VALUES('a');
INSERT INTO Foo (GroupID, datacol) VALUES(5, 'b');
SELECT UserID, GroupID, datacol
FROM Foo;
HTH,
Plamen Ratchev
http://www.SQLStudio.com