Post reply

bcp question

  • September 20, 2010 at 7:43 pm

    #146973

    Hello All,

    I am trying to bcp out some data, but this is really becoming something that I need some help on.

    Basically, I was to bcp out a single row from a table into a .xdl file using below command:

    BCP "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [dbname].[dbo].[tablename] ORDER BY Id DESC" queryout xmlfile.xdl -T -c –q

    This works perfectly using cmd line.

    Also, when I go to

    cmd --> bcp ==> this has no queryout option

    but C:\Program Files\Microsoft SQL Server\90\Tools\Binn ==> bcp has queryout option.

    how can I do this using TSQL. I dont want to use any .bat file.

    below is what I have tried:

    declare @sql varchar(max)

    set @sql = '

    BCP "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [dbname].[dbo].[tablename] ORDER BY Id DESC" queryout xmlfile.xdl -T -c –q '

    exec xp_cmdshell @sql

    but this is giving errors.

    Can any one help or shed some knowledge ?

    Thanks,

    Cheers !

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 20, 2010 at 9:03 pm

    #1224478

    but this is giving errors

    It sure would be nice if you'd post exactly what those errors are. 😉

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

  • September 21, 2010 at 12:37 am

    #1224553

    Check your PATH variable, another version of bcp.exe is probably in it (or even the bcp from sybase).

  • September 21, 2010 at 7:43 am

    #1224816

    Hello,

    when I use this:

    declare @sql varchar(max)

    set @sql = '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\BCP.exe" "SELECT top (1) [Deadlockgraph].query(''/TextData/deadlock-list'') FROM dbname.dbo.tablename" queryout d:\xmlfile.xdl -T -c -q'

    exec master..xp_cmdshell @sql

    this is the error:

    Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1

    Procedure expects parameter 'command_string' of type 'varchar'.

    when I use this:

    DECLARE @sql nvarchar(1000),@Query nvarchar(300)

    SET @Query='"SELECT top (1) [Deadlockgraph].query(''/TextData/deadlock-list'') FROM dbname.dbo.tablename'

    set @sql = '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\BCP.exe" '

    SET @sql=@SQL+@Query+'" queryout "'+'d:\xmlfile.xdl'+N'" -c -q -T -Slocalhost'

    print @sql

    exec master..xp_cmdshell @sql

    this is the error:

    'C:\Program' is not recognized as an internal or external command,

    operable program or batch file.

    NULL

    Any ideas ...

    Cheers !

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 22, 2010 at 9:27 pm

    #1226038

    Any ideas whats wrong ? 😉

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 24, 2010 at 6:12 am

    #1226888

    I can't run your exact queries, so I came up the same kind of query as you had.

    You basically want the xp_cmdshell to do something like this:

    EXEC master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\BCP.exe" "SELECT top (1) * from pubs.dbo.authors" queryout "C:\temp\authors_top_1.txt" -c -q -T -Slocalhost'

    While the error message states that xp_cmdshell does not recoginize the quoted path of BCP command executables. I had the same error message on my local PC too. So this might be a limitation from xp_cmdshell procedure.

    But if I use:

    EXEC master..xp_cmdshell 'BCP "SELECT top (1) * from pubs.dbo.authors" queryout "C:\temp\authors_top_1.txt" -c -q -T -Slocalhost'

    This works fine with the following output:

    output

    NULL

    Starting copy...

    NULL

    1 rows copied.

    Network packet size (bytes): 4096

    Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)

    NULL

    So I think the simplest solution for you is the verfiy the PATH environment variable in your windows system and make command 'BCP' recognizable from a simple command line. Then you can stay away from those quoted long path of BCP.exe.

    Hope this helps, cheers!

    Bazinga!

  • September 24, 2010 at 7:05 am

    #1226934

    Hey mate

    I've found an ultimate solution for all the non-standard executables to be run in xp_cmdshell.

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    Here is the the query with the same functionality and it works perfectly:

    exec master..xp_cmdshell 'C:\PROGRA~1\MI6841~1\100\Tools\Binn\BCP.exe "SELECT top (100) * from pubs.dbo.authors" queryout "C:\temp\authors_top_100.txt" -c -q -T -Slocalhost'

    Here PROGRA~1 represents "Program Files" while MI6841~1 represents "Microsoft SQL Server".

    To get the shortened path names, you have to use dir /X (this is a capital X)in the target directory.

    I think this should be a definitive solution to the xp_cmdshell limitation on path names.

    Bazinga!

  • September 24, 2010 at 7:15 am

    #1226944

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • September 24, 2010 at 7:40 am

    #1226962

    sjimmo (9/24/2010)

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    The orginal poster and I have already tried to quote the path, but that doesn't work.

    That's why we have to revert back to the 'tilde' solution.

    Bazinga!

  • September 24, 2010 at 8:45 am

    #1227032

    sqlapprentice (9/24/2010)

    sjimmo (9/24/2010)

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    The orginal poster and I have already tried to quote the path, but that doesn't work.

    That's why we have to revert back to the 'tilde' solution.

    Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂

    Thanks a lot !

    Cheers !

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 30, 2010 at 10:46 pm

    #1230025

    SQL_Quest-825434 (9/24/2010)

    sqlapprentice (9/24/2010)

    sjimmo (9/24/2010)

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    The orginal poster and I have already tried to quote the path, but that doesn't work.

    That's why we have to revert back to the 'tilde' solution.

    Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂

    Thanks a lot !

    Cheers !

    Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

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

  • October 1, 2010 at 8:45 am

    #1230261

    Jeff Moden (9/30/2010)

    SQL_Quest-825434 (9/24/2010)

    sqlapprentice (9/24/2010)

    sjimmo (9/24/2010)

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    The orginal poster and I have already tried to quote the path, but that doesn't work.

    That's why we have to revert back to the 'tilde' solution.

    Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂

    Thanks a lot !

    Cheers !

    Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

    Hello Jeff,

    That's perfectly fine and at SSC we value everybody's time and effort. I appreciate your concern.

    Cheers ! 🙂

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • October 1, 2010 at 8:45 am

    #1230260

    Jeff Moden (9/30/2010)

    SQL_Quest-825434 (9/24/2010)

    sqlapprentice (9/24/2010)

    sjimmo (9/24/2010)

    The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

    This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

    The orginal poster and I have already tried to quote the path, but that doesn't work.

    That's why we have to revert back to the 'tilde' solution.

    Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂

    Thanks a lot !

    Cheers !

    Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

    Hello Jeff,

    That's perfectly fine and at SSC we value everybody's time and effort. I appreciate your concern.

    Cheers ! 🙂

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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