SQLState=S1000, Proc To Export Tables Into Flat Files

Hi,

I am trying to create a Procedure that will export all the tables present in the database to corresponding flat files. The below procedure builds fine but gives a run-time error as below:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

What am I missing?

Divakar

CREATE Procedure BCP_Text_File_5
AS
BEGIN
Declare @table varchar(100)
Declare @FileName varchar(100)
Declare @str varchar(1000)

DECLARE export_cursor CURSOR LOCAL FOR
select name from dbo.sysobjects where xtype = 'U' and name = 'TestTable'

open export_cursor
FETCH NEXT FROM export_cursor INTO @table


BEGIN
set @FileName = 'D:SqlData' + @table + '.txt'
set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+ db_name() + '..' + @table + '" queryout "'+ @FileName + '" -U sa -P sa -c'''
EXEC (@str)

FETCH NEXT FROM export_cursor INTO @table

END
CLOSE export_cursor
DEALLOCATE export_cursor
end