Temporary table not getting created

Discussion in 'SQL Server' started by kpsendilkumar, Sep 11, 2007.

  1. kpsendilkumar

    kpsendilkumar New Member

    Joined:
    Sep 11, 2007
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: Sep 11, 2007
  2. jwshepherd

    jwshepherd New Member

    Joined:
    Aug 13, 2007
    Messages:
    135
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    VP Technology Corporation
    Location:
    Texas
    Home Page:
    http://www.officialhackers.com
    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.
     
  3. kpsendilkumar

    kpsendilkumar New Member

    Joined:
    Sep 11, 2007
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Thanks for your reply jwshepherd. But I am able to create other temporary tables with the same connection variable.
     
  4. jwshepherd

    jwshepherd New Member

    Joined:
    Aug 13, 2007
    Messages:
    135
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    VP Technology Corporation
    Location:
    Texas
    Home Page:
    http://www.officialhackers.com
    Are you getting an error when the reate table is running?
     
  5. kpsendilkumar

    kpsendilkumar New Member

    Joined:
    Sep 11, 2007
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  6. venkatesanj@hcl.in

    venkatesanj@hcl.in New Member

    Joined:
    Oct 19, 2007
    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    0
    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.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice