I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks
I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...
Any help is highly appreciated....
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2 @REID int, @LOID int, @RetValintoutput
AS
Declare @RRID int Declare @APID int Declare @intREID varchar(20) Declare @intIMID varchar(20)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null open crReqRec fetch next from crReqRec into @RRID
set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)
set @intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID )
insert into ImplementationTasks ( IMID, ITStatus, ITStatusDate ) VALUES ( @intIMID, '2', GetDate() ) SET @RetVal = @@Identity while @@fetch_status = 0 Begin
Update RequestRecords set ITID = @RETVal, RRStatus = 'IA' where REID = @REID and RRID = @RRID
FETCH NEXT FROM crReqRec into @RRID end
close crReqRec deallocate crReqRec
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.
I have a temp table populated with the below headers and their associated data.
The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.
The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.
One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.
The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.
The current script I have written is below. If you can offer any help I would greatly appreciate it.
Open a cursor , Fetch the record , during this kind of operation , will the specific table be locked and fail to be updated or select by another session ?
I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or???
What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.
For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @instrumentlinje remains empty.
If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument" statement doesn't execute.
DELETE FROM ALL_tbl_instrumentkoder
DECLARE @medlem int DECLARE @instrument varchar(10) DECLARE @instrumentlinje varchar(150)
DECLARE medlemmer_cursor CURSOR FOR SELECT medlemsnummer FROM ket.ALL_tbl_medlemsinfo (NOLOCK) WHERE medlemsnummer = 9611
DECLARE instrumenter_cursor CURSOR FOR SELECT [MCPS Kode] FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK) WHERE Medlemsnummer = @medlem
OPEN medlemmer_cursor
FETCH NEXT FROM medlemmer_cursor INTO @medlem
WHILE @@FETCH_STATUS = 0 BEGIN
OPEN instrumenter_cursor FETCH NEXT FROM instrumenter_cursor INTO @instrument
WHILE @@FETCH_STATUS = 0 BEGIN SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument FETCH NEXT FROM instrumenter_cursor INTO @instrument END
CLOSE instrumenter_cursor
INSERT INTO ALL_tbl_instrumentkoder VALUES(@medlem, @instrumentlinje)
FETCH NEXT FROM medlemmer_cursor INTO @medlem
END
CLOSE medlemmer_cursor DEALLOCATE medlemmer_cursor DEALLOCATE instrumenter_cursor
I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected) 6255.84 (1 row(s) affected) Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real DECLARE @updatedprice real DECLARE @updatedRe real DECLARE @updatedAUX real SET @lastprice = ( SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice) )
I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like:
Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table
I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:
CREATE PROCEDURE [Update_product_manufacturer] AS
Declare @product_id int Declare @supplier_name VarChar (255)
Declare ValueCursor Cursor For
select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name] from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product], [P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke] where [product].product_id = [VARIANT].[product_id] and [P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU] AND [P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid] AND [P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id] order by [product].[product_id] for read only
Open ValueCursor while (0 = 0) begin fetch next from ValueCursor Into @product_id, @supplier_name
update product set manufacturer = @supplier_name where product_id = @product_id
end
close ValueCursor Deallocate ValueCursor
Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows
it's been 2 hours, the job is still working.
Who has this kind of experience? How can I make updating quickly?
I want to put the fetch results of a cursor to a temporary DB for manipulation, Im selecting all columns from the table in the cursor and the number of total columns is unknow.
I want to send 1 email with all clientname records which the cursor gets for me. My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help. I ano table to understand here about the while if right. thanks. +++++++++++++++++++++++++++++++++++++++++ CREATE PROCEDURE test1 AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC for
select client_name from clients ------------------------- -- now prepare and send out the e-mails declare @ToEmail varchar(255) declare @FromEmail varchar(255) declare @Subject varchar(255) declare @Body varchar(2000) declare @UserID numeric(38) declare @UserName varchar(255) declare @SMTPServer varchar(100) set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor fetch next from myCursor into @clientName
--loop now: while (@@fetch_status=0)
begin -- while(@@fetch_status=0) -- check if valid "To" e-mail address was found if ((@clientName is null) or (ltrim(@clientName) = ''))
begin --should not come here anytime ideally set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = 'was emailed to wrong person' set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = '-testing' set @Body =
'this will send ClientName:'+ @clientName end --end else
-- send the e-mail --exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer --fetch next from myCursor into @clientName
fetch next from myCursor into @clientName
end --while(@@fetch_status=0) exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer close myCursor deallocate myCursor
I have a procedure in Oracle that contains the following cursor:
CURSOR SCHED_TRIPS IS SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID FROM Dbo.SCHEDTRIPS_VIEW WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE) AND DISPOSITION <> 'V'; BEGIN FOR S IN SCH_TRIPS LOOP UPDATE dbo.SCHEDULES T SET T.DIRTYBIT = 1 WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);
UPDATE dbo.SCHEDULES T SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE; END LOOP; COMMIT ; END;
My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.
Summary * The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats. * As far as I know a fetch-next-statement always returns a single row? * Using a static cursor works as aspected. * Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function create partition function fnTestPartition01( smallint ) as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
as declare @From varchar(8000) declare @Subject varchar(8000) declare @Body varchar(4000) declare @smtp varchar(8000) declare @counter int, @tbl varchar(8000) Declare @MailID int Declare @hr int Declare @To varchar(8000) Declare @tblquery varchar(8000) declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000) set @from = 'name@mail.com' set @subject = 'testheader' set @body = 'testing successful' set @smtp = 'smtp.com'
--========================================================================= --============================ get database name ======================= IF (LEN(MONTH(GETDATE())) = 1) BEGIN Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2)) --01 END ELSE BEGIN Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2)) --12 END SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth --aia_DATA200712 --=================================================================== --============================ get table number ======================= set @counter = 1 while @counter >= 59 begin IF (LEN(@counter) = 1) BEGIN Set @Tbl = '0' + @counter --01 END ELSE BEGIN Set @Tbl = @counter --12 END
--=========================check if table being created exists IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE catalog_name = 'temptable') GOTO table_1 --=================================== get all email accounts ===================== set @tblquery = ' select ID, Email INTO temptable FROM ( select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null )data DECLARE deptemail_cursor CURSOR FOR select id, email from temptable where id is not null '
--============================================================================= --====just above you can see the cursor.. below is sending the emails --======================================================================
exec(@tblquery)
OPEN deptemail_cursor
FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
WHILE @@FETCH_STATUS = 0 BEGIN --If LEN(@deptemail) > 0 --BEGIN set @to = @deptemail EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail <-- different mail server EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp EXEC @hr = sp_OASetProperty @MailID , 'From', @From EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID , 'To', @to EXEC @hr = sp_OAMethod @MailID , 'Send', NULL EXEC @hr = sp_OADestroy @MailID --END FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail END
CLOSE deptemail_cursor DEALLOCATE deptemail_cursor
Table_1: drop table temptable return
set @counter = @counter + 1
end
this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables). the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!
Thanks in advance, Larry
ALTER FUNCTION dbo.TestFoodDisLikes
(
@ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @RDLike varchar(50)
DECLARE @RDLikeList varchar(250)
BEGIN
SELECT @RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
In second Table store those records who are selected and stored in 2nd table. NameAppidFunctionCodeFunNameSubFunCode Data810Summary0 Data820View0 Data830&View0 Ad630Mbl20
Our requirements we use one query, In query fetch total 5 rows. and output show like this
Id StdId TeacherName Day subject 1 1 Archana Monday English 2 1 Archana Tue Marathi 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi 6 2 Archana Mon english 7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid 1 Shalini 1 2 Monika 1 3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject from StudentTeacherRelation inner join Student Student.id = StudentTeacherRelation.StuId where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show Id StdId TeacherName Day subject where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
---------------------------------------------------------- Declare @NextNo integer Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone Open Clone_Cursor Fetch Next from Clone_Cursor WHILE @@FETCH_STATUS = 0 BEGIN Select @NextNo = @NextNo + 1 Clone_Cursor("ABAN8") = @NextNo Update Clone_Cursor FETCH NEXT FROM Clone_Cursor END
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble.
Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
STATIC Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor declare ll cursor global static for select name, salary from ag open ll fetch from ll
while @@FETCH_STATUS=0 fetch from ll update ag set salary=200 where 1=1
close ll deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur DEALLOCATE DBCur
Part 2
SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is, how to join the part 1 n part 2? is there posibility?
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2 @REID int
AS
Declare @RRID int Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null open crReqRec fetch next from crReqRec into @RRID
Declare crImpGrp cursor for select ITID from RequestRecords where RRID = @RRID open crImpGrp fetch next from crImgGrp into @ITID while @@fetch_status = 0
Hello.. i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query ---- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() ) then all data call one by one change .. when the refresh the page the value of all zodiac is change that is wrong ,,, so what is the quary that one time in day the value off all zodiac is same next day the value has been change............................................
so please help me /........... tell what is the process to not data change into the page refresh Ashwnai
i am having an endless loop after the first record.... anything i missed?
ALTER PROCEDURE IMPGrpEscalationX
AS
Declare @IMID int Declare @IMID2 int Declare @FS1 int Declare @FS2 int
Declare crFirst cursor local for select IMID from ImplementationGroup where IMSTatus = 'Y' open crFirst fetch next from crFirst into @IMID
begin Declare crSecond cursor local for select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @IMID open crSecond fetch next from crSecond into @IMID2 set @FS2 = @@fetch_status if not exists(select IMID from Employees_ImplementationGroup where IMID = @IMID2 and IMType = 'T') Begin DECLARE @MsgText varchar(700) DECLARE @IMGRPNAME varchar(50) Set @IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @IMID2) --SET @MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @IMID2 + '&imgrpname=' + @IMGrpName
I need to see the records from two tables where the mobilenumbers are not same. I have two tables named as messages and subscribers with Id, MobileNumber fields.
Now here I need to see the records where the mobile number exist in one table but not in other table. Please give me the sql query for this.
