Author: sqldba

Editorial – Don’t Criticize Code
By Phil Factor
SQLServerCentral.com

After many years spent supervising development teams, I’ve come to recognize warning signs of problems ahead. There are many such signs, but the following is one that always makes me cringe: a developer looks at code that he or she is assigned to maintain, and says something like “Who one earth wrote such stupid and xxxx (unstructured/ mindless/ unintelligible/etc.) code?”

There is a developer showing his inexperience. Only adolescents writing their first application in some exciting new computer language in their bedroom ever believe that it is possible to write perfect code, or even that they are capable of doing it. Intensive commercial experience as a developer normally grinds off the idealism and naivety that inspires this attitude towards legacy code.

Never is it a good idea to criticize legacy code, let alone the poor developer responsible for it, particularly if you don’t know the history of the application and the circumstances of the code being written. If you don’t show the code due respect, you are in danger of refactoring it into something worse. You probably aren’t aware of the circumstances under which the code was written, or the real brief given to the programmer. The pressures of coding in a commercial development project are difficult to describe, but it is perfectly natural under such conditions to take decisions that, in retrospect look daft, but which actually saved the project.

Legacy code is something I pore through with eagerness, as if it were a rich historical archive; even if it merely illustrates the coder’s drift into lunacy. Many times, when doing so, I have been jolted out of a growing sense of contempt by coming across a technique or insight that is brilliant. This real sense of humility, when faced with the results of human endeavor under pressure, always served me well in my work as a consultant. When you’ve been called in to fix a project that is about to hit the wall, making any suggestion of criticism is probably the least helpful thing you could possibly do. Beyond the easy job of fixing things, comes the more difficult trick of tactfully attributing their original predicament to a cruel act of Fate.

For anyone eager for a long-term career in IT Development, humility and tact are probably more important habits to acquire than demon coding-skills.

SQLServerCentral.com

CREATE FUNCTION [dbo].[fnTitleCase]( @text AS varchar(8000) )

RETURNS varchar(8000)

AS

BEGIN

   DECLARE @Reset bit;

   DECLARE @Ret varchar(8000);

   DECLARE @i int;

   DECLARE @c char(1);

   SELECT @Reset = 1, @i=1, @Ret = '';
   WHILE (@i 
		

In Visual Studio I usually get errors when using 3rd party controls due to the assemblies not being deployed along with the application.

To resolve this, I put copies of the control assemblies in a sub-folder of the project called “References” and then add those files to the references in the project references in the solution explorer using browse.

You can then go to the properties page of the reference and set “Copy Local” to true. This will ensure that the module gets deployed to the “bin” folder of the app.

Had a problem with this after importing data from one database to another using “SELECT INTO” over a link. A co-worker developed this, so kudos go to him…

declare @table_name varchar(50)

declare @column varchar(50)

declare @datatype varchar(50)

declare @nullornot varchar(15)

declare @length varchar(3)

declare @getprops cursor

SET @getprops = CURSOR for
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = ‘YES’

   THEN ‘NULL’

   ELSE ‘NOT NULL’

   END AS NLL

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = ‘Metrics’

 AND COLLATION_NAME = ‘SQL_Latin1_General_CP1_CI_AS’
OPEN @getprops

FETCH NEXT

FROM @getprops into @table_name, @column, @datatype, @length, @nullornot

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT N’ALTER TABLE ‘ + @table_name + N’ ALTER COLUMN ‘ + @column

PRINT @datatype + N'(‘ + @length + N’) COLLATE SQL_Latin1_General_CP850_CI_AS ‘ + @nullornot

PRINT N’GO’

PRINT N”
FETCH NEXT

FROM @getprops into @table_name, @column, @datatype, @length, @nullornot

END

CLOSE @getprops

DEALLOCATE @getprops

GO

Log into the server’s console and connect as the system administrator, then reset the SYS and SYSTEM password.