Toughie! Using SET IDENTITY in trigger

Toughie! Using SET IDENTITY in trigger

Post by Gabriel Prefontai » Sat, 15 Aug 1998 04:00:00

Developing in Access, inserting into table 1 with an identity with a
trigger that inserts a record into table 2 also with an identity,

Fixed this by setting up a dummy table 3 with an identity, grabbing

IDENTITY_INSERT ON for table 3 and inserting the original identity

Any user running this gets:
Msg 8104, Level 16, State 1
Current user is not the DBO or object owner for table 'Set_ID', unable
to perform SET operation

I also tried putting the IDENTITY ON statement in a stored procedure
and giving rights to the sp to the user, to no avail.

On-line Help mentions SET IDENTITY use in triggers.  How do I give db
users rights to set this property on my table?

UnionWare Inc.


1. Toughie: multiple ADO recordsets fail using a SP

Using SQL 7.0,  ADO 2.1 from VB

Guys, this is driving me bonkers. I can use ADO to access multiple
recordsets from SP. Works like a charm UNLESS I put anything other than
SELECT statements in my SP. One INSERT statement kills the whole recordset
group - it will not open. rs.NextRecordset can't even be used.

I suspect SQLOLEDB or ADO doesn't know what to do when SQL returns the
results from such a query like:

"2751 row(s) affected."

Is there any way to tell SQL server or the SP not to tell the driver
anything  -- or is there something in ADO I'm missing that I need to
handle -- parameter-wise maybe?? I'm not trying to return any values, just
the multiple SELECT result sets.

Really at wit's end here.

2. Grid cell-level color

3. Set existing int column, IDENTITY option using T-SQL script

4. Can not alloc more than 8Mb in mode Shared Memory

5. Set identity property using T-SQL

6. DAO #6 withowneraccess option & createquerydef

7. Setting identity property using T SQL

8. Saving as delimited...

9. Using a Primary Key without setting as IDENTITY

10. MSSQLServer->Oracle: Identity and returning a result set using oracle procedure or function

11. Using @@Identity after an Insert Trigger occurs

12. Setting the Next identity value on an IDENTITY column