passing virtual tables using asp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

passing virtual tables using asp

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 06:01:16

i need to pass a virtual table that i have created on sp_1 to sp_2
but using asp and not directly!
is it possible?
is it possible at all to return a virtual table to asp and then to send it to another sp?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 06:30:09

no.

you can however create a global temp table ##globalTemp
in your sp_1 and access it in sp_2. then drop the ##globalTemp when done.

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 06:56:00

but when sp_1 ends running it is deleted from memory!
i am not caling sp_2 from sp_1 but from asp!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 06:58:07

a global temp table isn't deleted from memory as far as i know.
you have 2 kinds of temp tables:
#temp - local to connection
##temp - global to all connections

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-11-01 : 07:05:29

BoL: "Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server"

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 07:16:14

"##temp - global to all connections"
means all connection of a specffic user? (i belive so but just want to make sure)
andd how do i at the end delete total from memory this ##temp table after i finish using it?
thanks alot for the help
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 07:19:59

after i saw Kristen answer i understand now that what i have belived : "i belive so but just want to make sure" isnt true!
so how can i,maybe by passing a variable or something like that so that each user will access only his correct virtual table?
can i make dynamic names for table like :table_1 and so on
and access it like (dont know the code execlly) cast("table_"+(int)index)
thanks alot
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 07:33:03

well i never had problems with using global temps in the way you're using them...
if there are no globals you can use "guid names" for tables (t1_yourguid) which you create from your app and then delete them after use.

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 07:38:25

well spirit1 i am looking on how i can create this dynamic table name!
how to decide which unique dynamic name to give isnt a problem
but what i dont know is how to make it ##t1_yourguid
any idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 07:48:22

emm...
create table ##t1_... (columns)

but you can create a normal table for this and then drop it. that way you don't have to worry about temp's

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 08:11:22

again i understand i need to do :
create table ##t1_... (columns)
but how do i attach ##t1_ to what ever string i want

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 08:27:36

exec('create table ##t1_' + convert(varchar(36), newid()) + ' (columns)')

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-01 : 08:32:57

thanks alot i wil lcheck it out

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-03 : 01:58:12

bye the way can anyone tell me is there security hols with virtual tables,for example if there is an introder can he check on the SQL SERVER which virtual tables are currently running in memory (if he open the stored procedure it wont help him beacuse i pass a current random name for the table from the specific web user )
any idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-03 : 03:55:00

yes he can. he can take a look at tempdb to see any temporary table that still hasn't been deleted.
this isn't so for table variables that are stored in memory.

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-03 : 03:58:20

table variables u mean ## tables?
if not so what is table variables ?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-03 : 04:04:38

table variables are a new thing in sql server 2000.
they are stored in memory only for the duration of the scope. (until the first go)
read about them in BOL.


declare @MyTableVariable table (col1 int, col2 varchar(20))
insert into @MyTableVariable
select 1, 'gers' union all
select 2, 'htrdsh' union all
select 3, 'gdssfgs'

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-03 : 04:33:44

so you recomand to improve security using a virtual table then a table that is defined as #?
is there a diffrence?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-03 : 04:45:47

ok let's get terminology straight:
there are 4 kinds of tables:
1. normal tables: create table TableName
2. temporaray tables (either #local or ##global): create table #tableName or create table ##tableName
3. table variables: look at my previous post
4. pseudo tables in triggers: inserted and deleted

last 3 can all be called "virtual tables" so you need to start using terminology correctly so there are no misunderstandings.
as far as i understand your problem in your case you CAN NOT use table variables.

Go with the flow & have fun! Else fight the flow

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-03 : 04:51:14

execlly beacuse they are deleted from memory!
and the global temporary tables can be found by an introder on the tempdb!
so whats the best way and secure way to do it?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-03 : 05:59:42

i don't think you should worry about security here.
you should consider it way before. use propers users and userroles for security.
if anyone can get access to your sql server then he can read all of your tables regardless if they're temp or normal.

Go with the flow & have fun! Else fight the flow

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources