Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Error when searching f...
Finance | Project Operations, Human Resources, ...
Answered

Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

(1) ShareShare
ReportReport
Posted on by WCJMON 55

Hello all 

We recently upgraded GP 2016 with US 2020 Year End update. Now we seem to be getting the following error in the Search Vendor screen when adding a column definition: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition. I enabled the DEXSQL.log and reproduced the error, and it looks to be creating an temp table with several temp SPROCS, ultimately ending up in that error. I'm not familiar enough with the GP backend to find where the mismatch is. 

5265.SQL-Error.pngSearch-Vendors-Screen.png

Categories:
Microsoft Dynamics GP
  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    We actually only fixed the DYN_FUNC_1099_Type not updated for Nonemployee Compensation causing some SmartLIsts to report blank for 1099 Tax Types in 2020 Year End update.

    The issue with the procedure, we didn't fix as it was only happening when upgrading from older builds of Dynamics GP 2016 and we weren't able to re-create it. As it wasn't impacting all customers, we only have the work-around at this point.

    Thanks

  • Richard Prior Profile Picture
    Richard Prior 190 on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    Hi Derek and co

    I have updated a client from 16.00.0620 to 16.00.885 and the client discovered this same issue.  The stored procedure ASI_SP_VENDOR_LOOKUP was missing the VENDDB reference at this build and I used the details you provided Derek to alter it - now working.

    It might appear this has not been fixed in the February 2021 release.

    Rich

  • Sheilajr Profile Picture
    Sheilajr 745 on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    Thank you Derek,

    I just had a client who was upgraded in Dec to the 16.00.0864 version and told me about this issue.  It makes total sense that this new field would have caused the issue.  Thank you so very much for posting the script to fix this.  I'll put this solution in my toolbox since I did many upgrades in Dec/Jan that will more than likely have the same issue - eventually.  :-)

  • TechyWolves Profile Picture
    TechyWolves 35 on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    Thank you! We had the same error after the 2020 GP 2016 R2 YE Update. This script worked perfectly!

  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    I'm happy to hear that......hopefully this saves everyone some time when encountering this issue.

    Have a great day.

  • Suggested answer
    WCJMON Profile Picture
    WCJMON 55 on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    That script worked. Search Vendor functionality is working as expected now. Thank you for your help with this.

  • Verified answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    Here is an updated script, we found a couple issues with the previous one, use this one:

    /****** Object:  StoredProcedure [dbo].[ASI_SP_VENDOR_LOOKUP]    Script Date: 12/15/2020 5:32:38 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[ASI_SP_VENDOR_LOOKUP]  @temptable varchar(20),  @table varchar(20),  @db varchar(20),  @from1 varchar (254),  @from2 varchar (254),  @from3 varchar (254),  @from4 varchar (254),  @from5 varchar (254),  @from6 varchar (254),  @from7 varchar (254),  @from8 varchar (254),  @from9 varchar (254),  @where1 varchar (254),  @where2 varchar (254),  @where3 varchar (254),  @where4 varchar (254),  @where5 varchar (254),  @where6 varchar (254),  @where7 varchar (254),  @where8 varchar (254),  @where9 varchar (254) AS  exec('delete ' + @temptable +  ' insert ' + @temptable + ' SELECT ' +  @db + 'PM00200.VENDORID,' +  @db + 'PM00200.VENDNAME,' +  @db + 'PM00200.VNDCHKNM,' +  @db + 'PM00200.VENDSHNM,' +  @db + 'PM00200.VADDCDPR,' +  @db + 'PM00200.VADCDPAD,' +  @db + 'PM00200.VADCDSFR,' +  @db + 'PM00200.VADCDTRO,' +  @db + 'PM00200.VNDCLSID,' +  @db + 'PM00200.VNDCNTCT,' +  @db + 'PM00200.ADDRESS1,' +  @db + 'PM00200.ADDRESS2,' +  @db + 'PM00200.ADDRESS3,' +  @db + 'PM00200.CITY,' +  @db + 'PM00200.STATE,' +  @db + 'PM00200.ZIPCODE,' +  @db + 'PM00200.COUNTRY,' +  @db + 'PM00200.PHNUMBR1,' +  @db + 'PM00200.PHNUMBR2,' +  @db + 'PM00200.PHONE3,' +  @db + 'PM00200.FAXNUMBR,' +  @db + 'PM00200.UPSZONE,' +  @db + 'PM00200.SHIPMTHD,' +  @db + 'PM00200.TAXSCHID,' +  @db + 'PM00200.ACNMVNDR,' +  @db + 'PM00200.TXIDNMBR,' +  @db + 'PM00200.VENDSTTS,' +  @db + 'PM00200.CURNCYID,' +  @db + 'PM00200.TXRGNNUM,' +  @db + 'PM00200.PARVENID,' +  @db + 'PM00200.TRDDISCT,' +  @db + 'PM00200.TEN99TYPE,' +  @db + 'PM00200.MINORDER,' +  @db + 'PM00200.PYMTRMID,' +  @db + 'PM00200.MINPYTYP,' +  @db + 'PM00200.MINPYPCT,' +  @db + 'PM00200.MINPYDLR,' +  @db + 'PM00200.MXIAFVND,' +  @db + 'PM00200.MAXINDLR,' +  @db + 'PM00200.COMMENT1,' +  @db + 'PM00200.COMMENT2,' +  @db + 'PM00200.USERDEF1,' +  @db + 'PM00200.USERDEF2,' +  @db + 'PM00200.CRLMTDLR,' +  @db + 'PM00200.PYMNTPRI,' +  @db + 'PM00200.KPCALHST,' +  @db + 'PM00200.KGLDSTHS,' +  @db + 'PM00200.KPERHIST,' +  @db + 'PM00200.KPTRXHST,' +  @db + 'PM00200.HOLD,' +  @db + 'PM00200.PTCSHACF,' +  @db + 'PM00200.CREDTLMT,' +  @db + 'PM00200.WRITEOFF,' +  @db + 'PM00200.MXWOFAMT,' +  @db + 'PM00200.SBPPSDED,' +  @db + 'PM00200.PPSTAXRT,' +  @db + 'PM00200.DXVARNUM,' +  @db + 'PM00200.CRTCOMDT,' +  @db + 'PM00200.CRTEXPDT,' +  @db + 'PM00200.RTOBUTKN,' +  @db + 'PM00200.XPDTOBLG,' +  @db + 'PM00200.PRSPAYEE,' +  @db + 'PM00200.PMAPINDX,' +  @db + 'PM00200.PMCSHIDX,' +  @db + 'PM00200.PMDAVIDX,' +  @db + 'PM00200.PMDTKIDX,' +  @db + 'PM00200.PMFINIDX,' +  @db + 'PM00200.PMMSCHIX,' +  @db + 'PM00200.PMFRTIDX,' +  @db + 'PM00200.PMTAXIDX,' +  @db + 'PM00200.PMWRTIDX,' +  @db + 'PM00200.PMPRCHIX,' +  @db + 'PM00200.PMRTNGIX,' +  @db + 'PM00200.PMTDSCIX,' +  @db + 'PM00200.ACPURIDX,' +  @db + 'PM00200.PURPVIDX,' +  @db + 'PM00200.NOTEINDX,' +  @db + 'PM00200.CHEKBKID,' +  @db + 'PM00200.MODIFDT,' +  @db + 'PM00200.CREATDDT,' +  @db + 'PM00200.RATETPID,' +  @db + 'PM00200.Revalue_Vendor,' +  @db + 'PM00200.Post_Results_To,' +  @db + 'PM00200.FREEONBOARD,' +  @db + 'PM00200.GOVCRPID,' +  @db + 'PM00200.GOVINDID,' +  @db + 'PM00200.DISGRPER,' +  @db + 'PM00200.DUEGRPER,' +  @db + 'PM00200.DOCFMTID,' +  @db + 'PM00200.TaxInvRecvd,' + @db + 'PM00200.VENDDBA' +  @from1 + @from2 + @from3 + @from4 + @from5 + @from6 + @from7 + @from8 + @from9 + @where1 + @where2 + @where3 + @where4 + @where5 + @where6 + @where7 + @where8 + @where9  )    

    GO

    Thanks

  • WCJMON Profile Picture
    WCJMON 55 on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    That's it. Looking at that SPROC, I don't see any reference to that column. I'll need a maintenance window to correct it, but that definitely would cause the error we're seeing. I'll let you know, once I run the update script.

  • Verified answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    So, update.........it turns out this is an issue after upgrading to 16.00.0864, the 2020 YE update for GP 2016, the ASI_SP_VENDOR_LOOKUP procedure in the GP system database, is missing the VENDDBA reference to the PM00200 table.

    You can use the following script to alter this procedure, after making a backup of the DYNAMICS/system database:

    /****** Object:  StoredProcedure [dbo].[ASI_SP_VENDOR_LOOKUP]    Script Date: 12/15/2020 9:44:39 AM ******/

    SET

     ANSI_NULLS ON GO

    SET

     QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[ASI_SP_VENDOR_LOOKUP] @temptable varchar(20),

     @table varchar(20),

     @db varchar(20),

     @from1 varchar (254),

     @from2 varchar (254),

     @from3 varchar (254),

     @from4 varchar (254),

     @from5 varchar (254),

     @from6 varchar (254),

     @from7 varchar (254),

     @from8 varchar (254),

     @from9 varchar (254),

     @where1 varchar (254),

     @where2 varchar (254),

     @where3 varchar (254),

     @where4 varchar (254),

     @where5 varchar (254),

     @where6 varchar (254),

     @where7 varchar (254),

     @where8 varchar (254),

     @where9 varchar (254) AS exec(

       'delete ' + @temptable + ' insert ' + @temptable + ' SELECT ' + @db + 'PM00200.VENDORID,' + @db + 'PM00200.VENDNAME,' + @db + 'PM00200.VNDCHKNM,' +

    @db + 'PM00200.VENDSHNM,' + @db + 'PM00200.VADDCDPR,' + @db + 'PM00200.VADCDPAD,' + @db + 'PM00200.VADCDSFR,' + @db + 'PM00200.VADCDTRO,' + @db +

    'PM00200.VNDCLSID,' + @db + 'PM00200.VNDCNTCT,' + @db + 'PM00200.ADDRESS1,' + @db + 'PM00200.ADDRESS2,' + @db + 'PM00200.ADDRESS3,' + @db + 'PM00200.CITY,'

    + @db + 'PM00200.STATE,' + @db + 'PM00200.ZIPCODE,' + @db + 'PM00200.COUNTRY,' + @db + 'PM00200.PHNUMBR1,' + @db + 'PM00200.PHNUMBR2,' + @db + 'PM00200.PHONE3,'

    + @db + 'PM00200.FAXNUMBR,' + @db + 'PM00200.UPSZONE,' + @db + 'PM00200.SHIPMTHD,' + @db + 'PM00200.TAXSCHID,' + @db + 'PM00200.ACNMVNDR,' + @db + 'PM00200.TXIDNMBR,'

    + @db + 'PM00200.VENDSTTS,' + @db + 'PM00200.CURNCYID,' + @db + 'PM00200.TXRGNNUM,' + @db + 'PM00200.PARVENID,' + @db + 'PM00200.TRDDISCT,' + @db + 'PM00200.TEN99TYPE,'

    + @db + 'PM00200.MINORDER,' + @db + 'PM00200.PYMTRMID,' + @db + 'PM00200.MINPYTYP,' + @db + 'PM00200.MINPYPCT,' + @db + 'PM00200.MINPYDLR,' + @db + 'PM00200.MXIAFVND,'

    + @db + 'PM00200.MAXINDLR,' + @db + 'PM00200.COMMENT1,' + @db + 'PM00200.COMMENT2,' + @db + 'PM00200.USERDEF1,' + @db + 'PM00200.USERDEF2,' + @db + 'PM00200.CRLMTDLR,'

    + @db + 'PM00200.PYMNTPRI,' + @db + 'PM00200.KPCALHST,' + @db + 'PM00200.KGLDSTHS,' + @db + 'PM00200.KPERHIST,' + @db + 'PM00200.KPTRXHST,' + @db + 'PM00200.HOLD,'

    + @db + 'PM00200.PTCSHACF,' + @db + 'PM00200.CREDTLMT,' + @db + 'PM00200.WRITEOFF,' + @db + 'PM00200.MXWOFAMT,' + @db + 'PM00200.SBPPSDED,' + @db + 'PM00200.PPSTAXRT,'

    + @db + 'PM00200.DXVARNUM,' + @db + 'PM00200.CRTCOMDT,' + @db + 'PM00200.CRTEXPDT,' + @db + 'PM00200.RTOBUTKN,' + @db + 'PM00200.XPDTOBLG,' + @db + 'PM00200.PRSPAYEE,'

    + @db + 'PM00200.PMAPINDX,' + @db + 'PM00200.PMCSHIDX,' + @db + 'PM00200.PMDAVIDX,' + @db + 'PM00200.PMDTKIDX,' + @db + 'PM00200.PMFINIDX,' + @db + 'PM00200.PMMSCHIX,'

    + @db + 'PM00200.PMFRTIDX,' + @db + 'PM00200.PMTAXIDX,' + @db + 'PM00200.PMWRTIDX,' + @db + 'PM00200.PMPRCHIX,' + @db + 'PM00200.PMRTNGIX,' + @db + 'PM00200.PMTDSCIX,'

    + @db + 'PM00200.ACPURIDX,' + @db + 'PM00200.PURPVIDX,' + @db + 'PM00200.NOTEINDX,' + @db + 'PM00200.CHEKBKID,' + @db + 'PM00200.MODIFDT,' + @db + 'PM00200.CREATDDT,'

    + @db + 'PM00200.RATETPID,' + @db + 'PM00200.Revalue_Vendor,' + @db + 'PM00200.Post_Results_To,' + @db + 'PM00200.FREEONBOARD,' + @db + 'PM00200.GOVCRPID,'

    + @db + 'PM00200.GOVINDID,' + @db + 'PM00200.DISGRPER,' + @db + 'PM00200.DUEGRPER,' + @db + 'PM00200.DOCFMTID,' + @db + 'PM00200.TaxInvRecvd,' + @db + 'PM00200.VENDDBA'

    + @from1 + @from2 + @from3 + @from4 + @from5 + @from6 + @from7 + @from8 + @from9 + @where1 + @where2 + @where3 + @where4 + @where5 + @where6 + @where7 + @where8 + @where9

     ) GO

    Once you do this, make another backup, then launch GP 2016 and see if this doesn't resolve this error for you on looking up vendor IDs.

    You can let us know either way, as others will run into this as well.

    Thanks

  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Error when searching for Vendors: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition

    Which it should, and if you run a 'sp_helptext ASI_SP_VENDOR_LOOKUP' script, it should show this proc like this, where it does mention the new column: PM00200.VENDDBA.

    This stored procedure can be re-created using the Database Maintenance Utility, to rule that out as well.

    CREATE PROCEDURE ASI_SP_VENDOR_LOOKUP  @temptable varchar(20),  @table varchar(20),  @db varchar(20),  @from1 varchar (254),  @from2 varchar (254),  @from3 varchar (254),  @from4 varchar (254),  @from5 varchar (254),  @from6 varchar (254),  @from7 varcha

    r (254),  @from8 varchar (254),  @from9 varchar (254),  @where1 varchar (254),  @where2 varchar (254),  @where3 varchar (254),  @where4 varchar (254),  @where5 varchar (254),  @where6 varchar (254),  @where7 varchar (254),  @where8 varchar (254),  @where9

    varchar (254) AS  exec('delete ' + @temptable +  ' insert ' + @temptable + ' SELECT ' +  @db + 'PM00200.VENDORID,' +  @db + 'PM00200.VENDNAME,' +  @db + 'PM00200.VNDCHKNM,' +  @db + 'PM00200.VENDSHNM,' +  @db + 'PM00200.VADDCDPR,' +  @db + 'PM00200.VADCD

    PAD,' +  @db + 'PM00200.VADCDSFR,' +  @db + 'PM00200.VADCDTRO,' +  @db + 'PM00200.VNDCLSID,' +  @db + 'PM00200.VNDCNTCT,' +  @db + 'PM00200.ADDRESS1,' +  @db + 'PM00200.ADDRESS2,' +  @db + 'PM00200.ADDRESS3,' +  @db + 'PM00200.CITY,' +  @db + 'PM00200.STA

    TE,' +  @db + 'PM00200.ZIPCODE,' +  @db + 'PM00200.COUNTRY,' +  @db + 'PM00200.PHNUMBR1,' +  @db + 'PM00200.PHNUMBR2,' +  @db + 'PM00200.PHONE3,' +  @db + 'PM00200.FAXNUMBR,' +  @db + 'PM00200.UPSZONE,' +  @db + 'PM00200.SHIPMTHD,' +  @db + 'PM00200.TAXSC

    HID,' +  @db + 'PM00200.ACNMVNDR,' +  @db + 'PM00200.TXIDNMBR,' +  @db + 'PM00200.VENDSTTS,' +  @db + 'PM00200.CURNCYID,' +  @db + 'PM00200.TXRGNNUM,' +  @db + 'PM00200.PARVENID,' +  @db + 'PM00200.TRDDISCT,' +  @db + 'PM00200.TEN99TYPE,' +  @db + 'PM0020

    0.MINORDER,' +  @db + 'PM00200.PYMTRMID,' +  @db + 'PM00200.MINPYTYP,' +  @db + 'PM00200.MINPYPCT,' +  @db + 'PM00200.MINPYDLR,' +  @db + 'PM00200.MXIAFVND,' +  @db + 'PM00200.MAXINDLR,' +  @db + 'PM00200.COMMENT1,' +  @db + 'PM00200.COMMENT2,' +  @db + '

    PM00200.USERDEF1,' +  @db + 'PM00200.USERDEF2,' +  @db + 'PM00200.CRLMTDLR,' +  @db + 'PM00200.PYMNTPRI,' +  @db + 'PM00200.KPCALHST,' +  @db + 'PM00200.KGLDSTHS,' +  @db + 'PM00200.KPERHIST,' +  @db + 'PM00200.KPTRXHST,' +  @db + 'PM00200.HOLD,' +  @db +

    'PM00200.PTCSHACF,' +  @db + 'PM00200.CREDTLMT,' +  @db + 'PM00200.WRITEOFF,' +  @db + 'PM00200.MXWOFAMT,' +  @db + 'PM00200.SBPPSDED,' +  @db + 'PM00200.PPSTAXRT,' +  @db + 'PM00200.DXVARNUM,' +  @db + 'PM00200.CRTCOMDT,' +  @db + 'PM00200.CRTEXPDT,' +

    @db + 'PM00200.RTOBUTKN,' +  @db + 'PM00200.XPDTOBLG,' +  @db + 'PM00200.PRSPAYEE,' +  @db + 'PM00200.PMAPINDX,' +  @db + 'PM00200.PMCSHIDX,' +  @db + 'PM00200.PMDAVIDX,' +  @db + 'PM00200.PMDTKIDX,' +  @db + 'PM00200.PMFINIDX,' +  @db + 'PM00200.PMMSCHI

    X,' +  @db + 'PM00200.PMFRTIDX,' +  @db + 'PM00200.PMTAXIDX,' +  @db + 'PM00200.PMWRTIDX,' +  @db + 'PM00200.PMPRCHIX,' +  @db + 'PM00200.PMRTNGIX,' +  @db + 'PM00200.PMTDSCIX,' +  @db + 'PM00200.ACPURIDX,' +  @db + 'PM00200.PURPVIDX,' +  @db + 'PM00200.N

    OTEINDX,' +  @db + 'PM00200.CHEKBKID,' +  @db + 'PM00200.MODIFDT,' +  @db + 'PM00200.CREATDDT,' +  @db + 'PM00200.RATETPID,' +  @db + 'PM00200.Revalue_Vendor,' +  @db + 'PM00200.Post_Results_To,' +  @db + 'PM00200.FREEONBOARD,' +  @db + 'PM00200.GOVCRPID,

    ' +  @db + 'PM00200.GOVINDID,' +  @db + 'PM00200.DISGRPER,' +  @db + 'PM00200.DUEGRPER,' +  @db + 'PM00200.DOCFMTID,' +  @db + 'PM00200.TaxInvRecvd,' + @db + 'PM00200.VENDDBA' +  @from1 + @from2 + @from3 + @from4 + @from5 + @from6 + @from7 + @from8 + @fro

    m9 + @where1 + @where2 + @where3 + @where4 + @where5 + @where6 + @where7 + @where8 + @where9  )

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 601 Most Valuable Professional

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 416

#3
Adis Profile Picture

Adis 384 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans