SQL Server Exporting a VIEW to Excel hangs

When I try to export a TABLE to Excel, it works just fine.  

But when I try to export a VIEW, I get some warning messages beforehand (see attached file), then it hangs, and creates a HUGE tempdb file that would keep growing if I didn't stop the SQL service.  Am I missing something about exporting a view?  It's a very simple view, and works just fine when I query it.  I wouldn't worry if the process was SLOW, but to be creating a seemingly infinite tempdb file (10GB+) seems to be a problem.
SQL-Print-Screen.JPG
K AAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
the temp db size is probably related to the poor join/index usage that exists on the tables at present...

you could try changing the view to use EXISTS logic which is generally a better performer than an inlist

I have to ask why all the column in both tables are varchar(50) and Nullable...
do you have any indexes defined on the tables?

how many rows are there on the tables and how many do you expect to output?

SELECT     coid, coname, locid, locdesc, mainlocn, mainship, mainpoto, iscustco, issuplco, ismyco, isshipto, isbill, ispoto, isremit, street1, street2, city, state, country, zip, 
                      contact, title, phone, extens, fax, email, website, remarkid, recvlocn, recvvia, recvfob, buyer, shipfrom, shipvia, shipfob, taxdist, salesman, glsalegr, billid, billloc, 
                      remitid, remitloc, userid, timestmp, delmark, contact1, contact2, contact3, contact4, contact5, rptname, costatus, phone2, agentid, agentloc, siccode, active, loctype, 
                      cotype
FROM         dbo.coaddr2 as a
WHERE    coid is not null and exists  
                          (SELECT     billto
                            FROM          dbo.slheader2 as x
                          where a.coid=x.billto)

Open in new window

0
 
K AAuthor Commented:
I should add that when I tried to export to a CSV file, the exact same thing happens... so it doesn't appear to be part of Excel.
0
 
LowfatspreadCommented:
can you show us the ddl for the view ....?

what is it a join between several tables?
do they all come from the same database?

are you trying to export all rows , or are you using some selection criteria?

any order by requirements?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
K AAuthor Commented:
(all from the same database)

Here's the VIEW:

SELECT     coid, coname, locid, locdesc, mainlocn, mainship, mainpoto, iscustco, issuplco, ismyco, isshipto, isbill, ispoto, isremit, street1, street2, city, state, country, zip,
                      contact, title, phone, extens, fax, email, website, remarkid, recvlocn, recvvia, recvfob, buyer, shipfrom, shipvia, shipfob, taxdist, salesman, glsalegr, billid, billloc,
                      remitid, remitloc, userid, timestmp, delmark, contact1, contact2, contact3, contact4, contact5, rptname, costatus, phone2, agentid, agentloc, siccode, active, loctype,
                      cotype
FROM         dbo.coaddr2
WHERE     (coid IN
                          (SELECT     billto
                            FROM          dbo.slheader2))


Here's coaddr2:
                 
      [coid] [varchar](50) NULL,
      [coname] [varchar](50) NULL,
      [locid] [varchar](50) NULL,
      [locdesc] [varchar](50) NULL,
      [mainlocn] [varchar](50) NULL,
      [mainship] [varchar](50) NULL,
      [mainpoto] [varchar](50) NULL,
      [iscustco] [varchar](50) NULL,
      [issuplco] [varchar](50) NULL,
      [ismyco] [varchar](50) NULL,
      [isshipto] [varchar](50) NULL,
      [isbill] [varchar](50) NULL,
      [ispoto] [varchar](50) NULL,
      [isremit] [varchar](50) NULL,
      [street1] [varchar](50) NULL,
      [street2] [varchar](50) NULL,
      [city] [varchar](50) NULL,
      [state] [varchar](50) NULL,
      [country] [varchar](50) NULL,
      [zip] [varchar](50) NULL,
      [contact] [varchar](50) NULL,
      [title] [varchar](50) NULL,
      [phone] [varchar](50) NULL,
      [extens] [varchar](50) NULL,
      [fax] [varchar](50) NULL,
      [email] [varchar](50) NULL,
      [website] [varchar](50) NULL,
      [remarkid] [varchar](50) NULL,
      [recvlocn] [varchar](50) NULL,
      [recvvia] [varchar](50) NULL,
      [recvfob] [varchar](50) NULL,
      [buyer] [varchar](50) NULL,
      [shipfrom] [varchar](50) NULL,
      [shipvia] [varchar](50) NULL,
      [shipfob] [varchar](50) NULL,
      [taxdist] [varchar](50) NULL,
      [salesman] [varchar](50) NULL,
      [glsalegr] [varchar](50) NULL,
      [billid] [varchar](50) NULL,
      [billloc] [varchar](50) NULL,
      [remitid] [varchar](50) NULL,
      [remitloc] [varchar](50) NULL,
      [userid] [varchar](50) NULL,
      [timestmp] [varchar](50) NULL,
      [delmark] [varchar](50) NULL,
      [contact1] [varchar](50) NULL,
      [contact2] [varchar](50) NULL,
      [contact3] [varchar](50) NULL,
      [contact4] [varchar](50) NULL,
      [contact5] [varchar](50) NULL,
      [rptname] [varchar](50) NULL,
      [costatus] [varchar](50) NULL,
      [phone2] [varchar](50) NULL,
      [agentid] [varchar](50) NULL,
      [agentloc] [varchar](50) NULL,
      [siccode] [varchar](50) NULL,
      [active] [varchar](50) NULL,
      [loctype] [varchar](50) NULL,
      [cotype] [varchar](50) NULL


