Problem getting BCP out to work in SQL server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem getting BCP out to work in SQL server 2005

Author

Topic

GIMIBEL
Starting Member

3 Posts

Posted - 2009-07-26 : 11:07:33

Hello everybody,

SQL server 2005 info:

productversion - 9.00.1399.06
productlevel - RTM
edition - Express Edition

I've been trying to get BCP to work in order to export some data to a CSV file, the following query ran on SQL server machine with Name Pipe enabled.

-------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @SQLCmd VARCHAR(255)

SET @SQLCmd = 'bcp "SELECT * FROM EVEREST_FCM.DBO.FILE_DOWNLOAD " queryout ' +
' c:\test\SaleBackOrder.csv' + ' /T /c /a 516 /S @@SERVERNAME /U sa /P everest'

select @SQLCmd

EXEC master..xp_cmdshell @SQLCmd

----------------------------------------------------------------------------

output

bcp "SELECT * FROM EVEREST_FCM.DBO.FILE_DOWNLOAD " queryout c:\test\SaleBackOrder.csv /T /c /a 516 /S @@SERVERNAME /U sa /P everest

SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

I try with -T parameter and with -U & P parameters and got the same result.

BCP in SQL 2005 express does not work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 11:27:11

if you are using trusted connection, use -T


SET @SQLCmd = 'bcp "SELECT * FROM EVEREST_FCM.DBO.FILE_DOWNLOAD " queryout ' +
' c:\test\SaleBackOrder.csv' + ' -T -c -a 516 -S ' + @@SERVERNAME

else specify the ID and password via the -U -P


SET @SQLCmd = 'bcp "SELECT * FROM EVEREST_FCM.DBO.FILE_DOWNLOAD " queryout ' +
' c:\test\SaleBackOrder.csv' + ' -c -a 516 -S ' + @@SERVERNAME + ' -U sa -P everest'

KH
[spoiler]Time is always against us[/spoiler]

GIMIBEL
Starting Member

3 Posts

Posted - 2009-07-28 : 08:47:26

I tried both ways with the same negative results.

Cat it be becouse of SP never applied?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-28 : 09:05:07

I did something similar like this

DECLARE @bcpCommand VARCHAR(8000)

SET @bcpCommand = 'bcp SELECT * FROM dbo.Table (NoLock) queryout '
SET @bcpCommand = @bcpCommand + 'C:\TableExtract.txt -c -t"|" -S ServerName -T'

EXEC xp_cmdshell @bcpcommand xp_cmdshell @bcpcommand

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 09:09:13

yeah -- one or the other (trusted) not both.

Either -T OR (exclusive) -S & -P

Now, is it possible that the sqlserver you are running this on can't resolve the name you are passing?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GIMIBEL
Starting Member

3 Posts

Posted - 2009-07-28 : 22:21:16

thank a lot for responses.

I never used both -T and -U & -P, it was just sample.
I ran the similar script on SQL server command prompt with the same result.

May SP3 correct the problem? No SP had been applied to the server.

Greg.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources