I had to recreate a database in SQL Server the other day and my database user did not have privileges to recreate the database, so I found the following code which worked like a charm to drop all the tables in the database.  This example deletes all tables that start with PS, which drops all the PeopleSoft tables:

declare @SQL nvarchar(max)

SELECT @SQL = STUFF((SELECT ‘, ‘ + quotename(TABLE_SCHEMA) + ‘.’ + quotename(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘PS%’
FOR XML PATH(”)),1,2,”)

SET @SQL = ‘DROP TABLE ‘ + @SQL

PRINT @SQL

EXECUTE (@SQL) — uncomment to actually delete the tables

As part of my post go-live procedures I was trying to do some comparisons from the pre-upgrade environment to the actual production environment.  Usually this is very straight forward and can be done using the Linked Server.  As it turned out the linked server was already configured however when I tried to do the sql lookup on the PSOPRDEFN table I got an error.

Msg 7411, Level 16, State 1, Line 1

Server ‘mvDBSERVER? is not configured for RPC

To re-enable the RCP commands for the linked server you can do it in the options GUI or from the query window:

exec sp_serveroption @server=’myDBSERVER?, @optname=’rpc’, @optvalue=’true’

exec sp_serveroption @server=’myDBSERVER?, @optname=’rpc out’, @optvalue=’true’

Now when I run [myDBSERVER].[PREUPGDB].[dbo].[PSOPRDEFN] I get the results I am looking for!

I often get asked about performance issues with SQL Server. I often find with a quick check that the data and log files for the database are being stored in terrible inefficient ways. I am not going to argue local disks versus SAN storage in this post, as more often then not my clients are moving to virtualized environments so the storage is on a SAN or on a centralized disk array of some sort.

Lets start with one big drive is not a good way to run an Enterprise Database. I appreciate that SQL Server is easy to setup and get working, but making it work efficiently and effectively takes a little more understanding then accepting the default prompts during the install.

1. Have a small drive setup to run the OS layer of the machine, I have found 40gb is usually more then sufficient for this.
2. Storing Data files: these should be stored on a drive that is within a RAID 5, 6 or 10 configuration.
3. Storing Log Files: these should be stored on a drive that is within a RAID 1 or 10 (however, 5 or 6 will work too).
4. Storing TEMP DB Files: these should be stored on a drive that is RAID 1 (however, 5,6 or 10 will work too).

In a lot of cases, items 2,3,4 are all stored on the same drive. This maybe fine for non-critical stuff, but you will want your Production environments split up. The most common drive configuration I see is RAID 5 or 6, but in bigger SAN’s you will see RAID 10 often. Now that drives are relatively cheap I find disk arrays to be very cost effective for organizations and offer up great flexibility. This is one area that I recommend buying good equipment. Additionally, take speed over capacity and quantity over capacity. The faster the data can be read and/or written makes a difference, and the more drives you have the more heads you have for reading and/or writing.

Lastly, know what is sharing your storage areas. If you have a SAN that is carved up and server multiple databases or environments, you will end up spreading the read/write ability of the equipment across those environments. This is a common problem I see where people will say there must be something wrong with PeopleSoft because it isn’t doing anything and is still slow, but if you dig into the available resources you will find another application is hammering the resources which are shared with PeopleSoft, and therefore PeopleSoft is working fine it just doesn’t have any available resources to run effectively.

PeopleSoft is a cross platform application, and some databases use tablespaces and some do not.  This error will commonly occur when using Microsoft SQL Server, and to fix this navigate to:

PeopleTools > Utilities > Administration > PeopleTools Options

Uncheck Platform Compatibility Mode

You should now be able to save your record without being prompted for tablespaces what are not present in your world!

I was moving one of my database servers to a new cloud, and I could not find the product key that I used to install the SQL Server install.  So I did some google action and found a bunch of folks that had little to no clue on how to get the real product code.  I found a dude named Laser on the gearbox software website, that is a genius, and this worked for both my 2008 and 2012 versions of SQL Server.

In the registry I navigated to the key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup] – DigitalProductID, for SQL Server 2012 it will be in the “110” entries, and it might not be actually under this entry, I found it several spots so if you don’t see it there look around a bit.

Now I did find the the 2012 tag was actually the actual 16 2-digit hex values I was looking for where the 2008 tag was 164 2-digit hex values.  In the case of the 164 code, you need to 53rd pair to 69th pair to get your 16 2-digit hex value.

Next you need to take that value and reverse the entry, so for example,

01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16 becomes:  16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01

Now remove the commas and take your 32 digit code and put it into a Radix 16 to 24 converter, I found a calculator to do it.

Lastly, for Microsoft codes you need to take your results and do one last conversion:

0 ==> B,  1 ==> C,  2 ==> D,  3 ==> F,  4 ==> G,  5 ==> H,  6 ==> J,  7 ==> K,  8 ==> M,

9 ==> P,  A ==> Q,  B ==> R,  C ==> T,  D ==> V,  E ==> W,  F ==> X,  G ==> Y,  H ==> 2,

I ==> 3,  J ==> 4,  K ==> 6,  L ==> 7,  M ==> 8,  N ==> 9

Yep, it sounds crazy, but it works!  To be totally honest, I have never worked with radix based number systems before, and I am not about to start, and honestly, I get freaked out by hex values in the registry, but those codes cost a lot of money, so if you are like me and either forget to write it down because that would be easy, or inherit a system that was installed by somebody that didn’t write it down, this might just save you!