temporary tables with MS SQL 7

Can someone help me with few trick for using Temp tables from VB?

Thanks a lot for any help

[RIVES]

You should use temporary tables in stored procs...it is already transparent in vb. It is a limitation however in the in vb since it only uses tables within its connection. so results that you query from a #temptable could not be returned. What I do though is to create a physical temp table in the database where you connect and insert the records there and query it from there. I think(not sure) that if you use RDO, it you could query against #temptable.

Thanks a lot Rives.

Let me clarify my purpose:

I want to create a temp table from VB, use it in stored procedures, request for the results, and close it in VB.

if you can help me a bit on this side....

thanks again for any help you could provide me.

Why not make a permanent table and add an extra column which holds the @@SPID of the connection you are using or a client unique ID.
Then you could just pass the ID into the stored procedure that you want it to act on.
Temp tables are pretty bad normally, they can severely degrade performance as the indexing on them is different from permanent tables.

John.

[Lafor]

Keep in mind that there are 2 kinds of temp tables
1. Local - tables are prefixed with a # sign and are
local to the user who creates them (i,e only the user
who creates them has access to it)
So think about this:
What if an external user had access to the stored
procedure which accesses your local temporary table???

2. Global - tables are prefixed with ## (2 pound signs)
and are available to all users of the database (no
security rights though i.e any user can modify this
table) ... Also note that when the last user with
access to this table ends her session, boom!! This
temp table is destroyed


Example

The general syntax:

create table #vguru (houses_sold int null,
Total_sales money null,
Sales_PersonID int null)

Whether in VB or inside the stored, it is the same syntax
(of course)... just package it into a string in VB
and execute the statement

...Hope this helps

Thanks a lot to all. in fact you helped me more than I was expecting.

My issue was to handle an import from a text file into the Db.

I wanted this to be unique for each user.

I decided to use temp tables, as they are unique to the local user.

My problem was that I was not able to make selects in it., i.e. VB was rejecting the select statement 50% of the trys.
The solution is (thanks to RIVES "You should use temporary tables in stored procs.") that I will create the temp table in VB, run Stored procs against it (as they belong to the same connection it works).

Only issue: I can not use Views (they check that the table exist when I write them.... and more globally they seam not able to handle temp tables....)

Again thanks to all!!!!

[RIVES]

Hello.

Since you said that you are importing a txt file, I suggest that you use the bcp of SQL server, it loads data to tables a lot faster than reading it in vb and then insert one by one.

There are somethings that you should do first though.

Give access to the master database, you should check the Insert Into/ Bulk Copy in the options of your database.

There are two ways to connect using the bcp. You could use the TCP/IP where in you have to supply the -S servername -U userid -P password for the bcp.

Or, if you use trusted connection through the NETBeui Protocol, You could just specify the server name.

Hope this helps.