wierd, will give it a go..... here is the Code..this is one of the
iterations, I am sure its the one which is failing...

------------------------------------------
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER  PROCEDURE risp_PROCNAMEME (
        @iMessageID int,        
        @iMessageCancelledByID int,
        @tMessageCancellationText text
)

AS

BEGIN 

DECLARE @iEmailToID int,
           @iNewDeletedID int,
           @iDeletedID INT

SELECT @iEmailToID = CASE 
                                WHEN iMessageToID <> @iMessageCancelledByID
THEN iMessageToID 
                                WHEN iMessageFromID <>
@iMessageCancelledByID THEN iMessageFromID
                                ELSE 0
                        END
FROM tblDiaryMessage
WHERE iMessageID = @iMessageID

SELECT K_SO_ID, VAR_SO_Email1, VAR_SO_Fname, VAR_SO_Lname, VAR_SO_Logon,
VAR_SO_Password
FROM Sign_Ons
WHERE K_SO_ID = @iEmailToID

BEGIN TRANSACTION               

        -- Copy the message details to tblDiaryDeletedMessage
        INSERT INTO tblDiaryDeletedMessage (iDiaryID, iMessageFromID,
iMessageToID, tMessageText, tMessageCancellationText, dMessageStartTime,
dMessageEndTime, dMessageDate)
        SELECT iDiaryID, iMessageFromID, iMessageToID, tMessageText,
@tMessageCancellationText, dMessageStartTime, dMessageEndTime, dMessageDate
        FROM tblDiaryMessage
        WHERE iMessageID = @iMessageID
        
        -- Error Trapping : has an error occured?       
        IF @@ERROR <> 0
                --  trap it and send the SP to the rolback
                GOTO copyMeetingError

        SET @iDeletedID = @@IDENTITY
        
        
        -- Deletre the message from tblDiaryMessage
        DELETE FROM tblDiaryMessage
        WHERE iMessageID = @iMessageID

        -- Error Trapping : has an error occured?       
        IF @@ERROR <> 0
                --  trap it and send the SP to the rolback
                GOTO deleteMeetingError

-- no error 
COMMIT TRANSACTION      

        RETURN 0
        -- Get the deleted message details
        SELECT iDiaryID, iMessageFromID, iMessageToID, tMessageText,
tMessageCancellationText, dMessageStartTime, dMessageEndTime, dMessageDate
        FROM tblDiaryDeletedMessage
        WHERE iDeletedID = @iDeletedID

        

copyMeetingError:
        ROLLBACK TRANSACTION
        RETURN -1

deleteMeetingError:
        ROLLBACK TRANSACTION
        RETURN -1



END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





















-----Original Message-----
From: Tom Smith [mailto:[EMAIL PROTECTED]
Sent: 22 July 2003 11:09
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] CF5 / SP problem


OK, are you getting any large text fields in any of the queries that you are
returning, if so, make them the first query returned, and then test it.
This fixed the problem I was having...

----- Original Message ----- 
From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 22, 2003 11:00 AM
Subject: RE: [ cf-dev ] CF5 / SP problem


> Yes, but we have sp's which return 10 or more recordsets and they work
> fine.. I think it could be either the transaction or the insert into which
> is freaking it out....dunno though, for now it seems to be working where
we
> have split them all out.
>
> -----Original Message-----
> From: Tom Smith [mailto:[EMAIL PROTECTED]
> Sent: 22 July 2003 10:50
> To: [EMAIL PROTECTED]
> Subject: Re: [ cf-dev ] CF5 / SP problem
>
>
> yeah we've had this problem with 2 return queries, and we found that when
we
> ran the sp in query analyser, it was fine, then in CF it would break cf,
but
> the sql server was fine??? are these similar to your simptoms?
> ----- Original Message ----- 
> From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, July 22, 2003 10:45 AM
> Subject: RE: [ cf-dev ] CF5 / SP problem
>
>
> > yep, it was 3 though.  Basically CF does not like it for some
reason...its
> > just freaks.
> >
> > -----Original Message-----
> > From: Tom Smith [mailto:[EMAIL PROTECTED]
> > Sent: 22 July 2003 10:45
> > To: [EMAIL PROTECTED]
> > Subject: Re: [ cf-dev ] CF5 / SP problem
> >
> >
> > are you returning 2 queries from the sp?
> > ----- Original Message ----- 
> > From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Tuesday, July 22, 2003 10:32 AM
> > Subject: RE: [ cf-dev ] CF5 / SP problem
> >
> >
> > > this is what we were doing as well...believe me we have tried
everything
> > and
> > > it always kills CF....if we take the last select out and place it in a
> new
> > > sp or call it inline it works fine...?
> > >
> > > -----Original Message-----
> > > From: Tom Smith [mailto:[EMAIL PROTECTED]
> > > Sent: 22 July 2003 10:28
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: [ cf-dev ] CF5 / SP problem
> > >
> > >
> > > neil,
> > >
> > > create a local var and assign the @@identity to it when in the
> transaction
> > > block, then run the local variable again the second sql statement
> > >
> > > e.g.
> > >
> > > delcare @new_id int
> > > BEGIN TRANSACTION
> > > > INSERT INTO
> > > >    SELECT
> > > >
> > > > DELETE FROM
> > > >
> > > > SELECT @new_id = @@IDENTITY
> > > >
> > > > COMMIT
> > > >
> > > > SELECT WHERE @new_id
> > >
> > > I've not tested this, but it should work for you!
> > >
> > > let us all know if this works for you.
> > >
> > >
> > >
> > > Cheers,
> > >
> > > TOM
> > >
> > > ----- Original Message ----- 
> > > From: "Robertson-Ravo, Neil (RX)"
<[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Tuesday, July 22, 2003 10:11 AM
> > > Subject: [ cf-dev ] CF5 / SP problem
> > >
> > >
> > > > We have an sp which runs AOK in Query Analyser yet when its ran via
> > > > ColdFusion it crashes it - and I mean a total reboot.
> > > >
> > > > I will provide some pseudo code first to see if anyone has had this
> > > > problem..
> > > >
> > > > ---------------------------
> > > > BEGIN TRANSACTION
> > > > INSERT INTO
> > > >    SELECT
> > > >
> > > > DELETE FROM
> > > >
> > > > SELECT @@IDENTITY
> > > >
> > > > COMMIT
> > > >
> > > > SELECT WHERE @@IDENTITY
> > > >
> > > >
> > > >
> > > > The last SELECT seems to be causing the problem as when you remove
it
> > from
> > > > the SP it works AOK....but when its in it freak la sheek's?
> > > >
> > > > Wierd non?
> > > >
> > > > If you need code, let me know and I will post it on.
> > > >
> > > >
> > > >
> > > > -- 
> > > > ** Archive:
http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> > > >
> > > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > > For additional commands, e-mail: [EMAIL PROTECTED]
> > > > For human help, e-mail: [EMAIL PROTECTED]
> > >
> > >
> > > -- 
> > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> > >
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> > > For human help, e-mail: [EMAIL PROTECTED]
> > >
> > > -- 
> > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> > >
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> > > For human help, e-mail: [EMAIL PROTECTED]
> >
> >
> > -- 
> > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> >
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> > For human help, e-mail: [EMAIL PROTECTED]
> >
> > -- 
> > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> >
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> > For human help, e-mail: [EMAIL PROTECTED]
>
>
> -- 
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> For human help, e-mail: [EMAIL PROTECTED]
>
> -- 
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> For human help, e-mail: [EMAIL PROTECTED]


-- 
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]

-- 
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]

Reply via email to