Error creating temporary table from code

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

Transact-SQL (2000)

Error creating temporary table from code

Author

Topic

kpsendilkumar
Starting Member

2 Posts

Posted - 2007-09-11 : 08:17:42

Hi,

When I execute the following piece of code from VB, the temporary table is not getting created; also I am not getting any error message (I am able to create other temporary tables). But, if I run the query in the Query Analyser, the temporary table is getting created. I am using SQL Server 2000.

SQL = " CREATE TABLE #TEMP_CONTRACT_RO ( "
SQL = SQL & " [SALES_REFERENCE_NO] [varchar] (25) NULL ,"
SQL = SQL & " [REVISION_NO] [numeric](18, 0) NULL ,"
SQL = SQL & " [CONTRACT_SNO] [numeric](18, 0) NULL ,"
SQL = SQL & " [SPONSOR_TYPE] [varchar] (2) NULL ,"
SQL = SQL & " [PROGRAMME_NAME] [varchar] (255) NULL , "
SQL = SQL & " [REPEAT_NO] [numeric](18, 0) NULL DEFAULT 0, "
SQL = SQL & " [TOTAL_EPISODES] [numeric](18, 0) NULL DEFAULT 0, "
SQL = SQL & " [TOTAL_SECS] [numeric](18, 0) NULL DEFAULT 0,"
SQL = SQL & " [CONSUMED_EPISODES] [numeric](18, 0) NULL DEFAULT 0, "
SQL = SQL & " [CONSUMED_SECS] [numeric](18, 0) NULL DEFAULT 0,"
SQL = SQL & " [CONSUMED_EPISODES_VALUE] [money] NULL DEFAULT 0, "
SQL = SQL & " [CONSUMED_SECS_VALUE] [money] NULL DEFAULT 0, "
SQL = SQL & " [CURRENT_RO_EPISODES] [numeric](18, 0) NULL DEFAULT 0,"
SQL = SQL & " [CURRENT_RO_SECS] [numeric](18, 0) NULL DEFAULT 0,"
SQL = SQL & " [RATE_PER_UNIT_SECONDS] [money] NULL DEFAULT 0,"
SQL = SQL & " [TOTAL_COST] [money] NULL DEFAULT 0, "
SQL = SQL & " [COMPANY_CODE] [VARCHAR] (5) NULL, "
SQL = SQL & " [BRANCH_CODE] [VARCHAR] (5) NULL, "
SQL = SQL & " [CHANNEL_CODE] [VARCHAR] (5) NULL,"
SQL = SQL & " [PROGRAMME_START_TIME] [VARCHAR] (10) NULL,"
SQL = SQL & " [PROGRAMME_END_TIME] [VARCHAR] (10) NULL)"

gvarCnn.Execute UCase(SQL)

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 08:40:27

The temp table only is accessible in that particular session.

If you add

SQL = "SELECT * FROM #TEMP_CONTRACT_RO"
gvarCnn.Execute UCase(SQL)

what happens then?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-11 : 08:53:50

Also dont use concatenated sql, instead use stored procedure with parameters and handle temp table over there

Madhivanan

Failing to plan is Planning to fail

kpsendilkumar
Starting Member

2 Posts

Posted - 2007-09-12 : 02:14:59

Thanks Mr.Peso and Mr. Madhivanan.

Peso, the suggestion you have given throws an error "Object does not exist". After creating this table I am trying to use it for furhter processing, that's when I am getting the error "Object does not exist."

Madhivanan, I know that I can use SP instead. But, what is the problem in the given situation?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 02:34:05

That is evidence of the temp table does not exists between calls.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-12 : 02:39:19

As I told make you make use of stored procedure and handle temp tables there

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-12 : 06:40:29

why do you need the temptable?
can you not create the recordset in your vb then do whatever you want with it there?

just curious

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources