Post reply

How to use relative path in BULK INSERT

  • May 23, 2006 at 12:55 pm

    #112414

    Bulk Insert takes full path for data file and format file. This is sometimes not desired, as now I store data in %TMP% and format file in relative path.

    I guess we cannot change the way BULK INSERT doing things. But is there a way in SQL environment to get full name of the current path and the system temp path %TMP%.

    Thanks

  • May 23, 2006 at 1:42 pm

    #639591

    Try calling API

     

    ExpandEnvironmentVariable

    N 56°04'39.16"
    E 12°55'05.25"

  • May 23, 2006 at 2:01 pm

    #639596

    Thanks.

    Is this API in SQL environment or .NET environment ? Can it be called in SQL 2000 Trans-SQL ? I could not find it in BOL.

  • May 23, 2006 at 10:05 pm

    #639668

    Try this from Query Analyzer...

    EXEC Master.dbo.xp_DirTree 'C:\',0,1

    Then, read up on the dangers of using undocumented features.  Also keep in mind that Microsoft can also change or eliminate API's just as quickly as they can undocumented features.

    By the way, the "0" in the above controls how many levels to search down.  "0" means all levels... any other number is the number of levels to search.

    The "1" means to list files names, as well.  Try it and put the output into a table (you MUST have an autonumbering clustered PK on the table for the output to be usable in multi-levels) using INSERT/EXEC.

    Unlike xp_CmdShell, everyone can use xp_DirTree...

    --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)

  • May 24, 2006 at 7:24 am

    #639760

    You can get the value of the TMP environment variable (on the server) using something like this:

    CREATE TABLE #path

    (

      tmpPath varchar(200)

    )

    DECLARE @path varchar(200)

    INSERT #path EXEC master.dbo.xp_cmdshell 'SET TMP'

    SELECT @path = tmpPath FROM #path WHERE tmpPath IS NOT NULL

    PRINT @path

    DROP TABLE #path

     

Viewing 5 posts - 1 through 4 (of 4 total)

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