eConnect “Customer is Inactive, you must Activate the customer to enter transactions”

Started looking at the eConnect integration with one of our websites today. We get the following error when the customer has been marked as inactive. Inactive in our world is something the finance department quantity, however we obviously want to still accept sales and ask questions later.

The problem is when the customer places the order we get the eConnect error which makes business sense,

WebDocumentTransport.FileProcessingException: Error processing order: 39646 ---> System.Exception: Microsoft.GreatPlains.eConnect.eConnectException: Sql procedure error codes returned: Error Number = 1790 Stored Procedure taSopHdrIvcInsert Error Description = Customer is Inactive, you must Activate the customer to enter transactions Node Identifier Parameters: taSopHdrIvcInsert SOPNUMBE = 3899646 SOPTYPE = 2 Related Error Code Parameters for Node : taSopHdrIvcInsert CUSTNMBR = TEST

 

First I checked to see if there was a field in the XML that overrides this behaviour, there was not so next I thought I could use the econnect pre and post stored procedures to get around this. The procedures, taSopHdrIvcInsertPre and taSopHdrIvcInsertPost, supposedly run before and after the document is processed.

As I was seeking a simple rapid solution, I bunged some TSQL in to stash away the customer status if it was inactive, set customer as active and then restore the status afterwards in the post procedure.

Annoyingly this does not work. The taSopHdrIvcInsertPre never executes if the customer in inactive. I guess the error occurs before econnect hands off to taSopHdrIvcInsertPre. I admit this seems like a acceptable behaviour, just not the one I was seeking.

So looks like I have run the SQL procedures I wrote, before and after the sales document is created myself. Alternatively I have to go back to plan A that was to do the change through eConnect itself that might be the more correct way to handle this anyway.

Where are our successors?

Computer science & Electronic engineers shortage

Attending Design Means Business conference today, Ian Livingstone, co-founder of Games Workshop and now Life President of Eidos games brought up the subject of the skills shortage in the UK games industry. An industry worth staggering amounts of money it has to be said.

Ian hinted at the lack of support and understanding for the industry from the civil service, Government. Ian promoted that computer science should be taught in schools because ICT as it is currently taught is inadequate. Kids need to be introduced to more than how to operate Microsoft Office applications. If we are to supply the industry with the type of skills it demands.

Computer Science in schools

I do think kids need to understand more about how computers work, bits and all. Computers are now so pervasive in our lives that an understanding of how they work should put people in a good position for the many years ahead where they will be interacting with them in all their guises.

I had to chip in when it came to question time as I am seeing the same problem in the electronics industry. This was confirmed by those who came up to me later agreeing with my observations. Recruiting for a broad skilled electronic design engineer is turning out to be very difficult for me at the moment. During interviews what I think I am uncovering is that students of electronic engineering are less likely to have been experimenting with basic circuits in their bedrooms than in our generation. This results in them not knowing the basic building blocks required to design circuits. Could this be because Kids now have so many distractions competing for their time, such as smart phones, SMS text message conversations, very good video game consoles and handhelds, internet chat, social internet, Blue Ray DVD’s, many TV channels all day and night? In our youth, we had to entertain ourselves much more, for some kids this led to dismantling things and building things, often spending hours figuring out how things work. Is that is lost to this generation?

Perhaps the educational establishments are failing to teach what is actually required for industry? Some argue that the high calibre students are not longer attracted to engineering as the status and value of engineering has been slipping in society for many, many years.

How did our generation learn?

In the case of computers the 1980’s games only held so many hours of attention then you had to find other things to do like entering pages of computer code from magazines, working out what it was doing, tweaking it. Today software developer tooling is such that the developer is abstracted above the science of the machine and is more inclined to be dragging and dropping even if they do venture that far. An interest in computer science may never be germinated as computer science is never exposed.

In the electronics world, these days the hobbyist who picks up a copy of Make magazine or goes to instructables.com is faced with many electronics projects that are fun, however they are almost always backed with a microprocessor hobby board that makes it plug, program and play. This shortcuts the analogue circuit equivalent of these operations leaving little hope of developing analogue electronic engineers or RF engineers.

Inspirational software evangelists

What could be done to correct the situation we are in? The British excel at design and should be generating economic benefit from it.You may say the Government should invest in computer science and electronics in schools, instead perhaps it should be the private sector, computer games industry and electronics industries taking matters into their own hands. Ian said that they are sponsoring computer clubs at schools. This is a start, but I really have to ask about role models and inspiration. What was it that inspired a subject option choice or career aspiration when you were young? My guess is that this is usually though an individual who inspired you, a role model such as an unusually enthusiastic teacher or media role model. I cannot stress enough about the importance and impact that enthusiastic technology evangelists can have on children’s perceptions and acceptance of learning. The industry needs to find individuals who are good leaders, can inspire young people. Microsoft do this very effectively with their technology evangelists a handful of individuals who are good a public speaking and love what they do. Their role is to go around the internet and physical country enthusiastically talking to people about the Microsoft technology stack. Perhaps as electronic and software engineers, we should be putting our foot in the door of the computer clubs, kids social clubs using such inspirational individuals to inspire a next generation of wealth builders.

I remember when the Royal Institution Christmas lectures used to be shown with good TV schedule slots, and as there was not much choice kids ended up watching them, getting inspired by science & technology. They are now on after the kids have gone to sleep late evening and on an obscure TV channel. Does this tell us of the regard education of children has? Do you also remember how open university programs used to be on to fill gaps in TV scheduling? You would end up watching them, and often realise how interesting it was?

Education is competing with entertainment now more than ever before. Other developing market countries are pouring effort and cash into education where the kids are given more motivation to improve and less entertainment distractions. The gap is closing and we need to act to prevent electronic design and game design migrating to other countries.

My company wants innovation…

I caught myself asking for my newly acquired design team to be innovative something I now realise was unreasonable after my company also asked for the same thing of us.

Today at the Design Means Business conference the term innovation and innovative kept arising. Some clarity of the matter was distilled as the day progressed.

Mike Addison from Procter & Gamble provided me with some quotes that were reflections and affirmations of other sessions in the day;

  • "innovation is intrinsically risky if you are pushing as hard as you need to push" (and the business has to understand that)
  • "management want innovation as a process ... It requires specialness not common in everyone" (it is not a process it is a culture not everyone can be innovative)
  • "a culture of innovation cannot be switched on and off" (on demand)

Innovation is a culture that must be adopted by an organisation. If innovation is being asked for by the CEO then they must in turn support an innovative culture, from the top down, including the risks that come with it.

Talking to other delegates it seems that this is a common demand upon design functions in business yet, the necessary organisation commitment to the culture piece is rarely recognised or put into place.

http://esrccoi.group.shef.ac.uk/worddocs/innovculture.doc

 

The problem of generating new growth business is not solved by multiplying the number tries. It is solved when insights about how to do something better coincide with design thinking and entrepreneurial management in a host organisation that has the right combination of resources and sponsorship to take the opportunity forward.

The Growth Gamble: When Leaders Should Bet Big on New Businesses, and How
They Can Avoid Expensive Failures by Gary Hamel, Andrew Campbell and Robert Park

@@SERVERNAME is NULL & SQL Server Replication

Issuing the following query should return the server name of the instance you are working with. In the scenario that is about to be described, the server had some work done on it which left the query below returning NULL.

This causes problems when attempting to run tools such as the replication monitors and wizards as they appear to use this query internally and thus complain that a connection cannot be made to a (NULL) server during the login process. Also various other errors start occurring using Server Management Studio.

SELECT @@SERVERNAME
 
As the sever had been used for replication in the past, there was a node under linked servers for itself. The server was acting as its own distributor in replication, thus had a “replication” database under System Databases node in Management Studio.
There was also a Linked Server named repl_distributor that I take to be a pointer to the distributor, in this case the same server.
 
