Solved

SQL Server Exporting a VIEW to Excel hangs

Posted on 2013-11-20
7
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 49
sql 2014,  lock limit 5 46
Change this SQL to get all nodes 3 40
SQL- GROUP BY 4 25
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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