Sqlcmd not trimming trailing white spaces

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

SQL Server 2012 Forums

Transact-SQL (2012)

Sqlcmd not trimming trailing white spaces

Author

Topic

arbrahul
Starting Member

2 Posts

Posted - 2013-04-28 : 18:39:24

Hi all,

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 :(

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-28 : 19:11:36

Make sure that the empty spaces are not due to line feed, carriage return or some other non printable ASCII characters...
You can try LTRIM() RTRIM() functions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:40:36

does the stored procedure involve any html code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arbrahul
Starting Member

2 Posts

Posted - 2013-04-29 : 05:24:48

The stored procedure is a simple select with a few joins. Unfortunately I could not modify the SP as it has been supplied by a service provider.

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-04-30 : 05:36:18

Thinking out the box....

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;

SELECT RTRIM(TheDATA) FROM #MyTable
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources