January 12, 2010 at 10:28 am
I'm using SQL Server 2005 Express and I'm trying to run this:
declare @sql varchar(8000)
select @sql = 'bcp AICS_Employees.dbo.Employee out
C:\bcptest3.txt -c -t, -T -S TECH-01'
exec master..xp_cmdshell @sql
The error I'm getting is the basic bcp syntax error:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
The BCP I'm running works perfectly from the the cmd prompt and I have the xp_cmdshell enabled in the surface area config.
I'm using this as my source
January 12, 2010 at 11:37 am
all my snippets I'm looking at do not have a space after any of the flags, for exampe -S TECH-01
should be -STECH-01
SET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -T -c -Usa -PNotARealPassword -SD223\SQLEXPRESS'
--or
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T -Usa -PNotARealPassword -SD223\SQLEXPRESS'
'
Lowell
January 12, 2010 at 12:26 pm
no go 🙁
January 12, 2010 at 12:35 pm
it's probably permissions then...the service account does not have access to the C:\ drive.
if you change it to just the file name , without the C:\, the file would get created in one of the folders under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn, for example...your specific path would depedn on your installation.
Lowell
January 12, 2010 at 12:43 pm
eliminating the c:\ still gives me the same error, I am however getting a new error when i run this:
Execute master.dbo.xp_cmdshell 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T -Usa -Ppw -TECH-01'
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
January 12, 2010 at 12:45 pm
ok...how about the hyphen in the server name? wrap it in brackets?
-TECH-01 to -[TECH-01]
Lowell
January 12, 2010 at 12:51 pm
Lowell (1/12/2010)
ok...how about the hyphen in the server name? wrap it in brackets?-TECH-01 to -[TECH-01]
Back to the original error. I think my settings are failing me.
January 12, 2010 at 12:55 pm
actually i ran
EXEC master.dbo.xp_cmdshell 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T -Usa -Ppw -S[TECH-01]'
and i am getting
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 connecti
ons.
NULL
clearly looking like my login is whats doing it, anyways i can have this default?
January 12, 2010 at 2:49 pm
In the end I got it to work. You were right, it was an issue with permissions on the c:\ but just simply not specifying a path didn't help.
I ended up with
declare @sql varchar(8000)
select @sql = 'bcp AICS_Employees.dbo.Employee out c:\bcp\bcptest3.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql
worked perfectly.
If I simply changed the path to c:\
declare @sql varchar(8000)
select @sql = 'bcp AICS_Employees.dbo.Employee out c:\bcptest3.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql
i get this error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
thanks again for your help.
June 25, 2013 at 6:26 am
What permissions did you change to allow C:\blah access?
July 22, 2015 at 11:22 am
Hey,
I know this is a very old thread but I figured I might as well add what I found.
This is an example query that gave my the syntax error:
select @sql = 'bcp "SELECT * FROM syslogins"
queryout J:\bcp.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
My issue was my line break in my @sql string.
Basically, instead of being interpreted as
bcp "SELECT * FROM syslogins" queryout J:\bcp.csv -c -t, -T -S<server>
the cmd exec would run this
bcp "SELECT * FROM syslogins"
queryout J:\bcp.csv -c -t, -T -SUSGDC12S004-DEV\DBA
This means it would only run this part "bcp "SELECT * FROM syslogins"" as it's the first line.
All I had to do was put everything back on the same line like so:
select @sql = 'bcp "SELECT * FROM syslogins" queryout J:\bcp.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
I hope this makes sense.
September 12, 2016 at 2:55 pm
Appreciate the reply as I was hitting the same error and it was line breaks also
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply