Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

BCP through XP_CMDSHELL not responding Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:24 AM
Points: 7, Visits: 15
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.
Post #1503197
Posted Wednesday, October 9, 2013 11:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503224
Posted Wednesday, October 9, 2013 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:24 AM
Points: 7, Visits: 15
Less then a second.
Post #1503247
Posted Wednesday, October 9, 2013 3:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503317
Posted Wednesday, October 9, 2013 4:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:24 AM
Points: 7, Visits: 15
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
Post #1503324
Posted Wednesday, October 9, 2013 5:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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]..[table]" 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503328
Posted Thursday, October 10, 2013 6:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:24 AM
Points: 7, Visits: 15
Still not working. ='(
Post #1503501
Posted Thursday, October 10, 2013 8:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503603
Posted Thursday, October 10, 2013 8:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503605
Posted Thursday, October 10, 2013 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:24 AM
Points: 7, Visits: 15
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.
Post #1503783
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse