Hi all,

I have a stored procedure which executes a piece of SQL to inherit security from one object to another, in this case folders to documents in my document management system. This SQL executes fine when I hard code the passed in variables and run it in Query Analyser, but does not run in the SP.

The problem section is commented --add any other access rights to the document

All other parts of the SP run correctly. Any ideas?

create procedure DOCSADM.SP_AddToFolder
@foldernumber int,
@docnumber int

AS

DECLARE @v_new_system_id INT
DECLARE @v_next INT
DECLARE @v_isfirst varchar(1)
DECLARE @v_displayname varchar(128)
DECLARE @v_author INT
DECLARE @v_doc_system_id INT
DECLARE @v_folder_system_id INT
DECLARE @v_count INT

-- Make sure it is not already added
IF NOT EXISTS (SELECT SYSTEM_ID FROM DOCSADM.FOLDER_ITEM WHERE PARENT=@foldernumber AND DOCNUMBER=@docnumber)
BEGIN

--Get the new systemid
BEGIN TRANSACTION
exec docsadm.sp_nextkey 'SYSTEMKEY'
set @v_new_system_id=(select LASTKEY from DOCSADM.DOCS_UNIQUE_KEYS where TBNAME='SYSTEMKEY')
commit

        -- Check if it is the first item to be added
        IF NOT EXISTS(SELECT SYSTEM_ID FROM DOCSADM.FOLDER_ITEM WHERE PARENT=@foldernumber)
        BEGIN
                SET @v_isfirst='Y'
                SET @v_next=0
        END
        ELSE
        BEGIN
                SET @v_isfirst='N'
                --update the next column so it points to the new system id
                UPDATE DOCSADM.FOLDER_ITEM SET NEXT=@v_new_system_id where PARENT=@foldernumber AND next=0
        END

-- get the displayname, same as docname
SELECT @v_displayname=DOCNAME, @v_author=AUTHOR, @v_doc_system_id=SYSTEM_ID FROM DOCSADM.PROFILE WHERE DOCNUMBER=@docnumber

-- get the systemid of the folder
SELECT @v_folder_system_id=SYSTEM_ID FROM DOCSADM.PROFILE WHERE DOCNUMBER=@foldernumber

-- Check if the default marker needs updating on the profile
select @v_count = COUNT(*) FROM DOCSADM.SECURITY where thing = @v_folder_system_id and personorgroup not in
(select personorgroup from docsadm.security where thing = @v_doc_system_id)
IF @v_count > 0
BEGIN
    UPDATE PROFILE SET DEFAULT_RIGHTS = 1 WHERE SYSTEM_ID = @v_doc_system_id
END

--add any other access rights to the document
insert into DOCSADM.SECURITY (THING, PERSONORGROUP, ACCESSRIGHTS)
select @v_doc_system_id, PERSONORGROUP, ACCESSRIGHTS from docsadm.security
where thing = @v_folder_system_id and personorgroup not in
(select personorgroup from docsadm.security where
thing = @v_doc_system_id and ACCESSRIGHTS <> 255)

--Insert the new folder item
INSERT INTO DOCSADM.FOLDER_ITEM
(SYSTEM_ID,NODE_TYPE,PARENT,PARENT_LIBRARY,REMOTE_ SYSTEM_ID,ISFIRST,NEXT,LIBRARY,DOCNUMBER,VERSION_T YPE,VERSION,
PARENT_VERSION,DISPLAYNAME,HAS_PROPS,READONLY_DATE ,PREV_VERSION_TYPE,STATUS,FOLDERDOCNO_RO,FOLDERREM LIB_RO)
VALUES
(@v_new_system_id,'D',@foldernumber,0,0,@v_isfirst ,0,0,@docnumber,'R',0,
@foldernumber,@v_displayname,'',1/1/1753,'',0,0,0)

END

GO

Matt Eustace
Technical Architect
Attevo LTD

Any error messages?

Can you verify that your variables that work in QA are getting passed to the SP correctly to that section?

Hi Matt,

Can you also post how you called the SP with parameter values? When you say it is not executing that INSERT statement in question, what other ways did you try to verify that?

Can you comment that INSERT statement alone and see if the SELECT statement results any rows? Hope that helps
Cheers

_________________________
- Vijay G
Strive for Perfection

Hi and thanks for the replies. I call the SP using a trigger which passes in two variables. I know these are passed through because they are used in the second insert (which adds the file to the folder). Anyway, to ensure that I was getting everything I need, I simplified the SP a little and I started writing data out to temp tables by altering the SQL as follows;


CREATE procedure DOCSADM.SP_AddToFolder
@foldernumber int,
@docnumber int

AS

DECLARE @v_new_system_id INT
DECLARE @v_next INT
DECLARE @v_isfirst varchar(1)
DECLARE @v_displayname varchar(128)
DECLARE @v_author INT
DECLARE @v_doc_system_id INT
DECLARE @v_folder_system_id INT
DECLARE @v_count INT

-- Make sure it is not already added
IF NOT EXISTS (SELECT SYSTEM_ID FROM DOCSADM.FOLDER_ITEM WHERE PARENT=@foldernumber AND DOCNUMBER=@docnumber)
BEGIN

--Get the new systemid
BEGIN TRANSACTION
exec docsadm.sp_nextkey 'SYSTEMKEY'
set @v_new_system_id=(select LASTKEY from DOCSADM.DOCS_UNIQUE_KEYS where TBNAME='SYSTEMKEY')
commit

        -- Check if it is the first item to be added
        IF NOT EXISTS(SELECT SYSTEM_ID FROM DOCSADM.FOLDER_ITEM WHERE PARENT=@foldernumber)
        BEGIN
                SET @v_isfirst='Y'
                SET @v_next=0
        END
        ELSE
        BEGIN
                SET @v_isfirst='N'
                --update the next column so it points to the new system id
                UPDATE DOCSADM.FOLDER_ITEM SET NEXT=@v_new_system_id where PARENT=@foldernumber AND next=0
        END

-- get the displayname, same as docname
SELECT @v_displayname=DOCNAME, @v_author=AUTHOR, @v_doc_system_id=SYSTEM_ID FROM DOCSADM.PROFILE WHERE DOCNUMBER=@docnumber

-- get the systemid of the folder
SELECT @v_folder_system_id=SYSTEM_ID FROM DOCSADM.PROFILE WHERE DOCNUMBER=@foldernumber

-- Check if security needs applying
select @v_count = DEFAULT_RIGHTS FROM DOCSADM.PROFILE WHERE SYSTEM_ID = @v_doc_system_id
If @v_count < 1 OR @v_count IS NULL
BEGIN
    select @v_count = DEFAULT_RIGHTS FROM DOCSADM.PROFILE WHERE SYSTEM_ID = @v_folder_system_id
    If @v_count = 1
    BEGIN
        UPDATE PROFILE SET DEFAULT_RIGHTS = 1 WHERE SYSTEM_ID = @v_doc_system_id

        CREATE TABLE ##secdata (
        Activity int,
        Thing int,
        PersonOrGroup int,
        AccessRights int )

        CREATE TABLE ##vars (
        dataVal int,
        dataName char(30) )

        insert into ##vars(dataVal, dataName) VALUES(@v_doc_system_id, 'docsystemid')
        insert into ##vars(dataVal, dataName) VALUES(@v_folder_system_id, 'foldersystemid')

        insert into ##secdata(Thing, PersonOrGroup, AccessRights, Activity) select @v_doc_system_id, PERSONORGROUP, ACCESSRIGHTS, 1 from docsadm.security
        where thing = @v_folder_system_id

        insert into ##secdata(Thing, PersonOrGroup, AccessRights, Activity) select @v_doc_system_id, PERSONORGROUP, ACCESSRIGHTS, 2 from docsadm.security
        where thing = @v_folder_system_id and personorgroup <> 0

        --add any other access rights to the document
        BEGIN TRANSACTION
        insert into DOCSADM.SECURITY(THING, PERSONORGROUP, ACCESSRIGHTS) select @v_doc_system_id, PERSONORGROUP, ACCESSRIGHTS from docsadm.security where thing = @v_folder_system_id and personorgroup <> 0
        COMMIT

        insert into ##secdata(Thing, PersonOrGroup, AccessRights) select @v_doc_system_id, PERSONORGROUP, ACCESSRIGHTS from docsadm.security
        where thing = @v_folder_system_id and personorgroup <> 0
    END
END

--Insert the new folder item
INSERT INTO DOCSADM.FOLDER_ITEM
(SYSTEM_ID,NODE_TYPE,PARENT,PARENT_LIBRARY,REMOTE_ SYSTEM_ID,ISFIRST,NEXT,LIBRARY,DOCNUMBER,VERSION_T YPE,VERSION,
PARENT_VERSION,DISPLAYNAME,HAS_PROPS,READONLY_DATE ,PREV_VERSION_TYPE,STATUS,FOLDERDOCNO_RO,FOLDERREM LIB_RO)
VALUES
(@v_new_system_id,'D',@foldernumber,0,0,@v_isfirst ,0,0,@docnumber,'R',0,
@foldernumber,@v_displayname,'',1/1/1753,'',0,0,0)

END
GO

This now produces the following in the vars temp table;

95685 docsystemid
92295 foldersystemid

and in the secdata table

1 95685 9163 47
1 95685 28225 255
1 95685 55024 45
2 95685 9163 47
2 95685 28225 255
2 95685 55024 45
NULL 95685 9163 47
NULL 95685 28225 255
NULL 95685 55024 45

But STILL nothing goes into the security table! I am left with the only option that this must be some kind of security restriction (you can see that I have even tried ensuring that the transaction is commited).

The security table itself is very simple, only contains three columns of int data type. The owner of the SP and the table are the same. I am just off to check whether changing the service account that is used by SQL Server Agent is OK, but I am highly perplexed by this one.

Thanks for reading if you got this far.

Matt Eustace
Technical Architect
Attevo LTD

I feel it is better to debug this using print/select statements using query anlayser and then use that in the trigger. Let us know here if you find some clue on this.

Cheers.

_________________________
- Vijay G
Strive for Perfection

You start a transaction, but where do you rollback it or commit it?

I can see that Matt just COMMITs it somewhere in the middle, and not checking for errors to roll it back.

_________________________
- Vijay G
Strive for Perfection