Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
This is my code. What is wrong here?
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @.LadingType = 'inbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)If you supply the declares, that'd be a big help...|||Sorry, here are the declares:
CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)|||This compiles fine...it's something else...
DECLARE @.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'|||Table compiles fine as well....
Post the whole sproc...
it's massive, isn't it.....|||Yes it is, but here t is... :) I have to submit it in parts since I can only post 1000 characters.
It does compile fine, but because I use EXEC a string I create it won't show you the error until you execute it.:
CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)
IF @.LadingType ='' GOTO TotalsReport
IF @.LadingType <>'' GOTO LadingReport
LadingReport:
IF LOWER(@.LadingType) ='inbound' GOTO InboundReport
IF LOWER(@.LadingType) ='outbound' GOTO OutboundReport
OutboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.ShipperData + '.ShipperName AS ShipperName, ' +
@.ShipperData + '.ShipperName2 AS ShipperCompName, ' +
@.ShipperData + '.ShipperAddr AS ShipperAddr, ' +
@.ShipperData + '.ShipperCity AS ShipperCity, ' +
@.ShipperData + '.ShipperState AS ShipperState, ' +
@.ShipperData + '.ShipperZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)
END
GOTO Done|||InboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.AddrData + '.aName1 AS ShipperName, ' +
@.AddrData + '.aName2 AS ShipperCompName, ' +
@.AddrData + '.aAddr1 AS ShipperAddr, ' +
@.AddrData + '.aCity AS ShipperCity, ' +
@.AddrData + '.aState AS ShipperState, ' +
@.AddrData + '.aZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)
END
GOTO Done|||TotalsReport:
BEGIN
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @.LadingType = 'inbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
--Outbound
SET @.LadingType = 'outbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
--Misc
SET @.LadingType = 'misc'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
SELECT InvoiceNum AS InvoiceNumber,
ShipperNum AS ShipperNumber,
InvoiceDate AS InvoiceDate,
PickupTransDate AS ShipDate,
ShipperName AS ShipperName,
ShipperName2 AS ShipperCompName,
ShipperAddr AS ShipperAddr,
ShipperCity AS ShipperCity,
ShipperState AS ShipperState,
ShipperZip AS ShipperZip,
bName1 AS ConsigneeName,
bName2 AS ConsigneeCompName,
bAddr1 AS ConsigneeAddr,
bCity AS ConsigneeCity,
bState AS ConsigneeState,
bZip AS ConsigneeZip,
bCountry AS ConsigneeCountry,
bPhone AS ConsigneePhone,
TrackingNum AS TrackingNum,
CustRef1 AS RefNum1,
CustRef2 AS RefNum2,
UPSZone AS Zone,
ServiceLevel AS ServiceLevel,
Weight AS Weight,
Lading AS LadingDesc,
SMPCodeDesc AS SMPDesc,
GrossCharge AS GrossCharge,
Incentive AS Incentive,
NetCharge AS NetCharge,
AccessorialTotal AS AccessorialTotal,
CodeRefDesc AS ComResDesc,
HundredWeight AS HundredWeight
FROM #TotalsTemp
ORDER BY Lading
END
GOTO Done
Done:
END
GO|||So it's the execute that throws the error...
Instead of doing EXEC do SELECT @.SQLStr and take a look at it...can you post just that?
That'll be easier to debug...
Hey what's an extra 4 post counts...|||Well I get the error when I call the sproc. I don't think that the EXEC throws the error because this sproc worked fine with EXEC until I needed to make a chage on the bottom section starting with TotalsReport. so if you look at my original post it shows only that section and my second post shows all the declare's.
Thanks for your help.
P.S. the more posts the better :)|||What I'm suggesting is that the SQL statement is malformed...just putting the string together is fine...it's when you execute the sql that there's a problem (well, like duh brett)...
I was suggesting post what it buils...should be easier to see,,,wait...I can do that...|||YUP!
INSERT INTO #TotalsTemp SELECT tbl1ReportData.InvoiceNum, tbl1ReportData.ShipperNum, tbl1ReportData.InvoiceDate
, tbl1InvoiceData.PickupTransDate, tbl1AddrData.aName1, tbl1AddrData.aName2, tbl1AddrData.aAddr1
, tbl1AddrData.aCity, tbl1AddrData.aState, tbl1AddrData.aZip, tbl1ReportData.bName1, tbl1AddrData.bName2
, tbl1AddrData.bAddr1, tbl1ReportData.bCity, tbl1ReportData.bState, tbl1AddrData.bZip AS, tbl1AddrData.bCountry
, tbl1AddrData.bPhone, tbl1ReportData.TrackingNum, tbl1InvoiceData.CustRef1, tbl1InvoiceData.CustRef2
, tbl1ReportData.UPSZone, tblLegendServiceLevel.ServiceLevel, tbl1ReportData.Weight, tblLegendLading.Lading
, tblLegendSMPCodes.[Desc], tbl1InvoiceData.GrossCharge, tbl1ReportData.Incentive, tbl1ReportData.NetCharge
, tbl1ReportData.AccessorialTotal, tblCodeRef.[Desc], tbl1InvoiceData.HundredWeight
FROM tbl1ReportData INNER JOIN tbl1InvoiceData ON tbl1ReportData.DataID = tbl1InvoiceData.DataID
INNER JOIN tbl1AddrData ON tbl1ReportData.DataID = tbl1AddrData.DataID
INNER JOIN tblLegendServiceLevel ON tbl1ReportData.ServiceStandard = tblLegendServiceLevel.ServiceStandard
INNER JOIN tblLegendLading ON tbl1ReportData.LadingCode = tblLegendLading.LadingCode
INNER JOIN tblLegendSMPCodes ON tbl1ReportData.SMP2 = tblLegendSMPCodes.SMPCode
INNER JOIN tblCodeRef ON tbl1InvoiceData.ComRes = tblCodeRef.Code
INNER JOIN tblShipperNumberLookUp AS LookUp ON tbl1ReportData.ShipperNum = LookUp.ShipperNumber
INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID
INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child
INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl
WHERE (tbl1ReportData.InvoiceDate BETWEEN 'Mar 17 2004 12:00AM' AND 'Mar 17 2004 12:00AM')
AND (tblOrg_Unit_Hier.parent = 1) AND (tblOrg_lvls.Root = 1) AND (tblOrg_lvls.[Name] = 'Shipper Number')
AND (tblLegendLading.LadingType = 'Brett')|||WOW, I can't beleive I missed that. But with an sproc like this, it's bound to happend.
Thank you so much.
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
EXCEPTION_ACCESS_VIOLATION
I am using SQL Server 7 and the following query giving
exception access violation error.
Error is : ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Could you please suggest what could be wrong with the
Query.
It is working fine if I sub divide the Derived expression
to intermediate temporary tables.
SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
BUDGET_DATE,
(((D1.WB_AVG * ISNULL
(ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
@.MONTH_DAYS AS WB_EXPENSE
INTO #WB_MTD
FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
CHG_OFF_1,
COALESCE(D.REGION,L.REGION) AS REGION,
@.BUDGET_DATE AS BUDGET_DATE,
(((ISNULL(L.PRE_LOANS,0) - ISNULL
(D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
(L.CUR_LOANS,0)))/2) AS WB_AVG
FROM (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
FROM DEPOSITS_CUBE_V
WHERE BUSINESS_DATE = @.BUDGET_DATE AND
CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
FULL OUTER JOIN (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
FROM LOANS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
ON D.BUDGET_DATE = L.BUDGET_DATE AND
D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
(RATE_LAST_USED,0))/100) AS ANN_INTEREST
FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY REGION) AS R
ON D1.REGION = R.REGION
Thanks in advalceThere types of errors are typically bugs in SQL Server. Assuming you are current on service pack and
have searched KB already, I suggest you open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Girija Ponnaganti" <anonymous@.discussions.microsoft.com> wrote in message
news:006701c3ad6a$a59dd9b0$a401280a@.phx.gbl...
> I am using SQL Server 7 and the following query giving
> exception access violation error.
> Error is : ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 14 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Could you please suggest what could be wrong with the
> Query.
> It is working fine if I sub divide the Derived expression
> to intermediate temporary tables.
> SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
> BUDGET_DATE,
> (((D1.WB_AVG * ISNULL
> (ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
> @.MONTH_DAYS AS WB_EXPENSE
> INTO #WB_MTD
> FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
> CHG_OFF_1,
> COALESCE(D.REGION,L.REGION) AS REGION,
> @.BUDGET_DATE AS BUDGET_DATE,
> (((ISNULL(L.PRE_LOANS,0) - ISNULL
> (D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
> (L.CUR_LOANS,0)))/2) AS WB_AVG
> FROM (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
> FROM DEPOSITS_CUBE_V
> WHERE BUSINESS_DATE = @.BUDGET_DATE AND
> CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
> FULL OUTER JOIN (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
> FROM LOANS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
> ON D.BUDGET_DATE = L.BUDGET_DATE AND
> D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
> JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
> TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
> (RATE_LAST_USED,0))/100) AS ANN_INTEREST
> FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY REGION) AS R
> ON D1.REGION = R.REGION
>
> Thanks in advalce
exception access violation error.
Error is : ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Could you please suggest what could be wrong with the
Query.
It is working fine if I sub divide the Derived expression
to intermediate temporary tables.
SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
BUDGET_DATE,
(((D1.WB_AVG * ISNULL
(ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
@.MONTH_DAYS AS WB_EXPENSE
INTO #WB_MTD
FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
CHG_OFF_1,
COALESCE(D.REGION,L.REGION) AS REGION,
@.BUDGET_DATE AS BUDGET_DATE,
(((ISNULL(L.PRE_LOANS,0) - ISNULL
(D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
(L.CUR_LOANS,0)))/2) AS WB_AVG
FROM (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
FROM DEPOSITS_CUBE_V
WHERE BUSINESS_DATE = @.BUDGET_DATE AND
CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
FULL OUTER JOIN (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
FROM LOANS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
ON D.BUDGET_DATE = L.BUDGET_DATE AND
D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
(RATE_LAST_USED,0))/100) AS ANN_INTEREST
FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY REGION) AS R
ON D1.REGION = R.REGION
Thanks in advalceThere types of errors are typically bugs in SQL Server. Assuming you are current on service pack and
have searched KB already, I suggest you open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Girija Ponnaganti" <anonymous@.discussions.microsoft.com> wrote in message
news:006701c3ad6a$a59dd9b0$a401280a@.phx.gbl...
> I am using SQL Server 7 and the following query giving
> exception access violation error.
> Error is : ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 14 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Could you please suggest what could be wrong with the
> Query.
> It is working fine if I sub divide the Derived expression
> to intermediate temporary tables.
> SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
> BUDGET_DATE,
> (((D1.WB_AVG * ISNULL
> (ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
> @.MONTH_DAYS AS WB_EXPENSE
> INTO #WB_MTD
> FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
> CHG_OFF_1,
> COALESCE(D.REGION,L.REGION) AS REGION,
> @.BUDGET_DATE AS BUDGET_DATE,
> (((ISNULL(L.PRE_LOANS,0) - ISNULL
> (D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
> (L.CUR_LOANS,0)))/2) AS WB_AVG
> FROM (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
> FROM DEPOSITS_CUBE_V
> WHERE BUSINESS_DATE = @.BUDGET_DATE AND
> CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
> FULL OUTER JOIN (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
> FROM LOANS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
> ON D.BUDGET_DATE = L.BUDGET_DATE AND
> D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
> JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
> TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
> (RATE_LAST_USED,0))/100) AS ANN_INTEREST
> FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY REGION) AS R
> ON D1.REGION = R.REGION
>
> Thanks in advalce
Exception report in the SQL Profiler
Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
Thanks
Frank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David Gugick wrote:
> Frank Rizzo wrote:
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.
|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
Thanks
Frank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David Gugick wrote:
> Frank Rizzo wrote:
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.
|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Exception report in the SQL Profiler
Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>> Hello,
>> I keep getting an Event Class 'Exception' in the SQL Profiler.
>> The text data is 'Error: 208, Severity: 16, State: 0'
>> However, neither ObjectID or the ObjectName colums are populated.
>> Error 208 states that object which does not exist is referenced.
>> However, I can't seem to pinpoint what it's actually complaining
>> about.
>> The application that calls the sproc where this occurs reports no
>> errors and the sprocs do execute fine.
>> How can I troubleshoot this problem?
>> Thanks
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>> Hello,
>> I keep getting an Event Class 'Exception' in the SQL Profiler.
>> The text data is 'Error: 208, Severity: 16, State: 0'
>> However, neither ObjectID or the ObjectName colums are populated.
>> Error 208 states that object which does not exist is referenced.
>> However, I can't seem to pinpoint what it's actually complaining
>> about.
>> The application that calls the sproc where this occurs reports no
>> errors and the sprocs do execute fine.
>> How can I troubleshoot this problem?
>> Thanks
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Exception report in the SQL Profiler
Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Subscribe to:
Posts (Atom)