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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy