BCP - Export data to Text File

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

BCP - Export data to Text File

Author

Topic

U A
Starting Member

11 Posts

Posted - 2010-11-08 : 07:44:20

i using this query to Export data to Text File
but i query is executing but i cant get the tet file in that location

will you please help me regarding this
------------------------------------------------
CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @str varchar(1000)
set @str='Exec db1..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -E'''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'

EXEC BCP_Text_File 'sam','E:\sam.txt'

U A

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 07:49:13

Does the file "sam.txt" exists on the server?

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 07:55:50

Try to use xp_cmdshell outside the variable.
set @str = 'bcp "Select * from ' + db_name() + '..' + @table + '" queryout "' + @FileName + '" -E'

exec master..xp_cmdshell @str

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 08:11:51

The file will be created on the Server's directory and not in your local system

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 08:22:08

madhi and Sachin are correct.
The problem will be that you are looking for the file on your client machine but the file is created on the server.

No, you're never too old to Yak'n'Roll if you're too young to die.

U A
Starting Member

11 Posts

Posted - 2010-11-11 : 01:10:25

Mr.sachin sam.txt is the filename of our own name . for this also we have to check whether it is in server?

for running in the local system what we have to do ? Mr.webfred

U A

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 04:35:39

quote:
Originally posted by U A

Mr.sachin sam.txt is the filename of our own name . for this also we have to check whether it is in server?

for running in the local system what we have to do ? Mr.webfred

U A

Use UNC path

\\system_name\folder_name\file_name

Madhivanan

Failing to plan is Planning to fail

U A
Starting Member

11 Posts

Posted - 2010-11-12 : 01:26:49

Thank You Madhivanan

U A

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-12 : 05:06:52