How to suppress an error message in the output file using SQLCMD

How can I get SQLCMD to suppress an error message in the output file?  Specifically, when running the following script :
sqlcmd -S pdc-elite -E -h-1 -w 180 -r1 -m-0 -d son_db -I -i
D:\elite\custom\Interface\PositivePay\Suntrust\suntrust578.sql -o
D:\elite\custom\Interface\PositivePay\Suntrust\suntrust578.txt
I get the following at the beginning of my output file:

Msg 3701, Level 11, State 5, Server PDC-ELITE, Line 5
Cannot drop the table '#ckdetail', because it does not exist or you do not have permission.

I don't want to have this message returned in the output file.  I know my syntax isn't correct yet as I've been playing with different settings and haven't found one that works yet....still learning sqlcmd.  I'm getting the results I need, I just need it to omit that message as I have to use SED to modify this after the fact as is and I don't want to muddy the water even more with this message :-)
LVL 1
AkermanITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
why are you attempting to drop the table anyway since it wont exists when you run ... sqlcmd?

how have you coded the drop anyway...

if you're going to do it then

if exists (select name from sysobjects where name like '#ckdetail')
 begin
    drop table #chkdetail
 end


 
0
rboyd56Commented:
There is not a way to suppress this error. SQL Server returns the error and since you are logging into an output file it has to be logged.
0
AkermanITAuthor Commented:
Lowfatspread, thanks, that does indeed get rid of the error, don't know why I didn't think of that before.

My output file though comes back with a blank line between each entry, is there a way to prevent that from happening so it looks cleaner?  As an example, this is an excerpt of how it currently looks:
100004265657               CLERK OF THE COURT                                                                                      

100004265657               CLERK OF THE COURT                                                                                      

100004265657               F. A. ARIAS & MUNOZ    

I need to remove all of the whitespace....I currently do that using SED, which is a pain in the butt....can SQLCMD also handle this so it has the cleaner look?  ie:

100004265657               CLERK OF THE COURT                                                                                      
100004265657               CLERK OF THE COURT                                                                                      
100004265657               F. A. ARIAS & MUNOZ    

Thanks!!!
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

LowfatspreadCommented:
again why does it have a blank line...

is it mutlple select statements
or just 1?

if its just 1 select then  you've specified a width of 180,,, how long is the actual output line?
 did you allow for 2 spaces per column?

does your data contain tabs or cr lf commands? (if so replace them with spaces...
e.g.
replace(replace(replace(yourcolumn,char(9),' ') ,char(10),' '),char(13),' ')

post your script..
0
AkermanITAuthor Commented:
Here is the script, with your helpful suggestion of using the 'if exists' line.  When I run this in the query window the output looks fine, it only adds the extra lines when I run it from sqlcmd.

SET NOCOUNT ON
SET ANSI_PADDING OFF
if exists (select name from sysobjects where name like '#ckdetail')
begin drop table #ckdetail
end
CREATE TABLE #ckdetail
(acctno nchar (13),
cknum nchar (10),
ckamt nchar (10),
ckdate nchar(6),
addition nchar (15),
void nchar (1),
payee nchar (40),
filler nchar (65))
insert into #ckdetail
select right(replicate('0',13) + ltrim(acctnum),13)as acctno,
right(replicate ( '0', 10) + ltrim(cknum),10) as cknum,
right(replicate('0', 10) + ltrim(cast(sum(ckamt)* 100 as int)),10)as ckamt,
substring(convert(varchar(50),ckdate,101),1,2) +
substring(convert(varchar(50),ckdate,101),4,2) + substring(convert(varchar(50),ckdate,101),9,2)as ckdate,
' ' as addition,
' ' as void,
left(apname,40) as payee,
' ' as filler
from csd, ba, ap
where
---left(ckdate,11) like left(getdate(),11) and
csd.baid = ba.baid and csd.apnum = ap.apnum
and csd.baid like '1008%'
and left(csd.ckdate,11) = 'Mar 27 2007'
and ckstat = 'O'
group by ba.acctnum, csd.cknum, csd.ckdate, csd.ckstat, apname
select acctno + cknum + ckamt + ckdate + addition + void + payee + filler from #ckdetail
0
LowfatspreadCommented:
have you tried the -k parameter to remove control characters?
e.g.
sqlcmd -S pdc-elite -E -h-1 -w 180 -r1 -m-0 -d son_db -I -k2 -i
D:\elite\custom\Interface\PositivePay\Suntrust\suntrust578.sql -o
D:\elite\custom\Interface\PositivePay\Suntrust\suntrust578.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AkermanITAuthor Commented:
Thanks for the fast reply lowfat!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.