SQL Server xp_cmdshell fail to export data

Question

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:

What am I doing wrong?

score 0 · Accepted Answer · answered Nov 07 '15 at 04:50

0

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

answered Nov 07 '15 at 04:50

Spock

4,700
2
16
21

Yes the path does exist on the server, and yes i have tried c:\output, still same result. – PuchuKing33 Nov 07 '15 at 07:53
The path "C:\Users\b013904\Desktop\Output\bcptest.txt" also needs to be _accessible_ by the SQL Server service account. It looks like a user folder so it won't have access. You might not have access to C either with that account. – Nick.Mc Nov 07 '15 at 08:15
@Nick.McDermaid How can I give the SQL server service account rights to do it? Just to C:\ to make it easier? – PuchuKing33 Nov 07 '15 at 14:08
1

Don't put it in the root. Create a sub folder. Assign everyone full control on the folder to see if it is indeed a permissions issue. If you've identified it as a permission issue, then assign permission to the account that you're using top start sql server. – Spock Nov 07 '15 at 14:17
1

You can also try to execute the bcp statement above from a command line on the server to see if you're getting the same problem. If you don't, it also points to a permission issue. – Spock Nov 07 '15 at 14:19
I will give it a try, and see what happens. I will return when got any results, hope it works. Thanks – PuchuKing33 Nov 07 '15 at 20:21
Okay so it was rights problem. I created a sub folder in C:\ and gave it the rights and magic happened. Thanks for the help. – PuchuKing33 Nov 10 '15 at 13:41

SQL Server xp_cmdshell fail to export data

1 Answers1