Post reply

compilation error

  • February 2, 2009 at 11:48 pm

    #206148

    I got following error while this x_TruncateTable proc:

    Server: Msg 197, Level 15, State 1, Procedure x_TruncateTable, Line 126

    EXECUTE cannot be used as a source when inserting into a table variable.

    plz clarify on this.I am executing it on sql server 2000.

    thx

  • February 3, 2009 at 1:11 am

    #936941

    can you post the code?

    -Vikas Bindra

  • February 3, 2009 at 1:45 am

    #936948

    As the code is not posted, I am guessing you are doing something like:

    INSERT INTO @yourTableVar Execute yourSP

    Please note the following things about the table variables

    - You can not use the table variable to get the output of stored procedure.

    - You can not use SELECT INTO with table variables.

    - you can not use table variable in: INSERT INTO @tableVar SELECT * FROM anyTable.

    and lot more...

    -Vikas Bindra

  • February 3, 2009 at 7:54 am

    #937142

    vikas bindra (2/3/2009)

    you can not use table variable in: INSERT INTO @tableVar SELECT * FROM anyTable.

    You can't?

    create table TestingVariables (id int, strng varchar(10))

    insert into TestingVariables (id, strng) values (1,'a')

    insert into TestingVariables (id, strng) values (2,'b')

    insert into TestingVariables (id, strng) values (3,'c')

    insert into TestingVariables (id, strng) values (4,'d')

    GO

    declare @Testng2 table (id int, somestring varchar(10))

    insert into @Testng2

    select * from TestingVariables

    select * from @Testng2

    GO

    drop table TestingVariables

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 4, 2009 at 12:23 am

    #937736

    Hey Gail! Was this possible in SQL 2000 also?

    -Vikas Bindra

  • February 4, 2009 at 9:03 am

    #938063

    vikas bindra (2/4/2009)

    Hey Gail! Was this possible in SQL 2000 also?

    Try it and find out. My SQL 2000 instance is 400km away.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 4, 2009 at 10:58 pm

    #938606

    I too doesn't have SQL 2000.

    -Vikas Bindra

  • February 5, 2009 at 11:08 am

    #939056

    It workd on SQL 2000 ...

    My version -

    --------------------------------------------------------------------------------------------------------------------------------

    Microsoft SQL Server 2000 - 8.00.2273 (Intel X86)

    Mar 7 2008 22:19:58

    Copyright (c) 1988-2003 Microsoft Corporation

    Stand

    (1 row(s) affected)

    The results:

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (4 row(s) affected)

    id somestring

    ----------- ----------

    1 a

    2 b

    3 c

    4 d

    (4 row(s) affected)

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 5, 2009 at 1:36 pm

    #939167

    rudy komacsar (2/5/2009)

    It workd on SQL 2000 ...

    Thanks for the test.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2009 at 1:23 am

    #939337

    Thanks Rudy!!

    -Vikas Bindra

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply