My bcp is no output when run at sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

My bcp is no output when run at sp

Author

Topic

nut
Starting Member

8 Posts

Posted - 2012-03-21 : 09:14:24

Any one help me please? What is wrong
I use window2008 64bit and sql2008r2std
I have a problems when I run command in cmd mode(at server)
my command is
SET @command = 'bcp "select * from [dbInvoiceAX].dbo.InterfaceSaleOrder" queryout e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -Usa -P -fe:\data\temp\nut\ARAX1.xml'

when run result is 'Command(s) completed successfully.'
But No file , No output

nut
Starting Member

8 Posts

Posted - 2012-03-21 : 09:24:38

Sorry my full sp is
CREATE PROCEDURE bcpaxtext
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT OFF;
DECLARE @command varchar(5000), @localPath varchar(80), @sqltext varchar(255)
SET @localPath = '\\172.16.58.249\Data\Temp\nUT\'
SET @sqltext = 'select * from [dbInvoiceAX].dbo.InterfaceSaleOrder'
-- -S172.16.58.249 -Usa -P
--SET @command = 'bcp "'+ @sqltext +'" queryout ' + @localPath + 'bcptest.csv' +'-c -T -f'+@localPath+'errorlog.log'+ ' -f'+@localPath+'ARAX1.xml'
SET @command = 'bcp "select * from [dbInvoiceAX].dbo.InterfaceSaleOrder" queryout e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -Usa -P -fe:\data\temp\nut\ARAX1.xml'
--EXEC master..xp_cmdshell @command
exec master..xp_cmdshell @command
END
GO

Drop PROCEDURE bcpaxtext

GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 09:36:59

First, you don't need queryout

Just do

SET @command = 'bcp [dbInvoiceAX].dbo.InterfaceSaleOrder out e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -Usa -P -fe:\data\temp\nut\ARAX1.xml'

Second, just where are you looking. It had better be on the server

Can you post the messages that you are getting?

and please don't use sa...for anything

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nut
Starting Member

8 Posts

Posted - 2012-03-22 : 09:37:23

Thank you Brett.
Query out I just try.
Yes, I run this SP on server.
When I run This SP.msg is "Command(s) completed successfully."
But no file create.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 09:45:33

WHERE do you expect the file output to be?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nut
Starting Member

8 Posts

Posted - 2012-03-22 : 10:43:04

Dear Brett,

e:\data\temp\nut
Thank.

nut
Starting Member

8 Posts

Posted - 2012-03-22 : 10:44:59

Dear Bertt,

I ready find "bcptest.csv" in my computer but not found.

nut
Starting Member

8 Posts

Posted - 2012-03-22 : 10:47:26

But when I run
'bcp "select * from [dbInvoiceAX].dbo.InterfaceSaleOrder" queryout e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -Usa -P -fe:\data\temp\nut\ARAX1.xml'
form command prompt. I can get file from this path "e:\data\temp\nut"

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 11:08:12

I'm assuming that the database is on a Server?

THAT'S where the file is, on the server's E:\ Drive

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nut
Starting Member

8 Posts

Posted - 2012-03-23 : 04:46:53

Yes Database is on same server.
All my test csv file is on server E: Drive
path is e:\data\temp\nutI run at command prompt I can get file e:\data\temp\nut\bcptest.csv
so, I delete
and run as procedure. I don't get any file.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-23 : 09:20:04

Try This


SET NOCOUNT OFF;
DECLARE @command varchar(5000), @localPath varchar(80), @sqltext varchar(255)
SET @command = 'bcp [dbInvoiceAX].[dbo].[InterfaceSaleOrder] out e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -T'
print @command
--exec master..xp_cmdshell @command
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nut
Starting Member

8 Posts

Posted - 2012-03-23 : 09:26:10

I try to run as your advise
SET NOCOUNT OFF;
DECLARE @command varchar(5000), @localPath varchar(80), @sqltext varchar(255)
SET @command = 'bcp [dbInvoiceAX].[dbo].[InterfaceSaleOrder] out e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -T'
print @command
--exec master..xp_cmdshell @command
GO
result is
message=>bcp [dbInvoiceAX].[dbo].[InterfaceSaleOrder] out e:\data\temp\nut\bcptest.csv -c -S172.16.58.249 -T
But No file e:\data\temp\nut\bcptest.csv

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-23 : 10:42:21

ummm..uncomment the exec please

Do you have a c drive as well?

SET @command = 'bcp [dbInvoiceAX].[dbo].[InterfaceSaleOrder] out c:\bcptest.csv -c -S172.16.58.249 -T'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources