This topic will show you a way to register Sage 500 ERP without going into Maintain Site or use the Database Synchronization utility. I will provide you the T-SQLscript we use to register databases directly from SQL Server. This script should always be kept handy not only because it will register your 500 database(s), but can also provide an error message to use as a starting point with which to troubleshoot. I would not use this script as a regular way to re-register your Sage 500 database(s). I will talk about that in a later section.
The script has two basic parts, which I have color coded below for reference in this blog only. The green section updates the tsmsiteprofile table with the proper Serial Number, Unlock Key, and Customer number. If the database you are registering was a copy of production, these fields (SMUnlockKey,UserCustomerNo,SMSerialNo) will be populated already. In this case you need to update the SMSerialNo field and set to 'TEST' (without the quotes). You could copy only the green section, paste and run it to update the tsmSiteProfile table only if that is all you need to accomplish. If you do, you should also include the 'END' statement in the purple section so you will not get a syntax error.
The blue section is the code block that performs the product registration. Its going to use the registration information from the tsmSiteProfile table. So it is important that there is valid data in the SMUnlockKey, UserCustomerNo, SMSerialNo fields. You could copy only the blue section, paste and run it to perform the product registration.
If you need to run both the green and blue sections, then be sure to include the 'END' statement in the purple section to avoid a syntax error.
BEGIN TRAN UPDATE tsmSiteProfile SET SMUnlockKey = 'XXX', UserCustomerNo = 'YYYYYY', SMSerialNo = 'ZZZZZZ' IF @@ERROR <> 0 BEGIN PRINT 'There was an error while updating' ROLLBACK END IF @@ERROR = 0 BEGIN PRINT 'Updated Successfully' PRINT 'Now attempting registration' COMMIT TRAN
DECLARE @i_lOptions INT, @o_sRegReqMsg VARCHAR(512), @o_sRegMsg VARCHAR(512), @o_sGracePerStartDate VARCHAR(64), @o_lRetCode INT, @o_lLastErrorNo INT, @o_sLastErrorMsg VARCHAR(256) UPDATE tsmRgstrService SET RgstrApplURL = 'HTTP://Registration.SageSoftware.COM/Registration/ERWS.ASP' SELECT @i_lOptions = 4 -- Force to get a new one EXEC spsmRequestRegMsgFromERWS @i_lOptions, @o_sRegReqMsg OUTPUT, @o_sRegMsg OUTPUT, @o_sGracePerStartDate OUTPUT, @o_lRetCode OUTPUT, @o_lLastErrorNo OUTPUT, @o_sLastErrorMsg OUTPUT SELECT @o_sRegReqMsg AS 'RegReqMsg', @o_sRegMsg AS 'RegMsg', @o_sGracePerStartDate AS 'GracePerStartDate', @o_lRetCode AS 'RetCode', @o_lLastErrorNo AS 'LastErrorNo', @o_sLastErrorMsg AS 'LastErrorMsg'
END
Application
When a Sage 500 database remains unregistered, which might be accompanied with error messages during login such as 'Your registration has expired.." , I routinely use the blue section of the code block when I am working on a registration case, when I cannot use / access the Maintain Site or the Database Synchronization Utility.
Log into a machine with SQL Server Management Studio using an account like "sa". You could also use the Sage 500 ERP 'Admin' user account. Before running the code block, made sure the database context is set to the Sage 500 application database. Also, for the query window in which you are working, set the maximum number of characters displayed for each column to be 1024.
This is done from SQL Server Management Studio, Query, Query Options, Results, Text, and change the Maximum number of characters displayed in each column to 1024. The reason for this is so that the results for the columns we will be looking at will not be truncated.
The screenshot below is a pattern of a successful registration. The values in the RegReqMsg, RegMsg, GracePerStartDate fields are encrypted. Then notice the RetCode value, -1, that is a successful return code. There is typically not a value returned in LastErrorNo, nor LastErrorMsg when the registration is successful. The output screen can either be set to grid or text. Grid is more compact when you just want a quick look and that is what I used in the screenshot below. In order for this code block to work, the machine with SQL Server Management Studio needs to have access to the internet. If you are not sure whether it does, trying launching Internet Explorer and navigate to www.Google.com. If you can get there, you should be good.
Ran the query, now what?
If the query returned a Retcode of -1, then you are done. The next step is to attempt login to Sage 500 ERP using a valid user account. During logon, you should not experience any message related to registration. But what if the query does not return a Retcode = -1 ?
Look at the values return LastErrorNo and LastErrorMsg. The example below shows a message where the site does not exist in our registration database. Confirm that the value in CustomerNo is correct and/or does not have any embedded characters.
Check the messages that are received from logging into Sage 500 and/or the LastErrorMsg from the query above in our knowledgebase. Also, check out the link below to another blog I wrote that can help.
For more Registration information check out my earlier blog http://sagecity.na.sage.com/support_communities/sage500_erp/b/sage_500_erp_blog/archive/2013/11/20/registration.aspx. That blog will help troubleshoot problems while performing product registration.
The intention of this month's blog was to show you an unconventional method to register the product. It is not the preferred method. It requires that you have access to SQL Server Management Studio, access to the "sa" or "Admin" logon account. But when the conventional methods are inccessilble, this script should do the trick.
-Tony