I can't create a temp table

[Sergio]

Hi all!
I have a problem with a temp table.
I start creating my table:

bdsqlado.execut e ("CREATE TABLE #MyTable ...")

There is no error. The sql string has been tested and when it's
executed in the sql query analyzer it really creates the table.

After creating the table, I execute an insert statement:

bdsqlado.execut e ("INSERT INTO #MyTable VALUES(...) "

It returns an error like this: "Invalid Object Name #MyTable"

I don't understand what's wrong. If I execute both sql sentences in
the SQL Query Analyzer it works perfectly.
I use the same connection to execute both statements and I don't close
it before the INSERT is executed.
I think it may be something related to dynamic properties of the
connection, but I'm not sure. It's just an idea.

Please I need help.

Thanks a lot,

[Joseph Weinstein]

Re: I can't create a temp table



Sergio wrote:
[color=blue]
> Hi all!
> I have a problem with a temp table.
> I start creating my table:
>
> bdsqlado.execut e ("CREATE TABLE #MyTable ...")
>
> There is no error. The sql string has been tested and when it's
> executed in the sql query analyzer it really creates the table.
>
> After creating the table, I execute an insert statement:
>
> bdsqlado.execut e ("INSERT INTO #MyTable VALUES(...) "
>
> It returns an error like this: "Invalid Object Name #MyTable"[/color]

Hi. Let me play Kreskin... I'm guesing you're using JDBC, and MS's
free driver. If this is true, add the property selectMode=curs or to
your connection properties. What is happening is that the driver
*is spawning multiple actual DBMS connections* to support a
single logical connection having multiple concurrent open statements.
This means the spid of one statement will be different than another, and
therefore one statement will not be able to see another's temp table!

Joe Weinstein
[color=blue]
>
>
> I don't understand what's wrong. If I execute both sql sentences in
> the SQL Query Analyzer it works perfectly.
> I use the same connection to execute both statements and I don't close
> it before the INSERT is executed.
> I think it may be something related to dynamic properties of the
> connection, but I'm not sure. It's just an idea.
>
> Please I need help.
>
> Thanks a lot,[/color]