yeah that was the issue. thanks for all your help! Brad
-----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 9:26 PM To: SQL Subject: RE: Concatenation assistance... Normally, within an SP, the temp tables are discarded when the SP terminates. Are you running this within a Query Analyzer window?? If so, don't. Put it all in an SP. You may have to close the analyzer window and open a new one to clear the temp table away. This can happen if you get an error in a bad spot and leave the table hanging. Closing the window will discard it. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 16:54 To: SQL Subject: RE: Concatenation assistance... I just finished removing them, all seems ok so far; however when I resubmit the SQL i get a #temp already exists, i know how to delete NONtemp tables, but it doenst seem to work for #temporary tables....do you know the syntax for that? THANKS! s Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 4:45 PM To: SQL Subject: RE: Concatenation assistance... No @ signs on cursors. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 16:44 To: SQL Subject: RE: Concatenation assistance... I am getting a error when I attempt to OPEN @MyCursor. Can you tell what is my problem? All the remaining errors reference same thing, cant find MyCursor *starts at line 183 -- declareNewRecordListA - Registered but not completed Course DECLARE @NewRecordIDListA varchar(8000) -- setDefault SET @NewRecordIDListA = '' -- declareNewRecordListB - Completed Course DECLARE @NewRecordIDListB varchar(8000) -- setDefault SET @NewRecordIDListB = '' -- declareMyRowCounterA DECLARE @MyRowCounterA int -- default to zero SET @MyRowCounterA = 0 -- declareMyRowCounterB DECLARE @MyRowCounterB int -- default to zero SET @MyRowCounterB = 0 -- getExcludeListA - these are users whom signed up but didnt finish course DECLARE @tmpExcludeListA varchar(8000); SET @tmpExcludeListA = (SELECT ExcludeListA FROM Custom_NAMBDataDumpData WHERE ID = 1); PRINT @tmpExcludeListA -- getExcludeListB - these are users whom have completed the course DECLARE @tmpExcludeListB varchar(8000); SET @tmpExcludeListB = (SELECT ExcludeListB FROM Custom_NAMBDataDumpData WHERE ID = 1); PRINT @tmpExcludeListB -- cleanUpBListAddApostrophes DECLARE @CleanedExcludeListB varchar(8000) SET @CleanedExcludeListB = '' DECLARE @tmpDelim varchar(15) SET @tmpDelim = CHAR(39) + CHAR(44) + CHAR(39) IF (LEN(RTRIM(@tmpExcludeListB)) > 0) BEGIN SET @CleanedExcludeListB = REPLACE(@tmpExcludeListB, CHAR(44), @tmpDelim) END ELSE BEGIN SET @CleanedExcludeListB = '' END -- finalCleanup SET @CleanedExcludeListB = CHAR(39) + @CleanedExcludeListB + CHAR(39) -- DEBUG: cleanedListB PRINT @CleanedExcludeListB -- ifDrop#NAMBTempTbl IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'#NAMBTempTbl') DROP DATABASE [#NAMBTempTbl] -- createTempTable CREATE TABLE #NAMBTempTbl ( cyb_record_num varchar(25), cyb_course_code varchar(100), cyb_course_grade varchar(100), cyb_student_id varchar(25), cyb_profile_email varchar(300), cyb_profile_id_number varchar(111), cyb_profile_org varchar(400), cyb_profile_addr1 varchar(400), cyb_profile_addr2 varchar(400), cyb_profile_city varchar(400), cyb_profile_state varchar(400), cyb_profile_zip varchar(400), cyb_profile_country varchar(400), cyb_course_price money, cyb_course_complete_date datetime, cyb_billing_first_name varchar(400), cyb_billing_last_name varchar(400), cyb_order_addr1 varchar(400), cyb_order_city varchar(400), cyb_order_state varchar(400), cyb_order_zip varchar(400), cyb_order_phone varchar(300), cyb_order_email varchar(300), cyb_order_pay_method varchar(400), cyb_order_cc_num varchar(150), cyb_order_cc_expire varchar(150), cyb_order_datetime datetime ) -- dynamicSQL DECLARE @MySQL varchar(8000) SET @MySQL = 'INSERT INTO #NAMBTempTbl (cyb_record_num, cyb_course_code, cyb_course_grade, cyb_student_id, cyb_profile_email, cyb_profile_id_number, cyb_profile_org, cyb_profile_addr1, cyb_profile_addr2, cyb_profile_city, cyb_profile_state, cyb_profile_zip, cyb_profile_country, cyb_course_price, cyb_course_complete_date, cyb_billing_first_name, cyb_billing_last_name, cyb_order_addr1, cyb_order_city, cyb_order_state, cyb_order_zip, cyb_order_phone, cyb_order_email, cyb_order_pay_method, cyb_order_cc_num, cyb_order_cc_expire, cyb_order_datetime) SELECT x.cyb_record_num, x.cyb_course_code, x.cyb_course_grade, y.cyb_student_id, y.cyb_profile_email, y.cyb_profile_id_number, y.cyb_profile_org, y.cyb_profile_addr1, y.cyb_profile_addr2, y.cyb_profile_city, y.cyb_profile_state, y.cyb_profile_zip, y.cyb_profile_country, a.cyb_course_price, z.cyb_course_complete_date, (SELECT TOP 1 tbl.cyb_order_first_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_first_name, (SELECT TOP 1 tbl.cyb_order_last_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_last_name, (SELECT TOP 1 tbl.cyb_order_addr1 FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_addr1, (SELECT TOP 1 tbl.cyb_order_city FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_city, (SELECT TOP 1 tbl.cyb_order_state FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_state, (SELECT TOP 1 tbl.cyb_order_zip FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_zip, (SELECT TOP 1 tbl.cyb_order_phone FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_phone, (SELECT TOP 1 tbl.cyb_order_email FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_email, (SELECT TOP 1 tbl.cyb_order_pay_method FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_pay_method, (SELECT TOP 1 RIGHT(tbl.cyb_order_cc_num, 4) FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_num, (SELECT TOP 1 tbl.cyb_order_cc_expire FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_expire, (SELECT TOP 1 tbl.cyb_order_datetime FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_datetime FROM cyb_grade_book_mast x INNER JOIN cyb_student_profile_mast y ON x.cyb_student_id = y.cyb_student_id INNER JOIN cyb_required_course_mast z ON x.cyb_student_id = z.cyb_student_id INNER JOIN cyb_course_mast a ON x.cyb_course_code = a.cyb_course_code WHERE ((y.cyb_profile_id_number <> ''000000-00'') AND ((x.cyb_course_code = ''NAMB001M'' OR x.cyb_course_code = ''NAMB001NM'') OR (x.cyb_course_code = ''NAMB002M'' OR x.cyb_course_code = ''NAMB002NM'') OR (x.cyb_course_code = ''NAMB003M'' OR x.cyb_course_code = ''NAMB003NM'')) AND (x.variable_switch_1 <> ''M'') AND (x.cyb_org_id = 11) AND (z.cyb_course_code LIKE x.cyb_course_code) AND (z.cyb_org_id = 11) AND (x.cyb_record_num NOT IN (' + @CleanedExcludeListB + ')))' -- DEBUG: printDynamicSQL PRINT @MySQL -- DEBUG: executeDynamicSQL EXEC (@MySQL) -- declareFetchReturnVariables DECLARE @cyb_record_num varchar(25), @cyb_course_code varchar(100), @cyb_course_grade varchar(100), @cyb_student_id varchar(25), @cyb_profile_email varchar(300), @cyb_profile_id_number varchar(111), @cyb_profile_org varchar(400), @cyb_profile_addr1 varchar(400), @cyb_profile_addr2 varchar(400), @cyb_profile_city varchar(400), @cyb_profile_state varchar(400), @cyb_profile_zip varchar(400), @cyb_profile_country varchar(400), @cyb_course_price money, @cyb_course_complete_date datetime, @cyb_billing_first_name varchar(400), @cyb_billing_last_name varchar(400), @cyb_order_addr1 varchar(400), @cyb_order_city varchar(400), @cyb_order_state varchar(400), @cyb_order_zip varchar(400), @cyb_order_phone varchar(300), @cyb_order_email varchar(300), @cyb_order_pay_method varchar(400), @cyb_order_cc_num varchar(150), @cyb_order_cc_expire varchar(150), @cyb_order_datetime datetime -- declareCursor DECLARE MyCursor CURSOR FOR SELECT cyb_record_num, cyb_course_code, cyb_course_grade, cyb_student_id, cyb_profile_email, cyb_profile_id_number, cyb_profile_org, cyb_profile_addr1, cyb_profile_addr2, cyb_profile_city, cyb_profile_state, cyb_profile_zip, cyb_profile_country, cyb_course_price, cyb_course_complete_date, cyb_billing_first_name, cyb_billing_last_name, cyb_order_addr1, cyb_order_city, cyb_order_state, cyb_order_zip, cyb_order_phone, cyb_order_email, cyb_order_pay_method, cyb_order_cc_num, cyb_order_cc_expire, cyb_order_datetime FROM #NAMBTempTbl -- openCursor OPEN @MyCursor -- fetchFirstRow FETCH NEXT FROM @MyCursor INTO @cyb_record_num, @cyb_course_code, @cyb_course_grade, @cyb_student_id, @cyb_profile_email, @cyb_profile_id_number, @cyb_profile_org, @cyb_profile_addr1, @cyb_profile_addr2, @cyb_profile_city, @cyb_profile_state, @cyb_profile_zip, @cyb_profile_country, @cyb_course_price, @cyb_course_complete_date, @cyb_billing_first_name, @cyb_billing_last_name, @cyb_order_addr1, @cyb_order_city, @cyb_order_state, @cyb_order_zip, @cyb_order_phone, @cyb_order_email, @cyb_order_pay_method, @cyb_order_cc_num, @cyb_order_cc_expire, @cyb_order_datetime -- loopCursor WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @@CURSOR_ROWS /* -- DEBUG: displayRowData PRINT @cyb_record_num + '~' + @cyb_course_code + '~' + @cyb_course_grade + '~' + @cyb_student_id + '~' + @cyb_profile_email + '~' + @cyb_profile_id_number + '~' + @cyb_profile_org + '~' + @cyb_profile_addr1 + '~' + @cyb_profile_addr2 + '~' + @cyb_profile_city + '~' + @cyb_profile_state + '~' + @cyb_profile_zip + '~' + @cyb_profile_country + '~' + CAST(@cyb_course_price AS VARCHAR) + '~' + CAST(@cyb_course_complete_date AS varchar(44)) + '~' + @cyb_billing_first_name + '~' + @cyb_billing_last_name + '~' + @cyb_order_addr1 + '~' + @cyb_order_city + '~' + @cyb_order_state + '~' + @cyb_order_zip + '~' + @cyb_order_phone + '~' + @cyb_order_email + '~' + @cyb_order_pay_method + '~' + @cyb_order_cc_num + '~' + @cyb_order_cc_expire + '~' + CAST(@cyb_order_datetime AS varchar(44)) -- DEBUG: listComparision PRINT ',' + RTRIM(@tmpExcludeListA) + ',' PRINT '%,' + RTRIM(CAST(@cyb_record_num AS VARCHAR(25))) + ',%' */ -- Newly Registered User; A list; irrelevant if they completed course or not, since all data is written IF NOT ((',' + RTRIM(@tmpExcludeListA) + ',') LIKE ('%,' + RTRIM(CAST(@cyb_record_num AS VARCHAR(25))) + ',%')) BEGIN -- insertRecord -- appendIDTo @NewRecordIDListA IF LEN(RTRIM(@NewRecordIDListA)) > 0 BEGIN SET @NewRecordIDListA = @NewRecordIDListA + ',' + CAST(@cyb_record_num AS VARCHAR(25)) END ELSE SET @NewRecordIDListA = CAST(@cyb_record_num AS VARCHAR(25)) -- incrementMyRowCounterA SET @MyRowCounterA = @MyRowCounterA + 1 END ELSE -- Registered User, check to see if they have finished the course; if TRUE add to B list BEGIN IF (RTRIM(@cyb_course_grade) <> 0) BEGIN -- appendIDTo @NewRecordIDListB IF LEN(RTRIM(@NewRecordIDListB)) > 0 BEGIN SET @NewRecordIDListB = @NewRecordIDListB + ',' + CAST(@cyb_record_num AS VARCHAR(25)) END ELSE SET @NewRecordIDListB = CAST(@cyb_record_num AS VARCHAR(25)) -- incrementMyRowCounter SET @MyRowCounterB = @MyRowCounterB + 1 END END -- getNextRow FETCH NEXT FROM @MyCursor INTO @cyb_record_num, @cyb_course_code, @cyb_course_grade, @cyb_student_id, @cyb_profile_email, @cyb_profile_id_number, @cyb_profile_org, @cyb_profile_addr1, @cyb_profile_addr2, @cyb_profile_city, @cyb_profile_state, @cyb_profile_zip, @cyb_profile_country, @cyb_course_price, @cyb_course_complete_date, @cyb_billing_first_name, @cyb_billing_last_name, @cyb_order_addr1, @cyb_order_city, @cyb_order_state, @cyb_order_zip, @cyb_order_phone, @cyb_order_email, @cyb_order_pay_method, @cyb_order_cc_num, @cyb_order_cc_expire, @cyb_order_datetime -- endLoopCursor END -- closeCursor CLOSE @MyCursor -- deallocateCursor DEALLOCATE @MyCursor -- updateExcludeListA & B with NewRecordIDListA OR B PRINT 'A Data' PRINT @NewRecordIDListA PRINT @MyRowCounterA PRINT '' PRINT 'B Data' PRINT @NewRecordIDListB PRINT @MyRowCounterB GO THANKS BRAD ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Get the mailserver that powers this list at http://www.coolfusion.com
