Temporary table not getting created

kpsendilkumar's Avatar, Join Date: Sep 2007
Newbie Member
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.

Last edited by shabbir; 11Sep2007 at 18:49.. Reason: Code block
jwshepherd's Avatar, Join Date: Aug 2007
Team Leader
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.
kpsendilkumar's Avatar, Join Date: Sep 2007
Newbie Member
Thanks for your reply jwshepherd. But I am able to create other temporary tables with the same connection variable.
jwshepherd's Avatar, Join Date: Aug 2007
Team Leader
Are you getting an error when the reate table is running?
kpsendilkumar's Avatar, Join Date: Sep 2007
Newbie Member
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.
venkatesanj@hcl.in's Avatar
Go4Expert Member
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.