T-SQL Execute stored procedure for newbee

SQL Server, I assume.
The code worked for me...

How are you calling it (application code)

From Query Analyzer I run that exact EXECUTE statement.

What if you run it like this:

EXECUTE CU_SET_SECURITY 154407, 123223, 123223

Ah!  The problem is somewhere in the procedure text .  I changed the procedure to literally as above and it worked for me too.  It looks like this error is hiding the real issue.

Thanks

CREATE  PROCEDURE [DOCSADM].[CU_SET_SECURITY]
@PROF_SYSTEM_ID int,
@AUTHOR int,
@TYPIST int
AS
DECLARE @READ_ONLY int

SELECT @READ_ONLY = 45

UPDATE DOCSADM.PROFILE SET DEFAULT_RIGHTS = 1 WHERE SYSTEM_ID = @PROF_SYSTEM_ID

UPDATE DOCSADM.SECURITY
SET         ACCESSRIGHTS = @READ_ONLY
WHERE PERSONORGROUP NOT IN (@AUTHOR, @TYPIST)
AND        THING = @PROF_SYSTEM_ID

go

As "profile" is a reserved word, you might enclose the table name with [] in your procedure:

CREATE  PROCEDURE [DOCSADM].[CU_SET_SECURITY]
@PROF_SYSTEM_ID int,
@AUTHOR int,
@TYPIST int
AS
DECLARE @READ_ONLY int

SELECT @READ_ONLY = 45

UPDATE DOCSADM.[PROFILE] SET DEFAULT_RIGHTS = 1 WHERE SYSTEM_ID = @PROF_SYSTEM_ID

UPDATE DOCSADM.[SECURITY]
SET        ACCESSRIGHTS = @READ_ONLY
WHERE PERSONORGROUP NOT IN (@AUTHOR, @TYPIST)
AND       THING = @PROF_SYSTEM_ID

However, your procedure works fine for me...

I assume SQL Server 2000...

Yes, 2000 SP3a.  I've enclosed in [] and iI'm still getting this error

can you post the table structure of SECURITY and PROFILE?

CREATE TABLE [PROFILE] (
      [SYSTEM_ID] [int] NOT NULL ,
      [DOCNUMBER] [int] NULL ,
      [DOCNAME] [varchar] (240) COLLATE Latin1_General_CI_AS NULL ,
      [TYPIST] [int] NULL ,
      [AUTHOR] [int] NULL ,
      [DOCUMENTTYPE] [int] NULL ,
      [LAST_EDITED_BY] [int] NULL ,
      [LAST_LOCKED_BY] [int] NULL ,
      [LAST_ACCESS_ID] [int] NULL ,
      [APPLICATION] [int] NULL ,
      [FORM] [int] NULL ,
      [STORAGETYPE] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [RETENTION] [int] NULL ,
      [PROCESS_DATE] [datetime] NULL ,
      [CREATION_DATE] [datetime] NULL ,
      [CREATION_TIME] [datetime] NULL ,
      [LAST_EDIT_DATE] [datetime] NULL ,
      [LAST_EDIT_TIME] [datetime] NULL ,
      [LAST_ACCESS_DATE] [datetime] NULL ,
      [LAST_ACCESS_TIME] [datetime] NULL ,
      [ARCHIVE_DATE] [datetime] NULL ,
      [ARCHIVE_ID] [int] NULL ,
      [KEYSTROKES] [int] NULL ,
      [EDITING_TIME] [int] NULL ,
      [TYPE_TIME] [int] NULL ,
      [BILLABLE] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [FULLTEXT] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [FULLTEXT_DATE] [datetime] NULL ,
      [STATUS] [int] NULL ,
      [DEFAULT_RIGHTS] [int] NULL ,
      [ABSTRACT] [varchar] (254) COLLATE Latin1_General_CI_AS NULL ,
      [PATH] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
      [DOCSERVER_LOC] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
      [DOCSERVER_OS] [int] NULL ,
      [KEYWORDS_DISPLAY] [varchar] (254) COLLATE Latin1_General_CI_AS NULL ,
      [MATTER] [int] NULL ,
      [DJYEAR] [int] NULL ,
      [DJREF] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
      [PERSON_TO] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [ORGANISATION_TO] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [MAIL_MSG_ID] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [MAIL_MSG_IDX] [int] NULL ,
      [COMMENTS] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
      [CURRENTREVIEW] [int] NULL ,
      [DATENEXTREVIEW] [datetime] NULL ,
      [ORIGINALDOC] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [PRECEDENTTYPE] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [REVIEWSTATUS] [int] NULL ,
      [VALUE] [int] NULL ,
      [MAIL_FOLDER] [varchar] (144) COLLATE Latin1_General_CI_AS NULL ,
      [CONTRIBUTOR] [int] NULL ,
      [CATEGORY] [int] NULL ,
      [PRACTICE_AREA] [int] NULL ,
      [CASES] [text] COLLATE Latin1_General_CI_AS NULL ,
      [LEGISLATION] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
      [KNOWHOW] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [DATE_ON_DOCUMENT] [datetime] NULL ,
      [CASE_REF] [varchar] (254) COLLATE Latin1_General_CI_AS NULL ,
      [PRACTICE_AREA_2] [int] NULL ,
      [PRACTICE_AREA_3] [int] NULL ,
      [DJF_FULLTEXT_DATE] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
      [CLIENT] [int] NULL ,
      [DATE_ON_DOCUMENT_N] [datetime] NULL ,
      [READONLY_DATE] [datetime] NULL ,
      [RELATED] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_OBJ_TYPE] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
      [PD_DOC_BARCODE] [int] NULL ,
      [PD_FILE_PART] [int] NULL ,
      [PD_BOX] [int] NULL ,
      [PD_SUSPEND] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_ACTIONBY_DATE] [datetime] NULL ,
      [PD_FAVOURITES] [int] NULL ,
      [PD_SUPSEDED] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_SUPSEDES] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_VITAL] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_MEDIA_TYPE] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
      [PD_ORGANIZATION] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [PD_FILE_DATE] [datetime] NULL ,
      [PD_DATE_CREATED] [datetime] NULL ,
      [PD_PUBLISH_DATE] [datetime] NULL ,
      [PD_EMAIL_BCC] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [PD_EMAIL_CC] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [PD_EMAIL_DATE] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
      [PD_ADDRESSEE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [PD_ORIGINATOR] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
      [PD_CLASSIFICATION] [varchar] (240) COLLATE Latin1_General_CI_AS NULL ,
      [PD_SUPSEDED_DATE] [datetime] NULL ,
      [PD_DOC2CSGMT_LINK] [int] NULL ,
      [PD_ATTACH_CHANGE] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_ACTIONED] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
      [PD_VREVIEW_DATE] [datetime] NULL ,
      [PD_PRTO_ACTION] [int] NULL ,
      [PD_PRTO_AUTHORITY] [int] NULL ,
      [MAIL_ID] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
      [PARENTMAIL_ID] [varchar] (60) COLLATE Latin1_General_CI_AS NULL ,
      [THREAD_NUM] [int] NULL ,
      [ATTACH_NUM] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
      [MSG_ITEM] [int] NULL ,
      [DELIVER_REC] [int] NULL ,
      [PARTIES] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
      [STRING_EMAIL_DATE] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
      [PREV_SERVER_LOC] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
      [PREV_SERVER_OS] [int] NULL ,
      [PD_STATUSES] [int] NULL ,
      [EMAIL_RECEIVED] [datetime] NULL ,
      [EMAIL_SENT] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [SECURITY] (
      [THING] [int] NOT NULL ,
      [PERSONORGROUP] [int] NULL ,
      [ACCESSRIGHTS] [int] NOT NULL
) ON [PRIMARY]
GO


Owner DOCSADM

I cannot reproduce the error. I have to assume that there is some typo error on your side...

I appreciate your help but I have copied and pasted everything so definitely no typo.....

If you reduce the stored procedure, can you identify which statement makes the problem occur. You said that with a minimum procedure the error doesn't appear...

I have just found a trigger on the profile table that when dropped, there is no error.  This trigger is calling the same procedure

You put a lot of time into this question

10x