asked on

Problem with SQL Script - Cannot call methods on varchar

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Shilo

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