Solved

SQL Server Exporting a VIEW to Excel hangs

Posted on 2013-11-20
7
263 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 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