Inserting value to Identity Column

I want all users in database to be able to call Set IDENTITY_INSERT
[table] = ON
However when a user who is not the dbo logs in to execute the stored
procedure I get the following error message.
prcIdentityFudgeSave, Line 8
The current user is not the database or object owner of table
'IdentityFudge'. Cannot perform SET operation.
Please Help
(See Stored Prc below that the users are trying to execute)
CREATE PROCEDURE prcIdentityFudgeSave
(
@.IdentityBeforeTrigger int
) AS
--Fudge the Identity Column
delete From IdentityFudge
SET IDENTITY_INSERT IdentityFudge ON
Insert Into IdentityFudge (IndentityFudgeID) values
(@.IdentityBeforeTrigger)
GO> I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
Then why are you using IDENTITY at all?|||give permission to the SQL Server login [SQL Server authentication] / Window
login [Windows Authentication] to acess the table and sp.
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
> However when a user who is not the dbo logs in to execute the stored
> procedure I get the following error message.
> prcIdentityFudgeSave, Line 8
> The current user is not the database or object owner of table
> 'IdentityFudge'. Cannot perform SET operation.
> Please Help
> (See Stored Prc below that the users are trying to execute)
>
> CREATE PROCEDURE prcIdentityFudgeSave
> (
> @.IdentityBeforeTrigger int
> ) AS
> --Fudge the Identity Column
> delete From IdentityFudge
> SET IDENTITY_INSERT IdentityFudge ON
> Insert Into IdentityFudge (IndentityFudgeID) values
> (@.IdentityBeforeTrigger)
> GO
>|||Joel,
I think that is not enough. See "SET IDENTITY_INSERT (Permissions)" in BOL.
AMB
"Joel Leong" wrote:

> give permission to the SQL Server login [SQL Server authentication] / Wind
ow
> login [Windows Authentication] to acess the table and sp.
> "dermot" <dfrench@.tommyfrench.co.uk> wrote in message
> news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>
>|||If the value in this column has meaning to your users then don't use
IDENTITY. Why would you want to do this?
David Portas
SQL Server MVP
--

Inserting on Partitioned View

Hello, I'm trying to insert on a partitioned view and I get this error:

INSERT INTO dbo.TREP_NOVEDADES
( NMSEC_NOVEDAD,
CDCONCEPTO,
CDTIPO_VALOR,
CDPRECIO,
CDTIP_HECT_DTO_PAG,
FEGENERACION,
FEPAGO_COBRO,
CDMETODO_DISTRIBUC,
CDTIPO_DCTO_PAGO,
VRDESCUENTO_PAGO,
SNTERCERO,
CDCLASE_NOVEDAD,
CDENTIDAD,
CDESTADO,
CDCAUSA,
NMSEC_PREFIJO,
SNPAGO_CAJA,
SNVAL_PROPOR_PESO,
CDTIP_HECT_DISTRIB,
CDMONEDA,
CDCOMPANIA,
NMSEC_NOVEDAD_PADR,
SNGENERADA,
POEMBARGO,
CDTERCERO,
CDCIA_TERCERO,
NMSEM_GENERACION,
NMANO_GENERACION,
NMPER_GENERACION,
NMSEM_PAGOCOBRO,
NMANO_PAGOCOBRO,
NMPER_PAGOCOBRO,
CDTERCERO_CAJA,
CDSUCURSAL_CAJA,
CDCOMPANIA_CAJA)
VALUES ( 3904,
'C01',
'V',
null,
null,
GETDATE(),
GETDATE(),
null,
null,
25999,
'N',
'F',
'016',
'01',
'000001',
null,
'N',
'N',
null,
'PESOC',
'01',
null,
'N',
null,
null,
null,
null,
2007,
null,
null,
null,
null,
null,
null,
null)

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'TREP_NOVEDADES' is not updatable because a partitioning column was not found.
-

This is the tables information:

CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN2006] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2006_NMANOGENER_CK] CHECK ([NMANO_GENERACION] <= 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2006_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2006_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2006_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2006_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
GO
CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2007_NMANOGENER_CK] CHECK ([NMANO_GENERACION] > 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2007_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2007_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2007_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2007A_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
go
CREATE VIEW TREP_NOVEDADES
AS
SELECT * FROM dbo.TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM dbo.TREP_NOVEDADES_2007

My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.

Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.

Chris

|||

Chris Howarth wrote:

My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.

Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.

Chris

Hello, I think It's possible to do an UPDATE or INSERT into the view and I'll get my row modified or inserted into the correct base table, depending of the CHECK condition I created before on every base table without creating any trigger...

Isn't It possible..? Do I necessarily have to create the trigger ?

Thanks|||

I think you're right - check out the 'Updatable Partitioned Views' section in the page at this link:

http://msdn2.microsoft.com/en-us/library/aa933141(SQL.80).aspx

There are several criteria that have to be met for INSERTs/UPDATEs/DELETEs to function correctly against this type of View - one of which is that the partitioning column must be part of each of the tables' Primary Key.

If you can't meet the requirements laid out in the above document then your next best option would be to go down the INSTEAD OF TRIGGER route.

Chris