Re: Creating temp table in SQL Server

["White, Denise" <DWHITE@SYSTEMS.TEXTRON.COM>]

Bharati,

What is 'check all_objects'?  I tried looking up a 'check' command and an
'all_objects' keyword in every SQR reference I could think of, and couldn't
find either of them.  Is it specific to SQL Server?  We are Oracle.

Also, thanks to those who pointed out that the ON-ERROR clauses I specified
(WARN & SKIP) can only be used in the BEGIN-SETUP section.  I knew there was
a reason I was doing the drop in two different places!

Denise White
Sr. IT Application Developer
Textron Systems

> -----Original Message-----
> From: Automatic digest processor [SMTP:LISTSERV@list.iex.net]
> Sent: Friday, October 22, 1999 2:03 AM
> To:   Recipients of SQR-USERS digests
> Subject:      SQR-USERS Digest - 21 Oct 1999 to 22 Oct 1999
>
> Date:    Fri, 22 Oct 1999 08:43:24 -0400
> From:    "Mani, Bharati" <Bharati_Mani@WRIGHTEXPRESS.COM>
> Subject: Re: Creating temp table in SQL Server
>
> At the begining of the program why don't you check all_objects to see if
> the
> table exists. If it does then drop it and continue with the create
> otherwise
> just create it
>
> > -----Original Message-----
> > From: White, Denise [SMTP:DWHITE@SYSTEMS.TEXTRON.COM]
> > Sent: Thursday, October 21, 1999 5:08 PM
> > To:   Multiple recipients of list SQR-USERS
> > Subject:      Re: Creating temp table in SQL Server
> >
> > We are Oracle, not SQL Server, but I would create a 'permanent' table at
> > the
> > beginning of the program and then drop it at the end.  As Bob notes, the
> > program could abort before it reaches the drop, leaving the table in
> place
> > and causing an error next time the job is run and tries to do the
> create.
> > Good programming practice (at least, as I was taught) is to do a drop at
> > the
> > BEGINNING of the program (before the create), as well as at the end (you
> > can
> > just set up one procedure and call it from both places, if you want; I
> do
> > separate drops so I can handle errors differently - see next statement).
> > The BEGIN-SQL needs to have ON-ERROR=WARN, so that under normal
> > circumstances (when the table has been dropped correctly in the previous
> > run) the non-existence of the table will not cause the program to abort.
> > You may need to warn your users so they don't call you when they see the
> > warning message!  You can also use ON-ERROR=SKIP, which will not produce
> a
> > warning message, but I like to be able to see if some other message
> shows
> > up, indicating a real problem.
> >
> > Denise White
> > Sr. IT Application Developer
> > Textron Systems
> > > ------------------------------