Here is a handy script to check connections to your SQL Server database.

SELECT db_name(dbid) as DB,
count(dbid) as Connections,
loginame as Login
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
ORDER BY DB

I was recently trying to test a system for a client that was trying to do some year end testing. In order to do this testing in the past they use to simply use the Oracle Database function “FIXED_DATE” to future date their database. When you use the FIXED_DATE feature it effects the SYSDATE variable and returns a date that is not the date of the operating system.

Unfortunately, with the more current releases of PeopleSoft, they have changed several of their system calls from SYSDATE to SYSTIMESTAMP. For some reason SYSTIMESTAMP is NOT effected by the FIXED_DATE override parameter, and from everything I have read there is no override parameter for changing the SYSTIMESTAMP. This leads to the problem of how to make future date testing work within PeopleSoft.

The only real solution is to future date the operating system. However, there are some real concerns that can come with this idea. I personally would highly recommend that you clone the server you are working on and use the cloned machine for this future date testing.

My testing system is stored on RackSpace servers, and when I made the cloned server and tried to change the OS date, it said it changed it, but when you looked again, it was still the current date/time. It turned out that the RackSpace system uses XEN on the RHEL OS to keep the server times in sync. I found the best way to change this was to add the sysctl parameter:

xen/independent_wallclock = 1

Once, I rebooted the server, I had no issue controlling the OS clock.

When cloning databases in SQL Server from one server to another, you can run into the problem where the userid that is associated with the database is not valid in the security of the database engine that is hosting the new database.

An easy fix to this problem is to run the following statement:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user‘

You can list all the users that are orphaned by running the following statement:

EXEC sp_change_users_login ‘Report’

I found this video that shows you have to create a database when ASM is installed.

YouTube Video

I found that when I manually created the Oracle Database, that because the ASM dynamically names everything, I simply removed the directory & file name reference and replaced it with the +ASM group where I wanted the datafile to be located.  If you look at the ptddl script for a PeopleTools database or the hcddl script for an HR database you will see how it will say the directory+file name:

‘/oracle/oradata/dbfiles/tablespace1.dbf’ is replaced with:

‘+ASMHRORPTGROUP1’ depending on what you named it.

This little SQL is a sweet little piece of code I found somewhere in my travels.  It will find duplicate rows of data based on a keyset, however, this only works in Oracle.  I will try to find a similar piece for SQL Server, but for now here we go:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);