Category Archives: Database

How to Reset Zend Server Statistics

By | Quick Tips, SQL, sqlite, Zend, Zend Server | No Comments

Reset Zend Server Statistics

I had to work this one out myself, hopefully this will save you some time:

sudo rm /usr/local/zend/var/db/statistics.db
sudo sqlite3 statistics.db < /usr/local/zend/share/statistics_sqlite_create_database.sql
sudo sqlite3 statistics.db < /usr/local/zend/share/stats_sqlite_fixtures.sql

Zend Server 5 to 6 mysql 5.1 to 5.5 fix

By | MySQL, Software Development, Zend, Zend Server | No Comments

I recently upgraded a Zend Server installation from version 5 to version 6, a side effect of this was that MySQL server was upgraded from version 5.1 to version 5.5.

I was then receiving the error message:

“Error Code: 1548 Cannot load from mysql.proc. The table is probably corrupted”

After rebuilding the databases and trying the mysql_upgrade -u root -p command, i discovered this is just a try to fix the core issue.

The true cause of the issue is that the table definition of mysql.proc is different

To fix the mysql.proc table just run the following command :

alter table mysql.proc modify comment text;

TSQL Date Only Function

By | Code, Database, MSSQL2005, MSSQL2008, Quick Tips, Software Development, SQL Server, TSQL | No Comments
CREATE FUNCTION [dbo].[DateOnly] (@DATE DATETIME)
RETURNS DATETIME
BEGIN
    RETURN DATEADD(d, DATEDIFF(d, 0, @DATE), 0)
END

TSQL Last Day Of The Month

By | Code, MSSQL2005, MSSQL2008, Quick Tips, SQL Server, TSQL | No Comments
CREATE FUNCTION [dbo].[LastDayOfTheMonth] (@DATE DATETIME )
RETURNS DATETIME
BEGIN
    RETURN dateadd(ms, -3, DATEADD(mm, DATEDIFF(m, 0, @DATE) + 1, 0))
END

Zend Server CE on OSX 10.6 Snow Leopard

By | MySQL, PHP | No Comments

When I installed Zend Server CE on OSX 10.6 Snow Leopard the admin interface failed, the following fixed the issue :

1. Download this: Watchdog update

2. Backup watchdog with the command

sudo mv /usr/local/zend/bin/watchdog /usr/local/zend/bin/watchdogOLD

3. Extract the new watchdog to /usr/local/zend/bin/ (I used the go to folder in finder, if you can view hidden files)

4. Run the following command to restart the Zend server

sudo /usr/local/zend/bin/zendctl.sh restart

SQL Server Schema,Table,Function and Stored procedure Auditing

By | Code, iPhone, MSSQL2005, MSSQL2008, Objective-C, Software Development, SQL Server, TSQL | One Comment

If you like to know what’s changing on your SQL Server then the following code creates an audit table and the associated trigger for logging the changes

The Audit Object Table

CREATE TABLE [dbo].[AuditObjects](
 [EventID] [INT] IDENTITY(1,1) NOT NULL
,[EventData] [xml] NULL
PRIMARY KEY CLUSTERED (
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the associated Trigger

CREATE TRIGGER [TriggerAuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO [dbo].[AuditObjects](EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [TriggerAuditObjects] ON DATABASE

You can now use xpath queries to analyse the data

Search Stored Procedures

By | Code, Database, iPhone, MSSQL2005, MSSQL2008, Objective-C, Quick Tips, SQL Server, TSQL | No Comments

Ever had the need to search stored procedures to find redundant tables or text that needs replacing or re-factoring ?

Here’s a script to help (Sorry for the cursor, I’m sad with myself for using it 🙁 )

-- Search string
DECLARE @SearchString nvarchar(50)
SET @SearchString = 'Search String'
 
-- Required Declares
DECLARE @getdbname sysname
DECLARE @sqlstm nvarchar(1000)
DECLARE SeachCursor cursor FOR 
-- Get All The Names into the SeachCursor
SELECT '['+name+']' FROM [master].[dbo].[sysdatabases] ORDER BY name 
OPEN SeachCursor
-- Add the Search Pattern
SET @SearchString = '%' + @SearchString + '%'
--Get the first Name
FETCH NEXT FROM SeachCursor INTO @getdbname
WHILE @@FETCH_STATUS=0
	BEGIN
	--set the statement to define the search condition, with variables
	SET @sqlstm = '
	SELECT [SP].[Specific_Catalog] AS [Database_Name], [SP].[Routine_Name] AS [Stored Procedure Name],[SP].[Routine_Definition] AS [Routine_Definition]
	FROM '+ @getdbname+'.[Information_Schema].[Routines] AS [SP]
	WHERE PatIndex('+''''+@SearchString+''''+', [Routine_Definition]) > 0'
	--Execute the Query
	EXEC (@sqlstm)
	FETCH NEXT FROM SeachCursor INTO @getdbname
	END
--Close the Cursor and Deallocate it from memory
CLOSE SeachCursor
DEALLOCATE SeachCursor

SQL 2008 Enable Changes To Tables

By | Database, MSSQL2008, SQL Server, TSQL | 13 Comments

If you get the error in the SQL 2008 Management Studio :

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

You can enable changes inside the Microsoft SQL Server Management Studio by going to ‘Tools|Options|Designers’ then unchecking the ‘Prevent saving changes that require table re-creation’ option

Autostart Zend Core on Ubuntu

By | MySQL, PHP, Platform, Unix/Linux | No Comments

This is the 2nd time I have had to search for starting up Zend Core automatically on Ubuntu Workstation, so here is the answer:

First change the permissions of /etc/rc.local by opening a terminal window (Applications|Accessories|Terminal) and enter the following command:

sudo chmod 777 /etc/rc.local

Then open this file with the text editor (Applications|Accessories|Text Editor) and put the following text before the exit 0 command

cd /usr/local/Zend/Core/mysql && ./bin/safe_mysqld &
/usr/local/Zend/apache2/bin/apachectl start &

Zend Core, PHP and MySQL should now all start automaticaly at boot time.

TSQL Count Number Of Stored Procedures, Views, Tables or Functions

By | Code, Database, MSSQL2005, MSSQL2008, Software Development, SQL Server, TSQL | 16 Comments

You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view

/* Count Number Of Tables In A Database */
    SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'
    /* Count Number Of Views In A Database */
    SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS
    /* Count Number Of Stored Procedures In A Database */
    SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Count Number Of Functions In A Database */
    SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

The same methodology can be used to query for information :

/* Select Table Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
    /* Select View Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    /* Select Stored Procedure Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Select Function Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'