Trying to export a query to a text file

[bhp]

I have written the following simple SP, but it does not seem to create the file, and when I simply execute it from query anaylser it return null. I have checked the location is write and that the select statement works independantly.
Does anyone have any ideas:



CREATE proc Crystaldump
as
begin
DECLARE @bcpString varchar(250)

SET @bcpString = 'bcp "select * from contract_paymentdate
where datediff(day, payment_date, getdate()) = 0"
'+
'queryout C:\TextFile\contract.txt -t'

EXEC master..xp_cmdshell @bcpString
end
GO

 

[db999]

The following bcp example is from BOL

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword

The above example works fine for me both through the command shell and when I wrap it in a stored procedure and use your example running bcp through xp_cmdshell. I notice that you have not specified a server, username and password in your example?

 

[bhp]

Thankss for your response but even if I give the server name and password, it still does not export the file.
If I execute the query it returns NUll in qeury analyser.
Regards - James

 

[wilk]

The reason it would appear it is not working is you have not specified the table name where you are getting it from

eg pubs..authors

pubs is the database and authors is the table. so the format is database..tablename

 

[bhp]

Still can't get it to work, here is the code minus the sa pword:

CREATE proc Crystaldump
as
begin
DECLARE @bcpString varchar(250)
SET @bcpString = 'bcp "select * from whatifdb..contract_paymentdate
where datediff(day, payment_date, getdate()) = 0"
'+'queryout C:\TextFile\contract.txt -C -SEFACSUK -Usa -P'
EXEC master..xp_cmdshell @bcpString
end
GO

Regards....

 

[wilk]

try using the flags -c -T instead of the -C -SEFACSUK -Usa -P' this will use a trusted connection.

There could also be another problem with rights to bcp but try that first.

either that or run it from a batch file, they are very underated these days but still very powerful and after all this is what you are doing

 

[bhp]

Tried using these flags but still no joy!!!
Regards....

 

[db999]

does the bcp command work if you run it direct from the command shell? There could be a problem with your bcp application.

 

[wilk]

it is case sensitive, i have got it working on mine with this stored procedure

CREATE proc Crystaldump
as
begin
DECLARE @bcpString varchar(250)
declare @quote varchar(1)
set @quote = ''''
SET @bcpString = 'bcp "select * from enigmapharmacy..trapatient" queryout C:\contract.txt -c -T'

EXEC master..xp_cmdshell @bcpString
end
GO

 

[bhp]

Must be something else wrong here have tried using your syntax apart from the location and still no luck!
Regards ...

 

[wilk]

have a look at the rights you have, there is a bulk insert administrators permision, this could have something to do with it

 

[bhp]

Not sure if this makes a difference but the server the db sits on is windows only I myself have sa permissions!
Regards .....