Uncategorized

SQL Rounding Error when calculating percentages

Posted on

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 […]

Uncategorized

Show last executed query SQL Server – Disected

Posted on

I found this online, without explanation so I will dissect the query and explain what is happening. SELECT deqs.last_execution_time AS [TIME], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESCSELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time […]

Uncategorized

Why do I get a java.sql.SQLException: “Unable to get information from SQL Server” when trying to connect to an SQL Server instance?

Posted on

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 […]

Uncategorized

Remove/Change default constraints sql 2000/2005

Posted on

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__01D345B0Format: 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 […]

Uncategorized

Alter column size mysql – ms sql

Posted on

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) ALTER TABLE workbook modify NAME varchar(255) MS SQL ALTER TABLE workbook ALTER COLUMN [NAME] VARCHAR(255) NULL GOALTER TABLE workbook ALTER COLUMN [NAME] […]

Uncategorized

Detect SQL server version

Posted on

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 […]