Here's the output:
// BEGIN OUTPUT
Command was executed successfully
Warnings: --->
W (1): Nokia E71x (AT&T (Cingular))
W (2): Blackberry 9630 Tour (US Cellular)
<---
[Executed: 1/22/10 11:00:23 AM EST] [Execution: 498ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 14ms]
[Executed: 1/22/10 11:01:00 AM EST] [Execution: 703ms]
>[Error] Script lines: 1-23 -------------------------
Incorrect syntax near the keyword 'end'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 19
[Executed: 1/22/10 11:02:08 AM EST] [Execution: 250ms]
>[Error] Script lines: 1-21 -------------------------
Incorrect syntax near the keyword 'fetch'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 18
[Executed: 1/22/10 11:02:41 AM EST] [Execution: 301ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 94ms]
[Executed: 1/22/10 11:02:54 AM EST] [Execution: 1s]
>[Error] Script lines: 1-21 -------------------------
Incorrect syntax near the keyword 'fetch'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 18
[Executed: 1/22/10 11:04:39 AM EST] [Execution: 199ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 17ms]
[Executed: 1/22/10 11:04:50 AM EST] [Execution: 696ms]
>[Error] Script lines: 1-20 -------------------------
Incorrect syntax near the keyword 'union'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 16
[Executed: 1/22/10 11:10:33 AM EST] [Execution: 181ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms]
1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 7ms]
[Executed: 1/22/10 11:10:41 AM EST] [Execution: 490ms]
>[Error] Script lines: 1-21 -------------------------
Incorrect syntax near the keyword 'fetch'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 18
[Executed: 1/22/10 12:31:31 PM EST] [Execution: 187ms]
// END OUTPUT
Just a hunch, but I think it works if there is only one (or none) sets
of returned data.
Thanks for the help!
Brian Humes
Director, Interactive
JohnsonRauhoff
269-428-9257 (direct)
269-428-3377 (main)
269-428-3312 (fax)
www.johnson-rauhoff.com
[email protected]
On Jan 22, 2010, at 12:29 PM, Roland Dumas wrote:
what was the error you got after the UNION?
On Jan 22, 2010, at 9:27 AM, Brian Humes wrote:
Hi all,
I have the following in a Direct DBMS action:
//begin SQL
declare @phone_id int
declare @manu varchar(50)
declare @phone_name varchar(50)
declare @carrier_id int
declare @carrier varchar(50)
declare @rownum_phones int
declare phonelist cursor for
SELECT DISTINCT
b1.phone_id,b2.manufacturer,b1.phone_name,b3.carrier_id,c1.carrier
FROM dbo.bc_phone b1,dbo.bc_manufacturer b2,dbo.bc_pmc2
b3,dbo.bc_marketxdevice b4, dbo.bc_carrier c1 WHERE
((b3.status1='approved1' OR b3.status1='failed1') AND
(b4.market_id=1) AND ((b1.manufacturer_id=b2.manufacturer_id) AND
(b3.phone_id=b1.phone_id) AND (b4.phone_id=b3.phone_id) AND
(b3.carrier_id = c1.carrier_id))
open phonelist
fetch next from phonelist into
@phone_id,@manu,@phone_name,@carrier_id,@carrier
set @rownum_phones = 0
while <@literal value="@@FETCH_STATUS"> = 0
begin
set @rownum_phones = @rownum_phones + 1
if (SELECT COUNT(b1.phone_id) FROM dbo.bc_pvc
b1,dbo.bc_carrier b2,dbo.bc_pmc2 b3 WHERE (b1.phone_...@phone_id
AND b1.carrier_...@carrier_id) AND ((b2.carrier_id=b1.carrier_id)
AND (b3.phone_id=b1.phone_id))) = 0
select @manu,@phone_name,@carrier
fetch next from phonelist into
@phone_id,@manu,@phone_name,@carrier_id,@carrier
end
close phonelist
deallocate phonelist
//end SQL
The SQL call returns a list (phonelist) and then loops through that
list to find if some conditions are met. If so (if zero records are
returned) then I simply "print" the variables using a select call.
So, Witango stores the first return returned value, but no more. I
need to get all of them.
I tried putting in a UNION and UNION ALL beneath the "select
@manu,@phone_name,@carrier", but I got errors (presumably because I
was putting the UNION after the last cycle?).
Any ideas on how I can get all of the results? By the way, I was
doing this using Witango (search, then loop, then test for zero,
then addrows to an array) but it was taking WAY too long.
MS SQL Server 2005.
Any help would be greatly, greatly appreciated.
Thanks!
Brian Humes
Director, Interactive
JohnsonRauhoff
269-428-9257 (direct)
269-428-3377 (main)
269-428-3312 (fax)
www.johnson-rauhoff.com
[email protected]
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
