Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25

    Unanswered: Turn an array into a table?

    Hi,

    I'm starting to use the master..xp_cmdshell to achieve some directory listing and small task with transact SQL, but my main problem is that this procedure seems to return an array that i can't use in subqueries.

    Does anyone can help me turning the procedure result into something usable in subquerries?

    thanks alot!

    Gauthier

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Try inserting the resuls of xp_cmdshell into a temptable or a table variable. Something like:

    create table #tmp(result varchar(255))
    insert into #tmp exec xp_cmdshell 'dir c:\'
    select * from #tmp
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    Hi,

    It's simple but it accurately work!

    I've tried to manage like this but that don't work:

    declare @table table(result varchar(255))
    insert into @table exec xp_cmdshell 'dir c:'

    does the table named with # are temporary?
    did I need to drop them after use?

    Thanks alot for the dedicated help

    Gauthier

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The local table variable should work just fine.

    When you create a table with a '#' prefix the table is temporary to the session or stored procedure. You do not need to explicitly drop the table.

    Are you able to process result of the sp call?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    This strangely not work

    i get this error message:

    Server: Msg 197, Level 15, State 1, Line 2

    EXECUTE cannot be used as a source when inserting into a table variable.
    is there any advantage about table variable or # temp table?

    Thanks

    Gauthier

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Table variable were created to support returning results sets from user defined functions. Oddly enough if you look in the Books OnLine the following example is quoted...

    A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
    Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

    INSERT INTO table_variable EXEC stored_procedure

    SELECT select_list INTO table_variable statements.

    Apperantly there is a bug. '#' Temp table will work just fine. the are a little slow because you have to create the table each time but otherwise will work just fine.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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