October 16, 2015 at 4:34 am
Hi,
I'm currently trying to using this bcp script to export results to a csv.
USE crm001_spark
GO
EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [datebasename].dbo.table name]" queryout "C:\Customers.csv" -c -b 10000 -t , -T -S [server]\datebasename'
and the ERROR that is coming out is
SQLState = 08001, NativeError = -1
NULL
Error = [Microsoft][SQL Native Client]Login timeout expired
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
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 connecti
ons.
SQLState = S1T00, NativeError = 0
I have checked the server config manager TCP port and it is pointing to the correct port 1433.
Any ideas on what else could be causing the error ?
many thanks
October 16, 2015 at 4:43 am
Thanks for that , I changed it to just server. Now Its coming up with a different error
SQLState = 28000, NativeError = 18452
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.
NULL
October 16, 2015 at 4:50 am
Because you've specified a trusted connection (the -T) it's trying to use your windows login to access that server. If you don't have that login listed under Security\Logins for that server you'll get that.
Either create a login for your account on that server, or specify a SQL Server login instead (-U and -P).
October 16, 2015 at 5:01 am
Now im getting a Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
but the file does exist > C:\bcp and i have created a csv file [customers.csv] which is just a blank excel sheet
October 16, 2015 at 5:19 am
Spoke to one of our network guys and he said he will need to map the drive for it to be access. Which is causing my error.
My last problem would be , if i wanted to exported the results to have a tilda (~) to separate the values , how would i do it ?
October 16, 2015 at 6:43 am
You'll need to use a format file for that:
http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
October 16, 2015 at 10:21 am
Suth (10/16/2015)
Spoke to one of our network guys and he said he will need to map the drive for it to be access. Which is causing my error.My last problem would be , if i wanted to exported the results to have a tilda (~) to separate the values , how would i do it ?
That's what the -t is for. Just use -t~
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply