Losing temporal tables

Hi, i want to know if there's any particular reason why a temporal
table can be dropped before closing the session.
I'm having the following problem: i create the temp table when a form
of my application is created, work with it and then drop it on the
form's close event.
It works fine most of the time, but from time to time the table seems
to be dropped before i close the form because i'm having a: #MyTable
doesn't exists error message.
Any ideas?
Working against SQL2K, W2K Server, from a W2K Pro machine.
Thanx
If your connection is getting dropped at any time you will loose the temp
table. Temp tables are really designed for a brief life span. If you need
to hold certain information for long periods of time like that you may want
to consider using real tables. Or better yet maybe a RS on the client.
Andrew J. Kelly SQL MVP
"Guillermo Casta?o A" <guillermoc74@.hotmail.com> wrote in message
news:9350d78d.0409170946.33db2291@.posting.google.c om...
> Hi, i want to know if there's any particular reason why a temporal
> table can be dropped before closing the session.
> I'm having the following problem: i create the temp table when a form
> of my application is created, work with it and then drop it on the
> form's close event.
> It works fine most of the time, but from time to time the table seems
> to be dropped before i close the form because i'm having a: #MyTable
> doesn't exists error message.
> Any ideas?
> Working against SQL2K, W2K Server, from a W2K Pro machine.
> Thanx

Loopback linked servers cannot be used in a distributed transactions? This worked in Sql20

I recently did an upgrade to SQL 2005 and there's some odd behavior
that I'm seeing.
Given the following SQL:
Use MyDB1
go
create table #test (a int)
insert into #test
exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
where MyServer is a linked server that points back to itself. This
SQL works under SQL2000 but does not work in SQL2005. The error
returned in SQL 2005 is:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
In this whitepaper (http://msdn2.microsoft.com/en-us/library/
ms188716.aspx) i see the following warning:
"Loopback linked servers cannot be used in a distributed transaction.
Trying a distributed query against a loopback linked server from
within a distributed transaction causes an error, such as error 3910:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
use by another session."
The reason for the loopback linked server here is that in my live
enviornment, the linked server points to a different machine, (MyDB1
and MyDB2 are on different physical servers) but for my development
enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
to be able to keep that configuration without having to change code
when promoting stored procedures from the development branch to QA and
LIVE. Any way to get around this?
You should be able to get around it by running a second instance in
your development environment, one instance for each of the production
systems.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 07:27:06 -0700, MS <spilich@.gmail.com> wrote:

>I recently did an upgrade to SQL 2005 and there's some odd behavior
>that I'm seeing.
>Given the following SQL:
>Use MyDB1
>go
>create table #test (a int)
>insert into #test
>exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
>where MyServer is a linked server that points back to itself. This
>SQL works under SQL2000 but does not work in SQL2005. The error
>returned in SQL 2005 is:
>Msg 3910, Level 16, State 2, Line 1
>Transaction context in use by another session.
>In this whitepaper (http://msdn2.microsoft.com/en-us/library/
>ms188716.aspx) i see the following warning:
>"Loopback linked servers cannot be used in a distributed transaction.
>Trying a distributed query against a loopback linked server from
>within a distributed transaction causes an error, such as error 3910:
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
>use by another session."
>The reason for the loopback linked server here is that in my live
>enviornment, the linked server points to a different machine, (MyDB1
>and MyDB2 are on different physical servers) but for my development
>enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
>to be able to keep that configuration without having to change code
>when promoting stored procedures from the development branch to QA and
>LIVE. Any way to get around this?
|||On Aug 15, 10:53 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You should be able to get around it by running a second instance in
> your development environment, one instance for each of the production
> systems.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 15 Aug 2007 07:27:06 -0700, MS <spil...@.gmail.com> wrote:
>
>
>
> - Show quoted text -
Yes, you are correct, that works and I am aware of this. I was hoping
not to go this route because I already have multiple instances already
defined to allow different branches of development to run in
parallel. So, I have a DEV01, DEV02, and DEV03 instances to allow
three parallel branches of development. I'd have to effectively
double the number of instances (so i would have a DEV01a, DEV01b,
DEV02a DEV02b, DEV03a, DEV03b instance). I was hoping to not have to
manage this many instances on a single server. Sounds like I need to
acquire additional hardware. Is anyone aware of a way to get around
this error? Why does it work in 2000 and not in 2005?
Thanks
sql