Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: new stored procedure problem...

    using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
    here is what i am trying to do. but when i try to save it i get
    "Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
    Do i have to break this down into 2 SP's or can i do it some how with this one.
    Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.

    Code:
    CREATE    PROCEDURE isp_ap_load_checks
    @p_comp       	char(2),
    @p_vend        	char(6),
    @p_user         char(12),
    @p_date1        char(8),
    @p_date2        char(8)    
    as
    
    if (@p_user <= '')
    begin
       set @p_user = 'system'
    end
       
    if (@p_date1 <= '00000000') 
    begin
       set @p_date1 = '00000000'
    end
    
    if (@p_date2 <= @p_date1) 
    begin
       set @p_date2 = '99999999'
    end
    
    delete from XAPCHECKS
    where xapck_comp = @p_comp and xapck_vend = @p_vend and xapck_user = @p_user 
                                                         
    delete from XAPCHKDETAIL
    where xapcd_comp = @p_comp and xapcd_vend = @p_vend and xapcd_user = @p_user 
                                                         
    insert into XAPCHECKS
    select apph_comp, apph_vend, @p_user,
        max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')), apph_payck, chm_type, chm_stat, apt_bank,
    	apph_paymnts, apph_stat
             
    from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
    apt_type = apph_type and apt_id = apph_id
    LEFT JOIN APBANKF ON apb_code = apt_bank 
    left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
    where (apph_comp = @p_comp) and (apph_vend = @p_vend) and
        (apph_payck > 0 and 
        (str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
    group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id
    
    insert into XAPCHKDETAIL
    select apph_comp, apph_vend, @p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
         
    from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
    apt_type = apph_type and apt_id = apph_id
    LEFT JOIN APBANKF ON apb_code = apt_bank 
    left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
    where (apph_comp = @p_comp) and (apph_vend = @p_vend) and
        (apph_payck > 0 and 
        (str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
    group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id
    
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the DDL for XAPCHKDETAIL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2005
    Posts
    71

    sorry for my ignorance but not sure what ddl is so...

    here is the sql script for the table...
    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XAPCHKDETAIL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[XAPCHKDETAIL]
    GO
    
    CREATE TABLE [dbo].[XAPCHKDETAIL] (
    	[xapcd_comp] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapcd_vend] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapcd_user] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapcd_check] [int] NOT NULL ,
    	[xapcd_chk_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapck_check_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapcd_bank] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    	[xapcd_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    	[xapcd_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    	[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL 
    ) ON [PRIMARY]
    GO
    
     CREATE  UNIQUE  CLUSTERED  INDEX [IXAPCHKDETAIL0] ON [dbo].[XAPCHKDETAIL]([xapcd_comp], [xapcd_vend], [xapcd_user], [xapcd_check], [xapcd_chk_type], [xapck_check_status], [xapcd_bank]) ON [PRIMARY]
    GO

  4. #4
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    Visit http://support.microsoft.com/kb/878501 , an article from Microsoft to resolve the problem.

    Cheers,
    --Riaz

    Quote Originally Posted by hicpics
    using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
    here is what i am trying to do. but when i try to save it i get
    "Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
    Do i have to break this down into 2 SP's or can i do it some how with this one.
    Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Change this:
    Code:
    insert into XAPCHKDETAIL
    select apph_comp, apph_vend, @p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
    ...
    ...to this:
    Code:
    insert into XAPCHKDETAIL
    	([xapcd_comp],
    	[xapcd_vend],
    	[xapcd_user],
    	[xapcd_check],
    	[xapcd_chk_type],
    	[xapck_check_status],
    	[xapcd_bank],
    	[xapcd_type],
    	[xapcd_id])
    select apph_comp, apph_vend, @p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
    ...
    ALWAYS enumerate your column names.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2005
    Posts
    71

    Thank you for the info...

    Is there a way that you know of to only load up a distinct record into the XAPCHECKS table. The APPHISTF can have many ID's associated with a single check.
    apphistf:
    ID 1 check 1
    ID 2 check 1
    ID 3 check 1
    ID 4 check 2
    ID 5 check 3
    ID 6 check 3
    ...
    what i am trying to accomplish is to get a recap for a vend by check date, check number that doesnt have to tie back to the ID, that is where the XAPCHKDETAIL comes in.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Either SELECT DISTINCT, or use an aggregate query with MAX or MIN to select one of the IDs to insert.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •