EC2 and SQL Server Micro

As we are learning the new EC2 Amazon Cloud Services, I am compelled to post a “how to” when setting up and connecting to an AWS EC2 SQL Server box. This will answer many questions for others so they can get around the few issues that will creep up when Launching a new EC2 SQL instance. This same information can be used for other server launches too. Continue reading

Change Joomla Table Prefix

We are interested in your security and performance! To improve performance and enhance security of the Joomla platform be sure to provide a new prefix for Joomla database tables. This can not only provide improvements as indicated about security and performance, but it can provide a unique instance of the tables so more tables and Joomla installations can be performed. This may not be the case in your situation, like for single company installation, however it can provide a self-documenting installation. That’s to say that when you see the tables for a company “Joe’s Hardware Shop” in your database you can easily recognize the owning tables by the prefix. It’s more recognizable as to which business the database belongs to.

Just in case you installed Joomla with the default “jos_” table prefix, here are the instructions to change the prefix to something else to improve security. Notice:  follow the instructions as they’re listed and it will minimize downtime since youre website is running. Here at SnapJag Creative Designs we want our instructions are such that you can feel assured that you are able to perform the steps with accuracy, efficiency, and with as little impact as possible to your operations.

It is highly recommended that you perform these steps at a low-peak time. But make sure you are awake enough to follow the instructions if you’re doing it at night. =) Note: This method does not ask you to remove the tables first, so don’t delete the old “jos_” tables until the end. This is to help make sure you have a backup in place and that the old structure is still ready in case anything goes wrong.

  1. Kick people out: make sure no one is doing administration or management in the Joomla back-end.
  2. Login to phpMyAdmin.
  3. Click on the Export link, leaving all options as they are; specifically for:
    1. Export: SQL
    2. SQL options: SQL Export compatibility = None
    3. SQL options: Structure (Add AUTO_INCREMENT, Enclose table and field names with backquotes)
    4. SQL options: Data (Complete inserts, Extended inserts, Max Length: 50000, Use hexadecimal for binary fields, Export type: INSERT)
    5. Save to file: not on, you will use the query box to copy and paste the values to disk. If you have large content then use the method to capture to the server and retrieve the data.
  4. Click the Go link, this export may take a few minutes depending on the size of your content.
  5. Open your favorite editor (like UltraEdit), copy the content from the query box and paste it to a new text window in the text editor.
  6. Save the file as a new name to make a backup of the old data.
  7. Save the file again as a new name to make a copy of the new data.
  8. Do a search and replace for “jos_” and replace with the four character prefix of your choice. If you are using UltraEdit, choose to see a line listing of all results and scan them to make sure replacements didn’t happen with any other data than that of table name references.
  9. Comment out with dashes (‘–‘) the statement ‘Create Database…’ if it exists in the script so that an attempt to create the database doesn’t occur. This will cause an error, no damage, just an error and you’ll have to do this step and run it again if you don’t.
  10. Click on the SQL Query Window link to bring up a new SQL query window. You can’t do this next step in the same window as the query box output.
  11. Copy the text from the text editor and paste them into the SQL Query Window where it says “Run SQL query/queries on server localhost:”
  12. Click the Go button to begin the script to create the new tables.
  13. Wait for the response from the server in the browser window behind the SQL Query Window. Address any issues if you need to.
  14. If the response is not an error. Close the SQL Query Window show the phpMyAdmin home window.
  15. It’s possible the left side of the browser will say “No Tables Found”. Don’t worry, press F5 to refresh the window, and you’ll see all the new tables.
  16. Open your Joomla! back-end, go to Site | Global Configuration | Server
  17. Change the Database Settings | Database Prefix to your new prefix and click Save
  18. You may now be required to sign-in again. You will now be connected to your new tables.
  19. One more step is to delete the old ‘jos_’ tables. Go back to the phpMyAdmin window
  20. Click the Databases link, then the database where the tables exist
  21. Select all the ‘jos_’ tables, be careful to not select any new tables.
  22. At the bottom of the list, click the combo box “With Selected” and click Drop. This will build a script to remove the selected tables, confirm they are only the ‘jos_’ tables, and click Go.
  23. Wait for a ‘Executed Successfully’ statement and then
  24. Close the myPHPAdmin window
  25. For one final test, back in Joomla! logout and log back in and create some articles or do normal things to test the functionality.
  26. Also, confirm in the Site | Global Configuration | Server screen that the Database Prefix is set accordingly.

Hope this has been thorough and helpful. If you see any problems, errors, or changes that need to be made, please let us know.

SQL variables can act on each other

There are many situations when you have a variable in a stored procedure and you want to add to it. Well, there is definately a slick way to build on variables within a single select statement. It’s necessary to reveal how this works in detail because it’s difficult to explain. So, the answer is in the statement.

DECLARE @Number1 numeric(14,2)
DECLARE @Number2 numeric(14,2)
DECLARE @Number3 numeric(14,2)
DECLARE @Total numeric(14,2)

-- Step 1: One way to to set variables is like this, on individual lines
/*
SET @Number1 = 15
SET @Number2 = 25
SET @Number3 = 50
*/

-- Step 1: or, as I like to do it, use the SELECT because I can assign multiple variables at once.
SELECT @Number1 = 15
, @Number2 = 25
, @Number3 = 50

-- Step 2: then, add the numbers together traditionally.
SELECT @Total = @Number1 + @Number2 + @Number3
SELECT [Total] = @Total

In a recent project it was necessary to grab a lot of numbers from multiple tables, perform calculations with them, and then return the results. The trick to assign multiple variables at once as indicated above yields the results of the simple trick. Instead of adding all the numbers in separate lines, do it all in one SELECT statement. Fast efficient results.

SELECT @Number1 = 15
, @Number2 = 25
, @Number3 = 50
, @Total = @Number1 + @Number2 + @Number3

SELECT [Total] = @Total

It’s great that I can reference previous variables without having to write individual SELECT or SET statements. Makes for very slick code. Just make sure that if you move the variables around, they stay in the descending order of dependency or your numbers will be off.

SQL Recompiles

Here are some articles that describes the need to reference the owner (schema) of SQL Server stored procedure when called by applications. Also, making sure that the calls are sensitive to the case of the schema.ProcName.

Following these suggestion can reduce CPU activity, needless recompiles, and prevent extra procedure cache examinations, which reduces the profiler SP:CacheMiss activity and subsequent contention.

Continue reading

SMO SQL Server Error Reading Stored Procedures Collection

The following error is appearing while running Visual Studio 2005 and developing an application with SMO capabilities. It appears when trying perform a foreach( on ActiveDB.StoredProcedures).

Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. An attempt was made to load a program with an incorrect format.

I found an MSDN article that recommends downloading the 64 bit SMO assembly. Continue reading

ORACLE initialization or shutdown in progress

Error “ORA-01033 ORACLE initialization or shutdown in progress” plagued me twice, not a third, unprepared!

Since this was the second time this happened to me so I decided I should get all my ducks in a row cause if it happened twice, it will most likely happen a third time, if not to me, then to someone else.

The reason this happened was because my automated script that restores two databases and does everything to shut down and restart the instance got fowled up. It fowled up when I accidentally ran both scripts simultaneously. The first script did the shutdown operations, and the second script did the same. … which then barfed everything up and locked the files and considered everything in a shutdown state. Continue reading

.NET and MySQL

Searching for a document that helps to connect a .NET web application with MySQL is hard to find. So, I thought I would provide instructions I put together while developing my own applications.

The .NET infrastructure is very flexible and is easier to use than people think. Here are the steps that I went through to get everything working and connected. Because I program in C# this tutorial is presented in that language. This is an outline of the process and steps to accomplish this tutorial. It helps to keep the ideas in check and that there are no missing subjects.

  1. Install MySQL v5.0
  2. Install Visual Studio (use the express editions if you don’t have full versions)
  3. Install the MySQL 5.0 .NET Data Connector v5.2 (for example mysql-connector-net-5.2.1.zip) or other connector of your programming choice.
  4. Start building a database
    1. A nice application to use is ModelRight 3 community edition
    2. Create two tables and add some fields and join one of the tables to the other in a parent-child relationship
    3. Generate (engineer) the database to MySQL
  5. Open Visual Studio
  6. Go to the Server Explorer, right-click on Data Connections
  7. Click Add Connection
  8. Change the datasource to MySQL Database and make sure the Data provider is .NET Framerwork Data Provider for MySQL and click OK
  9. Login to the server with the following:
    1. Server name: localhost
    2. User name: root
    3. Password: the initial password you gave when setting up MySQL
    4. Database name: This is the name of the database you will attach to. It’s possible you haven’t set one up yet, see the Building a MySQL Database below.
  10. Create a website application using Visual Studio (File | New | Website)
  11. Pick ASP.NET Web Site (or ASP.NET AJAX-Enabled Web Site) if you have the .NET AJAX Toolkit installed.
  12. Add the connection string to Web.Config
  13. Update App_Code data layer classes to use the schema of the database
  14. Add an Object Data Connection to the web page with the following settings
  15. Put a DataGridView on the screen and connect it to the Object Data Connection object with these settings
  16. Run the application

Oracle Maximum Open Cursors Exceeded

In writing my application that checks certain settings and values every few seconds received the following error today.

ORA-01000: maximum open cursors

This error presented itself because I wasn’t closing the OracleDataReader object in my code. I’ll show below the entire code and then bold the code that I had to add to rid my code of the leak. I used TOAD for Oracle to find the opened cursors current in the Statistics of the Session Browser on the database. Continue reading