I should have mentioned this.in my original email.
The server this is running on is a MSSQL 7.0 server. (changing it is not an
option)
I've tried running this code below in Query Analyser. It throws the
following error:
-------------------------------------------------------------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@temp_table'.
-------------------------------------------------------------------
I've found an entry for it in the MSSQL server books online, but the entry
on this was _very_ brief and not very helpful.
I've tried it just as you gave the code in QA and also wrapping into a
stored proc, but it always throws the same error.
Am I doing something extraordinarily wrong, or is this just something that
doesn't work with this version of MSSQL?
Regards
Darren Tracey
Systems Analyst
HR Systems and FastTrack, Web and Integration Services
p: + 61 7 3232 4091 (x64091)
f: + 61 7 3232 4744
e: [EMAIL PROTECTED]
l: Lvl 3, 388 Queen St Brisbane QLD 4000
m: Suncorp IPC IT048, GPO Box 1453, Brisbane QLD 4000
> -----Original Message-----
> From: Scott Thornton
> Sent: Wednesday, 16 March 2005 7:17 AM
> To: CFAussie Mailing List
> Subject: [cfaussie] Re: SOT: MSSQL Trigger/Stored Proc/Temp Table
> problem
>
> perhaps you could use a variable table, eg
>
> declare @temp_table table (idfield integer, someotherfield varchar(10))
> select * from @temp_table
>
> Scott Thornton, Programmer
> Application Development
> Information Services and Telecommunications
> Hunter-New England Area Health Service
> Phone RNH +61 2 49236078 JHH +61 2 49214193
> Fax +61 2 49214191
>
> [EMAIL PROTECTED]
> >>> [EMAIL PROTECTED] 03/15/05 6:04 PM >>>
> Hi all.
> This is a problem that one of the other people at Suncorp had with some
> SQL
> that eventually gets called from some CF code.
> I've got a Stored Procedure that gets called from a whole bunch of
> places,
> including a few triggers.
> Code has been added to the stored proc that makes a temporary table,
> does
> some processing and returns some values.
> A flag is passed to tell it whether these new return values are
> required.
> This flag is never set to indicate that return values are required in
> the
> cases where the stored Proc is called from a trigger.
> Logic in the Stored proc bypasses all the code that creates, uses and
> removes the temorary table, when the flag does not indicate that
> returned
> values are required.
> Everything workls fine until the stored proc is called by a trigger,
> upon
> which it returns this error:
> (this was produced from MSSQL Enterprise manager)
>
> UPDATE TTxnTIdeaAcct SET SavedAmt = 1200
> WHERE IdeaAcctIDNo = 26840
>
> Produces:
>
>
> (66 row(s) affected)
>
> Server: Msg 287, Level 16, State 2, Procedure ap_PhasingAmountCalc, Line
> 216
> The CREATE TABLE statement is not allowed within a trigger.
> Server: Msg 287, Level 16, State 1, Procedure ap_PhasingAmountCalc, Line
> 374
> The DROP TABLE statement is not allowed within a trigger.
>
> The logic in the stored proc should not allow any of this code to be
> run,
> yet SQL seems to be parsing the code irespective of logic flow.
> The logic has been changed to 'if 1=0' around the temporary table
> creation
> code, so that there can be no way that it can be run, yet it still
> causes
> this error.
>
> Does anyone know of some clever way that this can be made to work, or do
> I
> need to go back to the drawing board?
>
> Regards
>
> Darren Tracey
> Systems Analyst
> HR Systems and FastTrack, Web and Integration Services
> p: + 61 7 3232 4091 (x64091)
> f: + 61 7 3232 4744
> e: [EMAIL PROTECTED]
> l: Lvl 3, 388 Queen St Brisbane QLD 4000
> m: Suncorp IPC IT048, GPO Box 1453, Brisbane QLD 4000
>
>
>
>
> --------------------------------------------------------------------------
> ---------
> This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one
> of its related entities ("Suncorp").
>
> Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on
> 13 11 55 or at suncorp.com.au.
>
> The content of this e-mail is the view of the sender or stated author
> and does not necessarily reflect the view of Suncorp. The content,
> including attachments, is a confidential communication between Suncorp
> and the intended recipient. If you are not the intended recipient, any
> use, interference with, disclosure or copying of this e-mail, including
> attachments, is unauthorised and expressly prohibited. If you have
> received this e-mail in error please contact the sender immediately and
> delete the e-mail and any attachments from your system.
>
> If this e-mail constitutes a commercial message of a type that you no
> longer wish to receive please reply to this e-mail by typing Unsubscribe
> in the subject line.
>
>
> ---
> You are currently subscribed to cfaussie as:
> [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
> Aussie Macromedia Developers: http://lists.daemon.com.au/
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
> Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
