Information Technology Knowledge Base

–Sample Making a column not null and make it default to a value
–When the table has been in use and the existing values shouldn’t change.
update test1 set code = 0 WHERE code is null;
alter table test1
alter column code tinyint not null;
ALTER TABLE test1 ADD CONSTRAINT DF_test1_code DEFAULT ((0)) FOR code;

–Sample Adding a column with a default value
ALTER TABLE test1
ADD code tinyint not null
CONSTRAINT DF_test1_code DEFAULT ((0));

–Removing a constraint
ALTER TABLE test1
DROP CONSTRAINT DF_test1_code;

–Removing a column
ALTER TABLE test1
DROP column code;

Open the Microsoft sql server management studio.
In the object explorer pane you connect to the base server.
You open the replication node then local publications and Rightclick on the desired replication.
Select generate script from the pop up menu.
Make sure “Create or enable the components.” is selected.
Script to file…
Then open this file replace the name of original server in the whole file with the name of the serevr you want to deploy to.

In my case it only was written once in the :exec sp_addpublication

Then look for @job_password = null if the @job_login isn’t null either you have to provide the password for that account.

Also look for
exec sp_grant_publication_access @publication = N’My publication’, @login = N’oldserverSQLServer2005SQLAgentUser$oldserver$MSSQLSERVER’

Change the oldserver to newserver name. (better check for the correct account first!

First enable distribution on your new server.
Then run the file you crafted.

Scenario: Using the sql server management studio you are setting up a server to act as distributor. Then while you have clicked finished the connection is lost between your workstation and the server. Now the server thinks he’s a distributor but then again he’s not sure! You try to redo the previous action but that won’t work. What you need to do is tell the server to give up being a distributor.

use master

exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

Now just restart the distribution creation wizard.

Sample :

DECLARE @replunit as char(2);
DECLARE @kantoor_nr as varchar(4);
DECLARE @kas_nr as varchar(4);
DECLARE @bediende_nr as varchar(4);

SET @replunit = ‘BE’;
SET @kantoor_nr = ‘141′;
SET @kas_nr = ‘1′;
SET @bediende_nr = ‘996′;

SELECT ‘
INSERT INTO KasBeginSaldo
(
KBS_ReplUnit,
KBS_KantoorNr,
KBS_KasNr,
KBS_ItemCode,
KBS_SubCode,
KBS_TijdStip,
KBS_BediendeNr,
KBS_Aantal,
KBS_Waarde,
KBS_VerschilWaarde,
KBS_BediendeNrTeller,
KBS_TijdstipTelling
) VALUES
( @replunit,
@kantoor_nr,
@kas_nr, ‘ +
”” + CAST(KBS_ItemCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(KBS_SubCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(getdate() AS varchar(8000)) + ”’, ‘ +
‘@bediende_nr, ‘ +
CAST(KBS_Aantal AS varchar(8000)) + ‘, ‘ +
CAST(KBS_Waarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_VerschilWaarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_BediendeNrTeller AS varchar(8000)) + ‘, ‘ +
”” + CAST(KBS_TijdstipTelling AS varchar(8000)) + ”” +
‘);’ as statement
FROM
KasBeginSaldo
WHERE
KBS_KantoorNr = @kantoor_nr AND
KBS_KasNr = @kas_nr AND
KBS_ReplUnit = @replunit;

This week I had to change the pre_creation_cmd from ‘delete’ to ‘drop’ on all articles of a publication. I could do this manually or I could find all articles by using sp_helparticle. Lazy as I’m I opt for the latest option.

DECLARE @articles table
(
[article id] int,
[article name] varchar(255),
[base object] varchar(255),
[destination object] varchar(255),
[synchronization object] varchar(255),
[type] int,
status varchar(255),
filter varchar(255),
description varchar(255),
insert_command varchar(255),
update_command varchar(255),
delete_command varchar(255),
[creation script path] varchar(255),
[vertical partition] varchar(255),
pre_creation_cmd varchar(255),
filter_clause varchar(255),
schema_option varchar(255),
dest_owner varchar(255),
source_owner varchar(255),
unqua_source_object varchar(255),
sync_object_owner varchar(255),
unqualified_sync_object varchar(255),
filter_owner varchar(255),
unqua_filter varchar(255),
auto_identity_range varchar(255),
publisher_identity_range varchar(255),
identity_range varchar(255),
threshold varchar(255),
identityrangemanagementoption varchar(255),
fire_triggers_on_snapshot varchar(255)

);
DECLARE @article_name varchar(255);

INSERT INTO @articles
exec sp_helparticle  'Your replication publication name';

DECLARE article_cursor CURSOR FOR
SELECT [article name] as base_object FROM @articles;

OPEN article_cursor;

FETCH NEXT FROM article_cursor
INTO @article_name;

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_changearticle
@publication = 'Your replication publication name',
@article = @article_name,
@property = N'pre_creation_cmd',
@value = 'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription= 1;

FETCH NEXT FROM article_cursor
INTO @article_name;

END

CLOSE article_cursor;
DEALLOCATE article_cursor;

GO

After this you need to reinit all the subscriptions.

Because I needed to hook my laptop up to a beamer, I recently found myself installing the proprietary ‘nvidia’ driver again. Unfortunately, when I restarted X, I was greeted with the following error:

(II) Setting vga for screen 0.
(**) NVIDIA(0): Depth 24, (–) framebuffer bpp 32
(==) NVIDIA(0): RGB weight 888
(==) NVIDIA(0): Default visual is TrueColor
(==) NVIDIA(0): Using gamma correction (1.0, 1.0, 1.0)
(**) NVIDIA(0): Enabling RENDER acceleration
(II) NVIDIA(0): Support for GLX with the Damage and Composite X extensions is
(II) NVIDIA(0): enabled.
(EE) NVIDIA(0): Failure reading maximum pixel clock value for display device
(EE) NVIDIA(0): CRT-0.
(WW) NVIDIA(0): Unable to read EDID for display device CRT-0
(EE) NVIDIA(0): Failure reading maximum pixel clock value for display device
(EE) NVIDIA(0): TV-0.
(EE) NVIDIA(0): Failure to determine the TV encoder.
(II) NVIDIA(0): NVIDIA GPU GeForce2 Go at PCI:1:0:0
(–) NVIDIA(0): VideoRAM: 16384 kBytes
(–) NVIDIA(0): VideoBIOS: 03.11.01.13.12
(II) NVIDIA(0): Detected AGP rate: 4X
(–) NVIDIA(0): Interlaced video modes are not supported on this GPU
(–) NVIDIA(0): Connected display device(s) on GeForce2 Go at PCI:1:0:0:
(–) NVIDIA(0): CRT-0
(–) NVIDIA(0): (null) TV Encoder (TV-0)
(–) NVIDIA(0): NVIDIA NVIDIA_SXGA (DFP-0)
(–) NVIDIA(0): CRT-0: 100.0 MHz maximum pixel clock
(–) NVIDIA(0): (null) TV Encoder (TV-0): 100.0 MHz maximum pixel clock
(–) NVIDIA(0): TV encoder: (null)
(II) NVIDIA(0): Assigned Display Device: CRT-0
(WW) NVIDIA(0): No valid modes for “1400×1050″; removing.
(WW) NVIDIA(0):
(WW) NVIDIA(0): Unable to validate any modes; falling back to the default mode
(WW) NVIDIA(0): “nvidia-auto-select”.
(WW) NVIDIA(0):
(II) NVIDIA(0): Validated modes:
(II) NVIDIA(0): “nvidia-auto-select”
(II) NVIDIA(0): Virtual screen size determined to be 1024 x 768
(WW) NVIDIA(0): Unable to get display device CRT-0’s EDID; cannot compute DPI
(WW) NVIDIA(0): from EDID.
(==) NVIDIA(0): DPI set to (75, 75); computed from built-in default

It’s a very uninformative error as I had neither a CRT monitor nor a TV hooked up to my laptop. The problem, it turns out, is an incompatibility between recent ‘nvidia’ drivers and the Dell Inspiron 8000/8100 BIOS. The solution is to simply upgrade your BIOS. You can download Dell BIOS upgrades here. Use Dell’s BIOSdisk if you’re otherwise unable to boot into DOS.

Search (Wraped around at end of file):

Search STRING forward :   / STRING.
Search STRING backward:   ? STRING.

Repeat search:   n
Repeat search in opposite direction:  N  (SHIFT-n)

Replace: Same as with sed, Replace OLD with NEW:

First occurrence on current line:      :s/OLD/NEW

Globally (all) on current line:        :s/OLD/NEW/g

Between two lines #,#:                 :#,#s/OLD/NEW/g

Every occurrence in file:              :%s/OLD/NEW/g

Getting awstats running with geoip

GeoIp C API

To start you‘ll need to install GeoIP C API. Download it :

wget http://www.maxmind.com/download/geoip/api/c/GeoIP.tar.gz 

Next thing is unzipping it and placing the directory where you want it.

I use midnight commander for this so just fire it up and copy it.

Starting midnight commander:

Mc

Go into the directory you just created by unzipping.

Do the following commands:

./configure

make

make check

make install

The GeoIP C API has been installed. Next we install the IP Perl Module.

When this fails and you are on a linux distro(debian taste) you might want to run:

apt-get update

apt-get upgrade

apt-get install gcc

apt-get install make

apt-get install texinfo

apt-get install libc6

apt-get install zlib1g-dev

GeoIP Perl API

Download Geo::IP Perl Module, use the command below but recplace THE_LAST_VERSION with the right tar.gz file

wget http://www.maxmind.com/download/geoip/api/perl/THE_LAST_VERSION

Next thing is unzipping it and placing the directory where you want it.

Go into the directory you just created by unzipping.

Now do the following commands. (if you have a problem with the make test see for instruction after the commands)

perl Makefile.PL

make

make test

make install

Only if you had problems

If you get a “libGeoIP.so.1: cannot open shared object No such file or

directory” error, add /usr/local/lib to /etc/ld.so.conf then run

/sbin/ldconfig /etc/ld.so.conf     

Awstats

Now you need to go to the config file of your website and alter the Plugin: GeoIP part.

Change /pathto/GeoIP.dat to where this database acctually is. You can quicly find it by

Doing a locate. Well quickly you’ll have to do a locate –u first of course which will take some time.

locate –u

locate GeoIP.dat

On my system it returns:

/usr/local/share/GeoIP/GeoIP.dat

Now I replace # LoadPlugin=”geoip GEOIP_STANDARD /pathto/GeoIP.dat”

By

LoadPlugin=”geoip GEOIP_STANDARD /usr/local/share/GeoIP/GeoIP.dat”

This is the section where to do it

# Plugin: GeoIP

# Perl modules required: Geo::IP or Geo::IP::PurePerl (from Maxmind)

# Country chart is built from an Internet IP-Country database.

# This plugin is useless for intranet only log files.

# Note: You must choose between using this plugin (need Perl Geo::IP module

# from Maxmind, database more up to date) or the GeoIPfree plugin (need

# Perl Geo::IPfree module, database less up to date).

# This plugin reduces AWStats speed of 8% !

#

#LoadPlugin=”geoip GEOIP_STANDARD /pathto/GeoIP.dat”

Also make sure that DNSLookup look up is turned off.

DNSLookup=0

Congratulations you have now a awstats with a working geoip module.

Today an error was reported by an end user of my program. They could get a crazy unstopable msgbox hell. After some investigation I found this happens when the user moves the focus from one faulty text field into another one which is also faulty. Both of these textfields have a lostfocus event. In the lostfocus event some checks are done and when they checks are positive a msgbox is shown and the focus is reset to the own textbox. Internally VB triggers a lost focus event when the user leaves the textbox. Then this lostfocus event does a setfocus on it’s own textbox. This triggers the lostfocus event of the other textbox, because by clicking on it the other textbox had the focus. The solution is simpel first do a setfocus and then show a msgbox with the error.

If Not ValidDate(Somefield.Text) Then
Somefield.SetFocus
msgBox "Some message"
End if