asked on why the output not shown
I have a MS SQL 2008 script to try to print out an output from a table for each row. But it gave me * output. I compile the code, I don't see an error. can someone give me advice how to fix it?
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Saurabh Bhadauria
Is this the full code attached?Whats the output your'e getting.Did you say you just getting '*'
as output?
as output?
ASKER
yes, this is the full code, the output I got is
*
*
*
*
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Do you mean I should put this way instead?
FETCH NEXT FROM devices_my INTO @device_id;
while @COUNTER <= @device_count
begin
IF @@FETCH_STATUS = 0
PRINT 'Device ID: ' + @device_id + ' Counter: ' + convert(varchar(10),@count
SET @COUNTER = @COUNTER + 1
end
FETCH NEXT FROM devices_my INTO @device_id;
while @COUNTER <= @device_count
begin
IF @@FETCH_STATUS = 0
PRINT 'Device ID: ' + @device_id + ' Counter: ' + convert(varchar(10),@count
SET @COUNTER = @COUNTER + 1
end
Is there any reason you are writing the CURSOR that way and why are you doing the following?
SET IDENTITY_INSERT SCHED_ITEMS ON
,,,
SET IDENTITY_INSERT SCHED_ITEMS OFF;
SET IDENTITY_INSERT SCHED_ITEMS ON
,,,
SET IDENTITY_INSERT SCHED_ITEMS OFF;
Yes please .See below.
Also check to see if @device_id has NULL VALUE or any special character in it.
FETCH NEXT FROM devices_my INTO @device_id;
while @COUNTER <= @device_count
begin
IF @@FETCH_STATUS = 0
PRINT 'Device ID: ' + @device_id + ' Counter: ' + convert(varchar(10),@count
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM devices_my INTO @device_id;
end
Also check to see if @device_id has NULL VALUE or any special character in it.
FETCH NEXT FROM devices_my INTO @device_id;
while @COUNTER <= @device_count
begin
IF @@FETCH_STATUS = 0
PRINT 'Device ID: ' + @device_id + ' Counter: ' + convert(varchar(10),@count
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM devices_my INTO @device_id;
end
ASKER
print is just test statement. I have "Insert into..." statement instead.
Change your statement like this....
DECLARE devices_my CURSOR LOCAL for
select cast(device_id as varchar(200)) from devices where DEVICE_PIN between 5009166661 and 5009166662 order by device_id;
I think there is some issue with implicit conversion...
DECLARE devices_my CURSOR LOCAL for
select cast(device_id as varchar(200)) from devices where DEVICE_PIN between 5009166661 and 5009166662 order by device_id;
I think there is some issue with implicit conversion...
DECLARE @device_id varchar(6);
DECLARE @counter decimal(10,0);
DECLARE @device_count INT;
DECLARE @lgroup_id varchar(20);
SET @counter = 1;
SET @device_count = 2;
SET @lgroup_id=6;
DECLARE devices_my CURSOR LOCAL for
select device_id from devices where DEVICE_PIN between 5009166661 and 5009166662 order by device_id;
open devices_my;
SET IDENTITY_INSERT SCHED_ITEMS ON
while @COUNTER <= @device_count
begin
FETCH NEXT FROM devices_my INTO @device_id;
IF @@FETCH_STATUS = 0
PRINT 'Device ID: ' + @device_id + ' Counter: ' + convert(varchar(10),@count
SET @COUNTER = @COUNTER + 1
end
CLOSE devices_my;
DEALLOCATE devices_my;
SET IDENTITY_INSERT SCHED_ITEMS OFF;