bcp to temporary table
Jos Stoop, x8456, Systems
it, and use the results subsequently in a select statement?
The problem seems to be that the temporary table is invisible for anyone but
the current user _IN_ the current (isql) session. Even when passing the full table
name (#tempdb______12345etc) from sysobjects to a second (eg bcp) session, it is not
recognized. However, the temporary table does appear when doing a select on the
sysobjects table from a second session. Am I forgetting something?
The reason I want to do this, is to allow multiple users to simultaneously go through
a process of deleting old data (if needed), importing data from a file, and running a
report on the imported data, without causing problems for each other.
We want to avoid giving create table permissions to the users.
Any suggestions?
Jos
Qing Yin
>
>We want to avoid giving create table permissions to the users.
>
I have a different question relating tempdb that might interest people who
deal with tempdb.
At one point, I needed to create temp tables in our
application. I don't care if it's a # table or regular table, but the data
has to be bcp'ed in, and, after it is shown at the front end, the table will
be dropped. To make a long story short, I planned to create regular tables
in tempdb since I need to keep our main databases' transaction log
playback'able. My steps to set things up are:
1) sp_adduser in tempdb.
2) grant create table permission to the user in tempdb.
3) write the program that creates table in tempdb and bcp the data.
4) the program works.
5) shutdown the server and re-start it.
**6) I found that the user is no longer in tempdb.
It seemed that, because tempdb is not recovered at server startup time, it
cannot remember it's users. I then created another database to solve this
problem and didn't bother to find out exactly what's going on with tempdb.
Therefore, my observation might be wrong.
Could someone make some comment on tempdb?
--
Vincent Q. Yin
um...@ccu.umanitoba.ca
David Hawkins
> 5) shutdown the server and re-start it.
> **6) I found that the user is no longer in tempdb.
>It seemed that, because tempdb is not recovered at server startup time, it
>cannot remember it's users. I then created another database to solve this
>problem and didn't bother to find out exactly what's going on with tempdb.
>Therefore, my observation might be wrong.
Yup, all tables in tempdb are recreated - including system tables,
like sysusers.
You can get around it by adding them as a user in the model database
and that's what gets copied over to tempdb, or have a script that runs
after a restart and adds them to tempdb. The problem with putting
them in model is that you might create a database you want secure and
forget that user_jan is automatically a member. (Although it doesn't
hurt to check sysusers on any new database.)
But what I'm more familiar with is where the SA has created a 'comdb'
and added users and changed permissions and then just deletes old
tables on a regular basis. Or deletes everything weekly.
later, david
--
David Hawkins dh...@netcom.com
A person who is passionately fond of music may quite well be a perverted
person--but I should find it hard to believe this of anyone who
thirsted for Gregorian chanting. -- Simone Weil
Steve Wall
>it, and use the results subsequently in a select statement?
>
>The problem seems to be that the temporary table is invisible for anyone but
>the current user _IN_ the current (isql) session. Even when passing the full table
>name (#tempdb______12345etc) from sysobjects to a second (eg bcp) session, it is not
>recognized. However, the temporary table does appear when doing a select on the
>sysobjects table from a second session. Am I forgetting something?
.
.
>
>Jos
>
You are right that the #temp_file_name is unique to a single session
and cannot be shared between sessions by referencing the table by
#temp_file_name. Thus, a bcp followed by sql (or DB-LIB) cannot
share the table via #temp_file_name.
The issue of using the FULL name of the # table (i.e
tempdb..foo_________nnnnnnnnn) depends on your version. I believe
prior to 4.2, you could share # tables between concurrent sessions
by referencing the complete table name. You may have also had to be
the owner of the table (I forget the permissions on a # table create).
Anyway, in 4.2 through 4.8, this mechanism didn't work - the same user
could not reference a # table via complete table name from two different
sessions. This is a MAJOR inconvenience when debugging intermediate tables
that use the #temp_file_name mechanism.
I hear that 4.9.1 (?) corrects this problem - we don't have it
installed yet, so I don't know for sure.
But, you're still left with your bcp and sql/DB-LIB sharing
# table problem. I don't know of a way around this - we've
implemented a unique table name generator tied to a clock
with millisecond granularity - this seems to cover us. But,
this method doesn't include the nice "auto removal" feature
of # tables when a session is closed.
Steve
--
Steve Wall (wa...@mcm.com)
Mellon Capital Management Corporation
San Francisco, CA
Jos Stoop, x8456, Systems
In article 79...@mcm.com, wa...@mcm.com (Steve Wall) writes:
> ..
> ..
> The issue of using the FULL name of the # table (i.e
> tempdb..foo_________nnnnnnnnn) depends on your version. I believe
> prior to 4.2, you could share # tables between concurrent sessions
> by referencing the complete table name. You may have also had to be
> the owner of the table (I forget the permissions on a # table create).
> Anyway, in 4.2 through 4.8, this mechanism didn't work - the same user
> could not reference a # table via complete table name from two different
> sessions. This is a MAJOR inconvenience when debugging intermediate tables
> that use the #temp_file_name mechanism.
>
> I hear that 4.9.1 (?) corrects this problem - we don't have it
> installed yet, so I don't know for sure.
SQL Server/4.9.1 is the one I used when I ran into this problem, so it is not
corrected (yet).
Jos Stoop (jo...@mtkgc.com)
Intuitive Products International Corp.
HUNTLEY JR A F
4.0 release you could simply give the FULL tablename as you have tried.
In a later 4.0.1 EBF they made it so that EVEN THE OWNER could only
access the table by giving the owner.FULL_tablename specification. As
of 4.8 Sybase has removed ALL capability of ANY other process from
touching the temp tables. This includes the SA account. If Sybase
happens to not clean up a temp table when a connection closes, it now
stays there until the dataserver gets restarted. The documentation
for temp tables STILL says that YOU CAN ACCESS THEM FROM ANOTHER
CONNECTION using the FULL_tablename spec, even for 4.9.1.
Another alternative is to create permanent tables in tempdb and
grant the appropriate permissions. Then you can do your own drop
table when done and be (reasonably) certain that it will go away.
Al Huntley