BCP through XP_CMDSHELL not responding

Question

BCP through XP_CMDSHELL not responding

diegoanckizes

SSC Veteran

Points: 239
More actions
October 9, 2013 at 10:19 am

#299028

Hi.
I'm trying to create a file from a query executing xp_cmdshell with BCP command, but it does not finish. No error messages are shown. It keeps executing query forever. I have to end the proccess by windows task manager.
I did:
check MSSQLSERVER permissions on folders -> It is ok;
run the same BCP command on CMD -> worked well;
run a mkdir on xp_cmdshell (no BCP commands) in SQL SERVER -> worked well.
Any clue?
Running on SQL SERVER 2012.
TY.

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

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

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

diegoanckizes (10/9/2013)
Hi.
I'm trying to create a file from a query executing xp_cmdshell with BCP command, but it does not finish. No error messages are shown. It keeps executing query forever. I have to end the proccess by windows task manager.
I did:
check MSSQLSERVER permissions on folders -> It is ok;
run the same BCP command on CMD -> worked well;
run a mkdir on xp_cmdshell (no BCP commands) in SQL SERVER -> worked well.
Any clue?
Running on SQL SERVER 2012.
TY.

How long does the query take to run when you execute it in SSMS?

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

diegoanckizes SSC Veteran Points: 239 More actions · Answer 2

diegoanckizes

SSC Veteran

Points: 239

October 9, 2013 at 12:18 pm

#1657269

Less then a second.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Can you post the xp_CmdShell call that you're using, please? I have a couple of ideas on the problem but I need to see that command. If it's not a trusted connection please replace the user name and password with "username" and "password" respectively. You can also replace the server name with "servername" if it makes you nervous to have that info out 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)

diegoanckizes SSC Veteran Points: 239 More actions · Answer 4

That thing:

EXEC('EXEC MASTER..XP_CMDSHELL ''BCP "SELECT * FROM [[db]]..[table]" QUERYOUT "C:\Users\processos\teste\TESTE.TXT" -t; -r -c -CRAW -U[user] -P[[pass]]''')

TY

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

diegoanckizes (10/9/2013)
That thing:
EXEC('EXEC MASTER..XP_CMDSHELL ''BCP "SELECT * FROM [[db]]..[table]" QUERYOUT "C:\Users\processos\teste\TESTE.TXT" -t; -r -c -CRAW -U[user] -P[[pass]]''')
TY

You dont' need the double EXEC and you don't need the doubled-up brackets.

EXEC MASTER..XP_CMDSHELL 'BCP "SELECT * FROM [db]..
" QUERYOUT "C:\Users\processos\teste\TESTE.TXT" -t; -r -c -CRAW -Uuser -Ppass'

It's also a very bad idea to use explicit user names and passwords. You should be using a trusted connection to that such things aren't in clear text.

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

diegoanckizes SSC Veteran Points: 239 More actions · Answer 6

diegoanckizes

SSC Veteran

Points: 239

October 10, 2013 at 6:12 am

#1657477

Still not working. ='(

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

diegoanckizes (10/10/2013)
Still not working. ='(

Any error messages or output from the command? Can you give me clue as to what you mean by "still not working"?

--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: 1003857 More actions · Answer 8

Ah... something I missed. You haven't told it which server to run the command against. Add the -s parameter with the server/instance name and see if that helps.

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

diegoanckizes SSC Veteran Points: 239 More actions · Answer 9

No error messages are shown. It keeps 'executing query' forever. I have to end the BCP proccess by windows task manager.

I did try -S.

None changed.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

I'll give it a try when I get home from work...

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

diegoanckizes SSC Veteran Points: 239 More actions · Answer 11

diegoanckizes

SSC Veteran

Points: 239

October 11, 2013 at 6:51 am

#1657885

Thanx

diegoanckizes SSC Veteran Points: 239 More actions · Answer 12

I DID IT!!

A friend sent me his BCP.exe and it worked well. I was testing directly in CMD by a BCP.exe that was not in PATH. When I runned by command the SQL SERVER it used the BCP.exe in path (bad one).

Thanx !!!!