Will A Cursor Fetch Lock The Table

Sep 8, 2003

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 ?

View 7 Replies


ADVERTISEMENT

Possible To Fetch Next From Cursor Into Table Variable?

Jan 28, 2008



Hello,

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






it does not work. any help would be great.

thnx

View 6 Replies View Related

SQL Server 2008 :: Write A Cursor To Fetch Required Data From Table?

Oct 21, 2015

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

579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00

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

[Code] .....

This query runs forever and doesn't stop.

View 6 Replies View Related

Fetch Cursor Help

Jun 22, 2004

Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be


RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

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

View 1 Replies View Related

Can't Fetch Record From Cursor

Nov 4, 2007

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;

View 3 Replies View Related

MDAC 2.8 And Cursor FETCH NEXT

Jan 15, 2004

it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned.

so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?

View 2 Replies View Related

Help With Cursor And Fetch Statement

Mar 17, 2008

Hello,

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.





Code SnippetDeclare @ItemID VarChar(30),
@QtyOnHand Decimal (16,8),
@WhseID VarChar (6),
@SumRcvd Int,
@TranID VarChar(30),
@TranDate DateTime,
@QtyRcvd Decimal (16,8),
@UnitCost Decimal (16,8),
@ItemKey Int,
@WhseKey Int,
@ShortDesc VarChar (40),
@StdCost Decimal (16,8)

DECLARE Temp_cursor CURSOR FOR
SELECT TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost
FROM #Temp1 tem

OPEN Temp_cursor

FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost


WHILE @@FETCH_STATUS = 0

BEGIN -- 0

Insert Into #Temp3
(TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost)

Values
(@TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost)

FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost

View 3 Replies View Related

Multiple While Fetch Cursor Code

Feb 28, 2006

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

View 3 Replies View Related

Transact SQL :: Cursor Fetch From Bottom To Top?

Sep 14, 2015

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)
    )

[code].....

View 4 Replies View Related

It Is Very Slow At Updating By Use Cursor (fetch Method)

Feb 5, 2001

Hi all,

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?

Thanks,

Kevin Zhang

View 1 Replies View Related

Fetch Results Of A Cursor To A Temporary DB For Manipulation

Apr 4, 2004

Hi all

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.


Please guide me on how this could be done...

Thanks in advance

Regards

Benny

View 1 Replies View Related

How To Return Multiple Results In 1 Fetch Cursor- Urgent Help!

Oct 7, 2004

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


GO

View 1 Replies View Related

Cursor-Fetch Problem:Oracle2SQL Server Migration

Dec 1, 2004

Dear all,

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.

Anxiously waiting for help!

View 11 Replies View Related

T-SQL (SS2K8) :: Fetch Next From Cursor Returns Multiple Rows?

Aug 9, 2014

I don't understand using a dynamic cursor.

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.

View 8 Replies View Related

Hi, Please Help Me W/ Fetch Cursor Querying, This Sends Email Automatically

Dec 18, 2007

create proc emailnew

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

View 2 Replies View Related

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?

Apr 7, 2008

Dear All

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.

Here is my query sample for yours to understand.



SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor


Why i use @qstring? It is because the query will be changed by different critiera.

Regards
Edmund

View 6 Replies View Related

SQL Server 2008 :: Create A Table Valued Function That Fetch Through The Table?

Apr 24, 2015

I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique

EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

View 9 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

How To Lock A Table So Others Cannot Lock It

May 23, 2001

Hi,

I want to lock a table so others cannot lock it but able to read it inside transactions.

The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction

I have tried the coding above, it won't prevent others from locking table1.

So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?

Thank you for any help

View 1 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

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?

View 1 Replies View Related

FETCH Index Details Of A Table

Jan 23, 2008

I Use the below QUERY to fetch the index details of a table from SQL Server 2000 database .

EXEC sp_helpindex 'mytable'

Is there any alternative for the above query .
Because the above query is not fetching some indexes in some version of SQL Server database .

I am trying the above query against SQL Server 2000,2005 and SQL Server 7 versions .

View 4 Replies View Related

Fetch Data From XML Column To Table

Aug 29, 2007

I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.

The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.

Table1
id int,
xmltag xml

I am starting to be desperate, I really need some help solving this one way or another.

View 2 Replies View Related

Fetch Data Based On SQL Server Table

Mar 26, 2003

Hi,

I am using DTS (Data Pump Task) to fetch selective data from AS/400. The Selection is based on a table which is on SQL Server.

I can not fetch the whole data as data is huge & only want for particular items. How can I achieve this taks

Thanks
Shafiq
:confused:

View 6 Replies View Related

T-SQL (SS2K8) :: Table With 4 Columns - How To Fetch Count

Apr 30, 2014

I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like
'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.

I used execute @queryfromvariable but it does not gives me count..

View 7 Replies View Related

In Master And Detail Table Fetch Record.

Jun 24, 2008

Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements

In Master table have 5 columns; In Master Table stored all records.

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20



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

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y


Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha

View 3 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

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?

View 1 Replies View Related

Analysis :: MDX To Fetch Measure Source Table And Column ID

Jun 12, 2015

MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.

View 4 Replies View Related

Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts

Feb 2, 2007

I simply made my script task (or any other task) fail

In my package error handler i have a Exec SQL task - for Stored Proc

SP statement is set in following expression (works fine in design time):

"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"

From progress:

Error: The Script returned a failure result.
Task SCR REIL Data failed

OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

And how did I get 4 errors? - I only set my script task result to failure

View 11 Replies View Related

Lock SQL Table Using Asp.net

Jul 23, 2004

Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!

View 5 Replies View Related

How To Lock A Table Or Row In Sql?

Nov 1, 2005

hi, i have an application that updates some records in sql tables, and i want to do a  web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table  so i dont have concurrency problems.tnx in advance.

View 2 Replies View Related

Table Lock...

Nov 5, 2007

Hello Friends,
I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...




Thanks in advance........
Rahul Jha

View 14 Replies View Related

Table Lock

Jun 5, 2008

Hi Gurus,

How can we know that particular table is locked.



Thanks,
ServerTeam

View 1 Replies View Related

How To Lock A Table

Mar 21, 2006

Hai to all,

I want to lock a patricular table during my transaction is this possible, for Example

Create sp() as
BEGIN
Lock Table
Do Some work
Relaese the Lock
END

like this.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved