I cant create a temp table

Hi everyone,

I saw a post of this, but i still can't solve it.
This is what i got:
SET @.sql = 'CREATE TABLE #SIAG_MatrizTemporal ...'
EXEC(@.sql)

this seems to work fine, but if i try to make a query on #SIAG_MatrizTemporal, the table doesn't exists, the error says: "Invalid object name '#SIAG_MatrizTemporal'."

If I add to @.sql a select statement the values are in there, but if i do it outside the same string, it just doesn't work.

Joseph Weinstein says that i should add "selectMode=cursor" where should i add it ?? I'm just working with the stored procedure, i'm not using VS, Java or any other programming language. Later i have to use it from SQL Reporting Services, but first i have to make it work from the Query Analyzer...

I think i have to activate or desactivate some property on the Server or the query, but i was looking in Internet and nothing... I really need to solve this, and i cant find anything that works.

If anyone can help me, i really appreciate it.
Thanks in advance :)

JoanSET @.sql = 'CREATE TABLE ##SIAG_MatrizTemporal ...'
EXEC(@.sql)

You can use ##SIAG_MatrizTemporal to Dispose.
我不懂英文！
这里你必须使用全局临时表。
follow is my code. ex：

IF EXISTS
(SELECT * FROM tempdb..sysobjects WHERE name LIKE '##fabtemp%')
DROP TABLE ##fabtemp

EXEC ('
SELECT GUID=NEWID(),MstGUID = ''' + @.MstGUID + ''',
iNumber = IDENTITY(int, 1,1),
sRollNo,MaterialGUID,sLotNo,
sStoragePlaceCD,sStkTypeCD,
sSupplySourceCD,
fAccountQty = fOnHandQty + fOnHoldQty,
fRealQty = fOnHandQty + fOnHoldQty,fProfitOrLossQty = 0,
sCtUid = ''' + @.sUserID + ''',
dCtDate = GETDATE() INTO ##fabtemp
FROM IMFabricRollStock
WHERE sStorageCD = ''' + @.sStorageCD + '''' + @.sCondition +
'ORDER BY sStoragePlaceCD ')

/*------------------
------------------*/

INSERT INTO imFabricCheckingDtl
SELECT * FROM ##fabtemp

I cant create a temp table

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

bdsqlado.execute ("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.execute ("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,
Sergio wrote:

> Hi all!
> I have a problem with a temp table.
> I start creating my table:
> bdsqlado.execute ("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.execute ("INSERT INTO #MyTable VALUES(...) "
> It returns an error like this: "Invalid Object Name #MyTable"

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=cursor 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

>
> 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,