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

BCP using xp_cmdshell Expand / Collapse
Author
Message
Posted Tuesday, January 12, 2010 10:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
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
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
Post #846362
Posted Tuesday, January 12, 2010 11:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #846407
Posted Tuesday, January 12, 2010 12:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
no go
Post #846447
Posted Tuesday, January 12, 2010 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #846452
Posted Tuesday, January 12, 2010 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
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

Post #846456
Posted Tuesday, January 12, 2010 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
ok...how about the hyphen in the server name? wrap it in brackets?
-TECH-01 to -[TECH-01]


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #846457
Posted Tuesday, January 12, 2010 12:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
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.
Post #846466
Posted Tuesday, January 12, 2010 12:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
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?
Post #846475
Posted Tuesday, January 12, 2010 2:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 1:19 PM
Points: 11, Visits: 81
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.
Post #846571
Posted Tuesday, June 25, 2013 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 7:19 AM
Points: 1, Visits: 12
What permissions did you change to allow C:\blah access?
Post #1467100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse