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.
| Author |
Topic |
|
cirugio
Yak Posting Veteran
90 Posts |
 Posted - 2012-09-10 : 16:13:33
|
| I currently have the stored procedure which basically runs a simple query and outputs the results into a file on my server. The procedure works great with the exception of when the query may have LTRIM or RTRIM around a field. The select statment runs fine, but it doesn't seem to recognize the ltrim or rtrim when executed through the osql command. Is there an additional osql command that I should add to the statement in order for functions like ltrim/rtrim to work when using osql commands in a stored procedure? stored procedure:Select @Cmd = 'osql /w500 /n /E /dsbc_db' + ' /qdbo.[' + @RPTName + '] >\\' + @@SERVERNAME + '\Data\Reports\' + @FileName query it executes:select ltrim(rtrim(name)) from Test |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cirugio
Yak Posting Veteran
90 Posts |
Posted - 2012-09-10 : 16:43:46
|
| Ugg... that was what I was afraid of. Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-10 : 17:40:34
|
| If you use the sqlcmd utility you can use the -W parameter (note the uppercase) that will trim trailing spaces from column output. However, since you didn't specify a column separator with -s, using -W will completely mess up your output. |
|
|
|
cirugio
Yak Posting Veteran
90 Posts |
Posted - 2012-09-11 : 09:48:36
|
| Hi Robvolk,Actually I did specify the -s. Sorry, when I must not have copied the entire thing. This is what I actually have:Select @Cmd = 'osql /w500 /n /E /dsbc_db' + ' /qdbo.[' + @RPTName + '] >\\' + @@SERVERNAME + '\Data\Reports\' + @FileName + ' -s ",""' Can you tell me, where would I place the -W in the above statement? I inherited this stored procedure so not exactly sure of the placement. Also, I am currently using SQLServer 2008. Thank you. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-11 : 10:04:30
|
| -W isn't available for osql, you'd have to use the sqlcmd utility: http://msdn.microsoft.com/en-us/library/ms162773.aspxIt's installed with SQL Server 2005 and later versions. It works nearly identically to osql so you shouldn't have to change your existing code. You can place -W anywhere in the argument list. |
|
|
|
cirugio
Yak Posting Veteran
90 Posts |
Posted - 2012-09-11 : 16:23:41
|
| wow! That worked out perfectly. I just changed the osql to sqlcmd and added the -W and it worked out perfectly. Thank you so much for the great advice. RobVolk It made my life a little bit easier. :0) |
|
|
|
|
|
|
|
|
using osql command to output query results