February 22, 2012 at 6:53 am
Hi there! I need help with a pesky cursor... I am developing in 2008 but my production server is still on 2000 for reference. I am basically needing to update table records in Oracle from SQL Server and for 1 of my key fields I could potentially have more than one insert to oracle, so I am using the cursor to loop through the records. However, it isn't looping. It just does the first record and not the second. My example recordset has 2 records that it should be processing. Also, when it gets to my close cursor statements it errors and says my cursor does not exist... why?
Thanks in advance!
IF @TRAN_TYPE = 9
BEGIN
DECLARE @SSN6 AS CHAR(6)
DECLARE @ZLIN_DEL AS CHAR(6)
DECLARE @I_CAT AS CHAR(1)
declare @lin varchar(10)
DECLARE @DISPLAY_ISSUE_NUM AS VARCHAR(15)
DECLARE @MESSAGE VARCHAR(3000)
DECLARE @QUERY VARCHAR(4000)
SET @LIN = 'Z01342'
DECLARE DELETE_CURSOR CURSOR FAST_FORWARD FOR
SELECT SSN6,LIN,I_CAT,B.DISPLAY_ISSUE_NUM
FROM REQUEST_DELETE_ZLIN A INNER JOIN REQUEST_CHANGE B ON A.ISSUE_NUM = B.ISSUE_NUM
LEFT JOIN XWALK_SSN6_LIN C ON A.ZLIN = C.LIN
WHERE A.ZLIN = @LIN
OPEN DELETE_CURSOR
FETCH NEXT FROM DELETE_CURSOR
INTO @SSN6, @ZLIN_DEL,@I_CAT,@DISPLAY_ISSUE_NUM
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY = 'INSERT OPENQUERY(LIWDEV, ''SELECT SSN6,LIN,R_CAT,SLAMIS_RQST,RQST_TYPE,LST_UPDT FROM SSN.SSN_LIN_REQUEST'') VALUES (''' + @SSN6 + ''',''' + @ZLIN_DEL + ''',''' + @I_CAT + ''',''' + @DISPLAY_ISSUE_NUM + ''',''3'',GETDATE())'
EXEC (@QUERY)
SELECT @QUERY AS QUERY INTO TEMP_QUERY_STRING_2
SET @QUERY = (SELECT REPLACE(QUERY,'''','''''') FROM TEMP_QUERY_STRING_2)
SET @MESSAGE = 'INSERT INTO LIW_UPDATE_LOG VALUES(''ZLIN'',''' + @DISPLAY_ISSUE_NUM + ''',''SSN_LIN_REQUEST'',''DELETE'',''SP_AESIP_POST_COMPLETED_REQUESTS'',''' + @QUERY + ''',GETDATE(),''PROCESSED'',NULL)'
EXEC (@MESSAGE)
PRINT @MESSAGE
FETCH NEXT FROM DELETE_CURS0R
INTO @SSN6, @ZLIN_DEL,@I_CAT,@DISPLAY_ISSUE_NUM
DROP TABLE TEMP_QUERY_STRING_2
END
CLOSE DELETE_CURSOR
DEALLOCATE DELETE_CURSOR
END
February 22, 2012 at 6:57 am
Haha! Well this forum solved my problem without anybody answering me!!! 🙂
Thanks to me posting my code here...the font was a little bit different and it helped me to see as I was reviewing my post that where I have my "fetch next" statement... it says DELETE_CURS0R << a zero instead of an O. I have been going round and round forever... thank you forums for showing me my typo! 🙂
February 22, 2012 at 7:30 am
From looking at this scaled down example I don't understand why you need a cursor and dynamic sql at all. It is just two insert statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2012 at 7:56 am
Well its not "just 2 insert statements" ... in my test it is 2 inserts (two totally different records inserting into the same oracle table). This is part of a dynamic stored procedure that processes transactions on a recurring basis. It may only insert 1 record or it may insert 10.
I don't know of any other way to dynamically pass openquery statements from sql to oracle...but if you do I'm all ears.
February 22, 2012 at 11:40 am
If you use "Exec At" you can use a stored proc with parameters instead of dynamic SQL.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply