For cross compatability between sql and mysql servers when we do a division on a aggregated variable like count
we need to promote the INT to a DOUBLE type by multiplying it by 1.00 otherwise our results will loose precision

Examples


1/2=0 Incorrect Results
1.00/2=.5 or 1.00/2.00=.5 Good

For sake of consistency both variables have been promoted to DOUBLE


SELECT ROUND((COUNT(FieldA) * 1.00) / (COUNT(FieldB) * 1.00) * 100, 0)
FROM TESTTABLE


Possible solution 1

I am using jtds driver so I would suggest checkingout their proposed solution first here http://jtds.sourceforge.net/faq.html#instanceGetInfo

Possible solution 2

Their solution did not for me so here is what I did :
From cmd prompt run
sqlcmd -L and make sure that the server you are connecting is listed in the returned list, if its not then there is your problem.
Simply restarting ‘SQL Browser’ and ‘SQL Server’ should work, run you sqlcmd -L command and make sure that your server is visible in the list.

Problem

When we add default constraint on a field it is automatically assigned a name in following format

Format:
DF__tablename__PARTOFFIELDNAME__HASHCODE
Example:
DF__scheduled__CREAT__00DF2177
DF__scheduled__MODIF__01D345B0

HashCode part of the format is different on each database so we can’t just find the name of constraint and use that in our alter script. That’s when sysobjects table comes to the rescue.

This will list all the default field values for all tables

SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
 WHERE xtype = 'D' 

from here we can construct our TSQL script to suit our needs.

Solution

1. Get default fields of interes t(ConstraintName, TableName)
2. Drop each constraint
3. Add new default constraint with a NAME

/**
@Desc: Remove default constraints from a given table, then add new default constraint
2000/2005 compatible
@Author Greg B.
**/
USE [mydb]
GO

BEGIN TRANSACTION
GO

Declare MyCursor Cursor For

SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
 WHERE xtype = 'D' 
AND (OBJECT_NAME(parent_obj) = 'procedures' AND  OBJECT_NAME(ID) LIKE '%DF__procedure__INHER%')

OR 
(
 OBJECT_NAME(parent_obj) = 'subarea'
AND   (OBJECT_NAME(ID) LIKE '%DF__subarea__ABS_INH__%') 
 OR (OBJECT_NAME(ID) LIKE '%DF__subarea__ASSIGNE__%') 
) 


DECLARE @SQLScript NVARCHAR(300)

Declare @NameofConstraint VARCHAR(255) 
Declare @SchemaName VARCHAR(255) 
Declare @TableName VARCHAR(255) 
Declare @ConstraintType VARCHAR(255) 

Open MyCursor
Declare @Count int
Select @Count = 0 
FETCH NEXT FROM MyCursor INTO @NameofConstraint, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
	--PINT 'RECORD='+@NameofConstraint +' :: '+@TableName
	Select @SQLScript= 'ALTER TABLE '+ @TableName +' DROP CONSTRAINT '+@NameofConstraint
	EXEC sp_executesql @SQLScript
	Select @Count=@Count+1
	--Advance to next record
	FETCH NEXT FROM MyCursor INTO @NameofConstraint,@TableName
END

Close MyCursor
DEALLOCATE MyCursor

if @Count != 0
BEGIN
print 'Adding alter'
-- Now we add the constrains back again to the tables with standarized names
  ALTER TABLE [dbo].[procedures] ADD CONSTRAINT DF_PROCEDURE_INHERENT_RISK DEFAULT ((1)) FOR [INHERENT_RISK]
  ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ABS_INHERENT_RISK DEFAULT ((1)) FOR [ABS_INHERENT_RISK]
  ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ASSIGNED_INHERENT_RISK DEFAULT ((1)) FOR [ASSIGNED_INHERENT_RISK]
END

COMMIT TRANSACTION 

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN
END 
GO

Changing column size in both MySQL and MS SQL Server. When changing from wider to narrower size data might be truncated so be aware of that.

MySQL

 ALTER TABLE workbook modify  NAME varchar(255) 

MS SQL

ALTER TABLE workbook
ALTER COLUMN [NAME] VARCHAR(255) NULL
GO

Columns that part of primary key can’t be altered.

Sometime we need to provide one script that works on multiple server, here is how to do in for SQL Servers 2000 and 2005.

We will use simple technique to detect the server type using the serverproperty('ProductVersion') for a list of options visit

SERVERPROPERTY (Transact-SQL). So lets see what are the pros and cons

of this technique.

Pros

This technique allows us to use either SQL 2000 or 2005 servers.

Cons

Cant’ just use new sql 2005 syntax since 2000 does not recognize it, so we are executing it with exec after the new code string have bean concatenated.

This could easily leave us open to sql injection if we are not sanitizing the input correctly in web environment.

Another approach would be to execute a script file with the osql or sqlcmd

              EXEC master..xp_cmdshell 'OSQL -S devserver -U sa -P pass -ic:\script2000.sql -n'

              EXEC master..xp_cmdshell 'SQLCMD -S devserver -U sa -P pass -ic:\script2005.sql'

Complete Example

    DECLARE @sql2005Code varchar(255)             

    Set @sql2005Code = 'print ''SQL 2005 Code'' '

    DECLARE @ver nvarchar(128)

    SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)

    SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

            -- SQL 2000

    IF ( @ver = '8' ) BEGIN

            print 'SQL 2000'

    END

            -- SQL 2005

    ELSE IF ( @ver = '9' )BEGIN

      exec (@sql2005Code)

    END