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]