The problem was the the Repliation >> Local Publications  node contained a publication that was not working.
 
Trying to run any commands resulted in the server complaining about the null server name, by the way the following command returned the correct server name;
SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

It seems this is held more up to date than the other property and syncs with the sever name better.
 

Change @@SERVERNAME

All the server names “registered” can be viewed with

sp_helpserver

In the returned table it was possible to see the Linked server, but there was no server with the ID column of 0. The row with the ID column of 0 is the server name returned when the @@SERVERNAME property is used. To set this add a server with the following;

sp_addserver '<YourServerName>','local'

The local parameter tells it that you are talking about this special server name. However attempting this in this case resulted in the server complaining that the server already existed, due to the LINKED server.

It should be possible to drop the existing linked server by issuing the following command;

SP_DROPSERVER '<YourServerName>','droplogins'

This didn’t work as it complained that the server was acting as a publisher. Argh!

Temporary Change to @@SERVERNAME

In order to “getinto” the SQL tools I creating a ficticous servername, adding that servername to the local hosts file of the server.

 
sp_addserver '<YourServerName>TEMP','local'

In the hosts file C:\WINDOWS\system32\drivers\etc\hosts

127.0.0.1       localhost
127.0.0.1    <YourServerName>TEMP

YOU MUST restart the SQL server service for the @@SERVERNAME variable to refresh, easy to forget!

So now @@SERVERNAME returned a false servername, however at least working on the server I could use Management Studio to connect to the server under this fake name and start accessing normal tools for replication.

Removing replication

 

At this point the aim was to totally remove replication and then add it all back in once it was all gone. To do this there is a guidance document How to manually remove a replication in SQL Server 2000 or in SQL Server 2005this seemed to work for 2008 too.

For each replicated database, this was used to rip out all replication from the databases.

exec master.dbo.sp_removedbreplication '<replicated_databse_name'
sp_dropdistributor @no_checks=1, @ignore_distributor=1
 
The rest of the commands in this section are a medley of commands that can be used to get to the point where the replication is gone.
 
To drop the replication distribution database use this;
alter database distribution set offline
drop database distribution
 
master.dbo.sp_serveroption @server=N'<YourServerName>', @optname=N'dist', @optvalue =N'false'



Helpful help functions the last two seemed to be the final step needed in this case to allow the dropping of the various objects (linked server and replication database).

sp_msrepl_check_server
sp_helpdistpublisher
sp_get_distributor
sp_helpdistributor
sp_helppublication
select * from msdb..msdistpublishers
delete from msdb..msdistpublishers
 
“There are still remote logins or linked logins for the server” - one biggest breakthrough with all this was the clue to try right clicking on the object you are trying to destroy, scripting it and then using that to get the server names for the above manually removing replication method and the other commands listed. It also gives you the scaffold to change the option values to false for the linked servers. See the following fragment that I hacked to get our options for replication and publication set to false after using the scripting technique.
 

/****** Object: LinkedServer [<YourLinkedServerName>] Script Date: 02/18/2011 02:43:09 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'<YourLinkedServerName>', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL

@useself=N'False',@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL

@useself=N'False',,@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'dist',

@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'data access',

@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'sub', @optvalue=

N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'pub', @optvalue=

N'false'


Changing @@SERVERNAME to what it should be

Now it was possible to change the servername back to what is should have been in the first place, and remember to delete the entry in the hosts file.

SP_DROPSERVER '<YourServerName>TEMP','droplogins'
sp_addserver '<YourServerName','local'
 
YOU MUST restart the SQL server service for the @@SERVERNAME variable to refresh, easy to forget!

sp_helpserver

Looking and help server should now return the server names with the actual servername having an ID of 0.

SQL Jobs

 
Don’t forget to delete all the sql replication jobs and agent jobs.
 
 
Thus after a few hours the server now has no replication and it is possible to start again.