SP Code hangs
-
Hello all,
I have an SP that hangs right before it opens or during the opening of the cursor.
The following is where it stops PRINT 'about to open cursor'
I'm thinking it has something to do with the transaction keeping a lock on the table maybe? any help is much appreciated.
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
PROCEDURE [dbo].[sp_importXML]
@filePath
VARCHAR( 255 )
, @userIdentity INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR( 4000 ), @importIdentity INT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
SELECT @importIdentity = MAX( importIdentity ) + 1
FROM tbl_importXML
SET @sql = '
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
INSERT INTO tbl_importXML ( userIdentity, myXML )
SELECT '
+ CAST( @userIdentity AS VARCHAR( 10 ) ) + ' AS userIdentity
, x.*
FROM OPENROWSET ( BULK '''
+ @filePath + ''', SINGLE_CLOB ) AS x
PRINT ''Importing XML file''
COMMIT TRANSACTION
'
PRINT 'Finished importing XML file'
EXEC (@sql)
COMMIT TRANSACTION
DECLARE @myXML XML
SELECT @myXML = myXML
FROM tbl_importXML
WHERE (importIdentity = @importIdentity)
PRINT 'Selected XML from import table'
DECLARE @itemIdentity BIGINT
, @url VARCHAR( 255 )
, @title VARCHAR( 80 )
, @description VARCHAR( 8000 )
, @quantity SMALLINT
, @summary VARCHAR( 100 )
, @category VARCHAR( 1000 )
, @price SMALLMONEY
, @classifiedIdentity INT
, @categoryIdentity INT
, @localityIdentity INT
, @imported BIT
DECLARE csr_import CURSOR FOR
SELECT myRow.myColumn.value( '@Id[1]', 'BIGINT' ) AS itemIdentity
, myRow.myColumn.value( 'Url[1]', 'VARCHAR(255)' ) AS url
, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(8000)' ) ) ) AS description
, myRow.myColumn.value( 'Quantity[1]', 'SMALLINT' ) AS quantity
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(100)' ) ) ) AS summary
, myRow.myColumn.value( 'Category[1]', 'VARCHAR(1000)' ) AS category
, CONVERT( SMALLMONEY, REPLACE( myRow.myColumn.value( '(Pricing/BasePrice)[1]', 'VARCHAR( 255 )' ), 'AU $', '' ) ) AS price
FROM @myXML.nodes('//Product') myRow( myColumn )
PRINT 'about to open cursor'
OPEN csr_import
PRINT 'opened cursor'
SELECT @localityIdentity = localityIdentity
FROM tbl_address
WHERE (userIdentity = @userIdentity)
IF (@localityIdentity IS NULL)
BEGIN
RETURN
END
FETCH NEXT
FROM csr_import
INTO @itemIdentity
, @url
, @description
, @quantity
, @summary
, @category
, @price
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT 'in cursor'
SELECT @categoryIdentity = categoryIdentity
FROM tbl_importCategoryMapping
WHERE (remoteCategory = @category)
IF ( EXISTS ( SELECT itemIdentity FROM tbl_importedXML WHERE itemIdentity = @itemIdentity ) )
BEGIN
SET @imported = 1
END
ELSE
BEGIN
SET @imported = 0
END
IF (NOT @categoryIdentity IS NULL AND @imported = 0)
BEGIN
PRINT 'importing: ' + @title
INSERT INTO tbl_classified (
categoryIdentity
, userIdentity
, quantity
, title
, summary
, description
, price
, localityIdentity
, statusIdentity
)
VALUES (
@categoryIdentity
, @userIdentity
, @quantity
, @title
, @summary
, @description
, @price
, @localityIdentity
, 4 -- 4 = awaiting scanning, ie negative keyword check
)
SET @classifiedIdentity = SCOPE_IDENTITY();
END
END TRY
BEGIN CATCH
SET @classifiedIdentity = 0
END CATCH
INSERT INTO tbl_importedXML (
importIdentity
, itemIdentity
, classifiedIdentity
)
VALUES (
@importIdentity
, @itemIdentity
, @classifiedIdentity -- 0 means the classified is not imported
)
FETCH NEXT FROM csr_import
INTO @itemIdentity
, @url
, @description
, @quantity
, @summary
, @category
, @price
END
CLOSE csr_import;
DEALLOCATE csr_import
UPDATE tbl_importXML
SET completeDate = GETDATE()
WHERE (importIdentity = @importIdentity)
-- return the identity of the import
SELECT @importIdentity AS importIdentity
END
-
I've removed the transaction (although I should get it working with at some stage) and it all seems to work, the problem is, it hangs for about 3-4 minutes
Right at the following code;
PRINT 'about to open cursor'
OPEN csr_import
PRINT GETDATE()
PRINT 'opened cursor'
--------------- following is the adjusted and current code
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
DECLARE
@importIdentity INT, @userIdentity INT
SET @importIdentity = 10
SET @userIdentity = 43822
DECLARE @myXML XML
SELECT @myXML = myXML
FROM tbl_importXML
WHERE (importIdentity = @importIdentity)
PRINT GETDATE()
PRINT 'Selected XML from import table'
DECLARE @itemIdentity BIGINT
, @url VARCHAR( 255 )
, @title VARCHAR( 80 )
, @description VARCHAR( 8000 )
, @quantity SMALLINT
, @summary VARCHAR( 100 )
, @category VARCHAR( 1000 )
, @price SMALLMONEY
, @classifiedIdentity INT
, @categoryIdentity INT
, @localityIdentity INT
, @imported BIT
PRINT GETDATE()
DECLARE csr_import CURSOR READ_ONLY
FOR
SELECT myRow.myColumn.value( '@Id[1]', 'BIGINT' ) AS itemIdentity
, myRow.myColumn.value( 'Url[1]', 'VARCHAR(255)' ) AS url
--, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(8000)' ) ) ) AS description
, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(80)' ) ) ) AS title
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(8000)' ) ) ) AS description
, myRow.myColumn.value( 'Quantity[1]', 'SMALLINT' ) AS quantity
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(100)' ) ) ) AS summary
, myRow.myColumn.value( 'Category[1]', 'VARCHAR(1000)' ) AS category
, CONVERT( SMALLMONEY, REPLACE( myRow.myColumn.value( '(Pricing/BasePrice)[1]', 'VARCHAR( 255 )' ), 'AU $', '' ) ) AS price
FROM @myXML.nodes('//Product') myRow( myColumn )
PRINT GETDATE()
PRINT 'about to open cursor'
OPEN csr_import
PRINT GETDATE()
PRINT 'opened cursor'
SELECT @localityIdentity = localityIdentity
FROM tbl_address
WHERE (userIdentity = @userIdentity)
IF (@localityIdentity IS NULL)
BEGIN
RETURN
END
FETCH NEXT
FROM csr_import
INTO @itemIdentity
, @url
, @title
, @description
, @quantity
, @summary
, @category
, @price
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'in cursor'
SELECT @categoryIdentity = categoryIdentity
FROM tbl_importCategoryMapping
WHERE (remoteCategory = @category)
IF ( EXISTS ( SELECT itemIdentity FROM tbl_importedXML WHERE itemIdentity = @itemIdentity AND classifiedIdentity > 0 ) )
BEGIN
SET @imported = 1
END
ELSE
BEGIN
SET @imported = 0
END
PRINT 'Category identity: ' + CAST( @categoryIdentity AS VARCHAR( 11 ) )
PRINT 'Imported: ' + CAST( @imported AS CHAR( 1 ) )
DECLARE @tbl_classifiedCountLeft TABLE (
classifiedCountLeft
INT NOT NULL
)
INSERT INTO @tbl_classifiedCountLeft
EXEC [sp_getClassifiedCountLeft] @userIdentity = @userIdentity
PRINT GETDATE()
PRINT CONVERT( DATETIME, GETDATE(), 113 )
-- make sure that
-- there is a category identity
-- the classified is not already imported previously
-- the user has classifieds left to upload
IF (NOT @categoryIdentity IS NULL AND @imported = 0 AND ( SELECT classifiedCountLeft FROM #tbl_classifiedCountLeft ) > 0 )
BEGIN
INSERT INTO tbl_classified (
categoryIdentity
, userIdentity
, quantity
, title
, summary
, description
, price
, localityIdentity
, statusIdentity
)
VALUES (
@categoryIdentity
, @userIdentity
, @quantity
, COALESCE( @title, '' )
, @summary
, @description
, @price
, @localityIdentity
, 4 -- 4 = awaiting scanning, ie negative keyword check
)
SET @classifiedIdentity = SCOPE_IDENTITY()
END
ELSE
BEGIN
PRINT 'Not importing this record'
IF ( @categoryIdentity IS NULL )
PRINT 'Category is null "' + @category + '"'
IF ( @imported = 1 )
PRINT 'Already imported'
IF ( ( SELECT classifiedCountLeft FROM #tbl_classifiedCountLeft ) = 0 )
PRINT 'No ads left'
END
INSERT INTO tbl_importedXML (
importIdentity
, itemIdentity
, classifiedIdentity
)
VALUES (
@importIdentity
, @itemIdentity
, COALESCE( @classifiedIdentity, 0 ) -- 0 means the classified is not imported
)
FETCH NEXT FROM csr_import
INTO @itemIdentity
, @url
, @title
, @description
, @quantity
, @summary
, @category
, @price
END
CLOSE csr_import
DEALLOCATE csr_import
UPDATE tbl_importXML
SET completeDate = GETDATE()
WHERE (importIdentity = @importIdentity)
-- return the identity of the import
SELECT @importIdentity AS importIdentity
PRINT GETDATE()
-
anyone know how to delete my own post? I've posted an updated version of it.
-
Go to edit a post and then click on the delete button at the bottom...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Now, that is helpful information I can work with. Thanks.
-
You still haven't said what the cursor is for, though
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply