I have a sql agent job which basically runs a stored procedure using sqlcmd and saves the output to a csv file. The stored procedure selects some data and there is one column with a lot of trailing spaces. I have used -W switch with sqlcmd to trim the space. This was working fine with SQL 2008 R2.
We have recently installed 2012 and moved the sql job to the new instance. Now the issue is that the text output file generated by the sqlcmd within the sqlagent job has all the trailing white spaces. Its not getting removed. Could anyone tell me how to get rid of these spaces ? I have run the sqlcmd command from a windows command against the new sql 2012 db and it works fine. It does not work with sql agent. That's all :(
Create a new stored procedure of your own In the stored procedure create a temporary table. Insert into the temp table and call the service provider stored procedure Return the information after removing extra characters. Change you SQLCMD to run your SP
Create Procedure MyProcedure As Create Table #MyTable (TheData varchar(max))
Insert Into #MYTable (TheData) EXECUTIVE ServiceProviderSP;