Archive for Author admin

Let the allocation suffering end this Festive Season.

We have been known to give things away from time to time, and this time we are not even going to charge shipping and handling. Its for all you Sage Evolution\100\200 or something (who knows what they are calling it now) resellers who every now and then has to suffer though an allocation corruption. And it sucks because the design comes from the mid nineties, and it sucks. So I have written a small gui that shows you which transactions are causing that damn button to be greyed out. it does not fix it for you so you still can lay claim on being a data-fixing genius.

I tried making it super complex to use, here us a picture of its convoluted interface:

allocation fixer interface.

download it by clicking on the here, or here, or here. Your antivirus might have an issue or something, but since you are consummate Software expert you should be able to handle it.

We don’t just use any software to help you, we rely on AnyDesk

If we have assisted your company before, or you need assistance now you would know that remote desktop support has become a key part of that process. Obviously it allows us to see what is going on and also assists us to work with you, changing plain old invisible support into a training and knowledge sharing session in many cases.

 

For years we have been relying on TeamViewer, but their business model has made it impractical to keep up so we (mostly Endy) set off to find an alternative.

It had to conform to the following things:

  1. Evergreen, the updating of one version to the next makes it almost impossible to keep up since older versions cannot connect to newer versions.
  2. Connectivity, it had to be able to connect without any fancy setup or in depth knowledge of networking.
  3. Compact, it had to small and easy to install and not take up huge amounts of resources when we try to help you.
  4. File Sharing: we need it as an interface to send you updates or scripts or new files
  5. Communication: we needed to be able to chat to you while we figure out whatever is going on together.
  6. Red. It needed to have a red icon so that it fits in with our image. (as if we have one)

 

So after a lot of searching and testing we found AnyDesk.

 

If you have a problem, if no one else can help, and if you can Download AnyDesk….maybe you can hire The KI-Team.

 

To Help you with this process we have prepared a short informal but informative presentation, to guide you which is on our YouTube Channel.

 

To access it just click on the link: https://youtu.be/IY1EfxiUGKw

 

If you find this helpful, please subscribe and like our channel, as there will be many more instructional and educational content in the future.

When you outgrow your identity.

Identity columns, we create them, and then forget about them, and for 12 years it has never been a problem. This is not an opinion about implicit and explicit Primary keys, and identity columns has been the easiest fastest best way to add a unique column to a database for decades. It has been used as primary keys on millions of Databases across the globe. And why not,  I have always been of the opinion that if the President cannot read it, then I don’t need to worry about it, so let it grow. They are everywhere, and most of the time you can forget about it but on Friday, the 29th of September 2017 the unthinkable happened. A table on a Database I have spent a lot of time with, suddenly could not insert values. The Identity value was out of range for an int.

Integers as with any other datatype are saved in Bytes. We live in a binary age and to make things more efficient at storing and retrieving, we need to define in which bytes we are going to save it . According to Microsoft the maximum value for an Integer is 2 147 483 647. This Gupta’esce number is big,  but a Bigint gets ridiculous at : 9 223 372 036 854 775 807.  After this event the question that lingers in my mind like a frustrated toddler on his/her way to a holiday is simple: Are we there yet?

This tries to answer that:

DECLARE @VLS VARCHAR(MAX) = ”
SELECT @VLS = @VLS + CASE WHEN ROW_NUMBER() OVER (ORDER BY t.name) = 1 THEN ”
ELSE ‘ UNION ALL ‘
END + ‘Select ”’ + t.name + ”’ as TableName,”’ + c.name + ”’ as ColumnName,”’ + tp.name + ”’ as ColumnType , CAST(max([‘ + c.name
+ ‘]) as bigint) as MAXValue,(1.00-(CAST(MAX([‘ + c.name + ‘]) AS BIGINT) /’
+ CAST(CASE WHEN tp.name = ‘bigint’ THEN CAST(9223372036854775807 AS BIGINT)
ELSE CAST(2147483648 AS BIGINT)
END AS VARCHAR(40)) + ‘))*100.00 as GrowthSpace

 

from [‘ + t.name + ‘] WITH (NOLOCK)’
FROM sys.columns c
INNER JOIN sys.types tp ON tp.user_type_id = c.user_type_id
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.is_identity = 1
ORDER BY t.name

 

 

EXEC (@VLS)

 

 

PRINT @VLS

 

Please don’t run it on a production environment during high load, but it should tell you how much room you have for growth.

Views gone wild.

Oh no, my data is gone, there is serious corruption, run for the hills, abandon ship! Where is my passport! WHY DID we do this upgrade???? Why did we release this patch? Why is the Database guy so relaxed?

Probably because he/ she haven’t refreshed the views yet. What are these views you speak about?

A view is a dynamic “view” of data in tables. The issue with them are that for practical reasons and sometimes just because of CRAZY AND TOTALLY UNREALISTIC DEADLINES, we use this little thing: * an asterisk, and just like his Gaulish friend Asterix, is a small thing that packs a big punch, but like magic potion it should be used wisely, not wildly.

So, what happens, due to a structural change in the database, new fields get added to tables being referenced by the views. The views have its own list definition of the fields it should contain, and nobody told it to update. As an example a table had 10 columns and now it has 12.

Someone wrote a view that looked something like this :

Create view v_someview as select * from TenColumnTable t inner join SecondTable s on t.foreignkey = s.primarykey

Column 11 and 12 before the upgrade referenced the 1st 2 columns of “basetable”. After the upgrade when you go select * from v_ someview you see the name of column 1 of Secondtable in column 11 of the view, but the data of column 11 of TenColumnTable. How is this possible? Well when a view gets created it saves the underlying structure in the system tables just as a column, and a table function, and that needs to be refreshed for the data to display correctly.

exec sp_refreshview ‘v_ someview’ will do just that and all the madness will end.

So remember, if you ever make changes to a database’s structure, and all hell breaks loose, before you skip the country, refresh the views.

Wat maak jy? A small little tool to help you figure out what SQL is doing and WHY you are up at the times you are.

Sometimes you are doing SQL server maintenance, rebuilding huge indexes, or massive backups and you don’t know how far it is or what its doing.  We complied all of the scripts we have used in the past into a small simple easy to use tool so you can try and figure out if its better to wait for it at 3 in the morning, or kill it and wait for the rollback… and on the topic of killing, there’s a button…

WMJ Screenshot

 

Download it here.