Solved

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

Posted on 2007-03-29
7
902 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.

770 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