Temporary table not getting created

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.

Code:

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.

 

It looks correct so far, but if it works in the analyzer I would more than guess that it is a permission problem based on the user rights assigned to the account in the open connection statement.

 

Thanks for your reply jwshepherd. But I am able to create other temporary tables with the same connection variable.

 

Are you getting an error when the reate table is running?

 

No at the time of creation no error is thrown. But when I try to access the temp table, "Object does not exist" error is thrown.

 

The problem may be with the connection sir, just check whether you are getting the rowcount value for the newly created table.

If you get that then no issues your table is created. You had some problem in creating the table itself which is due to connection sir.