Error when output .txt using bcp

[Jimmy2128]

I am trying to output a file but I am getting the following error
======
output
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
(null)

(0 rows(s) affected)

====

I am using the following

/*** Exporting data *****/

declare @db_name varchar(30), @path varchar(50), @msg varchar(255), @file_name varchar(20), @server_name varchar(50), @server_pasword varchar(50)


select @db_name = name from master..sysdatabases
where dbid = db_id()
select @path = 'J:\Shipping\ASI\SPSS\'
select @file_name = 'newcust_test'
select @server_name = @@servername

select @server_pasword = 'gtllp'


select @msg = 'EXEC master..xp_cmdshell ' + "'" + 'bcp ' + @db_name + '..ship_Contacts out ' +
@path + @file_name + '.txt /c /Usa /P' + @server_pasword + ' /e '+ @path + @file_name +'.err /m9999999 /S'+ @server_name + " '"
exec (@msg)


END -- 1
grant execute on sp_ship_Contacts to SHIP
GO




Thanks for your help

 

[Catadmin]

Try getting rid of your 'EXEC master..xp_cmdshell ' + on the Select @msg statement.

The way I had to do it (to make the xp_CmdShell work correctly) was:

Set @removefile = 'Del ' + @LogName;
Exec Xp_cmdshell @removefile;

Of course, you don't need the ; (I just do that for readability) and I only use Set because it's easier for others to see what I was doing if I only use Select in actual Select statements & subqueries.

BTW, I don't know if this is affecting your code or not, but you seem to have extraneous single quotes ' between your double quotes when you set @Msg. And doublequotes where maybe you should have single quotes?? Not sure about this last, but you might want to re-check the BCP command syntax.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???