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
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 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 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.
Select Division,ChasNo,BillDt Into #CMBills from Service_Bills Where Month(BillDt)=@Mh and Year(BillDt)=@Yr and Status Is Null
Select SB.Division,SB.ChasNo,CMB.BillDt as CurBillDt,Max(SB.BillDt) as PreBillDt,Datediff(day,Max(SB.BillDt),Max(CMB.BillDt)) as DiffDays from #CMBills CMB Left Join Service_Bills SB On SB.Division=CMB.Division and SB.ChasNo=CMB.ChasNo and SB.Status Is Null and SB.BillDt<CMB.BillDt Group By SB.Division,SB.ChasNo,CMB.BillDt
But I'm not getting details for all the bills I fetched.
How do I right queries to fetch first 10 records, then next 10 and so on..
My table structure is as follows: Columns -------- MPAD_ID (IDENTITY INCREMENT YES ) MPAD_EmpCode (Employee Code) MPAD_Date (Date) MPAD_Status (Attendance Status i.e. P, A, etc)
Sample Records for the same is given below
MPAD_ID MPAD_EmpCode MPAD_Date MPAD_Status ------- ------------ ---------- ----------- 1 1001 11/01/2007 P 2 1001 11/02/2007 P 3 1001 11/03/2007 A ... .. .. .. 14 1002 11/01/2007 P 15 1002 11/02/2007 A 16 1002 11/03/2007 P
I have a stored procedure that inserts one row into a table. From aSELECT statement I would like to call the SP on each row in theresults. Is setting up a cursor and using fetch statements the bestway (or even the only way) to do this?
Hello everyone, hope someone can help me with this.I have a SQL stored procedure that inserts a record into a table,creates a cursor to fetch the last record that was added to get theunique key that was created and then writes that and other info to aseparate table. This procedure was working fine at our ISP under NT 4and SQL 7.We recently moved to another ISP on servers that are windows 2000 andSQL 2000. Now this code is going kerplooey. It actually worked finein the staging area but now that it was moved into production, it isnot working. also wanted to mention that the production database wasrestored from a backup. below is the code.the first time this is run it is ok, for example the transactionnumber is 1. the next time it is run, a new record is created in thesweep results with a transaction number of 2. but for some reason,when i declare the cursor to fetch the last record, it goes back tothe transaction number 1 record. so the counts from transaction 1don't match counts from transaction 2 and the next step has an errorcondition and doesn't work.thanks in advance for any help you can provideAnn Williams-- update the sweep results tableINSERT tbl_sweepresults (del_wrkfeedback_count,updnull_feedback_count, swp_feedback_count,swp_count_error, del_error, updnull_error, swp_error, init_error,sweep_date)VALUES (@var_del_wrkfeedback_count, @var_updnull_feedback_count,@var_swp_feedback_count,@var_swp_count_error, @var_del_error, @var_updnull_error,@var_swp_error, @var_init_error, GETDATE())-- create cursorDECLARE tbl_sweepresults_cursor SCROLL CURSOR FORSELECT transaction_no, sweep_date, init_error, updnull_feedback_countFROM tbl_sweepresultsOPEN tbl_sweepresults_cursor-- get transaction number, sweep date, init error, feedback sweepcount and pass to tbl_currentTrans for OPAL comparisonFETCH LAST FROM tbl_sweepresults_cursor INTO @var_transaction_no,@var_sweep_date, @var_init_error, @var_swp_countzeroDELETE tbl_currentTransINSERT tbl_currentTrans (current_transaction_no, current_sweep_date,current_init_error, current_swp_countzero)VALUES (@var_transaction_no, @var_sweep_date, @var_init_error,@var_swp_countzero)-- close the cursorCLOSE tbl_sweepresults_cursorDEALLOCATE tbl_sweepresults_cursor
I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks
i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..
so.. i wanted to use the information_schema..
i have:
use dbase; select column_name from information_schema.columns where table_name = 'table1' and column_name != 'pkid'
so there i'm listing the columns, but how am i suppose to select those columns from my real table?
I'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]); SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable(); DataRow dr; //Adding the columns to the datatabledt.Columns.Add("CategoryIdIn"); dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items) { dr = dt.NewRow(); dr[0] = item.Cells[0].Text; dr[1] = item.Cells[1].Text; dt.Rows.Add(dr); }//ForEach da.Fill(dt); gd_freq.DataSource = dt; gd_freq.DataBind();
Hi, 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 ---- DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50); DECLARE @MyVariable CURSOR; SET @MyVariable = CURSOR FOR SELECT isnull(Filters.WorkflowIDs, ''), isnull(FilterElements.CreatedByUser, '')
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID WHERE FiltersItemID = @FilterID; OPEN @MyVariable;FETCH NEXT FROM @MyVariable INTO @WorkflowIDs, @CreatedByUser;
Hi, I want ot get time just like this : 8:40:15 AMI am using select convert(varchar(12),getdate(),8) to get this but this function return 24 Hrs format. How to achive this by using small coding. Jasim...
hi,im just a newbie in asp.net.i have my sqldatasource and a label. i want to get the data from sqldatasource to the label. how can i do that.need codes
Hi, I have stored procedure with cursor I have to use following step 1 fetch RateValidDates into @Id_RateCode, @TheValidDate .... get data
step 2 fetch prior from RateValidDates ... condition
step 3 fetch NEXT FROM RateValidDates into @Id_RateCode, @TheValidDate ... go to next record
Sql query analyzer output result of fetch NEXT FROM on the screen What seeting should i use to avoid output on screen (The reason My cursor takes about 8000 - 10000 rows when it's open)
I'm having trouble obtaining errors raised in a stored procedure via the ADO Errors collection after the second FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server database:
This is a very simple table with one column, and three rows containing the values 1, 2 and 3.
Consider this stored procedure: CREATE PROCEDURE TestStoredProc as BEGIN set rowcount 0 Set NoCount ON
declare @TestInt int declare @ErrMsg char(7) declare TestCursor cursor forward_only for select * from TestTable
open TestCursor Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1 Begin select @ErrMsg = 'Error ' + convert(char, @testint) raiserror(@ErrMsg, 16, 1) raiserror(@ErrMsg, 16, 1) Fetch next from TestCursor into @TestInt end
Close TestCursor DeAllocate TestCursor return END
This stored procedure simply defines a cursor on all rows in TestTable. For each row fetched from the cursor, the error message 'Error n' is raised twice, where n is the integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the above stored procedure. After the stored procedure is executed, the code loops through the errors collection, and creates a message box for each error in the collection:
Private Sub Form_Load() Dim cn As Connection Dim cm As Command Dim oErr As Error
Hello All, I need to find out which user are connected to a What database. For this I can Use SP_WHo. But How can i fetch the required fields only dat too in Query Analyzer
My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.
------T-SQL for fetching data from XML datatype------ WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my) SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet FROM MinaDekl CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1) -----------------
Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha
I keep getting the error "Could not fetch a row from OLE DB provider 'VFPOLEDB' when I do a query on a FoxPro Linked Server through SQL 2000. If the query returns 0 rows I don't get an error I just get the structure of the table. But if the query returns rows I get the error. I can replicate this on a test machine but my development machine works fine.
I have checked the SVRNETCN and the CLICONFG apps in SQL Server and they seem to be set the same on both machines. I am using NamedPipes on the server. I tried starting the DTS and that didn't fix the issue.
Would anyone know what I don't have working correctly?
I am getting problem in fetching data from SQL using C++ program. How to Fetch SQL Unicode characters? I am using ODBC driver calls in C++ to fetch SQL data. It is working fine with character data. But for Unicode data it displays €œ????€? instead of data.
I am using SQL Server SQLSRV32.DLL for creating datasource.
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.
Ok, here is the deal. In T-SQL, an error is handled depending on its severity. For example, a severity of 15 or less (or is it 10 or less? doesn't matter) will only raise a warning that can be caught through the message event. 16 or higher (or whatever) will cause an exception to be thrown on the .NET side, and 20 or higher causes the connection to be closed.So far so good.Now here's my issue: I have a stored procedure that does 2 queries (inside the same SP). Sometimes, the first query will succeed, while the second one will cause an error of severity 16. Again, in .NET that will throw an exception, making me unable to fetch the first resultset (which I require for logging purpose). If the error was, let say, severity 9, I could simply subscribe to the message event to get my error, yet still be able to get the first result set. But unfortunately life isn't perfect, and the error is indeed a severity 16.Anyone know of a way to be able to get the first result set, even if the second query (within the same SP) completly fails?
I am inserting value in the database with identity column, but I want to know the identity column value that is inserted by this command. I don't want to use any additional query to fetch this value, so I have created a stored Prosedure that Inserts value with identity column in the database and also returns last identity column value. But i don't know how to fetch this value in ASP.Net, because ExecuteNonQuery() is used for INSERT,UPDATE and DELETE only, bu using this method my half of need will be completed (that is insertion) but how i fetch returned value. I am putting table and Stored Prosedure, pls reply me how i get returned value. --following is my emp table create table emp ( EID int identity(1,1), EName varchar(50) ) --following is my stored prosedure
create proc ReturnID @ename varchar(50) as insert into emp values(@ename) begin tran declare @err int set @err = @@ERROR if @err <> 0 return select max(eid) from emp commit tran
--following command insert value in database and returns last ID ReturnID jasim
What is wrong with this stored procedure? This should work right?
Create PROCEDURE UpdRequestRecordFwd
@oldITIDint , @newITID int AS Declare @RRID int Declare @APID int Declare crReqRec cursor for select RRID from RequestRecords where ITID = @oldITID open crReqRec fetch next from crReqRec into @RRID while @@fetch_status = 0 Begin
Update RequestRecords set ITID = @newITID where RRID = @RRID
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
