Solved

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

Posted on 2007-03-29
7
878 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 44
MS SQL Backup 24 70
C# Application Local DB Connection String 23 58
SQL JOIN 6 32
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.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

18 Experts available now in Live!

Get 1:1 Help Now