You are currently browsing the category archive for the 'Tools' category.
For those into these kind of things, the excellent Kryoton Suite has reached the grand old age of 3 – its a superb control suite for use in Windows Applications and allows you to create the most eye catching software. It is also very reasonable for us developers on a budget 
.
Are you having issues installing Google Chrome? Cant get the install because of it being blocked? Well , here is is the link to full install if you need it.
http://cache.pack.google.com/chrome/install/149.30/chrome_installer.exe
If the above link no longer works, it because there is a newer version available. Simply check what the latest version is and replace 149.30 in the link with whatever the version is.
SQL Server 2008 Express tools are now available…
- SQL Server database engine – create, store, update and retrieve your data
- SQL Server database engine – create, store, update and retrieve your data
- SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases
- SQL Server database engine – create, store, update and retrieve your data
- SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases
- Full-text Search – powerful, high-speed engine for searching text-intensive data
- Reporting Services – integrated report creation and design environment to create reports
The script presented in this article is for SQL Server 2005 or SQL Server 2008.
Here’s the problem; SQL Server has no problem when you create a Stored Procedure that references a non-existing table. It has no problem when you use a column that is simply not there. It is not validated until it’s executed. All that is checked (parsed) is the syntax. This is made worse when the structure of your database changes or people within your organisation come and go and those one-off or test procedures are left in your database.
How much dead wood is left in your database? Who wants to remove a procedure, it may be used by some process somewhere but no one is actually that sure. Who is going to take the risk?.
Well, there are a number of ways to see procedures that are no longer in use – you can check the procedure cache and list out procedures that dont exist in the cache. This is quite a quick and simple way to see procedures that have not been used in a while. However, there might be procedures that are still valid – they still may work if run, whenever that may be. They still might be valid.
What we really want is a quick way to actually check the procedures to see if they still work against our current structure. We want to do this without actually executing them and potentially causing a 3 year old bug to appear or wipe out some table because a particular procedure was created to do just that, once , at some point in our application’s history.
As it turns out, it isn’t that straightforward but it can be done.
What I am going to show you here is a script which will obtain a list of your procedures, grab their parameters and then check each procedure , one at a time; if it turns out they are not valid they will be added to a temp table for listing at the end of the script.
First, I will outline the key steps. Then I will show the full script you can use. The first part of process is to list the procedures , their parameters and dummy (NULL) assignment. This will be used to execute the procedures.
SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
Next, when it has the list of procedures it creates a test harness for the procudure. E.g.
CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC up_TtDay_insert @Id=NULL;
SET FMTONLY ON;
EXEC up_TtDay_insert @Id=NULL; SET FMTONLY OFF
Then, finally it runs the test harness procudure; removes it and then moves onto the next procedure. Any errors are logged in a temporary table.
Use this script without warranty, we have found it very useful but please use wisely and in non-production environments until you are personally happy with it’s output:
SET NOCOUNT ON;
DECLARE @tblInvalid TABLE ( -- invalid objects
[type_desc] NVARCHAR(60), [name] sysname, [error_number] INT, [error_message] NVARCHAR(2048), [type] CHAR(2) );
DECLARE @type CHAR(2); -- object type
DECLARE @type_desc NVARCHAR(60); -- object type description
DECLARE @name sysname; -- object name
DECLARE @params NVARCHAR(MAX); -- parameters
DECLARE @sql NVARCHAR(MAX); -- dynamic sql
DECLARE curObjs CURSOR LOCAL FAST_FORWARD FOR
SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
OPEN curObjs;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM curObjs INTO @name, @type, @type_desc, @params;
IF (@@FETCH_STATUS != 0) BREAK;
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_compare_dbs_temp_invalid_sp_check;
SET @sql = N'
CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC '+ @name +' '+ @params +';'
PRINT @sql
EXEC (@sql);
BEGIN TRY
BEGIN TRANSACTION;
SET @sql = N'SET FMTONLY ON; EXEC '+ @name +' '+ @params +'; SET FMTONLY OFF;';
PRINT @sql;
EXEC (@sql);
ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
IF ( ERROR_NUMBER() != 0 )
INSERT INTO @tblInvalid
SELECT @type_desc, @name, ERROR_NUMBER(), ERROR_MESSAGE(), @type;
END CATCH
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_sqldev_SPCheck_exec;
END;
CLOSE curObjs;
DEALLOCATE curObjs;
SET NOCOUNT OFF;
SELECT [type_desc], [name], [error_number], [error_message]
FROM @tblInvalid
ORDER BY CHARINDEX( [type], ' U V PK UQ F TR FN TF P SQ ' ), [name];
GO
I want to give most of the credit for this script to Hue Holleran, a personal friend of mine who regularly presents at SQL Server themed events. For those of you who caught his presentation on SQLCMD at the March 2008 SQLBits, he won presenter of the day. Myself and Hue bounced around various of versions of this until Hue came up with the winning script. This is also forming part of a bigger script Hue is working on , so stay tuned for an follow up post that I hope Hue does to explain this.
If you have any suggestions for improvement or have any general questions about this please comment on this blog entry.
I must admit for a pure SQL guy I am a fan of GUI development and nothing is more appealing than a well developed Ribbon control. Well, the people behind the Krypton Ribbon have a new release on the way and it includes a really cool Gallery feature as seen in the 2007 Office Suite.
If you want to see what the Gallery looks like check out this blog entry from the source; Component Factory Blog
If you don’t mind giving out your email, they offer a FREE trial of the Ribbon and Navigator package. There’s also the excellent totally FREE Toolkit available. Go grab it now from:
Component Factory Download (Krypton Ribbon, Navigator and Toolkit)
Came across a neat tool today – I am not huge fan of Linux but I do like the ability to have multiple desktop spaces where you can divide up your work and maximise screen real estate. Well, you can now do this in Vista (or XP).
Try this out for yourself , its a little rough around the edges and it is still in beta but for those of you (like me) who like to be a little structured and organised this is a great gem of an app.
Do you need to use the excellent RMLUtilities (in particular ReadTrace) against SQL Server 2008 trace files? Well, ReadTrace does not support 2008 yet but if you need to convert your files , please try this free utility:
This is a command line tool, simply jump to the command line and use as follows:
trace.convert.exe -iC:TraceFiles (or any other folder).
Trace Convert will then convert all trace (.trc) files in this folder so that they can be used with ReadTrace. You can optionally include -r so that is converts trace files in all sub folders (recursively).
If you have a new SQL 2008 database and have used the new datatypes, this tool is not guaranteed to work as expected and you should wait until the new readtrace has been released that is compatible with SQL 2008. However, this is very handy for upgraded SQL 2005/2000 databases where you want to analyse trace output of re-playable traces.
Give it a try and see if it helps you..
I have been doing some benchmark/tracing for Katmai and came across the excellent RML Utilities. However I wanted to blog about a bug in this which has meant that the ostress tool couldn’t be used to replay the trace.
ReadTrace allows you to read a trace file and output/split this into RML files (one per spid). You can then do some fancy stuff with ostress to replay these files. However, readtrace appears to have a bug where it incorrectly parses your trace file if you have used named parameters in your rpc calls.
This is an example call from a trace file
exec up_Table_Search @tableid=1, @namedpar1 = N‘test’, @namedpar2 = 2, @namedpar3 = N‘test2′
When this is parsed by readtrace, it will produce a RML file that calls the proc like this:
exec up_Table_Search 1, ‘test’,2,‘test2′
Why is this a problem? Well, what if the order of parameters isn’t the same in the proc..
ReadTrace does not seem to cater for named parameters and products an RML file which can potentially either cause data corruption or fail to run at all if the datatypes are incompatible.
Please , someone let me know if there is a command line switch to fix this. There doesn’t seem to be.
