Temp tables don't seem to be "persistent" for me, that is, they seem to disappear
right after I create them. Here's my configuration:
Perl: v. 5.6.1 (ActiveState build 631, under Microsoft Windows 2000)
DBI: v. 1.23 (connecting to the database via ODBC)
Database: Microsoft SQL Server 2000
Here's an (admittedly silly) example that demonstrates the problem I'm having:
----- Start of Perl code -----
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my ($srvr, $db, $usr, $pwd) = @ARGV;
my %attr = (RaiseError => 1, AutoCommit => 0);
# Connect to the server:
my $dbh = DBI->connect("DBI:ODBC:$srvr", $usr, $pwd, \%attr)
or die DBI->errstr;
# Use the given database:
$dbh->do("use $db") or die $dbh->errstr;
# Create a temp table and insert into it:
$dbh->do('CREATE TABLE #foo (id INT PRIMARY KEY, val CHAR(4))')
or die $dbh->errstr;
$dbh->do("INSERT INTO #foo (id, val) VALUES (1, 'foo')")
or die DBI->errstr;
# Commit changes and disconnect from the server:
$dbh->commit;
$dbh->disconnect;
----- End of code -----
When I run this code (passing in the server, database, user name and password on the
command line), I get the following error message from line 20 (the insert into #foo):
----- Start of error message -----
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name '#foo'. (SQL-S0002)
[Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared. (SQL-37000)
(DBD: st_execute/SQLExecute err=-1) at foo.pl line 20.
----- End of error message -----
If you replace both occurrences of "#foo" with "foo" (i.e. create and insert into a
permanent table, rather than a temporary table), then it works fine. So what stupid
thing am I doing wrong?
My ultimate goal is to be able to create and populate temp tables that can then be
referenced by stored procedures. Any help would be greatly appreciated.
Thanks,
- Kennis Koldewyn
---- Kennis Koldewyn (koldewyn<at>cloud9.net) ----
Without computers, it would be virtually impossible
for us to accompliowur xow;gkc,mf(&( - Dave Barry
