Microsoft Dynamics GP (Archived)

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

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (13)

Answers (1)

Jonathan Fear on at

Like (0)

Report

Do you have a custom trigger or auditing products?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Jonathan,

No. I don't have customized trigger or auditing products.

Thanks,

Huan

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Haun,

Here is a similar post, which may help, depending on how you got to where you are right now:

community.dynamics.com/.../110799.aspx

It is possible you copied the databases from one server to another, and did not complete all the required steps, which are outlined in the post.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Jonathan Fear on at

Like (0)

Report

So you did have a custom trigger after all :)

Was this reply helpful? Yes No