Solved

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

Posted on 2007-03-29
7
890 Views
Last Modified: 2008-03-17
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 :-)
0
Comment
Question by:AkermanIT
  • 3
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18818363
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18818396
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
 
LVL 1

Author Comment

by:AkermanIT
ID: 18818466
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18818559
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
 
LVL 1

Author Comment

by:AkermanIT
ID: 18818582
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18818791
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
 
LVL 1

Author Comment

by:AkermanIT
ID: 18818909
Thanks for the fast reply lowfat!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now