Solved

SQL Server Exporting a VIEW to Excel hangs

Posted on 2013-11-20
7
259 Views
Last Modified: 2013-11-22
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
0
Comment
Question by:K A
  • 4
  • 3
7 Comments
 

Author Comment

by:K A
ID: 39663009
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39663265
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
 

Author Comment

by:K A
ID: 39663352
(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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 50

Accepted Solution

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

Author Comment

by:K A
ID: 39666790
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
 

Author Comment

by:K A
ID: 39667193
Thanks... using EXISTS seems to have straightened it all out.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668513
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

948 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

19 Experts available now in Live!

Get 1:1 Help Now