Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / [SQL Server Native Cli...
Microsoft Dynamics GP (Archived)

[SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

I got an error when trying create new user login in GP 2010. The SQL server is 2012.

Msg 102, Level 15, State 1, Procedure syUserMSTRInsertTrigger, Line 1
Incorrect syntax near '2147483647'.

I turned on the dex log and found the error came from following statement.

INSERT INTO DYNAMICS.dbo.SY01400 (USERID, USERNAME, Zoom_Fields_Font_Color, Zoom_Fields_Font_Style, Editable_Fields_Backgrou, Editable_Fields_Font_Col, Editable_Fields_Pattern, Editable_Fields_Pattern_, Scrolling_Fields_Backgro, Scrolling_Fields_Font_Co, Scrolling_Fields_Pattern, Scrolling_Fields_PatCor, Required_Fields_Font_Col, Required_Fields_Font_Sty, Internet_Browser, Internet_Address, Internet_Type, Internet_Address_Name, PASSWORD, USRCLASS, MDFRDENT, MODIFDT, CREATDDT, LSTUSRED, PRTOSCRN, PRTOPRTR, SHRQFLDS, DISTPROC, HSCRLARW, SOPTYPE, RELID, SECACCS, TODOOPEN, TODOPERSOPEN, TODOPERSROLL, PALETTETYPE, ORGANIZATIONALVIEW, ACCOUNTINGVIEW, USERLANG, UserRole) VALUES ( 'test', 'test', 6, 2, 10, 2, 4, 2, 10, 2, 4, 2, 2, 2, '', '', 0, '', 0x00202020202020202020202020202020, '', 0, '2014.09.14', '2014.09.14', 'sa', 0, 1, 1, 0, 0, 1, 0, 0x00000000, 0, 0, 0, 0, 0, 0, 0, 1)

I checked table SY01400 and have no clue where is wrong.

Here is the whole log:

---------------------------------

/*
/*  Date: 09/15/2014  Time: 11:01:22
SQLSTATE:(01000) Native Err:(5701) stmt(0):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed database context to 'master'.*/
/*
/*  Date: 09/15/2014  Time: 11:01:22
SQLSTATE:(01000) Native Err:(5703) stmt(0):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english.*/
/*
/*  Date: 09/15/2014  Time: 11:01:22
SQLSTATE:(00000) Native Err:(5703) stmt(0):*/
*/
/*
/*  Date: 09/15/2014  Time: 11:01:22
SQLSTATE:(00000) Native Err:(5703) status(0):*/
SWSTATUS DUMP*/

/*  Date: 09/15/2014  Time: 11:01:22
stmt(47142312):*/
BEGIN  SET DATEFORMAT ymd
SET NOCOUNT ON
 SET CURSOR_CLOSE_ON_COMMIT OFF
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS OFF
 SET ANSI_NULL_DFLT_ON ON
 SET ANSI_PADDING ON
 SET NUMERIC_ROUNDABORT OFF
END

/*  Date: 09/15/2014  Time: 11:01:22
stmt(47150392):*/
{ CALL DYNAMICS.dbo.zDP_SY01400SS_1 ( 'test' ) }

/*  Date: 09/15/2014  Time: 11:01:28
stmt(47154432):*/
{ CALL DYNAMICS.dbo.zDP_SY05400SS_1 ( 0 ) }

/*  Date: 09/15/2014  Time: 11:01:28
stmt(47146352):*/
BEGIN INSERT INTO DYNAMICS.dbo.SY01400 (USERID, USERNAME, Zoom_Fields_Font_Color, Zoom_Fields_Font_Style, Editable_Fields_Backgrou, Editable_Fields_Font_Col, Editable_Fields_Pattern, Editable_Fields_Pattern_, Scrolling_Fields_Backgro, Scrolling_Fields_Font_Co, Scrolling_Fields_Pattern, Scrolling_Fields_PatCor, Required_Fields_Font_Col, Required_Fields_Font_Sty, Internet_Browser, Internet_Address, Internet_Type, Internet_Address_Name, PASSWORD, USRCLASS, MDFRDENT, MODIFDT, CREATDDT, LSTUSRED, PRTOSCRN, PRTOPRTR, SHRQFLDS, DISTPROC, HSCRLARW, SOPTYPE, RELID, SECACCS, TODOOPEN, TODOPERSOPEN, TODOPERSROLL, PALETTETYPE, ORGANIZATIONALVIEW, ACCOUNTINGVIEW, USERLANG, UserRole) VALUES ( 'test', 'test', 6, 2, 10, 2, 4, 2, 10, 2, 4, 2, 1, 2, '', '', 0, '', 0x00202020202020202020202020202020, '', 0, '2014.09.15', '2014.09.15', 'sa', 0, 1, 1, 0, 0, 1, 0, 0x00000000, 0, 0, 0, 0, 0, 0, 0, 1) ;  SELECT @@IDENTITY ;  END
/*
/*  Date: 09/15/2014  Time: 11:01:28
SQLSTATE:(37000) Native Err:(102) stmt(47146352):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647'.*/
/*
/*  Date: 09/15/2014  Time: 11:01:28
SQLSTATE:(00000) Native Err:(102) stmt(47146352):*/
*/
/*
/*  Date: 09/15/2014  Time: 11:01:28
SQLSTATE:(00000) Native Err:(102) status(58):*/
SWSTATUS DUMP*/

---------------------------------

Please help.

Thanks in advance!

Huan

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Huan,

    I ran into this very issue this morning while working with a client running GP2010 on SQL 2012.  After testing, I replaced the existing trigger with the one from a GP2013 install and it seems to have resolved the issue when creating/saving new GP users.

    ------------------------------------------------------------------------------------

    USE [DYNAMICS]
GO
/****** Object:  Trigger [dbo].[syUserMSTRInsertTrigger]    Script Date: 4/7/2015 11:54:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[syUserMSTRInsertTrigger] on [dbo].[SY01400] for insert as  declare @iStatus int,  @iSQL_Error_State int,  @tErrorOccurred tinyint,  @iRaiserror int,  @cRaiserrorString char(255) select  @tErrorOccurred = 0  if exists (  select  1  from  inserted  where  RELID not in (select RELID from ORG00100) and RELID <> 0)  begin  select @iRaiserror  = 21041,  @tErrorOccurred = 1  end if @tErrorOccurred = 1 begin  select  @cRaiserrorString = SMESSAGE  from  GPS_SQL_Error_Codes  where  CODE = @iRaiserror  if @@rowcount <> 1 or @@error <> 0  begin  select @cRaiserrorString = 'The following error occurred, but message for error could not be retrieved. Message = '   + convert(char(255),@iRaiserror)  end  RAISERROR (@cRaiserrorString, -1, -1)  end  PRINT 'CREATING TRIGGER : dbo.dbo.syUserMSTRUpdateTrigger'


  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Hi Harry,

    Agree.

    Need to test more for SQL 2012 with GP 2010.

    Thanks,

    Huan

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    So I am thinking to still use SQL server 2008R2 with GP 2010. :-)

    Don't want to customize triggers. It will be troublesome for upgrade.

    Thanks,

    Huan

  • Jonathan Fear Profile Picture
    Jonathan Fear on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    So you did have a custom trigger after all :)

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Huan,

    I have moved databases to SQL 2012 servers, and to be frank, have encountered more security related issues on the platform than is normal.  This may be another example.  I currently still recommend clients use Windows 2008R2 and SQL 2008R2 as a foundation for Dynamics GP, and will continue to do so.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Hi Harry,

    Actually we found the cause.

    The highlighted error code of the trigger syUserMSTRInsertTrigger for SY01400 table is not recognized by SQL server 2012 but has no problem with 2008. Do you have any idea why it is not compatible?

    ------------------------------------------ 

    ALTER trigger [dbo].[syUserMSTRInsertTrigger] on [dbo].[SY01400] for insert as  declare @iStatus int,  @iSQL_Error_State int,  @tErrorOccurred tinyint,  @iRaiserror int,  @cRaiserrorString char(255) select  @tErrorOccurred = 0  if exists (  select  1  from  inserted  where  RELID not in (select RELID from ORG00100) and RELID <> 0)  begin  select @iRaiserror  = 21041,  @tErrorOccurred = 1  end if @tErrorOccurred = 1 begin  select  @cRaiserrorString = SMESSAGE  from  GPS_SQL_Error_Codes  where  CODE = @iRaiserror  if @@rowcount <> 1 or @@error <> 0  begin  select @cRaiserrorString = 'The following error occurred, but message for error could not be retrieved. Message = '   + convert(char(255),@iRaiserror)  end  raiserror 2147483647 @cRaiserrorString  end  PRINT 'CREATING TRIGGER : dbo.dbo.syUserMSTRUpdateTrigger'  

    Thanks,

    Huan

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Huan,

    Please check to make sure there is not an existing SQL login corresponding to the test user you are trying to enter.  Great Plains creates SQL logins when new user accounts are created.    

    If this is not the case, perhaps it would be best to eliminate the possibility you have restored a corrupt back up, or skipped a step in the process.  Here is a detailed overview.

    1. Back up the databases on current server.

    2. Run a restorefileslistonly on the databases on the new server to identify the SQL DB settings.

    3. Replicate the databases to be restored setting exactly.

    4. Restore the databases to the new containers (that match exactly).

    5. Move the logins to the new SQL Server from the old one.

    6. Change the database ownership to DYNSA for all GP databases, validate with sp_helpdb

    7. Run the Grant.SQL script against all GP Databases

    8. Make sure the GP 2010 version you have installed on the machine MATCHES exactly - if you are missing a service pack, this could be the source of your problem - validate the build numbers.

    9. Make sure the GP modules/products installed on server one, match those on server 2 (compare the dynamics.set files on the machines.

    If this does not work, you may have problems with your SQL instance install, security setup, or the like.  I would strongly recommend seeking the assistance of a GP Certified resource.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Hi Harry,

    Still got the same error when creating new user in GP... :(

    Btw, I login as "sa" to create a new GP user.

    Thanks,

    Huan

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Short List:

    1. Run Script to capture user logins from old server and move them to new server.

    support.microsoft.com/.../918992

    2. Change DB owner for any GP databases to DYNSA - after you have moved the logins.

    Execute: sp_changedbowner DYNSA -- on all GP databases (DYNAMICS and Companies)

    3. Run Grant.SQL script on all GP databases

    support.microsoft.com/.../864573

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '2147483647' when adding new GP user

    Hi Harry,

    Will check with DBA.

    Yes. We did migrate the SQL server from 1 to another server and upgraded SQL server from 2008 to 2012. Anything you can think might be missing please let me know.

    Thanks,

    Huan

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans