Cursor will not insert - urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursor will not insert - urgent

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 15:16:23

Hi,

I had question on this one last week , but today for some reason, it wont work. Sorry about that.

I have a SP:

ALTER procedure [dbo].[TESTING_TESTING]
@Tablename varchar(100)
AS
set nocount on
declare @devicename varchar(50)
declare @daterange varchar(50)
Declare @SQL VarChar(7000)

declare auto1 cursor for select systName, date from deviceNameUSC, dates

SELECT @SQL = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName

open auto1
fetch NEXT from auto1 into @devicename, @daterange
while @@fetch_status = 0
begin
SET @SQL = @SQL + ' exec server.dbname..spGetInterfaceData ''' + @devicename + ''',''' + @daterange + ''''
exec (@SQL)
--SELECT (@SQL)
fetch next from auto1 into @devicename, @daterange
end
close auto1
deallocate auto1

This takes in table name as input.
Fetches device name and date from other tables.
The table dates has 1 column named date and 3 rows;
08/05/2009
08/06/2009
08/07/2009

Tbl deviceNameUSC:
tHIS TABLE has 1 column systName and 2 rows:
ABC1
ABC2

The purpose of the cursor is to loop through every device in the deviceNameUSC table and fetch data for every date in the dates table.
So, it should get data for ABC1 for the 3 days and ABC2 for the 3 days.

Now when i execute this SP, it only inserts data for ABC1 for 08/05/2009 only and display on result window data for 08/06/2009 and 08/07/2009 for both devices.

Please help.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 15:36:38

Any suggestion please.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 15:39:35

Looks like you continue to append @sql with each iteration. comment out the exec(@sql) and uncomment the SELECT @sql. I'm guessing the first iteration is fine but the subsequent ones are not.

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 15:45:15

this is the output:
INSERT INTO UtilizationData_PVZ.dbo.TESTING1 exec FTGBOSDASH2WIN.Network..spGetInterfaceData 'CDAL01','08/05/2009'

but it should loop through ABC1 and ABC2 and 05,06,07 days.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:06:21

any reponse..?

On Aug 31st, Rohit and webfred helped me with this issue, but i did not test with mutiple devices and dates.

Now the same query will not work.

Please help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 16:11:12

I don't see a problem other than the one I pointed out.
Try executing this in a new query window:


set nocount on
declare @deviceNameUSC table (systName varchar(10))
insert @deviceNameUSC
select 'ABC1' union all
select 'ABC2'

declare @dates table ([date] datetime)
insert @dates
select '08/05/2009' union all
select '08/06/2009' union all
select '08/07/2009'

declare @tableName varchar(200)
set @tablename = 'testing1'

declare @sql varchar(2000)
       ,@deviceName varchar(200)
       ,@daterange varchar(200)

declare auto1 cursor for 
select systName, date from @deviceNameUSC, @dates

SELECT @SQL = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName

open auto1
fetch NEXT from auto1 into @devicename, @daterange
while @@fetch_status = 0
begin
       --test parameters
       --select @deviceName [@deviceName], @daterange [@daterange]
       
       SET @SQL = @SQL + ' exec server.dbname..spGetInterfaceData ''' + @devicename + ''',''' + @daterange + ''''
       --exec (@SQL)
       SELECT (@SQL) [@sql]
       fetch next from auto1 into @devicename, @daterange
end
close auto1
deallocate auto1

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:16:55

I see the output as follows:

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 6 2009 12:00AM'

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 6 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 7 2009 12:00AM'

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 6 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 7 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 5 2009 12:00AM'

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 6 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 7 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 6 2009 12:00AM'

INSERT INTO UtilizationData_PVZ.dbo.testing1
exec server.dbname..spGetInterfaceData 'ABC1','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 6 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC1','Aug 7 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 5 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 6 2009 12:00AM'
exec server.dbname..spGetInterfaceData 'ABC2','Aug 7 2009 12:00AM'

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:19:43

I will have more than 40 devices and more than 7-8 dates...so how can i generalize this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 16:32:41

You see what's happening, right? You need to re-initialize your @sql variable for each iteration so you don't keep concatenating onto the previous exec statements.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 16:38:23

So inside the cursor loop change the set @sql statement to this:


       set @sql = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName
       + ' exec server.dbname..spGetInterfaceData ''' + @devicename + ''',''' + @daterange + ''''

and you can remove the one before the cursor.

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:39:37

Hi TG,

I did not,
open auto1
fetch NEXT from auto1 into @devicename, @daterange
begin
SELECT @SQL = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName + ' exec FTGBOSDASH2WIN.Network..spGetInterfaceData ''' + @devicename + ''',''' + @daterange + ''''
--exec (@SQL)
SELECT (@SQL)
fetch next from auto1 into @devicename, @daterange

but it wont loop through all device and for all dates.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 16:43:10

>>I did not,
You did not what?

Post the code that is not working. My sample code is looping so I'm not sure what problem you're having

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:44:13

it wont loop through all devices and all dates. it will only do for ABC1 and for 08/05/2009.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:45:19

typo mistake: 'i did this' in my previous message.

below is the code:

alter procedure [dbo].[TESTING_TESTING1]
@Tablename varchar(100)
AS
set nocount on
declare @devicename varchar(50)
declare @daterange varchar(50)
Declare @SQL VarChar(7000)

declare auto1 cursor for select systName, date from deviceNameUSC, dates

--SELECT @SQL = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName

open auto1
fetch NEXT from auto1 into @devicename, @daterange
begin
set @SQL = 'INSERT INTO UtilizationData_PVZ.dbo.'+ @TableName + ' exec FTGBOSDASH2WIN.Network..spGetInterfaceData ''' + @devicename + ''',''' + @daterange + ''''
exec (@SQL)
--SELECT (@SQL)
fetch next from auto1 into @devicename, @daterange

end
close auto1
deallocate auto1

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-08 : 16:53:29

so whats the issue here^^^??

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:54:44

It wont loop through.
I have 2 devices and 3 dates.
It will fetch and insert data only the first device and the first date.
It wont loop through the rest of the device and dates. No error though.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 16:54:44

You forgot the WHILE statement:


open auto1
fetch NEXT from auto1 into @devicename, @daterange
While @@fetch_status = 0
begin

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 16:56:47

I am very sorry. My bad. i forgot the while.
its working now.
i am very sorry abt that.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 17:02:09

You're welcome but...

quote:

Any suggestion please.

any reponse..?

so whats the issue here^^^??

It wont loop through.

Take a freakin' chill-pill, Padhma. Have some patience. This was only a few minutes between responses. You're not the only one here with a job, you know.

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 17:03:21

I understand. I need a break. I was in the verge of submitting a report when I came across this one.
I am very sorry about this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 17:12:42

apology accepted - I actually feel much better having used the word "chill-pill"