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
ADVERTISEMENT
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
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
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
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
Sep 7, 2007
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
Any help is greatly appreciated
View 5 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
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
Mar 21, 2008
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
View 4 Replies
View Related
Aug 27, 2001
Any idea what this error is??
Thanks.
Attempt to fetch logical page (3:8360) in database 'XYZ' belongs to object '1934538212', not to object 'product'. [SQLSTATE HY000] (Error 605). The step failed.
View 1 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Nov 15, 2013
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>
Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);
View 2 Replies
View Related
Apr 28, 2004
Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @table_name = 'tablename', @column_name='columnname'
--I want the type_name value
View 3 Replies
View Related
Apr 25, 2002
Can I do something like
CREATE PROCEDURE ProcA
AS
DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT
END
GO
I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.
AB
View 1 Replies
View Related
Aug 7, 2007
Need a little help here.
I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.
I need the results as one table.
Here is my code.
___________________________________
SET NOCOUNT ON
DECLARE @IdsString VARCHAR(255), @Id int
SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
OPEN GetData
FETCH NEXT FROM GetData
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id
FETCH NEXT FROM GetData
INTO @Id
END
CLOSE GetData
DEALLOCATE GetData
_____________________________________
Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?
Any help would be much appreciated.
NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:
Regards
Shaun McGuile
View 14 Replies
View Related
Feb 8, 2001
Just upgraded from 6.5 to 7 and in query analyzer I get double spaced results on queries that previously single-spaced. Installed 2000 QA and still get same results. Is there a setting that we are missing or a workaround to this?
Note ==> The cursor query returns single spaced results when ran alone.
Test code below:
DECLARE @pol char(10)
DECLARE testcursor CURSOR FOR
SELECT policy_no FROM policy_history
WHERE policy_chg_end_dt is null and policy_deleted_sw = 0
OPEN testcursor
FETCH NEXT FROM testcursor INTO @pol
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @pol
FETCH NEXT FROM testcursor INTO @pol
END
CLOSE testcursor
DEALLOCATE testcursor
View 1 Replies
View Related
Jun 8, 2006
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony Murunion
View 3 Replies
View Related
Oct 28, 2015
Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.
Details: servername, databasename, datafile usage, logfile usage, free % age...etc.
View 3 Replies
View Related
Oct 8, 2001
I get the following errors while running a SQL procedure.
[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The cursor is already open
[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'qst_Ruler_AllExpenses' already exists
I am creating, opening and deallocating the cursor.
Advice please.
View 1 Replies
View Related
Dec 13, 2007
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.
I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.
The code that is not working is listed below. It does not work. It returns Null. I suck.
DECLARE @column_names varchar(8000)
SET @column_names = ''
SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'
SELECT @column_names
little help?
View 6 Replies
View Related
Dec 14, 2000
Hi,
I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks
CREATE PROCEDURE [MYSP] AS
Declare @pdesc nvarchar(30)
Declare @ssc int
Declare @myid int
Declare @name nvarchar(30)
Declare new_cur CURSOR DYNAMIC FOR
SELECT ProductDescription, SubSubCatID
FROM C2000HPB
FOR UPDATE
Open new_cur
FETCH FROM new_cur INTO @pdesc, @ssc
While @@FETCH_STATUS = 0
BEGIN
Declare new_cur2 CURSOR DYNAMIC FOR
SELECT SubSubCatID, SubSubCategory FROM SSC
FOR READ ONLY
Open new_cur2
FETCH FROM new_cur2 INTO @myid, @name
While @@FETCH_STATUS = 0
BEGIN
IF PATINDEX ('@name%',@pdesc) = 0
Set @ssc = @myid
UPDATE C2000HPB
SET SubSubCatID = @ssc
FETCH NEXT FROM new_cur2 INTO @myid, @name
END
Close new_cur2
DEALLOCATE new_Cur2
FETCH NEXT FROM new_cur INTO @pdesc,@ssc
END
Close new_cur
DEALLOCATE new_Cur
View 1 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Mar 13, 2015
I am trying to get this query to the point where it will return only results that meet specific conditions. Here is the current query I have:
use mdb
SELECT call_req.ref_num, prob_ctg.sym, act_type.sym, act_log.description
FROM call_req
INNER JOIN prob_ctg
ON call_req.category = prob_ctg.persid
INNER join act_log
ON call_req.persid = act_log.call_req_id
INNER JOIN act_type
ON act_log.type = act_type.code
where prob_ctg.sym like 'rd1.%' and prob_ctg.auto_assign=1 and call_req.active_flag=0 ORDER BY call_req.ref_num, act_log.time_stamp DESC
This produces results as follows:
I3835493FY15 RD1.Desktop Transfer Transfer Asignee from " to Jordana, Jose"
I3835493FY15 RD1.Desktop Close Status changed from 'Resolved' to 'Closed'
I3835493FY15 RD1.Desktop Close Status changed from 'Open' to 'Resolved'
I3835493FY15 RD1.Desktop Event Occurred AHD05447 No eligible Request Locations
I3835493FY15 RD1.Desktop Initial Create new request/incident/problem
[Code] .....
This query gives me the total number of records that meet my overall criteria, but what I need to have is a count of the total distinct number of call_req.ref_num and then I need a count of the number of records where act_type.code = TR and the text of act_log.description contains text including "Transfer Group From" and then I need a count of the records where act_type.code = EVT and the text of act_log.description contains text including "AHD054".
View 6 Replies
View Related
Nov 20, 2006
USE AdventureWorksGO-- Retrieve order manifestSELECT SalesOrder.SalesOrderID,SalesOrder.OrderDate,SalesOrder.AccountNumber,SalesOrder.Comment,Item.ProductID,Item.OrderQtyFROM Sales.SalesOrderHeader SalesOrderJOIN Sales.SalesOrderDetail ItemON SalesOrder.SalesOrderID = Item.SalesOrderIDWHERE SalesOrder.SalesOrderID = 436591. Examine the query in the file, noting that it retrieves data from theSales.SalesOrderHeader and Sales.SalesOrderDetail tables in theAdventureWorks database.2. ON the toolbar, click Execute.3. Review the query results.?Modify the query to retrieve <rowelements1. Make the necessary changes to the query to return the data in thefollowing XML format (hint: use FOR XML).Thanks In Advance,~CK
View 3 Replies
View Related