At times there is a need to control order of execution of triggers. This could be due to business requirement but also as a way to control history of a record I usually don’ t see people put any way to prevent trigger nesting, example update trigger updating same records would fire the trigger again.
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 […]
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 […]
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 […]
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 […]
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] […]
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 […]