quote:

---

Server: Msg 1101, Level 17, State 10, Procedure ap_get_sales_data, Line 10
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

---

quote:

---

OK, don't know about your error, but why are you inserting in to a temp table? Why not collapse your SQL staements in to 1 SELECT?

---

quote:

---

...temp file is getting too big (more than 6 gigs)

---

quote:

---

Not for nothing...but that's huge....how big is your database?

---

quote:

---

Tara, what does that have to do with tempdb?

It's 6 gig!

---

quote:

---

What is your database recovery model set to? If it's FULL, then you need to backup your transaction logs periodically. If you don't care about point in time recovery, change it to SIMPLE. If it's already set to simple, then try running DBCC SHRINKDATABASE and DBCC SHRINKFILE. See BOL for syntax on both. If that doesn't work, then you can detach the log and create a new one. But that will take the database down temporarily.

---

quote:

---

Because label asked "While we're on the subject of disk space, how I do I get rid of some of my really large transaction logs? I do a complete backup and then try and shrink them and I've got a couple that won't let me shrink them below 2 gigs.....Surely I don't need a 2 gig trans log? Isn't there any way to get rid of those large files?"

Tara

---

quote:

---

How many other databases are on the box?

---

select distinct(parentId), partnumber, max([customer Name]) as Customer
, sum([Total Sales]) as [Total Sales], min([first_download]) as [First Download]
, max([last_download]) as [Last_Download] 
FROM (
select distinct(partnumber), convert(money, sum(totalsalenet)) as [Total Sales], 
c.customer_name, min(et.requestdatetime) as [First_Download], max(et.requestdatetime) as [Last_Download]
, se.etech_customer_id As ParentID 
from 
sales_history s 
join 
customer_data c on 
s.accountnumber=c.accountnumber 
join 
smc_etech_key se on 
c.accountnumber=se.accountnumber 
left outer join 
smc_new_products.dbo.etechmodelrequests et on 
convert(varchar(50), se.etech_customer_id)=et.username 
and s.partnumber=et.configname 
and requestdatetime between @datefrom and @dateto 
and interfacename like '%down%' 
where convert(datetime, (substring(convert(varchar(8), purchase_date), 5, 2) + '/' + right(purchase_date, 2) + '/' + left(purchase_date, 4)), 101) between @datefrom and @dateto 
group by partnumber, customer_name, etech_customer_id 
) As xxx
where [first_download] is not null 
group by parentid, partnumber 
order by parentID 

quote:

---

Ok, this is certainly WITHOUT optimization, but:

select distinct(parentId), partnumber, max([customer Name]) as Customer
, sum([Total Sales]) as [Total Sales], min([first_download]) as [First Download]
, max([last_download]) as [Last_Download] 
FROM (
select distinct(partnumber), convert(money, sum(totalsalenet)) as [Total Sales], 
c.customer_name, min(et.requestdatetime) as [First_Download], max(et.requestdatetime) as [Last_Download]
, se.etech_customer_id As ParentID 
from 
sales_history s 
join 
customer_data c on 
s.accountnumber=c.accountnumber 
join 
smc_etech_key se on 
c.accountnumber=se.accountnumber 
left outer join 
smc_new_products.dbo.etechmodelrequests et on 
convert(varchar(50), se.etech_customer_id)=et.username 
and s.partnumber=et.configname 
and requestdatetime between @datefrom and @dateto 
and interfacename like '%down%' 
where convert(datetime, (substring(convert(varchar(8), purchase_date), 5, 2) + '/' + right(purchase_date, 2) + '/' + left(purchase_date, 4)), 101) between @datefrom and @dateto 
group by partnumber, customer_name, etech_customer_id 
) As xxx
where [first_download] is not null 
group by parentid, partnumber 
order by parentID 

couple of comments though. If you can, lose the spaces in column names. Second (and I don't pretend to understand your statement), but I think you can combine them. Maybe not. But if you can (because you'll eliminate a second grouping operation) look at using a HAVING Clause. Third, you don't need a distinct clause on a column your grouping on...I was suprised it even worked...

USE Northwind
GO

SELECT Distinct(CustomerId), Max(OrderDate), OrderId
FROM Orders
GROUP BY CustomerId, OrderId
GO


Anyway, Good Luck...



Brett

8-)

---

Alright....I did a lot of fine-tuning on the tables, ended up copying all of the ones I was using, removed superflous fields, established good indexs and some relationships between them, made sure all the data types and naming conventions matched and it ran alot quicker.

Here's the final query:

quote:

---

Select parentId, partnumber, max([customer_Name]) as Customer, sum(Total_Sales) as Total_Sales, min([first_download]) as [First Download], max([last_download]) as [Last_Download]
FROM (
select partnumber, convert(money, sum(totalsalenet)) as Total_Sales,
c.customer_name as customer_name , min(et.requestdatetime) as First_Download, max(et.requestdatetime) as Last_Download, se.etech_customer_id As ParentID
from
sales_history s
join
customer_data c on
s.accountnumber=c.accountnumber
join
smc_etech_key se on
c.accountnumber=se.accountnumber
left outer join
etechmodelrequests et on
se.etech_customer_id=et.username
and s.partnumber=et.configname
and requestdatetime between @datefrom and @dateto
where purchase_date between 20020401 and 20030401
group by partnumber, customer_name, etech_customer_id
) As MyResults
group by parentid, partnumber
order by parentID

---

Thanks for your help guys!

(PS. Still haven't found a way to shrink my stupid transaction log files though.... )