June 27, 2011 at 11:54 am
Here's the stored procedures. 85% of the time table 1 is being populated, the other 15 percent it doesnt and causes the code to error out. The error from the code is: Cannot find table 1. I'm not sure what's happeneing.
Here's the stored procedure.
ALTER PROCEDURE [dbo].[GetPubsListByAdminCode]
(
@pAdminCode varchar(10),
@pPubID varchar(20),
@pTitle varchar(500),
@pXMLValue varchar(2048)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
DECLARE @intDocHandle INT
DECLARE @NbrTitles int
DECLARE @TotalQty int
DECLARE @DropCode bit
DECLARE @AdminCode varchar(10)
DECLARE @Title varchar(500)
DECLARE @PubID varchar(20)
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @pXMLValue
if( @pAdminCode IS NULL)
SET @AdminCode = NULL
else
SET @AdminCode = @pAdminCode + '%'
if( @pPubID IS NULL)
SET @PubID = NULL
else
SET @PubID = @pPubID + '%'
if ( @pTitle IS NULL )
SET @Title = NULL
else
SET @Title = '%' + @pTitle + '%'
SET @DropCode = 1;
-- SET @PubStatusID = (SELECT PubStatusID from PubStatusRef where [Description] like @pStatus )
WITH AdminList (AdminCode)
AS
( SELECT AdminCode FROM OPENXML(@intDocHandle, 'Root/ArrayOfAnyType', 1)
WITH
(
AdminCode varchar(10)
)
)
SELECT
[PublicationsID],
[PubID],
[PubNbrLegacy],
[AdminID],
[DivisionID],
[Title],
[DropCode],
[GroupID],
[CategoryID],
[QtyOnHand],
[WebAvailable],
[ReorderLevel],
[PubStatusID],
[ActiveDate],
[UpdatedBy],
[UpdatedDateTime],
[LastReqDate],
[Description],
[DeleteReqDate],
[DeletedBy],
[PubVendorNbr],
[MaterialTypeID],
[PubAbstract],
[PDFName],
[PubsURL],
[PubsOrderLimit],
[IssueDate],
[ContactName],
[ContactPhone],
[ContactEmail],
[Category],
[DiseaseCategory],
[EmailLtDt]
FROM [NCHM_CDCINFO_PUBLICATIONS].[dbo].[Publication]
INNER JOIN AdminList ON [NCHM_CDCINFO_PUBLICATIONS].[dbo].[Publication].AdminID LIKE AdminList.AdminCode
WHERE [AdminID] LIKE ISNULL( @AdminCode, [AdminID]) AND [Title] LIKE ISNULL( @Title, [Title])
AND [PubID] LIKE ISNULL( @PubID, [PubID])
and DropCode = '0';
WITH AdminList1 (AdminCode)
AS
( SELECT AdminCode FROM OPENXML(@intDocHandle, 'Root/ArrayOfAnyType', 1)
WITH
(
AdminCode varchar(10)
)
)
(SELECT COUNT(*) AS NumberOfTitles, SUM([QtyOnHand]) AS TotalQtyOnHand FROM [NCHM_CDCINFO_PUBLICATIONS].[dbo].[Publication]
INNER JOIN AdminList1 ON [NCHM_CDCINFO_PUBLICATIONS].[dbo].[Publication].AdminID LIKE AdminList1.AdminCode
)
EXEC sp_xml_removedocument @intDocHandle
END TRY
BEGIN CATCH
SELECT
Cast( 1 AS bit ) AS HasErrors,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
GO
June 27, 2011 at 2:05 pm
pym8 (6/27/2011)
Here's the stored procedures. 85% of the time table 1 is being populated, the other 15 percent it doesnt and causes the code to error out. The error from the code is: Cannot find table 1. I'm not sure what's happeneing.
You must have more details than "Cannot find table 1". 😀
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2011 at 2:59 pm
Your procedure doesn't have any INSERTs, so I can't see how anything is being populated. :Whistling: Which table is table 1? :unsure:
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 27, 2011 at 9:46 pm
Based on the little bit of info you've given us, this sounds like either an intermittent network or file system error.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply