Exporting column using xp_cmdshell to plain text file error

Question

Exporting column using xp_cmdshell to plain text file error

Nicegirl13

SSC Enthusiast

Points: 133
More actions
November 6, 2015 at 3:27 pm

#322134

Hello everyone
I hope that i am posting the question in the right place. I am trying 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:\ExportTest.txt" -T -Ssit-dvh-test\SIT-DVH -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 mesage and no file is been created:
What am i doing wrong? i have the rights (sysadmin) and i am saving the file in the same server where the database is.
Thanks in advanced
Daniel

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I'm thinking that there are two possibilities here.

1. If you have sysadmin privs, xp_CmdShell doesn't run as you. It runs as the service login for SQL Server and, depending on how someone set that login up, it IS possible that login can't see the directory that you're trying to export out to.

2. Another possibility is the dashes in your server name. Change this...

Ssit-dvh-test\SIT-DVH

... to this ...

S"sit-dvh-test\SIT-DVH"

... and see what happens.

Shifting gears a bit, the root directory of drives isn't a good place to store files. I'd make a sub-directory and store the files there.

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

Nicegirl13 SSC Enthusiast Points: 133 More actions · Answer 2

Thanks Jeff for the reply, i will have a look at it and try it out, when get any results i will return 🙂

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 3

The problem is -c, which is /character mode. BCP in /character mode expects to be passed a /format file (or it will interactively prompt you for the output's format, which won't work with xp_cmdshell). You can verify that is the problem by running bcp.exe from cmd.exe - if you have to interact with bcp then you cannot use those arguments in xp_cmdshell.

If the BCP file will be going to another SQL Server, you should consider using /native mode (instead of /character mode), because no /format file is needed. On the other hand, if you truly need to use /character mode, you also need to use -f and ensure it points to a /format file that sqlservr.exe (more accurately, bcp.exe via xp_cmdshell) is able to read.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

SoHelpMeCodd (11/7/2015)
The problem is -c, which is /character mode. BCP in /character mode expects to be passed a /format file (or it will interactively prompt you for the output's format, which won't work with xp_cmdshell). You can verify that is the problem by running bcp.exe from cmd.exe - if you have to interact with bcp then you cannot use those arguments in xp_cmdshell.
If the BCP file will be going to another SQL Server, you should consider using /native mode (instead of /character mode), because no /format file is needed. On the other hand, if you truly need to use /character mode, you also need to use -f and ensure it points to a /format file that sqlservr.exe (more accurately, bcp.exe via xp_cmdshell) is able to read.

Careful now... Please check BOL where it states (emphasis is mine)...

-c
Performs the operation using a character data type. [font="Arial Black"]This option does not prompt for each field[/font]; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r (newline character) as the row terminator.

... and you do not need a format file for a query driven output especially with the "-c" option.

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 5

You are way too gentle Jeff. I struck out my comment - my apologies:)

Nicegirl13 SSC Enthusiast Points: 133 More actions · Answer 6

Thanks for the replies, i have sorted out the issue. It was rights problems. I created a Sub folder i C:\ and exportet there, magic happened and it worked 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Nicegirl13 (11/10/2015)
Thanks for the replies, i have sorted out the issue. It was rights problems. I created a Sub folder i C:\ and exportet there, magic happened and it worked 🙂

Glad that worked but don't stop now. I treat the C: drive as a sacred cow on all my servers. I strongly recommend that the C: drive be used only for the OS, Windows, and programs. I even allocate a separate "drive" just for the swap file to get it off my "C: drive".

Designate a spot (subfolder) on some other drive and go thru the throws of figuring out how to give the SQL Server login privs to read and write from and to there.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

SoHelpMeCodd (11/8/2015)
You are way too gentle Jeff. I struck out my comment - my apologies:)

I've seen your posts. You've got a good spirit and a great heart not to mention good knowledge. In spite of your knowledge, you remain humble. My old boss also said that "If you don't make mistakes along the way, then you're not pushing yourself hard enough". I couldn't let your post go but how could I get mad at someone that's trying to do the right thing?

Thank you for your correction and your feedback.

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

Nicegirl13 SSC Enthusiast Points: 133 More actions · Answer 9

Jeff Moden (11/10/2015)
Nicegirl13 (11/10/2015)
Thanks for the replies, i have sorted out the issue. It was rights problems. I created a Sub folder i C:\ and exportet there, magic happened and it worked 🙂
Glad that worked but don't stop now. I treat the C: drive as a sacred cow on all my servers. I strongly recommend that the C: drive be used only for the OS, Windows, and programs. I even allocate a separate "drive" just for the swap file to get it off my "C: drive".
Designate a spot (subfolder) on some other drive and go thru the throws of figuring out how to give the SQL Server login privs to read and write from and to there.

Good point, i will start with ones i have figuered out the issue with semi-colon.

Thanks Jeff