Kenneth Wilhelmsson (11/16/2005)
Whenever I get this message, it's because of one ofthree things:
1) The path/filename is incorrect (check your typing / spelling)
2) The file does not exist. (make sure the file is where you expect it to be)
3) The file is already open by some other app. (close the other app to release the file)
For 1) and 2) - rememberthat <EM>paths arerelative to where bcp is executing.</EM> Make sure that bcp.exe can access the file/path from it's context.
/Kenneth
adding one more point to Kenneth's points
4) Ensure your SQL Server Agent is Enabled / Running, else make it enabled..
Note: Because this point was the issue in my case.
sample Code I executed is as below:
use AdventureWorks2008R2
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'ad hoc distributed queries',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
--exec master..xp_fixeddrives
declare @sql varchar(8000),@tablename varchar(150)
declare exporttabletotextfile cursor global
for select name from sys.objects where [type]=N'U'
open exporttabletotextfile
fetch exporttabletotextfile into @tablename
while @@FETCH_STATUS=0
begin
select @sql = 'bcp "select * from AdventureWorks2008R2..'+@tablename+'" queryout C:\SSIS\'+@tablename+'.txt -c -t, -T -S ' + @@servername
print @sql
exec xp_cmdshell @sql
fetch next from exporttabletotextfile into @tablename
end
close exporttabletotextfile;
deallocate exporttabletotextfile;
exec sp_configure 'xp_cmdshell',0
go
reconfigure
go
exec master..sp_configure 'ad hoc distributed queries',0
go
reconfigure
go
exec master..sp_configure 'show advanced options',0
go
reconfigure
go
Regards,
Prabhu