tag:blogger.com,1999:blog-1376516553035122580Thu, 24 Oct 2024 14:55:48 +0000mssqlcoldfusionhttp://cfkris.blogspot.com/noreply@blogger.com (Unknown)Blogger18125en-usnonoreply@blogger.comtag:blogger.com,1999:blog-1376516553035122580.post-8350531266155278713Wed, 17 Mar 2010 16:06:00 +00002010-03-17T18:07:43.886+02:00mssqlThe following function returns the prime numbers of a list of numberCREATE FUNCTION dbo.PrimeNumbers(@NumberList nvarchar(max))RETURNS nvarchar(max)ASBEGINdeclare @pdv as nvarchar(150)set @pdv = ''select @pdv = @pdv + value + ','from (select s.value,case when exists(select 1 from dbo.fn_t_dstring2table(@NumberList, ',') dwhere cast(d.value as numeric(18,2)) < cast(s.value as numeric(18,2)) and http://cfkris.blogspot.com/2010/03/sql-prime-numbers.htmlnoreply@blogger.com (Unknown)1tag:blogger.com,1999:blog-1376516553035122580.post-20328872913878778Sun, 28 Feb 2010 09:09:00 +00002010-02-28T17:43:49.053+02:00mssqlThe following function converts arabic numbers (nvarchar) to digital numbersAlter Function [dbo].[Convert_Arabic_To_Decimal]( @String nvarchar(100))Returns numeric(20,6)asBEGIN declare @String2 nvarchar(100) declare @rtrn as numeric(20,6) declare @iMode as smallint declare @bMode as smallint declare @Digit as numeric(18,6) set @String2 = @String set @rtrn = 0 set @iMode = 1 set @bMode = 1 while http://cfkris.blogspot.com/2010/02/convert-arabic-numbers-in-sql-2005.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-1028837831927019857Sun, 28 Feb 2010 09:04:00 +00002010-02-28T11:09:10.629+02:00mssqlThe arabic numbers in SQL 2005 have unicode between 1632 - 1641 where 1632=0 and 1641=9, whoever when converting arabic numeric string to digit number the system usually confuse the NChar(1632) with NChar(1776), both character they look the samehttp://cfkris.blogspot.com/2010/02/arabic-numbers-in-sql-2005.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-1196727552531697328Sat, 27 Feb 2010 15:59:00 +00002010-02-27T18:01:56.474+02:00mssqlThe following function locates the position of an element in a delimited listALTER FUNCTION [dbo].[listFind]( @List nvarchar(max), @Element nvarchar(150), @Delimiter nvarchar(10) = ',')RETURNS smallintASBEGIN declare @IndexPos int declare @CurrentElement nvarchar(150) declare @I int declare @TemList nvarchar(max) set @TemList = @list if (len(@List)<=0) return 0 set @IndexPos = charIndex(@http://cfkris.blogspot.com/2010/02/listfind-function-in-ms-sql-2005.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-3396143584952226502Sat, 27 Feb 2010 15:49:00 +00002010-02-27T17:57:15.993+02:00mssqlThe following list return the element at the X position in a delimited sting listALTER FUNCTION [dbo].[listGetAt]( @List nvarchar(max), -- delimited list @ElementPos int, -- the element position @Delimiter nvarchar(10) = ',', -- the delimiter string @ReturnOnNotFound nvarchar(100) = null -- the default value if element does not exist)RETURNS nvarchar(100)ASBEGIN declare @StrartPos int http://cfkris.blogspot.com/2010/02/listgetat-functions-in-sql-2005.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-2534191935831794780Thu, 24 Jan 2008 08:05:00 +00002008-02-18T16:08:12.496+02:00sp_settriggerorder use for defining the After trigger execution order. You can specify only the first trigger to be executed and the last trigger to be executed.the syntax of the command issp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername' , [ @order = ] 'value(First or Last)' , [ @stmttype = ] 'statement_type ( Insert or update or delete)' [ , [ @namespace =http://cfkris.blogspot.com/2007/10/spsettriggerorder-use-for-defining.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-153121383420912147Mon, 22 Oct 2007 15:59:00 +00002007-10-22T18:02:57.975+02:00mssqlYou can use the Update from statement to update tables of one table from anotherfor example you have a table Employee_old_table (employee_id,employee_name,employee_lname)and e table employee_new_table (employee_id,employee_full_name,employee_first_name,employee_last_name)to set the employee order.employee_full_name = employee.employee_name + ' ' + employee_lnameyou can do thisupdate http://cfkris.blogspot.com/2007/10/you-can-use-update-from-statement-to.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-5638785673031674213Tue, 16 Oct 2007 15:54:00 +00002007-10-19T17:47:02.488+02:00mssqlStore procedures can return value using a return statement in the store procedureyou can do something like thiscreate PROCEDURE dbo.test1 ( @iID int )ASBEGIN SET NOCOUNT ON; Declare @iNewId int set @iNewId = @iID + 1 return @iNewIdENDdeclare @iT intexec @iT = dbo.test1 6print @iThttp://cfkris.blogspot.com/2007/10/return-value-from-strore-procedure.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-47337248548413165Tue, 16 Oct 2007 07:45:00 +00002007-10-19T17:47:15.598+02:00coldfusionWhile I was playing with a request structure in coldfusion I come across a strange scenario with adding elements in the structureThere are 3 ways to add an element in the structure&lt;cfset request.sMyStructre[elementid] = value /&gt;&lt;cfset structInsert(request.sMyStructre,elementid,value) /&gt;&lt;cfset request.sMyStructre.elementid = value /&gt;Even though the result of those 3 commant 99% http://cfkris.blogspot.com/2007/10/while-i-was-playing-with-request.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-4335426168034563797Wed, 10 Oct 2007 16:30:00 +00002007-10-19T17:48:08.435+02:00coldfusionColdfusion 8 handles MSSQL triggers different that older versions of Coldfusion.IN older CFMXWe had some For insert triggers on tables for updating related tables etc.. The last trigger used to return the new identity using select SCOPE_IDENTITY() as @iNewRecthe cfquery was&lt;cfquery name="qInsert" datasource="#myDSN#"&gt;insert into tbl1 (name,fname)values('test1','test2') &lt;/cfquery&gt;Then http://cfkris.blogspot.com/2007/10/coldfusion-8-handles-mssql-triggers.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-3700676670743966128Mon, 08 Oct 2007 15:36:00 +00002007-10-19T17:48:08.435+02:00coldfusionDateDiff in coldfusion does not work the same way as dateDiff in sqlI believe Coldfusion gets the different between date1 and date2 in ms and then converts this value to the date part that you specify on the dateDiff function. Sql subtracts the datepart value of date 1 from the date part value of date2.What does that meanDateDiff('d','2007/10/02','2007/12/01') in coldfusion is : 1 dateDiff(month,http://cfkris.blogspot.com/2007/10/datediff-in-coldfusion-does-not-work.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-5946937260484279352Wed, 03 Oct 2007 16:08:00 +00002007-10-19T17:48:08.435+02:00coldfusionI did some testing whether i should use this to call public methods with in the component and it seams that calling a public method with in the component is faster if you do not use the 'this' .I created 2 simple cfc that were calling internally a public function the one with this.function name and the other one the function name directly not using the this was faster for 15-20 %http://cfkris.blogspot.com/2007/10/calling-public-methods-with-in.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-7608183642337104656Wed, 19 Sep 2007 14:23:00 +00002007-10-19T17:48:28.754+02:00mssqlWe had an sql function that we didn't know if is used for anywhere, so we decided to remove it. There was no reference to the store procedure in our code so the only place remaining was the sql store procedures, functions and triggers and viewsTo do this we use the sql queryselect *from sys.objectswhere type_desc in ('SQL_SCALAR_FUNCTION','VIEW','SQL_STORED_PROCEDURE','SQL_SCALAR_FUNCTION','http://cfkris.blogspot.com/2007/09/mssql-2005-search-in-code-of-store.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-5794430870684376039Mon, 17 Sep 2007 10:35:00 +00002007-10-19T17:48:08.435+02:00coldfusionI discover recently that Query of queries is case sensitive. to solve the problem i use the function Lcase to convert the coldfusion variables to low case and the query function Lower to convert the query column to low case. select * from qQ2 where lower(column_1) = '#LCase(cF1)#'http://cfkris.blogspot.com/2007/09/query-of-queries-is-case-sensitive.htmlnoreply@blogger.com (Unknown)0tag:blogger.com,1999:blog-1376516553035122580.post-5290864763912691082Fri, 14 Sep 2007 08:40:00 +00002007-10-19T17:48:08.435+02:00coldfusionThis is an error i found on CF 8 using the coldfusion.server.ServiceFactoryWhen you use the createObject("JAVA","coldfusion.server.ServiceFactory").getCronService().listAll() to get the list of schedule task on the server it doesn't return any newly added schedule tasks that were added with the cfschedule command (it return [undefined array element]) - however this is not http://cfkris.blogspot.com/2007/09/cf8-bug-cronservice-fails-with-whith.htmlnoreply@blogger.com (Unknown)0