I love the “View Update SQL” button on the SalesPad mobile server installer. This button generates the SQL that is about to be executed against your GP database into a notepad window for inspection. It was reassuring to see and understand what was about to be done to the database.
If you look above you will see the way that SalesPad embrace namespaces for the objects that form their systems. They are up front and in in front of your eyes.
So I started reading the SQL, sadly enjoying it – as you do, knowing that you tick all the boxes of the Facebook nerd test. Here is what the resulting script looked like…
Looking through I recognised much SQL analogous to that I’ve written before, myself for auto fulfilment and handling multiple bins. I did especially like the way they create a virtual table as a view for resolving SOPTYPE. It is a SQL technique I’ve seen elsewhere and I should adopt it more in my coding. I have also seen it used inside stored procedures too, in a similar way to create a virtual table to join against, as a common table expression.
Also note that they put all the db objects into the [spgpm] database schema, so I’m not the only one using schemas for what the creator intended! See my other post: Care naming database objects in Dynamics GP for custom GP Addins.
We have all been there, tired and forget that if the machine is running on 64bit, the WOW64 process is looking after you and the ODBC connection for GP needs setting up in the 32 bit version of the ODBC manager. This catches me out from time to time as we deploy ODBC through the network automatically, these days it is rare I do this by hand.
32 vs 64 bit
“You must directly invoke the 32-bit ODBC Administrator tool from the SysWoW64 folder. You can use the 32-bit ODBC Administrator tool to manage user DSNs and system DSNs that are used by WOW64 processes.” [ref 1]
so for example assuming default locations for things on the machine:
Also note: “Just a note don't use the search box in the start menu to look for odbcad32.exe (the same as if you type odbc only). Even the name is the same it opens the one in C:\Windows\System32 not c:\Windows\SysWOW64\” [ref 2]
ref 1 :https://support.microsoft.com/en-us/kb/942976
ref 2: http://superuser.com/questions/419832/how-can-i-open-the-32-bit-odbc-data-source-administrator-in-windows-7-64-bit
When both the following are true:
- the machine is woken from sleep or the network connection has been interrupted to SQL server
- SQL Server Managment Studio Activity Monitor was open on a tab somewhere in the editor deck in SSMS.
-then It will be no longer be possible to interact with SSMS. It will feel like the SQL Server Management Studio has crashed or has frozen.
SSMS crashing as a result of connection lost or sleep is caused by the modal dialog indicating the loss of connection for the activity monitor popping up in a modal mode behind the application surface somewhere. You cannot see this window by right clicking on the application in the bar, but it IS there. Modal mode prevents interaction with any other windows in the applicaiton until that modal window is closed.
To get up and running again, click ALT+Tab until you find the pesky dialog window, then click OK on that window to dismiss it. SSMS will spring back to life. This is a regular occurrance for me after computer sleeps and I’ve been monitoring the SQL server.