Re: Temporary tables in SQL Server 2000

[Philippe Godin <philippe.godin@VIDEOTRON.CA>]

Good day Brian,

        Here's the solution for your problem...

        With Sql Server you can use either #mytable or ##mytable. The first
statement creates a temporary table that is accessible only to the user that
have created it and it only last the time of the connection. The second
statement also creates a temp table which is available for all the users
connected to the database. My experimentations with this makes me believes
that each time you put a BEGIN-SQL and END-SQL block into your code, SQR
opens a new connection to the database and then closes it. This means that
your table is created but destroyed at the end of the block.

        When your using a statement like: create temporary table ##mytable
(blablabla) SQR is really creating the temp table like this: #mytable. This
symbol "#" is usually used to tell SQR that the following variable is to
contain a number. If it isn't the case you have to double up the "#"
caracter. (This is similar to CGI script if your willing to use a backslach
you need to double it in order to use it.)

        It might be a bit complicated but what you need to use is something like
this: create temporary table ###mytable
which will really create a ##mytable in Sql Server.

        Since this table will be available to any user of the database you might
want to use a dynamic name when creating such a table. If two users execute
the report at the same time there could be errors generated. The solution
I've found for this is to create the table using a timestamp. Here's the
code I usually use to achieve this:

BEGIN-SELECT
current_timestamp &timestamp
from dummy_table
END-SELECT

        Let $timestamp = translate(&timestamp,'-','')
        Let $timestamp = translate($timestamp,':','')
        Let $timestamp = translate($timestamp,'/','')
        Let $timestamp = translate($timestamp,' ','')
        Let $temptable = '###' || $timestamp

BEGIN-SQL
        CREATE TABLE [$temptable] (col1SQL varchar(30),col2 int,col3 float,col4
float,col5 float,col6 float,col7 float,col8 float,col9 float,col10
float,col11 float,col12 float,col13 float);
END-SQL

Hope this helps
Philippe Godin

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Brian Smith
Sent: 3 juillet, 2001 12:03
To: SQR-USERS@list.iex.net
Subject: Temporary tables in SQL Server 2000
Importance: High


When setting up an ODBC connection to a Microsoft SQL Server 2000 database,
the option "Create temporary stored procedures for prepared SQL statements
and drop the stored procedures" is forced ON and is not able to be cleared.
This has the result that an SQR report which uses any temporary tables
(CREATE TABLE ##temp in the begin-setup section) does not work, since the
#temp table was created inside a stored procedure, and thus ceases to exist
when the procedure ends.

SQR then gives an error such as:
(SQR 5528) ODBC SQL dbexec: SQLExecute error 208 in cursor 11:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#temp'.
(SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 11:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

Error on line 125:
   (SQR 3735) Could not execute SQL.

This is from numerous reports that work perfectly when targetting a SQL
Server 6.5 database.
Unfortunately the -XP command-line option is not available under Windows.

Is there a fix for this (without changing the report)?


Brian Smith


***IMPORTANT***
The information in this mail is confidential and is intended solely for the
addressee. Access, copying or re-use of information in it by anyone else is
unauthorised. Any views or opinions presented are solely those of the author
and do not necessarily represent those of Tempo Ltd or any of its
affiliates.