by Joga Sing » Thu, 09 Mar 2000 04:00:00
Regards:
Joga Singh
by Alejandro Mes » Thu, 09 Mar 2000 04:00:00
This is from BOL:
Programming Stored Procedures in BOL
Stored Procedure Rules
a.. If you create a private temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.
> Regards:
> Joga Singh
by William Talad » Thu, 09 Mar 2000 04:00:00
Creating a temp table in a batch leaves the temp table until your spid
(connection) is closed.
Creating a temp table in a procedure deletes the temp table at the end of
the procedure.
A temp table is visible from procB if procB is called from procA after a
temp table has been created.
Creating #mytab in a batch. Then, executing procA which creates #mytab will
hide the first #mytab created; you will have two #mytab temp tables. One
will be accessible inside the procA and the other will be hidden to it.
>Regards:
>Joga Singh
by Fernando G. Guerrer » Thu, 09 Mar 2000 04:00:00
1. You open a connection and create a temporary table called #t1
2. From that connection you execute stored procedure SP1. Inside SP1 you can
use #t1 because the scope of your connection covers SP1.
3. If inside SP1 you create another table called #t1 again, it is considered
a new temporary table which scope is just SP1, there is no way to refer to
the #t1 created in the connection externally to SP1, because inside SP1
there is already an object called #t1.
4. Inside SP1 you create another temporary table called #t2. Once SP1
finished execution, every temporary object created inside SP1 will be
destroyed.
5. After SP1 execution finishes, you can't use #t2 because it was created
and destroyed inside SP1. If you call another procedure SP2 you can't use
#t2 inside SP2, because that temporary object doesn't exists anymore.
6. Once your connection is closed, #t1 will be destroyed
7. If inside your connection or any stored procedure called by your
connection you create a global temporary table called ##t1, that table will
be visible from any object in any connection. Once your connection is
closed, your global temporary objects will be destroyed as well as any local
temporary objects.
I hope this helps
--
Fernando G. Guerrero
MCDBA, MCSD, MCT, MCSE+Internet
QA Group Ltd., UK
Quote:> Hi,
> According to BOL, a #Temp table is removed, when the connection is closed
to
> SQL Server. But I m experiencing an opposite behaviour.
> From my Qey Analyzer, I m executing a SP, which creates a #Temp table. So
> when I execute another SP, that #Temp table is not visible, it gives the
> error Invalid object name '#Temp' table name.
> Is there anybody who could just explain the real situation ??????
> Regards:
> Joga Singh
by Tibor Karasz » Thu, 09 Mar 2000 04:00:00
I am not an API expert, but may I suggest that the two queries are executed
from different connections? Perhaps due to connection pooling?
You should be able to check this by tracing with Profiler. Check the spid.
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.
By providing DDL where appropriate, you're more likely to get a (good)
response.
> After that I run a SP using a Command object. So here it gives me the
error
> Object name #Temp not found. This time I m using the same Connection
object
> Cn.
> Any more suggestions ???????????
> Regards:
> Joga Singh
> About temp tables created with one # sign:
> Creating a temp table in a batch leaves the temp table until your spid
> (connection) is closed.
> Creating a temp table in a procedure deletes the temp table at the end of
> the procedure.
> A temp table is visible from procB if procB is called from procA after a
> temp table has been created.
> Creating #mytab in a batch. Then, executing procA which creates #mytab
will
> hide the first #mytab created; you will have two #mytab temp tables. One
> will be accessible inside the procA and the other will be hidden to it.
> >Hi,
> >According to BOL, a #Temp table is removed, when the connection is closed
> to
> >SQL Server. But I m experiencing an opposite behaviour.
> >From my Qey Analyzer, I m executing a SP, which creates a #Temp table. So
> >when I execute another SP, that #Temp table is not visible, it gives the
> >error Invalid object name '#Temp' table name.
> >Is there anybody who could just explain the real situation ??????
> >Regards:
> >Joga Singh
by Joga Sing » Thu, 09 Mar 2000 04:00:00
After that I run a SP using a Command object. So here it gives me the error
Object name #Temp not found. This time I m using the same Connection object
Cn.
Any more suggestions ???????????
Regards:
Joga Singh
About temp tables created with one # sign:
Creating a temp table in a batch leaves the temp table until your spid
(connection) is closed.
Creating a temp table in a procedure deletes the temp table at the end of
the procedure.
A temp table is visible from procB if procB is called from procA after a
temp table has been created.
Creating #mytab in a batch. Then, executing procA which creates #mytab will
hide the first #mytab created; you will have two #mytab temp tables. One
will be accessible inside the procA and the other will be hidden to it.
>Regards:
>Joga Singh
by William Talad » Thu, 09 Mar 2000 04:00:00
Quote:>> Thanks a ton guys for suggestions.
>> But my problem still persists. From Query Analyzer it works fine. But I m
>> using ADO 2.1 from VB.
>> So after establishing a connection, I execute a batch like this:
>> Cn.Execute "CREATE TABLE #Temp (CompanyId char(3))"
>> After that I run a SP using a Command object. So here it gives me the
>error
>> Object name #Temp not found. This time I m using the same Connection
>object
>> Cn.
by Fernando G. Guerrer » Thu, 09 Mar 2000 04:00:00
DIM connA As ADODB.Connection
Set connA = New ADODB.Connection
connA.Provider = "SQLOLEDB"
connA.Open "Server=MSSQLFGG;UID=sa;PWD=;Initial Catalog=Pubs;"
connA.Execute "create table #tmp1(ID int)"
connA.Execute "create procedure spA as select * from #tmp1"
connA.Execute "insert #tmp1 values (1)"
Dim rs As ADODB.Recordset
Dim cm As New ADODB.Command
Set rs = connA.Execute("select * from #tmp1")
Debug.Print rs!id
rs.Close
cm.CommandText = "spa"
cm.CommandType = adCmdStoredProc
Set cm.ActiveConnection = connA
Set rs = cm.Execute
Debug.Print rs!id
--
Fernando G. Guerrero
MCDBA, MCSD, MCT, MCSE+Internet
QA Group Ltd., UK
> After that I run a SP using a Command object. So here it gives me the
error
> Object name #Temp not found. This time I m using the same Connection
object
> Cn.
> Any more suggestions ???????????
> Regards:
> Joga Singh
> About temp tables created with one # sign:
> Creating a temp table in a batch leaves the temp table until your spid
> (connection) is closed.
> Creating a temp table in a procedure deletes the temp table at the end of
> the procedure.
> A temp table is visible from procB if procB is called from procA after a
> temp table has been created.
> Creating #mytab in a batch. Then, executing procA which creates #mytab
will
> hide the first #mytab created; you will have two #mytab temp tables. One
> will be accessible inside the procA and the other will be hidden to it.
> >Hi,
> >According to BOL, a #Temp table is removed, when the connection is closed
> to
> >SQL Server. But I m experiencing an opposite behaviour.
> >From my Qey Analyzer, I m executing a SP, which creates a #Temp table. So
> >when I execute another SP, that #Temp table is not visible, it gives the
> >error Invalid object name '#Temp' table name.
> >Is there anybody who could just explain the real situation ??????
> >Regards:
> >Joga Singh
by Rob Vieir » Thu, 09 Mar 2000 04:00:00
Question for you: Are you using a connection object, or are you passing a
connect string in with each command? If it's the later, then I would be
virually certain that connection pooling is your problem. If you're using a
connection object (the same for both executions), then connection pooling
should not be the issue as long as you don't close and reopen the connect in
between commands.
--
Rob Vieira MCSD, MCT, MCDBA
STEP Technology
Author, "Professional SQL Server Programming", ISBN 1861002319
Know a good Database or Systems Architect looking for a Job? Have them
e-mail
me a resume! It's with a great company and the leading Web developer in the
Pacific
NW according to Media magazine.
> After that I run a SP using a Command object. So here it gives me the
error
> Object name #Temp not found. This time I m using the same Connection
object
> Cn.
> Any more suggestions ???????????
> Regards:
> Joga Singh
> About temp tables created with one # sign:
> Creating a temp table in a batch leaves the temp table until your spid
> (connection) is closed.
> Creating a temp table in a procedure deletes the temp table at the end of
> the procedure.
> A temp table is visible from procB if procB is called from procA after a
> temp table has been created.
> Creating #mytab in a batch. Then, executing procA which creates #mytab
will
> hide the first #mytab created; you will have two #mytab temp tables. One
> will be accessible inside the procA and the other will be hidden to it.
> >Hi,
> >According to BOL, a #Temp table is removed, when the connection is closed
> to
> >SQL Server. But I m experiencing an opposite behaviour.
> >From my Qey Analyzer, I m executing a SP, which creates a #Temp table. So
> >when I execute another SP, that #Temp table is not visible, it gives the
> >error Invalid object name '#Temp' table name.
> >Is there anybody who could just explain the real situation ??????
> >Regards:
> >Joga Singh
by Joga Sing » Sat, 11 Mar 2000 04:00:00
It works fine, if nothing else being done on the same Connection object, but
even if you issue any other queries, it starts giving errors. I tried even
after Disabling the Pooling, still it gives the error.
So here are the Error Free and Error Prone code, for you guys to experiment:
Error Free code
=======
conn.Open "DSN=Scope21;UID=sa;PWD=;"
conn.Execute "create table #tmp1(ID int)"
conn.Execute "insert #tmp1 values (1)"
Error Prone code
============
conn.Open "DSN=Scope21;UID=sa;PWD=;"
conn.Execute "create table #tmp1(ID int)"
'Extra query, which causes the disaster
Set rs = conn.Execute("select * from customer")
'Now here it will give error
conn.Execute "insert #tmp1 values (1)"
I think MS guys should can better comment on this.
> After that I run a SP using a Command object. So here it gives me the
error
> Object name #Temp not found. This time I m using the same Connection
object
> Cn.
> Any more suggestions ???????????
> Regards:
> Joga Singh
> About temp tables created with one # sign:
> Creating a temp table in a batch leaves the temp table until your spid
> (connection) is closed.
> Creating a temp table in a procedure deletes the temp table at the end of
> the procedure.
> A temp table is visible from procB if procB is called from procA after a
> temp table has been created.
> Creating #mytab in a batch. Then, executing procA which creates #mytab
will
> hide the first #mytab created; you will have two #mytab temp tables. One
> will be accessible inside the procA and the other will be hidden to it.
> >Hi,
> >According to BOL, a #Temp table is removed, when the connection is closed
> to
> >SQL Server. But I m experiencing an opposite behaviour.
> >From my Qey Analyzer, I m executing a SP, which creates a #Temp table. So
> >when I execute another SP, that #Temp table is not visible, it gives the
> >error Invalid object name '#Temp' table name.
> >Is there anybody who could just explain the real situation ??????
> >Regards:
> >Joga Singh
by Tibor Karasz » Sat, 11 Mar 2000 04:00:00
As per my previous suggestion, I'd run Profiler to see what is actually
happening.
Perhaps ADO create stored procedures for your statements (As per Williams
observation)?
And, perhaps ADO tries to optimize and create _one_ stored procedure in one
of the cases, and separate in the other case(so the temptable goes out of
scope)?
No matter if above holds or not, run profiler to see what is actually
happening. "The truth is out there, and Profiler holds the key."
I find Profiler invaluable when passing SQL through layers which "messes"
with my code (no matter how good the intention of this messin' is).
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.
By providing DDL where appropriate, you're more likely to get a (good)
response.
Quote:> Hi Guys
> Took me some time to experiment and found the actual reason for the error.
> Looks like there is some problem with ADO.
> It works fine, if nothing else being done on the same Connection object,
but
> even if you issue any other queries, it starts giving errors. I tried even
> after Disabling the Pooling, still it gives the error.
> So here are the Error Free and Error Prone code, for you guys to
experiment:
> Error Free code
> =======
> conn.Open "DSN=Scope21;UID=sa;PWD=;"
> conn.Execute "create table #tmp1(ID int)"
> conn.Execute "insert #tmp1 values (1)"
> Error Prone code
> ============
> conn.Open "DSN=Scope21;UID=sa;PWD=;"
> conn.Execute "create table #tmp1(ID int)"
> 'Extra query, which causes the disaster
> Set rs = conn.Execute("select * from customer")
> 'Now here it will give error
> conn.Execute "insert #tmp1 values (1)"
> I think MS guys should can better comment on this.
by Alejandro Mes » Sat, 11 Mar 2000 04:00:00
Here is what i did.
Private Sub cmd6_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
On Error GoTo ErrorHandler
With cn
.Provider = "sqloledb"
.ConnectionString = "data source=myserver;initial
catalog=northwind;trusted_connection=yes"
.CursorLocation = adUseClient
.Errors.Clear
.Open
End With
cn.Execute "create table #temp(pkfld int)", , adCmdText +
adExecuteNoRecords
Set rs = cn.Execute("select employeeid from employees", , adCmdText)
cn.Execute "insert #temp values(1)", , adCmdText + adExecuteNoRecords
ExitSub:
On Error Resume Next
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume ExitSub
End Sub
> It works fine, if nothing else being done on the same Connection object,
but
> even if you issue any other queries, it starts giving errors. I tried even
> after Disabling the Pooling, still it gives the error.
> So here are the Error Free and Error Prone code, for you guys to
experiment:
> Error Free code
> =======
> conn.Open "DSN=Scope21;UID=sa;PWD=;"
> conn.Execute "create table #tmp1(ID int)"
> conn.Execute "insert #tmp1 values (1)"
> Error Prone code
> ============
> conn.Open "DSN=Scope21;UID=sa;PWD=;"
> conn.Execute "create table #tmp1(ID int)"
> 'Extra query, which causes the disaster
> Set rs = conn.Execute("select * from customer")
> 'Now here it will give error
> conn.Execute "insert #tmp1 values (1)"
> I think MS guys should can better comment on this.
1/>
> > Thanks a ton guys for suggestions.
> > But my problem still persists. From Query Analyzer it works fine. But I
m
> > using ADO 2.1 from VB.
> > So after establishing a connection, I execute a batch like this:
> > Cn.Execute "CREATE TABLE #Temp (CompanyId char(3))"
> > After that I run a SP using a Command object. So here it gives me the
> error
> > Object name #Temp not found. This time I m using the same Connection
> object
> > Cn.
> > Any more suggestions ???????????
> > Regards:
> > Joga Singh
> > About temp tables created with one # sign:
> > Creating a temp table in a batch leaves the temp table until your spid
> > (connection) is closed.
> > Creating a temp table in a procedure deletes the temp table at the end
of
> > the procedure.
> > A temp table is visible from procB if procB is called from procA after a
> > temp table has been created.
> > Creating #mytab in a batch. Then, executing procA which creates #mytab
> will
> > hide the first #mytab created; you will have two #mytab temp tables.
One
> > will be accessible inside the procA and the other will be hidden to it.
> > >Hi,
> > >According to BOL, a #Temp table is removed, when the connection is
closed
> > to
> > >SQL Server. But I m experiencing an opposite behaviour.
> > >From my Qey Analyzer, I m executing a SP, which creates a #Temp table.
So
> > >when I execute another SP, that #Temp table is not visible, it gives
the
> > >error Invalid object name '#Temp' table name.
> > >Is there anybody who could just explain the real situation ??????
> > >Regards:
> > >Joga Singh
by Joga Sing » Sat, 11 Mar 2000 04:00:00
Joga Singh
> As per my previous suggestion, I'd run Profiler to see what is actually
> happening.
> Perhaps ADO create stored procedures for your statements (As per Williams
> observation)?
> And, perhaps ADO tries to optimize and create _one_ stored procedure in
one
> of the cases, and separate in the other case(so the temptable goes out of
> scope)?
> No matter if above holds or not, run profiler to see what is actually
> happening. "The truth is out there, and Profiler holds the key."
> I find Profiler invaluable when passing SQL through layers which "messes"
> with my code (no matter how good the intention of this messin' is).
> --
> Tibor Karaszi, Cornerstone Sweden AB
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Please reply to the newsgroup only, not by email.
> By providing DDL where appropriate, you're more likely to get a (good)
> response.
> > Hi Guys
> > Took me some time to experiment and found the actual reason for the
error.
> > Looks like there is some problem with ADO.
> > It works fine, if nothing else being done on the same Connection object,
> but
> > even if you issue any other queries, it starts giving errors. I tried
even
> > after Disabling the Pooling, still it gives the error.
> > So here are the Error Free and Error Prone code, for you guys to
> experiment:
> > Error Free code
> > =======
> > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > conn.Execute "create table #tmp1(ID int)"
> > conn.Execute "insert #tmp1 values (1)"
> > Error Prone code
> > ============
> > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > conn.Execute "create table #tmp1(ID int)"
> > 'Extra query, which causes the disaster
> > Set rs = conn.Execute("select * from customer")
> > 'Now here it will give error
> > conn.Execute "insert #tmp1 values (1)"
> > I think MS guys should can better comment on this.
by Joga Sing » Sat, 11 Mar 2000 04:00:00
JS
> Here is what i did.
> Private Sub cmd6_Click()
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Set cn = New ADODB.Connection
> Set rs = New ADODB.Recordset
> On Error GoTo ErrorHandler
> With cn
> .Provider = "sqloledb"
> .ConnectionString = "data source=myserver;initial
> catalog=northwind;trusted_connection=yes"
> .CursorLocation = adUseClient
> .Errors.Clear
> .Open
> End With
> cn.Execute "create table #temp(pkfld int)", , adCmdText +
> adExecuteNoRecords
> Set rs = cn.Execute("select employeeid from employees", , adCmdText)
> cn.Execute "insert #temp values(1)", , adCmdText + adExecuteNoRecords
> ExitSub:
> On Error Resume Next
> rs.Close
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
> Exit Sub
> ErrorHandler:
> MsgBox Err.Description
> Resume ExitSub
> End Sub
> > Hi Guys
> > Took me some time to experiment and found the actual reason for the
error.
> > Looks like there is some problem with ADO.
> > It works fine, if nothing else being done on the same Connection object,
> but
> > even if you issue any other queries, it starts giving errors. I tried
even
> > after Disabling the Pooling, still it gives the error.
> > So here are the Error Free and Error Prone code, for you guys to
> experiment:
> > Error Free code
> > =======
> > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > conn.Execute "create table #tmp1(ID int)"
> > conn.Execute "insert #tmp1 values (1)"
> > Error Prone code
> > ============
> > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > conn.Execute "create table #tmp1(ID int)"
> > 'Extra query, which causes the disaster
> > Set rs = conn.Execute("select * from customer")
> > 'Now here it will give error
> > conn.Execute "insert #tmp1 values (1)"
> > I think MS guys should can better comment on this.
> 1/>
> > > Thanks a ton guys for suggestions.
> > > But my problem still persists. From Query Analyzer it works fine. But
I
> m
> > > using ADO 2.1 from VB.
> > > So after establishing a connection, I execute a batch like this:
> > > Cn.Execute "CREATE TABLE #Temp (CompanyId char(3))"
> > > After that I run a SP using a Command object. So here it gives me the
> > error
> > > Object name #Temp not found. This time I m using the same Connection
> > object
> > > Cn.
> > > Any more suggestions ???????????
> > > Regards:
> > > Joga Singh
> > > About temp tables created with one # sign:
> > > Creating a temp table in a batch leaves the temp table until your spid
> > > (connection) is closed.
> > > Creating a temp table in a procedure deletes the temp table at the end
> of
> > > the procedure.
> > > A temp table is visible from procB if procB is called from procA after
a
> > > temp table has been created.
> > > Creating #mytab in a batch. Then, executing procA which creates
#mytab
> > will
> > > hide the first #mytab created; you will have two #mytab temp tables.
> One
> > > will be accessible inside the procA and the other will be hidden to
it.
> > > >Hi,
> > > >According to BOL, a #Temp table is removed, when the connection is
> closed
> > > to
> > > >SQL Server. But I m experiencing an opposite behaviour.
> > > >From my Qey Analyzer, I m executing a SP, which creates a #Temp
table.
> So
> > > >when I execute another SP, that #Temp table is not visible, it gives
> the
> > > >error Invalid object name '#Temp' table name.
> > > >Is there anybody who could just explain the real situation ??????
> > > >Regards:
> > > >Joga Singh
by Alejandro Mes » Sat, 11 Mar 2000 04:00:00
I couldn't simulate it because i was using .cursorlocation property equal
adUseClient. After a read your last post, i change to adUseServer and i got
the error.
> JS
> > Why do not you try using oledb provider?
> > I ran the same code and i did not get any error.
> > Here is what i did.
> > Private Sub cmd6_Click()
> > Dim cn As ADODB.Connection
> > Dim rs As ADODB.Recordset
> > Set cn = New ADODB.Connection
> > Set rs = New ADODB.Recordset
> > On Error GoTo ErrorHandler
> > With cn
> > .Provider = "sqloledb"
> > .ConnectionString = "data source=myserver;initial
> > catalog=northwind;trusted_connection=yes"
> > .CursorLocation = adUseClient
> > .Errors.Clear
> > .Open
> > End With
> > cn.Execute "create table #temp(pkfld int)", , adCmdText +
> > adExecuteNoRecords
> > Set rs = cn.Execute("select employeeid from employees", , adCmdText)
> > cn.Execute "insert #temp values(1)", , adCmdText + adExecuteNoRecords
> > ExitSub:
> > On Error Resume Next
> > rs.Close
> > cn.Close
> > Set rs = Nothing
> > Set cn = Nothing
> > Exit Sub
> > ErrorHandler:
> > MsgBox Err.Description
> > Resume ExitSub
> > End Sub
> > > Hi Guys
> > > Took me some time to experiment and found the actual reason for the
> error.
> > > Looks like there is some problem with ADO.
> > > It works fine, if nothing else being done on the same Connection
object,
> > but
> > > even if you issue any other queries, it starts giving errors. I tried
> even
> > > after Disabling the Pooling, still it gives the error.
> > > So here are the Error Free and Error Prone code, for you guys to
> > experiment:
> > > Error Free code
> > > =======
> > > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > > conn.Execute "create table #tmp1(ID int)"
> > > conn.Execute "insert #tmp1 values (1)"
> > > Error Prone code
> > > ============
> > > conn.Open "DSN=Scope21;UID=sa;PWD=;"
> > > conn.Execute "create table #tmp1(ID int)"
> > > 'Extra query, which causes the disaster
> > > Set rs = conn.Execute("select * from customer")
> > > 'Now here it will give error
> > > conn.Execute "insert #tmp1 values (1)"
> > > I think MS guys should can better comment on this.
> > 1/>
> > > > Thanks a ton guys for suggestions.
> > > > But my problem still persists. From Query Analyzer it works fine.
But
> I
> > m
> > > > using ADO 2.1 from VB.
> > > > So after establishing a connection, I execute a batch like this:
> > > > Cn.Execute "CREATE TABLE #Temp (CompanyId char(3))"
> > > > After that I run a SP using a Command object. So here it gives me
the
> > > error
> > > > Object name #Temp not found. This time I m using the same
Connection
> > > object
> > > > Cn.
> > > > Any more suggestions ???????????
> > > > Regards:
> > > > Joga Singh
> > > > About temp tables created with one # sign:
> > > > Creating a temp table in a batch leaves the temp table until your
spid
> > > > (connection) is closed.
> > > > Creating a temp table in a procedure deletes the temp table at the
end
> > of
> > > > the procedure.
> > > > A temp table is visible from procB if procB is called from procA
after
> a
> > > > temp table has been created.
> > > > Creating #mytab in a batch. Then, executing procA which creates
> #mytab
> > > will
> > > > hide the first #mytab created; you will have two #mytab temp tables.
> > One
> > > > will be accessible inside the procA and the other will be hidden to
> it.
> > > > >Hi,
> > > > >According to BOL, a #Temp table is removed, when the connection is
> > closed
> > > > to
> > > > >SQL Server. But I m experiencing an opposite behaviour.
> > > > >From my Qey Analyzer, I m executing a SP, which creates a #Temp
> table.
> > So
> > > > >when I execute another SP, that #Temp table is not visible, it
gives
> > the
> > > > >error Invalid object name '#Temp' table name.
> > > > >Is there anybody who could just explain the real situation ??????
> > > > >Regards:
> > > > >Joga Singh
1. scope of temp tables created dinamicaly
When I create a #temp_table using dynamic sql, I cannot access the table
later in the procedure. Can I change that?
If I use ##temp_table instead, droping at the end, do I need to make sure
that the names are unique? I.e. if two internet users click to run the
procedure at the same time, will SQL server distinguish the two?
When I create table using
EXEC ('SELECT user_id INTO #users_clubs FROM users_clubs WHERE club_id IN ('
the following later in the procedure does not work:
SELECT user_id FROM users WHERE user_id IN #users_clubs
Thnak you for any tips.
Justin
2. Connecting to a database on a web server
3. Temp table scope within stored procedures
5. Global Temp Table Scope Issue
6. sqlce tool
8. Internet Database Connectivity - Help
10. temp table scope
11. Temp table scope within stored procedures
12. Temp table scope within stored procedures`
13. Massive Updates: Temp Or Not Temp Tables?