Results 1 to 6 of 6

Thread: output of xp_cmdshell

  1. #1
    Join Date
    Sep 2002
    Posts
    159

    output of xp_cmdshell

    Hi,

    Without using bcp or dts we want load

    some data from file to table
    ----
    create table #file
    (a varchar(8000))

    DECLARE @PATH_FILE VARCHAR (250)
    DECLARE @SQL VARCHAR (400)


    SET @PATH_FILE = '\\Mypc\tmp$\price.txt'
    SET @sql = 'findstr /b /c:"Z" ' + RTRIM (@PATH_FILE)

    insert into #file
    exec xp_cmdshell @sql

    select a from #file
    --
    Works fine if lenth of column in file <= 255 ,if lenth is >255
    all data after postion 255 inserted into new row

    Any way to insert all characters from one line into single row ?

    Thank you
    Alex

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    Not by using xp_cmdshell. It returns the output as nvarchar(255).

    Why don't you want to use bcp? You can use findstr with xp_cmdshell to find the rows you want and write them to another file and then bcp in that file. Or you could bcp in the whole file and delete the records you don't want.

  3. #3
    Join Date
    Sep 2002
    Posts
    159
    permissions

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    bcp doesn't require more permissions than executing xp_cmdshell.

  5. #5
    Join Date
    Sep 2002
    Posts
    159
    thank you Rawhide


    we will go with bcp


    Alex

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Just put user in bulk insert administrators server role, it's saver than xp_cmdshell.

Posting Permissions

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