Here slheader2:

      [apprcode] [varchar](50) NULL,
      [apprdate] [varchar](50) NULL,
      [docid] [varchar](50) NULL,
      [doctype] [varchar](50) NULL,
      [ordrstat] [varchar](50) NULL,
      [billto] [varchar](50) NULL,
      [billlocn] [varchar](50) NULL,
      [shipto] [varchar](50) NULL,
      [shiplocn] [varchar](50) NULL,
      [coname] [varchar](50) NULL,
      [street1] [varchar](50) NULL,
      [street2] [varchar](50) NULL,
      [city] [varchar](50) NULL,
      [state] [varchar](50) NULL,
      [zip] [varchar](50) NULL,
      [country] [varchar](50) NULL,
      [created] [varchar](50) NULL,
      [ordrdate] [varchar](50) NULL,
      [donedate] [varchar](50) NULL,
      [expires] [varchar](50) NULL,
      [printed] [varchar](50) NULL,
      [arstat] [varchar](50) NULL,
      [shipstat] [varchar](50) NULL,
      [shipvia] [varchar](50) NULL,
      [fob] [varchar](50) NULL,
      [terms] [varchar](50) NULL,
      [duedate] [varchar](50) NULL,
      [applied] [varchar](50) NULL,
      [discused] [varchar](50) NULL,
      [taxamt] [varchar](50) NULL,
      [totalamt] [varchar](50) NULL,
      [origin] [varchar](50) NULL,
      [originid] [varchar](50) NULL,
      [custpono] [varchar](50) NULL,
      [salesman] [varchar](50) NULL,
      [shipfrom] [varchar](50) NULL,
      [taxexmt] [varchar](50) NULL,
      [autodone] [varchar](50) NULL,
      [shipstax] [varchar](50) NULL,
      [taxdist] [varchar](50) NULL,
      [glsaleid] [varchar](50) NULL,
      [rlsgroup] [varchar](50) NULL,
      [ordrdisc] [varchar](50) NULL,
      [linertrn] [varchar](50) NULL,
      [idlotsn] [varchar](50) NULL,
      [userid] [varchar](50) NULL,
      [timestmp] [varchar](50) NULL,
      [delmark] [varchar](50) NULL,
      [commpaid] [varchar](50) NULL,
      [cmpddate] [varchar](50) NULL,
      [commstat] [varchar](50) NULL,
      [refto] [varchar](50) NULL,
      [reflocn] [varchar](50) NULL,
      [lockstat] [varchar](50) NULL,
      [rev] [varchar](50) NULL,
      [revdate] [varchar](50) NULL,
      [currency] [varchar](50) NULL,
      [exchange] [varchar](50) NULL,
      [gstexmt] [varchar](50) NULL,
      [gstamt] [varchar](50) NULL,
      [gstrate] [varchar](50) NULL,
      [acptdate] [varchar](50) NULL,
      [hasvoids] [varchar](50) NULL,
      [sendmail] [varchar](50) NULL,
      [misc01] [varchar](50) NULL,
      [misc02] [varchar](50) NULL,
      [misc03] [varchar](50) NULL
0
 
K AAuthor Commented:
Thanks for your help so far...

Regarding why all the columns are varchar and nullable - only because that's just the default way the data came in when it was imported from Excel.  No indexes are defined.

There are approximately 8400 rows in coaddr2 and approximately 28000 rows in slheader2.  I'm exepecting about 5600 rows to be output.

One thing confuses me - (since working with this for many, many hours since I origianally posted the question)  I AM getting about 93 rows to export to Excel... so I'm considering that I've got a bad row in the data somewhere... BUT - the first 93 rows are based on the order of the data in the "IN" clause (slheader2), not the "source" table of coaddr2.  That's making it harder to diagnose where that bad data might be.  

I've had trouble with ORDER BY clauses in creating and using views.  How can I specify the CREATE VIEW to use an ORDER BY a column in the coaddr2 table (i.e. coid)?
0
 
K AAuthor Commented:
Thanks... using EXISTS seems to have straightened it all out.
0
 
LowfatspreadCommented:
putting an order by clause in a view  has no effect (unless the order by clause is in an OVER function) ....

your data is probably bad , that the real danger of working with user spreadsheets as a data input source...

are you sure that the spreadsheet data doesn't contain tabs, or other non space whitespace characters...?

try doing
select billto,billtolength,count(*)
 from (
select '(' + billto +')' as billto, Datalength(billto) as Billtolength
  from slheader2
) as X
group by billto
order by 1

and


select coid,coidlength,count(*)
 from (
select '(' + coid +')' as coid, Datalength(coid) as coidlength
FROM         dbo.coaddr2
) as X
group by coid
order by 1


and see what data you've got.

in general creating an index on slheader2 for the billto column should help
e.g.
create index dbo.slheader2_Billto as (billto asc)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.