Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: tempdb..table access problem

    Hello All,

    i am facing a vague problem...

    my parent proc(a) creates some temporary tables(tempdb..<tablename>)
    and then calls child proc b

    child proc(b) populates the temporary table created by parent proc (a)

    now when i executes 'a'....i get errors from 'b' saying tempdb..<tablename> not found.

    Any help would be great,

    --Pooja

  2. #2
    Join Date
    Apr 2003
    Posts
    54
    I hope proc a executes proc b. So proc a also creates some temp tables and then executes proc b which populates them. To do this you must have created the proc b before but at that time the temp tables were not created. Iwould suggest you to create the temp tables in the b proc rather tyhan doing it in the proc a which basically executes proc b. Hope this helps.......

    Regards

    Subhas

  3. #3
    Join Date
    Mar 2002
    Location
    india
    Posts
    7

    Re: tempdb..table access problem

    Hi,

    While you are creating temp table in tempdb you should not get any problem like this (there is a chance if you are creating # tables). I believe there is some thing wrong in the proc(a) while checking and droping the tempdb table if it already exists ( i guess!!). Plz make sure of the code in proc (a).

    Cheers..
    Gamini

  4. #4
    Join Date
    Dec 2002
    Posts
    104

    Re: tempdb..table access problem

    Originally posted by gamini
    Hi,

    While you are creating temp table in tempdb you should not get any problem like this (there is a chance if you are creating # tables). I believe there is some thing wrong in the proc(a) while checking and droping the tempdb table if it already exists ( i guess!!). Plz make sure of the code in proc (a).

    Cheers..
    Gamini
    hi!

    thanx for replying....
    proc a is properly checking ..if objects exists then only drop them and recreate. and table are made with tempdb..<tablename>.

    the series of procedure was working fine...i made some changes in proc b and got it complied..after that it stopped working...there is no syntactical error in proc b and working flow was not changed....

    do u think the way of complilation can cause some problem.??

    will recompling all the related objects will help??

    thanx for ur help
    --Pooja

  5. #5
    Join Date
    Apr 2003
    Posts
    54
    Can you post the entire sql please???

    Thanks

    Subhas

  6. #6
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by jaideep
    Can you post the entire sql please???

    Thanks

    Subhas
    i cant post real code....policy violation....

    but pose an example:

    create proc A
    as
    begin
    create table tempdb..abc(a1 char(5),a2 char(5))
    create table tempdb..abc1(a11 char(5),a21 char(5))
    create table tempdb..abc2(a12 char(5),a22 char(5))

    exec B
    end
    go

    create proc B
    as
    begin
    insert into abc (select abc1,abc2
    from tab1,tab2
    where tab1.col1= tab2.col1
    and tab1.col2='123')

    end
    go

    something like this....................

    now my proc got complied successfully...no error with it........

    But when i do

    exec A

    error comes which is something like

    tempdb..abc not found (this error comes from B)

    hope now muy problem wud be lil more clear to u

    ---Pooja

Posting Permissions

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