Post reply

Export/Import Task

  • November 25, 2010 at 11:37 pm

    #142755

    Hi,

    I am trying to copy the records from source server A to Destination server b by using the sql server task import/export wizard by using the query. While placing the below query saying that its not valid sql statement .

    sp_executesql @statment = N' create table #chk (a int )

    insert #chk

    select 1

    select * from #chk

    '

    but same query running successfully in the query analyzer window .

    Can anyone explain the issues on this ?

  • November 27, 2010 at 11:20 am

    #1255980

    This will work if you use table variable instead of temp table:

    EXEC sp_executesql @statment = N' DECLARE @t1 TABLE

    (

    ID int

    )

    insert @t1

    select 1

    select * from @t1'

  • November 27, 2010 at 9:38 pm

    #1255997

    magasvs (11/27/2010)

    This will work if you use table variable instead of temp table:

    EXEC sp_executesql @statment = N' DECLARE @t1 TABLE

    (

    ID int

    )

    insert @t1

    select 1

    select * from @t1'

    Thanks for your suggestion. Here issue is , i want store the out put of the extended stored procedures result on the temporary tables since its not possible to store the result sets of the stored procedures in table variables at sql server 2000 .

  • November 28, 2010 at 9:35 am

    #1256038

    sudhakara (11/25/2010)

    Hi,

    I am trying to copy the records from source server A to Destination server b by using the sql server task import/export wizard by using the query. While placing the below query saying that its not valid sql statement .

    sp_executesql @statment = N' create table #chk (a int )

    insert #chk

    select 1

    select * from #chk

    '

    but same query running successfully in the query analyzer window .

    Can anyone explain the issues on this ?

    It's a scope problem. Create the Temp table outside of the dynamic SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2010 at 9:36 am

    #1256039

    As a side bar, wouldn't a simple insert from one table to another work?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2010 at 10:05 am

    #1256041

    I agree with Jeff, scoping is an issue, but also, why the temporary table?

  • November 28, 2010 at 7:05 pm

    #1256082

    Jeff Moden (11/28/2010)

    sudhakara (11/25/2010)

    Hi,

    I am trying to copy the records from source server A to Destination server b by using the sql server task import/export wizard by using the query. While placing the below query saying that its not valid sql statement .

    sp_executesql @statment = N' create table #chk (a int )

    insert #chk

    select 1

    select * from #chk

    '

    but same query running successfully in the query analyzer window .

    Can anyone explain the issues on this ?

    It's a scope problem. Create the Temp table outside of the dynamic SQL.

    tired,its not accepting .

  • November 29, 2010 at 10:07 am

    #1256328

    Can you provide more information on what didn't work?

Viewing 8 posts - 1 through 7 (of 7 total)

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