Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: Unable to Locate table

    I am creating a table on the fly and then trying to associated the table
    inside of a cube, using vbs script. For some reason I get a table not found
    error when I try and open the cube, but when I look at the SQL server I am
    able to see the table and I can insert the table via analysis manager, but I
    get Unable to retrieve the list of columns for the <table name> table.

    Any Ideas why I am not able to add the table from the analyzer?

  2. #2
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    try out doing the insertion within a transaction..shud work
    as table creation is a DDL and its autocommit so no issue of table not found.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Who is the owner of the newly created table? "dbo", or a username? If a username, then your cube will not be able to see the table unless it is logged in as the same user, or you explicitly grant permissions and include the object owner when referencing the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2006
    Posts
    5

    Rights

    I have tried to create the table as dbo and as the user, both do not work, even if I grant rights to the user.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, first, it is never a good idea to be creating tables on the fly as part of your business processing. Only temporary tables and table variables should be created dynamically.

    Second, clarification: can you cube NEVER see the table, or is it only not available immediately after it is created? What I mean is, after the table has been created and you can view it in Enterprise Manager, can your cube see it then? Can you refresh your cube's datasource and see the data? If so, then it may be a transactional issue as pavan was suggesting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2006
    Posts
    5
    OK I will try inserting it in a transaction. I am not able to ever get the data from the table even if I create a new data source. I will let you know about the transaction idea.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are not ever able to get the data, then it is almost certainly an ownership/permissions issue. Please recheck how your cube is accessing the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2006
    Posts
    5
    Ok the transaction did not work. I am granting permissions after I create the table then inserting data.

    If I create a new datasource, create a new cube, then attempt to access the table I get the following error message:

    The selected table lacks the required numeric columns.

    So I added a primaryKey and I still get this error message

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Spanky411
    If I create a new datasource, create a new cube, then attempt to access the table I get the following error message:

    The selected table lacks the required numeric columns.

    So I added a primaryKey and I still get this error message
    OK, so it may not be a connectivity issue, or you may actually have two issues to deal with. I confess that I don't have much experience with cubes, but I'm wondering about the datatypes of the table you are creating. Is it possible that some columns are being created as strings rather than expected numeric datatypes? Post the DDL for the table that is created.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2006
    Posts
    5
    i feel like an idiot. Thank you for your help. The problem had to do with an extra space in the name. I can't believe that caused all my grief.

    You have a great day.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Spanky411
    i feel like an idiot.
    Like nobody on this forum has ever made such a mistake! Sometimes you just need an extra set of eyes (even from the blindman).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •