VB Dates and DBNull.Value SQL

One think I find that I have to look up often is how to work with database dates in vb.net code. I can never remember the best way to do it and end up looking at older code all the time to do it.

Dates are not usually the problem as much as empty dates. Moving an empty date from vb to SQL and back again. I don't expect my solution to be the best solutions. Personally I feel its too complicated so there had to be a better solution that I just have not found yet. With that said, here is how I handle null dates in vb.net.

I don't like working with null values when crossing between applications and code. Mostly because a blank value can serve the same purpose with less code. Dates are the one place I can't use a blank value and it must be null. I will show the use of both strings and dates so you can easily see how they differ.

Here is how I grab values from a DataRow object called row:

dim name as string = row("Name").ToString()
dim DOB as Nullable(Of DateTime) = row.Field(Of Nullable(Of DateTime))("DOB")

My use of ToString() is a quick way to convert DBNull strings into a empty string. I could store my date as a string and do the same thing, but there are times I want to keep it as a datetime object. I could use a plain DateTime and just check the value as I read it for DBNull and adjust to that. Or I can use a Nullable DateTime object to do the same thing for me.

When I am saving the values, I do check for values when I add them as Parameters. Here is how that code looks like:


Dim command As New SqlClient.SqlCommand()

command.Parameters.AddWithValue("@Name", Name)
If DOB Is Nothing Then
command.Parameters.AddWithValue("@DOB", DBNull.Value)
Else
command.Parameters.AddWithValue("@DOB", DOB)
End If

One other thing that you need to watch is using DBNull values in a where statement. By default NULL=NULL is false. You have to check for IS NULL instead. If you want more details on why that is look up ANSI_NULLS. I mention this because its very common to want to use a parameter in a WHERE statement. If you don't craft it correctly, you will not get the results you expect when the value is NULL.

In your mind you expect this to work:

SELECT * FROM people WHERE DOB = @DOB

You want to use a DBNULL to find all the people without a DOB but it will return 0 records. I bet you could mess with the ANSI_NULL option or change the query to this:

SELECT * FROM people WHERE (DOB = @DOB OR (@DOB IS NULL AND DOB IS NULL))

This will do the normal check on the param, but will also check to see if both are null. I know I am doing a lot of extra work someplace, but this does work for me. Turning off the ANSI_NULL option will make the first query work, but I have not looked into it enough to know if it has any negative effects.

New transaction cannot enlist in the specified transaction coordinator

I was tying to set up a special feature in the software I work with that involved linked servers. This was test setup so I configured some SQL servers I already had set up and ran into this error.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

The only message the application gave me was the first line but I uncovered a sql command that let me test it without the overhead of running my application. Here is that command:


BEGIN DISTRIBUTED TRANSACTION
select * from linkname.databasename.dbo.tablename
COMMIT TRAN

I was able to get the inner command to run on its own. As a distributed transaction, it would fail every time.

I eventualy found this KB from microsoft to get me in the right direction. http://support.microsoft.com/kb/839279

You may receive a 7391 error message in SQL Server 2000 when you run a distributed transaction against a linked server after you install Windows Server 2003 or Windows XP Service Pack 2

It turned out to be a settings issue with the Microsoft Distributed Transation Cordinator (MS DTC). I had no idea what that realy was. It turns out MS DTC should be enabled when SQL is installed but by default its locked down (or is with 2003/xp sp2).

I used dcomcnfg to get to the component services. Console Root -> Component Services -> Computers -> My computer -> Properties . On the Default properties enable distributed COM on the computer. Under MSDTC -> Security Configuration check the option for Network DTC Access.

I did make those changes on both servers, I don't know if that was needed or not. I did the linked server last and it worked as soon as I applied the changes. I know those were the exact changes I made on the 2nd server. The first one prabably has every thing checked or opened up trying to figure it out.

Other thing you may try if having issues it to make sure the Distributed Transaction Coordinator is running as network service and not local system. Reinstalling it as a service and rebooting were a few other tips I read while trouble shooting this problem.

Single Level Active Directory Domain

If you are considering giving your Active Directory name just a single level name, don't do it. Just stop and pick a new name. If your already stuck with a single level AD domain, I feel your pain. I see the warning now when I try to fix my problems after the fact.

So whats the big deal? Windows XP and Server 2003 do not update DNS records to a single level domain. They also have issues joining a domain cross subnet. Our biggest problems show up when dealing with more then one subnet.

The main problem is that DNS will be missing records. Domain controllers have lots of integration with DNS. So if one of those is not updating its DNS records, you have a huge mess. Domain controllers failing to replicate was our big issue. Every time we added a domain controller, we ended up rebuilding the DNS records by hand. Adding the new server keys where needed. Most of the time replication would work one way but not the other. Fixing DNS fixed replication.

Over time I have uncovered more documentation and most importantly this registry key:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dnscache\Parameters]"UpdateTopLevelDomainZones"=dword:00000001

I run this on every server just to make sure. Once you run this, you will have to reboot the server so the netlogon process can register the DNS.

You can run this on your workstations if you want them in your DNS too. we opted not to for most of ours.

I believe there is now a group policy setting that sets the same value. But I do not recall where it is. If I find it, I will update this post.

The second big issue we ran into was we could not join the domain from the other subnets. In XP and 2003, Microsoft changed them to use DNS more. The computer could see the domain and tell you that it exists. It would even point out that the SRV records in DNS are correct. But it would fail to join. At some point in the process it sees the single level domain name and used netbios instead of DNS. It would work fine if on the same subnet, but with out a wins server or lmhost file it would fail.

For the longest time, we used a lmhost file to point to the domain. We built it into the default ghost image and it worked great. Then came the network restructure that ended up changing our IP range. Evey one of those file we used in the last several years had to be updated. The symptom was a 10 min login on those computers for existing users on the computer.

Here is a sample of what we put in the lmhost file

10.0.0.1 servername1 #PRE #DOM:doaminname
10.0.0.2 servername2 #PRE #DOM:domainname
10.0.0.1 "domainname \0x1b" #PRE
10.0.0.2 "domainname \0x1b" #PRE

Inside the quotes on the bottom line must be exactly 20 characters or it won't work.After saving the changes to the LMHOSTS file you have to enable NetBIOS over TCP/IP and import theLMHOSTS file.

The correct way to do this would be to set up a wins server. One experiment that I tried was to put one of the domain controllers address as the wins server and it solved our import issue. The only thing we needed a wins server for was to join computers to the domain on a different subnet. By telling the computer that the domain controller is the wins server, it was able to find it and import just fine.

So our work around was to run a registry hack on every server so they update DNS and to put in a fake wins server address that points to our domain controller for workstation importing.

Ventrilo - Ranks and Mute/Queue Options

There are lots of reasons you may or may not want to give users their own accounts on your vent server. I use it to give me more control over a large group. Let me give you an example.



I play alot of World of Warcraft and our raid groups have 25 members. A few need to be able to talk all the time while others just talk all the time and don't stop. If you get alot of people telling everyone how to do something their special way, it just makes it more complicated for everyone else.



So I have a raid channel set up that mutes all guests to that channel. All guild members have an account so they can talk. New and random people to the group we keep silent. We want them to listen to our method. If they have a sugestion, they can send it silently to a raid leader in game to relay to the group. This option is in the channel options under "Disable Guest Account Transmitting". Checking this will keep all guests from broadcasting in that channel.



Another thing you can do is give each member a rank and mute low ranked members in a channel. I experimented with this but it does not work as smooth as I would like it to work. Im taling about the voice mode on the channel. The default option is normal that allows all ranks to talk. The other 2 options are Queued and Muted. The each act a little different but will keep ranks under a set level from broadcasting.



Queued: This one will mute everyone else while someone is talking. So only one person can broadcast at a time. It uses the Transmit Rank Level to only allow set ranks to broadcast at all. The person that gets the mic keeps it until he stops broadcasting. Once he stops, the next new broadcast gets it. The catch is if someone starts to broadcast just before they last person has released his mic, they will stay muted. I don't like this setting because someone could think they are eaying something important, but because they never got the green light it never broadcasted. I feel like I have to watch vent on a 2nd monitor to use this. The exception here is if you only have one person that will ever talk in this setting. You can adjust this setting on the fly.

Ventrilo - How to connect?

Ventrilo is a very popular voice chat program. It is used with many games to cordinate the actions of many players at once. The first step to using Vent is to get connected.

First step is to create a user name. This is the name that everyone will see when you connect. You can create a phonetic for it so it will announce when you join or leave the channel to everyone there (that have not turned that annoying feature off). Be respectfull with that because it follows you from server to server and its easy to forget you have one set up.

If its a new connection, you will need to add the server before you can connect to it. You will usualy be provided a server/port/password to enter. If they do not provide a password, you prabably do not need one.

The server can be a port or an IP address. It can look like guild.typefrag.com or 10.120.34.4. You may see the port number attached to the server name with a colin. If the port number is 12345 its possible it can look like this; guild.typefrag.com:12345 or 10.120.34.4:12345. If you are not given a port number, look closer at the sever name.

Once you add the server you can click connect. If everything is correct, you should find yourself in the root channel. From here you can double click on a channel to join it.

Ventrilo Guide on its way

I am doing alot of work with Ventrilo and finding the information available on the features I wan't to use either don't exist or are hard to find. It's my plan to document some of these things to make it easier for others.

For now check out this guide: http://www.trap17.com/forums/Ventrilo-Gamers-Friends-t61530.html

KTT: Damage Meter AoC Combat Log Processing

I wrote little mod to track damage stats for Age of Conan when I first started playing it. There was another damage meter at the time, but it didn't have the look and feel that I wanted. Over time I added more and more features and started several others. In the end I stoped playing Age of Conan and stoped maintaining the mod.

My core features were stable on my last release and I have watched it from a distance as people continue to download it. I can't it does not have bugs and many of my newer features are more proof of concept that show what can be done.

How did I gather dps stats?
The first big roadblock was the fact that Age of Conan did not have true mod support. It did however have a feature I could use to my advantage. AoC would generate a plain text file in realtime that contained the combat log. Every attack, heal, buff, debuff, and xp gain was recorded in that file. That one feature gave me a world of options and features that could be implemented.

Getting the correct file:
The log file was located in a sub folder within the AoC directory. During install, a registry key is set to the install path of AoC. The logs were named in such a way that an alpha sort would get me to the correct ime. On first scan of the directory, I grab the logical file and set a marker to the end of it. I start at the end of the file to exclude old stats. I assume that starting KTT is when stat collection should start.

I also add a watch on the folder for new files getting created. There are times when KTT will be running already before the logfile gets created. If I did not watch for new files it would be stuck on the old one and never collect any stats.

Processing the log:
Twice a second I reopen that file. From the marker to the end of the file, I add every plain text line into a queue. The marker is updated with the end of file and the file is closed. I had to open the file in a read only non locking stream so it did not cause issues with AoC writing to that same file. I used the queue so I could collect the data as fast as possible and get that file closed as fast as possible.

I would then work that queue with regular expression to pull out the various values and actions. I ended up with a fairly complicated regex query that did most of the work for me. I found 4 distinct sentence structures that indicate source, target, action, and a value. AoC made the combat log in sentences that were proper English that even reflected 2nd person when needed. While it sounds nice when read out loud, it creates a lot more work when writing a parser. So the regex ended up to be 4 page widths long but I didn't have to post process any of the values.

Once each line was processed into an action object, it was then placed on another queue. I used a queue her so I could multi thread the log reading/parsing away from the rest of the application. As I work this queue I would pass the object off to the plugin framework. The damage meter plugin would flatten that stats as it received them. When calculating total damage, I just add the damage on the fly so the reporting side can just report the precalculated total. (vs summing the damage every time I want to report it). I did this for any stat that I could think of.

Because I compressed the stats like this, I could keep the queue clear of actions. The point of this is my memory usage is only as wide as the number of stats I'm recording and not as large as the log file. During a large raid, the log file can grow to very large sizes very quickly. While KTT will jump in size very quickly as it sees a stat for the first time, as that stat is repeated over and over many times in the log, it will not take much additional memory.

In closing:
I may do little write ups on other features of my mod (or its potential) later. I wrote this for other mod writers. To show them that with very simple things and ideas you can do some very cool things. While I may not have described the best way to do something, it still worked very well for me. The mod is listed as KTT Damage Meter, KoS, Timers, & Sounds over at curse.com.