November 12, 2002 at 3:06 pm
Server: Msg 8648, Level 17, State 95, Line 60
Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
huh what? I have a procedure with a select, and it runs fine but If I append the INSERT INTO blah1, blah2 FROM ( a whole lotta stuff) I end up with this problem... can anyone offer a little help?
-Francisco
-Francisco
November 12, 2002 at 3:21 pm
I am confused when you state INSERT INTO blah, blah. Are you trying to insert into 2 tables, or can you be a bit more descriptive on this?
November 12, 2002 at 3:23 pm
of course...
I'll post this out as I have it ...
(code follows)
USE devREDe
SET NOCOUNT ON
/***************************************************/
/* Eliminate Case Repitition...Speed boost*/
/*
if not exists (select * from tempdb.dbo.sysobjects where id = object_id(N'#tCompany'))
BEGIN
DROP TABLE #tCompany
END
*/
DECLARE @myContactAddrID as uniqueIdentifier
CREATE TABLE #tCompany (
[CompanyID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbl_Company_CompanyID] DEFAULT (newid()),
[CompanyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
INSERT INTO #tCompany (CompanyID, CompanyName)
SELECT Co.CompanyID, 'CompanyName' = CASE
WHEN LEFT(Co.CompanyName,3) = 'HFO' AND Co.ShortName IS NOT NULL THEN Co.SHORTNAME
ELSE Co.CompanyName
END
FROM devREDe..tbl_Company Co
/***************************************************/
/* Eliminate Duplicate Company/Contact address for the ContactAddressID resolution*/
CREATE TABLE #t_ContactAddress (
[contactAddrID] [uniqueidentifier] NULL,
[CompanyID] [uniqueidentifier] NULL,
[ContactID] [uniqueidentifier] NULL
)
INSERT INTO #t_ContactAddress (ContactAddrID, CompanyID, ContactID)
SELECT ContactAddrID, Co.CompanyID, CtcA.ContactID
FROM devREde..tbl_ContactAddress CtcA
Inner Join tbl_Contacts Ctc On Ctc.ContactID = CtcA.ContactID
Inner Join tbl_Address A on ctcA.AddrID = A.AddrID
Inner Join tbl_company Co on A.CompanyID = Co.CompanyID
DECLARE @Dups int, @fld1 varchar(100), @fld2 varchar(36), @fld3 varchar(36)
DECLARE Dups CURSOR FOR
SELECT ContactID fld1, CompanyID fld2, COUNT(*) - 1 AS Duplicates
FROM #t_ContactAddress Table1
GROUP BY ContactID, CompanyID, ContactID --fld1, fld2
HAVING COUNT(*) > 1
OPEN Dups
WHILE(1=1)
BEGIN
FETCH NEXT FROM Dups INTO @fld1, @fld2, @Dups
IF @@FETCH_STATUS < 0 BREAK
SET ROWCOUNT @Dups
DELETE #t_ContactAddress WHERE ContactID = @fld1 And CompanyID = @fld2
SET ROWCOUNT 0
END
DEALLOCATE Dups
/***************************************************/
/****** Object: Table [dbo].[tbl_Company] Script Date: 11/1/2002 2:28:41 PM ******/
INSERT INTO devREDe..tbl_HelpDeskCallDetails(HelpDeskID, LegDealerID, ContactAddrID,
CallNotes, ReasonID, CallTypeID, UnNecessaryID, entryTIME, entryUSER)
SELECT HD.HelpDeskID,
--For ContactAddrID Lookup
--OuterSubQ.ContactID,
--OuterSubQ.lkupCoID,
legDealerID,
CAResolve.ContactAddrID,
CallNotes,
ReasonID,
CallTypeID,
UnNecessaryID,
OutersubQ.entryTIME,
OutersubQ.entryUSER
FROM (SELECT subQ.CustomerCall, CTC2.ContactID, Cust.CompanyID As CustomerCompanyID, Dlr.CompanyID As legDealerID,
SubQ.CustomerCTC, SubQ.CaseNumberID, subQ.Customer as CustomerCompanyName,
'lkUpCoID' =
CASE
WHEN CustomerCall = 1 AND (Cust.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Cust.CompanyID
WHEN CustomerCall = 0 AND (Dlr.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Dlr.CompanyID
ELSE NULL
END,
'CallNotes' =
CASE
WHEN CustomerCall = 1 AND (CTC2.ContactID IS NULL OR Cust.CompanyID IS NULL) THEN
'Customer Company: ' + subQ.Customer + Char(13)
+ 'Customer: ' + subQ.CustomerCTC + Char(13)
+ ISNULL(SubQ.CallNotes,'')
ELSE subQ.CallNotes
END,
subQ.ReasonID, subQ.CallTypeID, subQ.unNecessaryID, subQ.entryTime, subQ.entryUser
FROM (SELECT
C.CallNumber As CaseNumberID,
(C.[DATE] + C.[TIME]) - ('8:00:00.000') as entryTime,
Ctc.ContactID as entryUser,
C.CustomerCall,
C.Dealership,
C.Customer,
'CustomerCTC' =
CASE
When (C.CustomerCall = 1 AND C.ServiceTech IS NULL) THEN RTRIM(LTRIM(C.CustomerCP))
ELSE RTRIM(LTRIM(C.ServiceTech))
END,
C.[Notes] as CallNotes,
HDR.ReasonID,
HDCT.CallTypeID,
HDCU.UnNecessaryID
FROM dev_CM..Calls C
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc ON (C.HaasPhoneTech = Ctc.ContactName)
LEFT OUTER JOIN devREDe..tbl_HelpDeskReasons HDR ON (C.Reason = HDR.Reason)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallType HDCT ON (C.CallVia = HDCT.CallType)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallsUnNecessary HDCU ON (C.[ID] = HDCU.UnNecessaryID )
WHERE (C.REASON <> 'Svc Alert') AND C.SN <> 'R' AND C.SN IS NOT NULL AND C.MODEL IS NOT NULL
) as SubQ
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc2 ON (subQ.CustomerCTC = Ctc2.ContactName)
LEFT OUTER JOIN #tCompany Dlr ON (subQ.DealerShip = Dlr.CompanyName)
LEFT OUTER JOIN #tCompany Cust ON (subQ.Customer = Cust.CompanyName)
) as outerSubQ
LEFT OUTER JOIN#t_ContactAddress CAResolve ON (OuterSubQ.lkupCoID = CAResolve.CompanyID AND OuterSubQ.ContactID = CAResolve.ContactID)
LEFT OUTER JOIN tbl_HelpDesk HD ON (OuterSubQ.CaseNumberID = HD.CaseNumberID)
DROP TABLE #tCompany
DROP TABLE #t_ContactAddress
--Select * from tbl_HelpDesk
SET NOCOUNT OFF
(end code)
-Francisco
-Francisco
November 13, 2002 at 4:39 am
I believe the problem is that your derived query from (select ...) has too much data.
Solution is to remove the wide column, CallNotes I presume. Join back to the table outside of the derived table and get the data that way. The resaon is that work tables cannot contain text pointers.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 1:05 pm
Well I took your advise, and fixed it up like so...This runs fine, but I had to append log backups, it's either that or increase the size of the log for this operation... so I just added the backups since it was quick... another oddity.. I tried to copy paste this into a DTS process and it came back stating that #t_Company is invalid? when I click the parse query... why is it doing this?
DECLARE @myContactAddrID as uniqueIdentifier
CREATE TABLE #t_Company (
[CompanyID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbl_Company_CompanyID] DEFAULT (newid()),
[CompanyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
CREATE TABLE #t_CallNotes(
[HelpDeskID][uniqueidentifier],
[CallNotes][varchar](7100))
INSERT INTO #t_Company (CompanyID, CompanyName)
SELECT Co.CompanyID, 'CompanyName' = CASE
WHEN LEFT(Co.CompanyName,3) = 'HFO' AND Co.ShortName IS NOT NULL THEN Co.SHORTNAME
ELSE Co.CompanyName
END
FROM devREDe..tbl_Company Co
/***************************************************/
/* Eliminate Duplicate Company/Contact address for the ContactAddressID resolution*/
CREATE TABLE #t_ContactAddress (
[contactAddrID] [uniqueidentifier] NULL,
[CompanyID] [uniqueidentifier] NULL,
[ContactID] [uniqueidentifier] NULL
)
INSERT INTO #t_ContactAddress (ContactAddrID, CompanyID, ContactID)
SELECT ContactAddrID, Co.CompanyID, CtcA.ContactID
FROM devREde..tbl_ContactAddress CtcA
Inner Join tbl_Contacts Ctc On Ctc.ContactID = CtcA.ContactID
Inner Join tbl_Address A on ctcA.AddrID = A.AddrID
Inner Join tbl_company Co on A.CompanyID = Co.CompanyID
SET NOCOUNT ON
DECLARE @Dups int, @fld1 varchar(100), @fld2 varchar(36), @fld3 varchar(36)
DECLARE Dups CURSOR FOR
SELECT ContactID fld1, CompanyID fld2, COUNT(*) - 1 AS Duplicates
FROM #t_ContactAddress Table1
GROUP BY ContactID, CompanyID, ContactID --fld1, fld2
HAVING COUNT(*) > 1
OPEN Dups
WHILE(1=1)
BEGIN
FETCH NEXT FROM Dups INTO @fld1, @fld2, @Dups
IF @@FETCH_STATUS < 0 BREAK
SET ROWCOUNT @Dups
DELETE #t_ContactAddress WHERE ContactID = @fld1 And CompanyID = @fld2
SET ROWCOUNT 0
END
DEALLOCATE Dups
SET NOCOUNT OFF
/***************************************************/
/****** Object: Table [dbo].[tbl_Company] Script Date: 11/1/2002 2:28:41 PM ******/
INSERT INTO devREDe..tbl_HelpDeskCallDetails(HelpDeskID, LegDealerID, ContactAddrID,
/*CallNotes,*/ ReasonID, CallTypeID, UnNecessaryID, entryTIME, entryUSER)
SELECT HD.HelpDeskID,
--For ContactAddrID Lookup
--OuterSubQ.ContactID,
--OuterSubQ.lkupCoID,
legDealerID,
CAResolve.ContactAddrID,
--CallNotes,
ReasonID,
CallTypeID,
UnNecessaryID,
OutersubQ.entryTIME,
OutersubQ.entryUSER
FROM (SELECT subQ.CustomerCall, CTC2.ContactID, Cust.CompanyID As CustomerCompanyID, Dlr.CompanyID As legDealerID,
SubQ.CustomerCTC, SubQ.CaseNumberID, subQ.Customer as CustomerCompanyName,
'lkUpCoID' =
CASE
WHEN CustomerCall = 1 AND (Cust.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Cust.CompanyID
WHEN CustomerCall = 0 AND (Dlr.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Dlr.CompanyID
ELSE NULL
END,
'CallNotes' =
CASE
WHEN CustomerCall = 1 AND (CTC2.ContactID IS NULL OR Cust.CompanyID IS NULL) THEN
'Customer Company: ' + subQ.Customer + Char(13)
+ 'Customer: ' + subQ.CustomerCTC + Char(13)
+ ISNULL(SubQ.CallNotes,'')
ELSE subQ.CallNotes
END,
subQ.ReasonID, subQ.CallTypeID, subQ.unNecessaryID, subQ.entryTime, subQ.entryUser
FROM (SELECT
C.CallNumber As CaseNumberID,
(C.[DATE] + C.[TIME]) - ('8:00:00.000') as entryTime,
Ctc.ContactID as entryUser,
C.CustomerCall,
C.Dealership,
C.Customer,
'CustomerCTC' =
CASE
When (C.CustomerCall = 1 AND C.ServiceTech IS NULL) THEN RTRIM(LTRIM(C.CustomerCP))
ELSE RTRIM(LTRIM(C.ServiceTech))
END,
C.[Notes] as CallNotes,
HDR.ReasonID,
HDCT.CallTypeID,
HDCU.UnNecessaryID
FROM dev_CM..Calls C
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc ON (C.HaasPhoneTech = Ctc.ContactName)
LEFT OUTER JOIN devREDe..tbl_HelpDeskReasons HDR ON (C.Reason = HDR.Reason)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallType HDCT ON (C.CallVia = HDCT.CallType)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallsUnNecessary HDCU ON (C.[ID] = HDCU.UnNecessaryID )
WHERE (C.REASON <> 'Svc Alert') AND C.SN <> 'R' AND C.SN IS NOT NULL AND C.MODEL IS NOT NULL
) as SubQ
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc2 ON (subQ.CustomerCTC = Ctc2.ContactName)
LEFT OUTER JOIN #t_Company Dlr ON (subQ.DealerShip = Dlr.CompanyName)
LEFT OUTER JOIN #t_Company Cust ON (subQ.Customer = Cust.CompanyName)
) as outerSubQ
LEFT OUTER JOIN#t_ContactAddress CAResolve ON (OuterSubQ.lkupCoID = CAResolve.CompanyID AND OuterSubQ.ContactID = CAResolve.ContactID)
LEFT OUTER JOIN tbl_HelpDesk HD ON (OuterSubQ.CaseNumberID = HD.CaseNumberID)
WHERE NOT EXISTS(SELECT existsHD.HelpDeskID FROM devREDe..tbl_HelpDeskCallDetails existsHD
WHERE HD.HelpDeskID = existsHD.HelpDeskID)
/***************************************************/
/**********BACK UP TRANSACTION LOG *****************/
BACKUP LOG [devREDe] TO [devREDe_Log] WITH NOINIT , NOUNLOAD , NAME = N'devREDe backup', NOSKIP , STATS = 10, NOFORMAT
/***************************************************/
/***************************************************/
/**********INSERT NOTES INTO TEMP TABLE*************/
/***************************************************/
/***************************************************/
INSERT INTO #t_CallNotes(HelpDeskID, CallNotes)
SELECT HD.HelpDeskID,
CallNotes
FROM (SELECT subQ.CustomerCall, CTC2.ContactID, Cust.CompanyID As CustomerCompanyID, Dlr.CompanyID As legDealerID,
SubQ.CustomerCTC, SubQ.CaseNumberID, subQ.Customer as CustomerCompanyName,
'lkUpCoID' =
CASE
WHEN CustomerCall = 1 AND (Cust.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Cust.CompanyID
WHEN CustomerCall = 0 AND (Dlr.CompanyID IS NOT NULL AND CTC2.ContactID IS NOT NULL) THEN Dlr.CompanyID
ELSE NULL
END,
'CallNotes' =
CASE
WHEN CustomerCall = 1 AND (CTC2.ContactID IS NULL OR Cust.CompanyID IS NULL) THEN
'Customer Company: ' + subQ.Customer + Char(13)
+ 'Customer: ' + subQ.CustomerCTC + Char(13)
+ ISNULL(SubQ.CallNotes,'')
ELSE subQ.CallNotes
END,
subQ.ReasonID, subQ.CallTypeID, subQ.unNecessaryID, subQ.entryTime, subQ.entryUser
FROM (SELECT
C.CallNumber As CaseNumberID,
(C.[DATE] + C.[TIME]) - ('8:00:00.000') as entryTime,
Ctc.ContactID as entryUser,
C.CustomerCall,
C.Dealership,
C.Customer,
'CustomerCTC' =
CASE
When (C.CustomerCall = 1 AND C.ServiceTech IS NULL) THEN RTRIM(LTRIM(C.CustomerCP))
ELSE RTRIM(LTRIM(C.ServiceTech))
END,
C.[Notes] as CallNotes,
HDR.ReasonID,
HDCT.CallTypeID,
HDCU.UnNecessaryID
FROM dev_CM..Calls C
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc ON (C.HaasPhoneTech = Ctc.ContactName)
LEFT OUTER JOIN devREDe..tbl_HelpDeskReasons HDR ON (C.Reason = HDR.Reason)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallType HDCT ON (C.CallVia = HDCT.CallType)
LEFT OUTER JOIN devREDe..tbl_HelpDeskCallsUnNecessary HDCU ON (C.[ID] = HDCU.UnNecessaryID )
WHERE (C.REASON <> 'Svc Alert') AND C.SN <> 'R' AND C.SN IS NOT NULL AND C.MODEL IS NOT NULL
) as SubQ
LEFT OUTER JOIN devREDe..tbl_Contacts Ctc2 ON (subQ.CustomerCTC = Ctc2.ContactName)
LEFT OUTER JOIN #t_Company Dlr ON (subQ.DealerShip = Dlr.CompanyName)
LEFT OUTER JOIN #t_Company Cust ON (subQ.Customer = Cust.CompanyName)
) as outerSubQ
LEFT OUTER JOIN#t_ContactAddress CAResolve ON (OuterSubQ.lkupCoID = CAResolve.CompanyID AND OuterSubQ.ContactID = CAResolve.ContactID)
LEFT OUTER JOIN tbl_HelpDesk HD ON (OuterSubQ.CaseNumberID = HD.CaseNumberID)
/***************************************************/
/***************************************************/
/**********BACK UP TRANSACTION LOG *****************/
BACKUP LOG [devREDe] TO [devREDe_Log] WITH NOINIT , NOUNLOAD , NAME = N'devREDe backup', NOSKIP , STATS = 10, NOFORMAT
/***************************************************/
/***************************************************/
/**********UPDATE tbl_HelpDeskCallDetails***********/
/***************************************************/
/***************************************************/
UPDATE devREDe..tbl_HelpDeskCallDetails
SET CallNotes = T.CallNotes
FROM #t_CallNotes AS T
WHERE devREDe..tbl_HelpDeskCallDetails.HelpDeskID = T.HelpDeskID
/***************************************************/
DROP TABLE #t_Company
DROP TABLE #t_ContactAddress
DROP TABLE #t_CallNotes
-Francisco
-Francisco
November 13, 2002 at 2:01 pm
This is because at parse time the table does not exist. You should split this into seperate stored procedures,
Main SP creates tables and calls the sub sps
Sub SP1 populates company, address
Sub SP2 updates the address using the cursor
Sub Sp3 populates callnotes.
and
Sub SP4 returns the data
This will result in the best plans being used and will avoid recompiles.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 2:13 pm
You should also use the username in your object names i.e devREDe.dbo.tbl_Company
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 2:54 pm
Having looked at this it can be written as one query. What are the volumes in company, contact and address. You haven't got an index on the temp tables.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 2:55 pm
I'm acctually spitting this into a DTS sql script, I thought the TempTable would only live for the life of the session thus the temptables wouldn't be available for the next procedure? is this right or do temp tables live for a longer scope?
-Francisco
-Francisco
November 13, 2002 at 3:09 pm
What I am trying to get at is that you are populating the temp tables and then joining to them. If you stick with this option then you will should index the tables.
You should be able to write this as one insert statement though, which should result in better performance.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply