Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Exporting a VIEW to Excel hangs

Posted on 2013-11-20
7
Medium Priority
?
267 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 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