I'm pretty sure table data types are SQL 2000 only.
Darryl
[EMAIL PROTECTED] wrote on 16/03/2005 03:25:20 PM:
> 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/
To unsubscribe from this email please forward this email to [EMAIL PROTECTED]
This email message is confidential and may be privileged. If you are not the
intended recipient
please forward the email to [EMAIL PROTECTED] and delete the original.
ABN AMRO Morgans Limited and its associates hold or may hold securities in the
companies/trusts mentioned herein.
Any general advice included in this email has been prepared without taking into
account your objectives, financial situation or needs.
Before acting on the advice, you should consider its appropriateness or discuss
with one of our investment advisors.
ABN AMRO Morgans Limited (ABN 49 010 669 726 AFSL 235410) A Participant of ASX
Group.
A principal member of the Financial Planning Association.
---
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/
