Search code examples
sql-serversql-server-2008t-sqlxp-cmdshell

SQL Server xp_cmdshell fail to export data

I want to export values from a column (TcpIpAddress) from a table called dbo.DimServere to a plain text (located in the server). I have sysadmin rights.

    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1;
    GO
    -- To update the currently configured value for advanced options.
    RECONFIGURE;
    GO
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1;   -- 1 for at enable
    GO
    -- To update the currently configured value for this feature.
    RECONFIGURE;
    GO
    
    -- Extracting information from the databse
   EXEC xp_cmdshell 'bcp "SELECT TcpIpAddress FROM [SIT-DVH].[dbo].[DimServere]" queryout "C:\Users\b013904\Desktop\Output\bcptest.txt" -T -c -t,'
    
    
    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1;
    GO
    -- To update the currently configured value for advanced options.
    RECONFIGURE;
    GO
    -- To disable the feature.
    EXEC sp_configure 'xp_cmdshell', 0;   -- 0 for at disable
    GO
    -- To update the currently configured value for this feature.
    RECONFIGURE;
    GO

However, when I run this script, I get the following message and no file is been created:

enter image description here

What am I doing wrong?

Solution

  • The path in that bcp statement will be relative to the server since you're executing it on the server.

    Does that path exist on the server?

    Also, try changing the path to something more accessible like c:\output. .. then you can play around with the permissions on that folder to ensure that is not a os permission that's causing the statement to fail.

    Hope that helps