Problem with SQL Script - Cannot call methods on varchar

mgmhicks
mgmhicks used Ask the Experts™
on
I have the following script and keep receiving the error,"Cannot call methods on varchar".  Could someone tell me whats wrong witht he script.  It points to line 26

declare @cursor cursor
declare @mUserID char(25) = 'shicks'
declare @Propertyid char(2)
declare @BldgID char(30)
declare @UnitID char(30)
declare @ResiID char(10)
 
set @cursor = CURSOR FOR SELECT distinct PropertyId,BldgId,UnitId,ResiId
FROM         TransactionHeader
WHERE   (TransType = '3') AND (GLPeriod = '201204') AND (ACHFlag = 'N') AND (ImportedFrom IS NULL) AND (SourceCode <> 'AO') and PropertyId  IN (select code from locationList where LocationID = 8)
ORDER BY PropertyId,BldgId,UnitId,ResiId

open @cursor;

FETCH NEXT FROM @cursor INTO @propertyid,@bldgid,@unitid,@resiid

WHILE @@FETCH_STATUS = 0
 BEGIN
 declare @cursor2 cursor
 declare @ResiFirstName char(50)
 declare @ResiLastName char(50)
 declare @ResiStatus char(1)
 declare @Phone1 char(25)
 declare @Email char (25)
 
      set @cursor2 = CURSOR FOR SELECT a.ResiFirstName,a.ResiLastName,a.ResiStatus.b.phone1No,b.email from lease a
      inner join Addressbook b on a.PrimaryAddrID = b.addrID
      
      
      
      where (a.propertyid=@propertyid and a.bldgid=@bldgid and a.unitid=@unitid and a.resiid=@resiid) and (a.ResiStatus = 'c' or a.ResiStatus='n')
      
      open @cursor2
      fetch next from @cursor2 into @resiFirstName,@resiLastName,@resistatus,@Phone1,@Email
      while @@FETCH_STATUS= 0
      begin
            insert into dbo.mgm_TempTable1 Values(@mUserID,@Propertyid,@BldgID,@UnitID,@ResiID,@resiFirstName,@resilastname,@resiStatus,@Phone1,@email)
            fetch next from @cursor2 into @resiFirstName,@resiLastName,@resistatus,@Phone1,@Email
      end
      deallocate @cursor2
 FETCH NEXT FROM @Cursor INTO @propertyid,@bldgid,@unitid,@resiid
 END
 DEALLOCATE @Cursor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you have a syntax error there

...a.ResiStatus.b.phone1No...

replace the "." with a comma.

set @cursor2 = CURSOR FOR SELECT a.ResiFirstName,a.ResiLastName,a.ResiStatus,b.phone1No,b.email from lease a
      inner join Addressbook b on a.PrimaryAddrID = b.addrID
Aaron ShiloChief Database Architect

Commented:
your syntax is strange try this

declare @cursor cursor
declare @mUserID char(25) = 'shicks'
declare @Propertyid char(2)
declare @BldgID char(30)
declare @UnitID char(30)
declare @ResiID char(10)
 
DECLARE Cur_1  CURSOR FOR 
SELECT distinct PropertyId,BldgId,UnitId,ResiId
FROM         TransactionHeader
WHERE   (TransType = '3') AND (GLPeriod = '201204') AND (ACHFlag = 'N') AND (ImportedFrom IS NULL) AND (SourceCode <> 'AO') and PropertyId  IN (select code from locationList where LocationID = 8)
ORDER BY PropertyId,BldgId,UnitId,ResiId

open Cur_1  ;

FETCH NEXT FROM Cur_1  INTO @propertyid,@bldgid,@unitid,@resiid

WHILE @@FETCH_STATUS = 0
 BEGIN
 declare @cursor2 cursor
 declare @ResiFirstName char(50)
 declare @ResiLastName char(50)
 declare @ResiStatus char(1)
 declare @Phone1 char(25)
 declare @Email char (25)
  
DECLARE Cur_2 CURSOR FOR
 SELECT a.ResiFirstName,a.ResiLastName,a.ResiStatus.b.phone1No,b.email from lease a 
      inner join Addressbook b on a.PrimaryAddrID = b.addrID
            where (a.propertyid=@propertyid and a.bldgid=@bldgid and a.unitid=@unitid and a.resiid=@resiid) and (a.ResiStatus = 'c' or a.ResiStatus='n')
      
      open Cur_2 ;
      fetch next from Cur_2 into @resiFirstName,@resiLastName,@resistatus,@Phone1,@Email
      while @@FETCH_STATUS= 0
      begin
            insert into dbo.mgm_TempTable1 Values(@mUserID,@Propertyid,@BldgID,@UnitID,@ResiID,@resiFirstName,@resilastname,@resiStatus,@Phone1,@email)
            fetch next from @cursor2 into @resiFirstName,@resiLastName,@resistatus,@Phone1,@Email
      end
  close Cur_2 
      deallocate Cur_2 
 FETCH NEXT FROM Cur_1 INTO @propertyid,@bldgid,@unitid,@resiid
 END
close Cur_1
 DEALLOCATE Cur_1

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial