Long time no update
I haven’t had the desire to spend much time adding posts or upgrading this website lately although I still keep the O/S up-to-date.
Eliminate gtk warnings
The following post helped me out a lot this morning:
When executing the vmware workstation bundle, the following gtk2 errors are displayed:
Extracting VMware Installer…done.
Gtk-Message: Failed to load module “pk-gtk-module”: libpk-gtk-module.so: cannot open shared object file: No such file or directory
Gtk-Message: Failed to load module “canberra-gtk-module”: libcanberra-gtk-module.so: cannot open shared object file: No such file or directory
Create a gtk2 configuration file and reload libraries.
~]# echo “/usr/lib64/gtk-2.0/modules” > /etc/ld.so.conf.d/gtk2.conf
The installer should run without gtk2 errors.
Microsoft Excel Carriage Return in a Cell
To go to the next line in a cell without skipping directly to the next cell type in:
Banner for Red Hat Enterprise Linux 7
Below is the simplest way to change the login banner for RHEL 7.
sudo -u gdm dconf-editor —> org —> gnome —> login-screen
sudo -u gdm dconf update
Banner for Red Hat Linux 6
chmod u+s /lib64/dbus-1/dbus-daemon-launch-helper
#disable power button
sudo -u gdm /usr/bin/gconftool-2 —type bool —set /apps/gdm/simple-greeter/disable_restart_buttons true
#disable showing local user list
sudo -u gdm /usr/bin/gconftool-2 —type bool —set /apps/gdm/simple-greeter/disable_user_list true
#enable the banner and display /etc/issue
sudo -u gdm /usr/bin/gconftool-2 —type bool —set /apps/gdm/simple-greeter/banner_message_enable true
sudo -u gdm /usr/bin/gconftool-2 —type string —set /apps/gdm/simple-greeter/banner_message_text “$(cat /etc/issue)”
Intel Graphics for CentOS 6
Recently, I started seeing error messages regarding many bad sectors in the hard drive in my HP laptop. After looking at various websites I determined that it wouldn’t be a bad idea to purchase a new hard drive as opposed to trying to fix this issue with disk utilities.
After purchasing and installing the new hard drive, I had to install CentOS again. One thing I quickly noticed is that the default Installer did not seem to work so I tried “Install with basic video driver”. Long story short, after doing the install with the basic video driver I had to do 2 things to enable CentOS to use the Intel graphics card.
1. Edit the /etc/X11/xorg.conf file replacing the word “vesa” with “intel”
2. Edit the /boot/grub/menu.lst file by removing the word “nomodeset”
Credit is voluntarily given to the person who created the website below.https://jehurst.wordpress.com/2011/06/26/rhel-6-for-the-clueless-intel-graphics/
PostgreSQL Current Process List
To kill a process:
— don’t kill my own connection!
procpid <> pg_backend_pid()
— don’t kill the connections to other databases
AND datname = ‘database_name’;
Or you can just leave off the FROM clause
To view process details:
SELECT datname, procpid, current_query FROM pg_stat_activity;
“All matter originates and exists only by virtue of a force… We must assume behind this force the existence of a conscious and intelligent Mind. This Mind is the matrix of all matter.”
Check I/O in Linux
[root@wks1 ~]# hdparm -tT /dev/sda
Convert .ova to .vmx
[root@wks]# ovftool test.ova test.vmx
I found http://invoice-generator.com a few minutes ago.
Thoughts of the day
My manual database migration from Sybase 12.5.3 to 15.7 was a complete success. It makes me very happy.
Living and Learning
I’m just like anyone else. I make mistakes.
One thing I am still trying to do is learn from other people’s mistakes.
There are some points I need to remember that are below the next time I decide to work for another client:
1. Have a signed contract outlining the work to be performed and the amount of money to be paid.
2. Do not do ANY work without receiving a deposit, specifically half up front and the other half BEFORE receiving code, not after, no exceptions.
4. Know what your services are worth and charge accordingly.
5. Don’t do work outside of the contract. If you agreed on creating two different web pages, then do not do three pages just because the client asked.
Any questions? Read rules 1-5 again.
This site below is very useful for generating a textured background for a website.
How do I log in to MySQL via the command line in Linux?
mysql -h localhost -u myname -p mydb
An A-Z Index of the Windows CMD command line
Helpful information below for those of us fortunate enough to work with Windows DOS batch files!
Burn ISO to DVD
Use ImgBurn! It’s Free!
Sybase 12.5 Useful Commands
From the command line: isql -U sa -D database_name
dump database database_name_no_quotes
The keyword go must be used unlike the semicolon Oracle uses.
Quote of the day
“Educated men are as much superior to uneducated men as the living are to the dead.” – Aristotle, 384–322 B.C.
Oracle NOT NULL Constraints
NOT NULL constraints can not be created “out of line” such as in the following example:
CREATE TABLE PORTS
(PORT_ID NUMBER, PORT_NAME VARCHAR2, CONSTRAINT PORT_ID_NN NOT NULL (PORT_ID) );
However, the following is correct syntax:
CREATE TABLE PORTS
(PORT_ID NUMBER, PORT_NAME VARCHAR2, CONSTRAINT PORT_ID_UN UNIQUE (PORT_ID) );
Install Programs In Citrix
D:\>CHANGE USER /INSTALL
Enable install mode. This command has to be run before installing any new software on a Terminal Server.
Loading Data into MySQL
These notes below are for me and me alone. :)
load data infile‘D:\filename.csv’into table tablenamecolumns ENCLOSED BY ‘’‘’ESCAPED BY ‘,’
Restore Master database
1. create a new master device:
dataserver -b “location of datafiles”
2. start ASA in single-user mode
startserver -f RUN_SYBASE -m
3. load the backup — once completed it will shut down ASA automatically
load database master from “location of master backup”
4. start ASA in single-user mode and check that all databases, devices, logins appear to be restored correctly
Sybase 15.7 Drop All Constraints
Below is a script to drop all of the constraints of a database regardless of the table owner:
sp_configure ‘allow updates’, 1
declare cur cursor
select SOR.Name as ‘constraint’, s2.name as ‘owner’, SOT.Name as ‘table’
from sysreferences SR
join sysobjects SOR on SOR.id = SR.constrid
join sysconstraints SC on SC.constrid = SR.constrid
join sysobjects SOT on SOT.id = SC.tableid
join sysusers s2 on SOT.uid = s2.uid
declareSQL varchar(500)open cur
fetch cur into
sqlstatus = 0)
SQL = 'setuser ' + '''' +owner + ‘’‘’
SQL = 'alter table '+owner+’.’‘ drop constraint ‘+@constraint
exec (SQL)fetch cur into
deallocate cursor cur
Empty The Sybase Transaction Log
SQL Server and Sybase Versions
It’s fairly easy to find out which version of SQL Server / Sybase you’re using. Both Microsoft SQL Server and Sybase implement T-SQL so the command below will work for either RDBMS. Just open a query window and type in:
But if you don’t have access to query a database that resides on a SQL Server instance you can always go to the registry and find out by going to the following key:
—> Microsoft SQL Server
—> “Your SQL Server Instance”
Connecting to Oracle Enterprise Manager
I don’t usually install Oracle that often, but when I do it’s usually a successful install without too much fuss. Well, lately I’ve noticed I can’t get Enterprise Manager up in Internet Explorer. At first I just blamed it on the internet security settings being too harsh, but today I had some free time so I decided to make certain I have the talent to open it.
Long story short not being able to view the Enterprise Manager is caused by a certificate with a key length of less than 1024 bits according to Microsoft: http://support.microsoft.com/kb/2661254
open a command prompt
certutil -setreg chain\EnableWeakSignatureFlags 8
browse to Enterprise Manager
Oracle Install Tips
There are probably multiple data files that are zipped up that you need to unzip to install.
For me, installing 11g 32-bit on Windows 2003 Enterprise Server, I had 6 total zip files to unzip before officially attempting to install Oracle.
My biggest tip is to first unzip each zip file to the same directory. Otherwise you are guaranteed to get errors.
You may still get errors, but if you are trying to install each zip file separately then forget ‘bout it.
Converting Folder To ISO
A VMware Virtual Machine can connect to ISO files locally. However, not every file that needs to be uploaded to the VM is in an ISO format.
So, to solve this problem I have downloaded Folder2ISO. It does this one task and does it very well for FREE!
Debugging SQL Server Stored Procedures
I’ve written probably 100,000 lines minimum of SQL Server stored procedures. I had to. Otherwise they wouldn’t have paid me. Some of those stored procedures took hours to run. So if I was debugging them I couldn’t necessarily wait that long on the PRINT function in SQL Server to show me what results I was expecting because that function does not produce output until the stored procedure is over. So I turned to the RAISERROR function. It’s kind of a “hack” as it’s designed to show errors mainly (I think) but I use it since it has the option of NOWAIT which should be pretty explanatory. Below is an example:
raiserror ( ‘text goes right here’ , 0 , 1 )
Oracle 11g Client Install on Windows 7 x64
After many hours trying to get Oracle to work with SQL Server I decided to give up with the current software installed and reinstall the client for 11g. Long story short the only way I was able to get this to work was running the setup on the command line as follows:
SQL Server Maximum Key Length
Warning! The maximum key length is 900 bytes. The index ‘PK_BATCH_ORACLE_SHIPMENTS’ has maximum length of 919 bytes. For some combination of large values, the insert/update operation will fail.
This message bothered me until I found a way around it (kind of sorta). Instead of using all 360 characters of a particular field to use as a key I chose to use only 320 (it’s a customer name). I used a calculated column with keyword PERSISTED.
So…instead of using
[customer] [nvarchar](360) not null
as my primary key I use part of it instead
[customer_truncated] as left ([customer], 330) persisted
I hope this helps anyone interested in overcoming this issue (although I hope you can find better columns to build a primary key…).
Divide by Zero
Instead of using a CASE statement inside a SELECT statement in SQL Server, I have found a better, cleaner method is to implement NULLIF which takes two parameters and compares them. If they are equal then NULL is returned. This forces the divisor to become NULL instead of zero. And dividing by NULL makes the entire equation NULL.
For my project this morning I need to return 0 instead of NULL so I will also implement a COALESCE function which compares the output.
So rather than use this logic below:
when isNull(divisor, 0) = 0
end as value
select coalesce( numerator / nullif( divisor, 0 ) , 0)
SQL Server Date Conversions from Excel
Going from Excel data to SQL Server data shouldn’t be difficult. A common problem when doing this conversion is how to go from an Excel date like ’6/4/2011 11:34’ to a SQL Server date that is identical, but with a datetime data type. The first thing to do in SQL Server is to convert the data from NVARCHAR to Float. From there convert the float to datetime. It really is almost too easy.
cast([column name] as float)
-2 as datetime
) from [table]
Oracle Patch Install
I ran into an issue installing an Oracle patch a few months ago. The solution was found at:
Just though I would share my findings on this issue.
I’m a Information Assurnace engineer for a large communications company. I’m responcible for developing security solutions for a vast array of Windows based devices. Recently it had come to my attention that our security solution was blocking the install of the JAVA Runtime Enviroment. I naturally set to work on a resolution and here is what ultimatly solved this problem. I noticed that the JRE install worked on a freshly installed copy of the OS, but after applying security settings. . .they immediatly failed. I was able to root cause our failure to two registry keys. Below is the details:
Please note, that we had this 1330 error due to the fact that our prodcuts are NOT connected to the internet, hence they are off-line.
This solution worked for: XP, 2003, 2008, Vista, and 7.
WARNING: I cannot be held responcible for any issues the below may cause. Plase use at your own risk. Take good notes so that you can restore the two settings below to the origional “as found” values. Exporting the registry key before making changes is a good idea, that way it can be restored.
Basically there are two registry keys that can cause the JRE install to fail with 1330.
The first Key is a very complex beast. It is located in:
Registry Hive: HKEY_CURRENT_USER
Subkey: \Software\Micorosft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing\
Value Name: State
The default windows value for “STATE” should be 23c00 in hexidecimal / 146432 decimal. If the STATE value is NOT 23c00, then someone, or something changed this setting for a reason, and this is the root cause of your problem. Now, the quick and easy fix is to set this key back to 23c00 HEX. But you may want to investigate why this value was changed. . .it may be important.
The resason why this settin is complex, is because the BINARY representation of this number activates a bunch of switches related to .NET security. 23c00 = 100011110000000000 in BINARY. Look at page 65 of this document (http://iase.disa.mil/stigs/checklist/dot_net_checklist_v1r2-3.pdf) to understand the individual switches, as I don’t have the time to explain them here. Please note however, that the DISA / FDCC / CIS security requriments for this setting is 10,000 HEX or 010000000000000000 BINARY, or 65536 DECIMAL. If you see this number, some form of security has been applied to your box. Please note that 10800 HEX, or 010001000000000000 BINARY, or 69632 DECIMAL will also fix the JAVA 1330 problem.
Lastly, since this setting is based on a “per-user” basis. . .this is the reason why logging into another account to install JRE sometimes works. If this setting was set in a LGPO or GPO. . .then all accounts would fail. But if this setting was only set in for one user, then all other user accounts would allow JRE to install.
NOTE: The above setting will fix 1330 ONLY if JRE had been installed previously. If the above setting does not resolve the problem, then you will need to change the following registry as well.
Registry Hive: HKEY_LOCAL_MACHINE
Value Name: DisableRootAutoUpdate
Quite simply, this registry value should NOT exist in a default install of any Windows OS. Once again, if this value exists someone or something has applied some level of security to the computer. Simply deleting this value (in conjunction with the about value) will enable JRE offline to install without errors.
PLEASE NOTE: It’s probably a good I idea to put these registry settings back to how your found them when you are done installing JRE. . .as these settings are important security settings.
Hope this helps,
Clustered Server Issues
I was able to upgrade SQL Server 2008 with Service Pack 3 in a formerly clustered server by changing the following registry key from 2 to 0.
HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft --> WindowsNT --> CurrentVersion --> Cluster Server --> ClusterInstallationState
Move All SQL Server System Databases
Copied directly from:
There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought of wiriting an article for moving the databases so that the users can use the same and enjoy the task.
Tasks for moving system databases:
1.Moving tempdb databases.
a.) Execute the script below.
alter database tempdb
(NAME = tempdev,FILENAME=‘NEW PATH’);
alter database tempdb
(NAME = templog,FILENAME=‘NEW PATH’);
b.) Restart services.
c.) Confirm path of database files.
2. Moving model and msdb databases.
a.) Execute the script below.
alter database msdb
(NAME = MSDBData,FILENAME=‘NEW PATH’);
alter database msdb
(NAME = MSDBLog,FILENAME=‘NEW PATH’);
alter database model
(NAME = modeldev,FILENAME=‘NEW PATH’);
alter database model
(NAME = modellog,FILENAME=‘NEW PATH’);
b.) Stop services
c.) Copy the files to the new location
d.) Restart services.
e.) Confirm path of database files.
3.) Moving master database:
a.) Edit the startup parameters to reflect the new path for –d, –l and -e parameters.
b.) Stop the services.
c.) Move the master and resource database files to the new location
d.) Change the sql port to different one than usual to avoid excess connections and create an alias with that port
e.) Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)
f.) Execute the script given below from sqlcmd
alter database mssqlsystemresource
(NAME = data,FILENAME=‘NEW PATH’);
alter database mssqlsystemresource
(NAME = log,FILENAME=‘NEW PATH’);
alter database mssqlsystemresource set READ_ONLY;
g.) Stop the services
h.) Change the port back to 1433
i.) Remove the alias
j.) Start sql services.
k.) Confirm if the data files and log files reside on desired path.
Microsoft Excel data import to SQL Server
This is a very common task, data import to SQL Server from Excel. Load the Excel file with First Row as Header off even it contains the header. This can ensure all the fields are read in text format. And then add a Conditional Split to skip the first row.
Oracle Date Comparisons
Comparing date ranges is a frequent action completed in Relational Database Management Systems. One of my favorite ways of decided whether an arbitrary date is between two other dates is use of the word BETWEEN in SQL Server. As luck (or something else) would have it, Oracle also has a BETWEEN function. This is a good thing! However, make sure the dates you’re comparing are actually of the date data type OR if they’re of the timestamp data type. And it’s very possible you may hardcode the date value so you would have to use the to_date(‘dd-mmm-yy’) syntax to convert your hardcoded value from a string data type to a date data type.
Oracle Table Details
Desc is a good way to find out vital details about an Oracle table. An even better was is to use SQL Developer, highlight the table name, right-click (or hit Shift – F4). Not only do you get data types, but also other vital details like table dependencies and primary key details.
I thought I knew PL/SQL fairly well, but there’s always something to learn. Today’s lesson involved the dreaded ORA-01971: not a SELECTed expression error. Long story short, it’s not cool to have an Order By and Distinct function in the same Select statement. CORRECTION: just found this bit of wisdom on stackoverflow.com: “When doing a DISTINCT your order by needs to be one of the selected columns.”
Obviously my Order By was incorrect. Oh well. Actually doesn’t matter a bit b/c it’s going straight into SQL Server 2008 R2 Enterprise Version.
Locked Out Of SQL Server??
How to get back in SQL Server 2005 / 2008 if you are locked out AND have local admin rights on your server:
- Start the instance of SQL Server in single-user mode by adding ;-m to the end of the startup parameters
- Start command prompt as local administrator
- Connect to the server:
sqlcmd -E -S . (be sure to add the period!)
- Add yourself (computername\username) to the sysadmin SQL group:
- create login [computername\username] from windows;
- exec sp_addsrvrolemember ‘computername\username’, ‘sysadmin’;
- Restart the server in normal mode