New, LOW Price for EZ SQL Restore

We have just announced an all new, lower price for EZ SQL Restore. This makes our powerful drag and drop database utility even more affordable! Check out the new prices here: http://www.hawkesoft.net/Purchase.aspx

HawkeSoft SQL Tips and Tricks - Part 6 - The Power of OBJECTPROPERTY

OBJECTPROPERTY is your one-stop shopping function for finding out all kinds of system information on database objects. It is especially powerful for triggers, contraints, and indexes. The sample code below shows one of my favorite uses for this function, finding disabled and untrusted constraints. This can be extremely useful when bad data has been introducted into a system, or to check clean up code after a data migration/conversion. (SQL 2000/2005)

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsDisabled') = 1

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1

To read more check out http://msdn.microsoft.com/en-us/library/ms176105.aspx.

HawkeSoft SQL Tips and Tricks - Part 5 - Move tempdb's Data and Log Files

A common oversite when setting up SQL Server is to leave the system database's data and log files on the system (C:) drive. These databases generally stay at a small size except for one, tempdb. tempdb can grow quickly and unexpectedly, bringing your server down before you know it. To easily move tempdb's files to a safer, larger drive simply customize the code below. (SQL Server 2000/2005)

ALTER DATABASE tempdb MODIFY FILE (name=tempdev, filename='D:\MSSQL\Data\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE (name=templog, filename='D:\MSSQL\Data\templog.ldf')

HawkeSoft SQL Tips and Tricks - Part 4 - Script All Triggers in a Database

Triggers in SQL Server are often treated as being directly tied to the table they fire off of. This makes sense in most scenarios but sometimes they need to be treated more as code objects. Most SQL tools make scripting triggers out a sub function of scripting tables. This script allows you to script out all triggers on a given database without concerning yourself with the tables (SQL Server 2005, large script).

Make sure to run this script in TEXT output mode and have a large buffer in your output window to handle large triggers.

-- USE the database you wish to script all triggers out of
USE [Database]
GO

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[DBAGenerateTriggerScript]
(
@TriggerName varchar(255)
)
AS
/******************************************************************************
** Name: DBAGenerateTriggerScript
** Desc: Generates the DROP and CREATE statements for the given @TriggerName.
** IMPORTANT: Run this script in TEXT output mode
** Make sure you have a large buffer in your output window to handle large triggers
**
** Auth: ghawkes
** Date: 07/07/2008
*******************************************************************************/

SET NOCOUNT ON

-- Check to make sure the stored procedure exists
IF(NOT EXISTS(SELECT object_id
FROM sys.all_objects SO
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')))
BEGIN
PRINT '''' + @TriggerName + ''' is not a valid trigger on the ''' + DB_NAME() + ''' database.'
RETURN
END

DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the comment header
SELECT '/***** Object: Trigger [dbo].[' + @TriggerName + '] Script Date: ' + CONVERT(varchar, GETDATE(), 100) + ' *****/' + @NewLine + @NewLine

UNION ALL

-- Generate the DROP statement
SELECT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TriggerName + ']'') AND type in (N''TR''))' + @NewLine +
'DROP TRIGGER [dbo].[' + @TriggerName + ']' + @NewLine + 'GO' + @NewLine + @NewLine

UNION ALL

SELECT 'SET ANSI_NULLS ON' + @NewLine +
'GO' + @NewLine +
'SET QUOTED_IDENTIFIER ON' + @NewLine +
'GO'

UNION ALL

-- Generate the CREATE statement
SELECT
ISNULL(SM.definition, SSM.definition)
FROM sys.all_objects SO
LEFT OUTER JOIN sys.sql_modules AS SM ON SM.object_id = SO.object_id
LEFT OUTER JOIN sys.system_sql_modules AS SSM ON SSM.object_id = SO.object_id
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')

UNION ALL

SELECT @NewLine + 'GO' + @NewLine
GO

-- Invoke the trigger script creation procedure for all triggers on the database
DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the USE statement
SELECT 'USE [' + DB_NAME() + ']' + @NewLine + 'GO' + @NewLine + @NewLine
DECLARE @SQL varchar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC DBAGenerateTriggerScript ''' + ST.name + '''' + ' '
FROM sys.triggers ST

EXEC(@SQL)
GO

-- Drop the trigger script creation procedure
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBAGenerateTriggerScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBAGenerateTriggerScript]
GO

EZ SQL Restore - User Feedback Survey

At HawkeSoft we always value our customer's opinions. If you have the time please take the survey below so that we can better serve you going forward. Thank you for your time!

(You will be re-directed to another site)
http://www.surveymonkey.com/s.aspx?sm=h5Hm6k7mpkm_2bPPLIOfOmCA_3d_3d

HawkeSoft SQL Tips and Tricks - Part 3 - Getting Excel Column Headers

It has been a while since we have posted SQL Server Tips and Tricks so today is a 2 for 1 special!

Sometimes database administrators and data architects need to paste table result sets into Microsoft Excel. This is easy to do but sometimes the column list is so long that re-typing it can be tedious. In SQL Server 2000 copying from the results window in Enterprise Manager would retain the column headers. For some reason this functionality appears to be removed from SQL Server 2005 SSMS.

The code below will generate the column header row for easy pasting into Excel, Word, etc... For best results run the script in grid output mode and make sure you have a large buffer for the output window (SQL Server 2005).

DECLARE @TableName varchar(255)
SET @TableName = 'TableName'

DECLARE @TableID int
SELECT @TableID = object_id
FROM sys.tables
WHERE [name] = @TableName

IF(@TableID IS NULL)
BEGIN
PRINT 'Table [' + @TableName + '] does not exists in database ' + DB_NAME() + '.'
RETURN
END

DECLARE @SQL varchar(max),
@MaxColumnID int
SET @SQL = 'SELECT '
-- Find the max column ID for the fencepost problem
SELECT @MaxColumnID = MAX(column_id)
FROM sys.columns
WHERE object_id = @TableID

SELECT @SQL =
CASE WHEN column_id <> @MaxColumnID
THEN @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + '], '
ELSE @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + ']'
END
FROM sys.columns
WHERE object_id = @TableID
ORDER BY column_id ASC

EXEC(@SQL)

HawkeSoft SQL Tips and Tricks - Part 2 - Determine Exact SQL Server 2005 Version

Sometimes it can be a real pain to find something as simple as version information. The script below will return such information as product version, level, edition, and more (SQL Server 2005).

SELECT SERVERPROPERTY('productversion') AS [Product Version],
SERVERPROPERTY ('productlevel') AS [Product Level],
SERVERPROPERTY ('edition') AS [Edition]
SELECT @@VERSION AS [Version]
SELECT @@MICROSOFTVERSION AS [Microsoft Version]

HawkeSoft SQL Tips and Tricks - Part 1 - Writing SQL with SQL

This is the first in a series of posts describing SQL tips and tricks for SQL Server 2000/2005/2008. We at HawkeSoft hope you enjoy this series, and as always, we welcome your feedback!

Writing SQL with SQL:
Want to know the number of rows in every table in your database? No problem, until you realize your database has hundreds of tables. Try the code sample below for a quick dynamic SQL solution (SQL Server 2005).

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT COUNT(1) AS [' + ST.name + ' Count] FROM ' + ST.name + ' ' FROM sys.tables ST

--SELECT @SQL
EXEC(@SQL)

As you can see this approach is very powerful and can be applied in many different situations such as database maintenance commands, code re-compilations, updating statistics, etc...

If this type of code interests you should also check out the undocumented stored procedures sp_MSforeachtable and sp_MSforeachdb.

Remember, when dealing with code that has the potential to update several objects at once in your database ALWAYS make sure it is going to do what you anticipate, and consider the performance impacts. Happy coding!

Musings of a Startup - Part 2 - More Advertising

Hello again, things on the advertising front have come a long way since my last post. I am now getting plenty of clicks. I am hitting my daily ad budget on Google Adwords nearly everyday. Also, using Google Analytics I am tracking download conversions and getting between a 25%-50% daily conversion rate. So far so good!

I am finding Google analytics an incredibly useful tool for learning about the traffic hitting the HawkeSoft website. In addition to conversions it is very interesting to see what countries are hitting the site the most (India and the United States are the clear leaders at this point).

Well, advertising is coming along well now, time to see if it starts translating into sales. If anyone has questions about the EZ SQL Restore application, or has run into any problems just let me know.

- Grant

Musings of a Startup - Part 1 - Advertising

HawkeSoft is my first startup, and I am having to learn many new things... fast!

Google AdWords and Microsoft adCenter are my newest interests. Trying to get the news out on a new company/product is an interesting task. I must admit that working with the ad campaigns is actually quite fun on some level. It connects to my interest in fantasy sports and the stock market from a pure number crunching standpoint.

Has anyone else worked with these ad technologies and had any kind of epiphany on how to get clicks? So far I am registering thousands of impressions and almost no clicks (which means not too many people are probably reading this blog... hmm).

I am also considering submitting my application to well-read blogs and other online publications.

So far the whole startup venture has been very rewarding, and interesting to say the least. It appears as though my first musing has turned into more of a rambling... be back soon.

- Grant

EZ SQL Restore enhancements brainstorming

Now that EZ SQL Restore has launched we can focus on working on improvements and enhancements.

Some items that jump to mind are:
Support for other database platforms, mySQL most likely being at the top of the list.
SQL Server 2008 Support
Windows Server 2008 Support (this item is in fact, already in progress, EZ SQL Restore functions on Windows Server 2008, but some security tweaks are required)
... Any other suggestions anyone has in mind? Let us know!

Welcome!

Welcome to the HawkeSoft development blog! This is the location for all my product updates, ideas, musings, etc... I will try to keep everyone up to date with the products we are developing and enhancing. Most of the posts will be technical, but along the way let's try to have some fun. I welcome your feedback so please let me know what you think!