Bcp Error with SQL 2005

Question

Bcp Error with SQL 2005

Simon Parry

Ten Centuries

Points: 1297
More actions
September 4, 2008 at 4:49 am

#193121

Hi All
We have just upgaded our old sql server from 2000 to a 2005 cluster server
I am now having problems running some bcp commands
The command i am running is
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o c:\invoice\template\bcpOutput.txt'
the error i am getting is
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
i have tried to add in -U and -P but then get the below
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
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 have also tried adding in -SSERVERNAME\INSTANCE
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o -STCSQL1\sqlcluster c:\invoice\template\bcpOutput.txt'
but i get the same error saying it could not open a connection to SQL server
Can anyone help at all ?
thanks
Simon

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

You need to use -S to specify the name of the server/instance you are trying to connect to AND either the -T (if you are using Windows Authentication) or -U and -P (if you are using SQL authentication).

Simon Parry Ten Centuries Points: 1297 More actions · Answer 2

Hi

i have tried adding

-T

bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o -T -SSQLCLUSTER\SSQLSERVER c:\invoice\template\bcpOutput.txt

but then i get the error

bcp: no output file specified: No Error

any ideas?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

You've added the -S in the middle of the -o parameter, try:-

bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -T -SSQLCLUSTER\SSQLSERVER -o c:\invoice\template\bcpOutput.txt

Simon Parry Ten Centuries Points: 1297 More actions · Answer 4

Hi

I tried as you suggested using

bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -T -SSQLCLUSTER\SSQLSERVER -o c:\invoice\template\bcpOutput.txt

but i now get the error

SQLState = 08001, NativeError = 87

Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Connection string is not valid [87].

SQLState = HYT00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timeout expired

SQLState = 08001, NativeError = 87

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.

any ideas ?

thanks

Simon

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

The sysntax is right, but now it either can't find the server, or the server won't allow the remote connection. Are you sure there isn't a typo in the instance name... does it really have 2 S's

bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -T -SSQLCLUSTER\SSQLSERVER -o c:\invoice\template\bcpOutput.txt

Simon Parry Ten Centuries Points: 1297 More actions · Answer 6

Hi

I think i had the wrong data like you suggested

i have changed it to

bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -T -SSQLCLUSTER -o c:\invoice\template\bcpOutput.txt

and it now works ok

thanks for your help

Simon