Parayı/Rakamı yazıya çeviren SQL Function

-- Name: Parayı/Rakamı yazıya çeviren SQL function
-- Description:
--sayısal değeri yazıya çevirir
--virgülden sonra 2 haneyi alır
--Eğer @isMoney = true ise dönüş değerine TL ve kuruş eklenir
create function dbo.fnc_ParayiYaziyaCevir (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS
BEGIN

--declare @num numeric(18,2)
--declare @isMoney bit
--set @num = 1001111.99
--set @isMoney = 1
DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(1)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, 'Bir'
UNION SELECT 2, 'İki'
UNION SELECT 3, 'Üç'
UNION SELECT 4, 'Dört'
UNION SELECT 5, 'Beş'
UNION SELECT 6, 'Altı'
UNION SELECT 7, 'Yedi'
UNION SELECT 8, 'Sekiz'
UNION SELECT 9, 'Dokuz'
UNION SELECT 10, 'On'
UNION SELECT 11, 'Onbir'
UNION SELECT 12, 'Oniki'
UNION SELECT 13, 'Onüç'
UNION SELECT 14, 'Ondört'
UNION SELECT 15, 'Onbeş'
UNION SELECT 16, 'Onaltı'
UNION SELECT 17, 'Onyedi'
UNION SELECT 18, 'Onsekiz'
UNION SELECT 19, 'Ondokuz'
UNION SELECT 20, 'Yirmi'
UNION SELECT 30, 'Otuz'
UNION SELECT 40, 'Kırk'
UNION SELECT 50, 'Elli'
UNION SELECT 60, 'Altmış'
UNION SELECT 70, 'Yetmiş'
UNION SELECT 80, 'Seksen'
UNION SELECT 90, 'Doksan'
SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT İki characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMBiry THEN treat each digit seperately (eg 11 = Bir Bir)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 characters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split
BEGIN
SET @CharText = ''
SET @HunText = ''
SET @SetText = ''
SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3)
IF len(@Numset) = 3 --Calculate ANY hundreds
BEGIN
SET @Char = left(@NumSet, 1)
if @Char='1'
SET @HunText = 'Yüz'
ELSE
SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + 'Yüz', '')
SET @SetText = @HunText
END
--look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2))
IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM Ons/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ''
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
SET @SetText = '' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
if @iSplit = 1 AND @SetText='Bir' BEGIN
SET @SetText=''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN 'Bin'
WHEN 2 THEN 'Milyon'
WHEN 3 THEN 'Milyar'
WHEN 4 THEN 'Trilyon'
WHEN 5 THEN 'Katrilyon'
END + CASE WHEN len(@NumText) > 0 THEN '' ELSE '' end
END
ELSE
BEGIN
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN 'Bin'
WHEN 2 THEN 'Milyon'
WHEN 3 THEN 'Milyar'
WHEN 4 THEN 'Trilyon'
WHEN 5 THEN 'Katrilyon'
END + CASE WHEN len(@NumText) > 0 THEN '' ELSE '' end
END
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'Bir'
SET @NumText = @NumText + ' TL'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'Bir'
SET @NumText = @NumText + ' TL'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'Bir'
SET @CentText = @CentText + ' kuruş'
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'Bir'
SET @CentText = @CentText + ' kuruş'
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
SET @CentText = ' ' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'virgül' + @NumText
RETURN @NumText
END

Logo Objects Web Kullanımı için Yapılacak Ayarlar

UnityObjects(Web) calismasi icin yapilmasi gereken bazi parametre ayarlari vardir. Aşağıda bu ayarlar ile ilgili ayrıntıları bulacaksınız;

• Logo Unity 1.20.00.00 ve 4.20.00.00, Logo HR 3.04.03.00 versiyonlarından sonra Normal ve Web olmak üzere iki ayrı DLL dosyası hazırlanmadı. Bu DLL dosyaları birleştirilerek aynı DLL dosyasının hem Normal hemde web uyarlamarında çalışması sağlandı. Hangi amaçla kullanılacağını belirtir ForWebUse özelliği ekendi. Objects’i webte kullanabilmek için CreateObjects işleminden sonra ve Login veya Connect gibi bağlantı kurucu fonksyonlardan önce ForWebUse özelliği mutlaka TRUE olarak set edilmelidir.

Option Explicit
'LogoObjects.DLL dosyasını VB editöründe; Project\Reference bölümünden referans ederek kullanım;
Global UnityApp As New UnityObjects.UnityApplication
'UnityObject Dll'ini "Runtime" referans ederek kullanım;
'Global UnityApp As Variant
'Nesne runtime oluşturuarak çalışılacaksa initialize bölümünde aağıdaki kod işletilmelidir;
'Set UnityApp = CreateObjects("UnityObjects.UnityApplication")
Function ConnectToUnity() As Boolean
ConnectToUnity = False
If IsEmpty(UnityApp) Then
mdiFrmMain.statMain.SimpleText = " Unity Objects Objects Can not Create !!!"
Else
UnityApp.ForWebUse = True
If UnityApp.Connect Then
ConnectToUnity = True
mdiFrmMain.statMain.SimpleText = "Connected ..."
Else
mdiFrmMain.statMain.SimpleText = " Error code : " & str(UnityApp.GetLastError) & " " & UnityApp.GetLastErrorString
End If
End If
End Function
Not : HrObjects.DLL dosyasının Web kulanımı için sadece create satırı değimekte diğer kısımlar aynı kamaktadır.
'Set HrApp = CreateObjects("HRObjects.HRApplication")

• Eğer DLL dosyasını yukarıdaki gibi WEB olarak kullanırsanız veri tabanıya bağantı kurmak için gerekli olan bilgilerin bulunduğu LogoDB.CFG dosyasının buunduğu konumu (Set ile aynı konumdadır) local Registery atındaki ilgili anahtarlardan değil kullandığınız işletim sistemi klasörü (Windows 2000 için C:\winnt) altında oluşturacağınız INI dosyalardan okuyacaktır.Oluşturacağınız INI file ve içerikleri aşağıdadır.
- Logo Unity ürün ailesi için; LGold.INI dosyası olmalıdır, içeriği aşağıdadır.
[Program]
GoldPath=D:\GOLD\Unity110
[Gold Data Source]
DataPath=D:\GOLD\Unity110

One or more errors encountered while loading the designer. The errors are listed below. Some errors can be fixed by rebuilding your project, while others may require code changes.

In Microsoft Visual Studio 2005, you rebuild a solution and try to view a Microsoft Windows Form in Design view. When you do this, Design view may not show the Windows Form. Additionally, you may receive the following error message and call stack:

PROBLEM:
One or more errors encountered while loading the designer. The errors are listed below. Some errors can be fixed by rebuilding your project, while others may require code changes. TypeLoad failure. Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

at System.Reflection.Module.GetTypesInternal(StackCrawlMark& stackMark)
at System.Reflection.Assembly.GetTypes()
at Microsoft.VisualStudio.Shell.Design.AssemblyObsoleteEventArgs..ctor(Assembly assembly)
at Microsoft.VisualStudio.Design.VSDynamicTypeService.ReloadAssemblyIfChanged(String codeBase)
at Microsoft.VisualStudio.Design.VSDynamicTypeService.CreateDynamicAssembly(String codeBase)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.AssemblyEntry.get_Assembly()
at Microsoft.VisualStudio.Design.VSTypeResolutionService.AssemblyEntry.Search(String fullName, String typeName, Boolean ignoreTypeCase, Assembly& assembly, String description)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.SearchProjectEntries(AssemblyName assemblyName, String typeName, Boolean ignoreTypeCase, Assembly& assembly)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase, ReferenceType refType)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.AggregateTypeResolutionService.GetType(String name, Boolean throwOnError, Boolean ignoreCase)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.AggregateTypeResolutionService.GetType(String name, Boolean throwOnError)
at System.ComponentModel.Design.Serialization.CodeDomSerializerBase.GetType(ITypeResolutionService trs, String name, Dictionary`2 names)
at System.ComponentModel.Design.Serialization.CodeDomSerializerBase.FillStatementTable(IDesignerSerializationManager manager, IDictionary table, Dictionary`2 names, CodeStatementCollection statements, String className)
at System.ComponentModel.Design.Serialization.TypeCodeDomSerializer.Deserialize(IDesignerSerializationManager manager, CodeTypeDeclaration declaration)
at System.ComponentModel.Design.Serialization.CodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager manager)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.VSCodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager serializationManager)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.VSCodeDomDesignerLoader.DeferredLoadHandler.Microsoft.VisualStudio.TextManager.Interop.IVsTextBufferDataEvents.OnLoadCompleted(Int32 fReload)

RESOLUTION:

For more information about the MSDN Code Gallery, visit the following Microsoft Web site:
http://code.msdn.microsoft.com/KB912019

To apply this hotfix, you must have Visual Studio 2005 installed on the computer.

Saving SQL Query Results as HTML

USE:
declare @rc int

declare @MessageText VARCHAR(8000)

--section -1- SaveTableAsHTML
EXECUTE SaveTableAsHTML
@DBFetch = 'SELECT MektupGecerlilikTarihi,IhaleKayitNo,KurumAdi,IsinAdi,BankaAdi,MektupTipi,MektupTutari FROM
VW_TEMINAT_MEKTUPLARI_LISTESI_HAFTALIK_MAIL ',
@DBWhere = ' 1=1 ',
@TableStyle=' font-size: 11px; color: black; font-family: verdana; table-layout: auto; width: 83%;
border-collapse: collapse; border-right: black thin double; border-top: black thin double; border-left: black thin double;
border-bottom: black thin double; '
,@Header = 1, @outputText=@MessageText OUTPUT

-- section -2- Send Mail

exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'Sqlmailservice@gintem.com.tr',
@FROM_NAME = N'SQL Mail Service - Teminat Mektupları',
@replyto = N'',
@TO = N'serkansonmez@gintem.com.tr',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'15 gün içinde Vadesi Dolacak Teminat Mektupları' ,
@message = @MessageText,
@type = N'text/html',
@messagefile= N'',
@attachment = N'',
@attachments= N'',
@timeout = 10000,
@server = N'mail.gintem.com.tr'
select RC = @rc
go
 
RETURN VALUE:

MektupGecerlilikTarihi	IhaleKayitNo	KurumAdi	IsinAdi	BankaAdi	MektupTipi	MektupTutari
03/01/2009	-	İZSU	WİNWİN	DENIZBANK	Geçici	349000.00

-- SaveTableAsHTML.sql

if exists (select * from dbo.sysobjects where id = object_id(N`[dbo].[SaveTableAsHTML]`) and OBJECTPROPERTY(id, N`IsProcedure`) = 1)
drop procedure [dbo].[SaveTableAsHTML]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
SaveTableAsHTML.sql
http://www.virtualobjectives.com.au
http://www.serkansonmez.com

History:
27/02/2009 - John Buoro - Created.
31/03/2009 - John Buoro - OSQL replaces BCP as query length of BCP is limited to only 1023 chars.
14/11/2009 - Serkan SONMEZ - add new OutputText parameter which return to html code
*/

CREATE PROCEDURE dbo.SaveTableAsHTML
@PCWrite varchar(1000) = NULL,
@DBFetch varchar(4000),
@DBWhere varchar(2000) = NULL,
@DBThere varchar(2000) = NULL,
@DBUltra bit = 1,
@TableStyle varchar(1000) = `border-width: thin; border-spacing: 2px; border-style: solid; border-color: gray; border-collapse: collapse;`,
@Header bit = 1,-- Output header. Default is 1.
@OutputText varchar(8000) OUTPUT
AS

SET NOCOUNT ON
SET @OutputText = ``
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @TPre varchar(10)

DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint

SET @TPre = ``

SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1

DECLARE @DBAE varchar(40)
DECLARE @Task varchar(6000)
DECLARE @Bank varchar(4000)
DECLARE @Cash varchar(2000)
DECLARE @Risk varchar(2000)
DECLARE @Next varchar(8000)
DECLARE @Save varchar(8000)
DECLARE @Work varchar(8000)
DECLARE @Wish varchar(8000)
DECLARE @hString varchar(8000)
DECLARE @tString varchar(8000)
DECLARE @fString varchar(50)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)

DECLARE @Rank smallint
DECLARE @Kind varchar(20)
DECLARE @Mask bit
DECLARE @Bond bit
DECLARE @Size int
DECLARE @Wide smallint
DECLARE @More smallint

DECLARE @DBAI varchar(2000)
DECLARE @DBAO varchar(8000)
DECLARE @DBAU varchar(8000)

DECLARE @Fuse int
DECLARE @File int

DECLARE @FuseO int
DECLARE @FileO int
DECLARE @TmpPathObj int
DECLARE @TmpPath varchar(127)
DECLARE @TmpFile varchar(127)
DECLARE @TmpFilename varchar(1000)

DECLARE @HeaderString varchar(8000)
DECLARE @sHeaderString varchar(8000)
DECLARE @HeaderDone int

SET @DBAE = `##SaveFile` + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)

SET @Task = `IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ` + CHAR(39) + @DBAE + CHAR(39) + `) DROP TABLE ` + @DBAE
EXECUTE (@Task)

SET @Bank = @TPre + @DBFetch

IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = `U` AND name = @Bank)
BEGIN
SET @Bank = CASE WHEN LEFT(LTRIM(@DBFetch),6) = `SELECT` THEN `(` + @DBFetch + `)` ELSE @DBFetch END
SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39))
SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))
END

IF @DBWhere IS NOT NULL
BEGIN
SET @Cash = REPLACE(@DBWhere,`WHERE` ,CHAR(32))
SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39))
SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))
END

IF @DBThere IS NOT NULL
BEGIN
SET @Risk = REPLACE(@DBThere,`ORDER BY` ,CHAR(32))
SET @Risk = REPLACE(@Risk, CHAR(94),CHAR(39))
SET @Risk = REPLACE(@Risk,CHAR(45)+CHAR(45),CHAR(32))
SET @Risk = REPLACE(@Risk,CHAR(47)+CHAR(42),CHAR(32))
END

SET @DBAI = ``
SET @DBAO = ``
SET @DBAU = ``

IF ASCII(LEFT(@Bank,1)) < 64 BEGIN SET @Task = `SELECT * INTO ` + @DBAE + ` FROM ` + @Bank + ` AS T WHERE 0 = 1` IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return DECLARE Fields CURSOR FAST_FORWARD FOR SELECT C.name, C.colid, T.name, C.isnullable, C.iscomputed, C.length, C.prec, C.scale FROM tempdb.dbo.sysobjects AS O JOIN tempdb.dbo.syscolumns AS C ON O.id = C.id JOIN tempdb.dbo.systypes AS T ON C.xusertype = T.xusertype WHERE O.name = @DBAE ORDER BY C.colid SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END ELSE BEGIN DECLARE Fields CURSOR FAST_FORWARD FOR SELECT C.name, C.colid, T.name, C.isnullable, C.iscomputed, C.length, C.prec, C.scale FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id JOIN systypes AS T ON C.xusertype = T.xusertype WHERE ISNULL(OBJECTPROPERTY(O.id,`IsMSShipped`),1) = 0 AND RTRIM(O.type) IN (`U`,`V`,`IF`,`TF`) AND O.name = @Bank ORDER BY C.colid SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END OPEN Fields SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain -- Convert to character for header. SET @HeaderString = `` SET @sHeaderString = `` declare @sql nvarchar(4000) declare @aHeader nvarchar(9) declare @zHeader nvarchar(9) set @aHeader = ``
set @zHeader = ``

WHILE @@FETCH_STATUS = 0 AND @Status = 0
BEGIN
-- Build header.
IF LEN(@HeaderString) > 0 SET @HeaderString = @HeaderString + `+lower(````)` + `+ISNULL(``` + @Same + ```,SPACE(0))+` + `lower(````)+`
IF LEN(@HeaderString) = 0 SET @HeaderString = `+lower(````)` + `+ISNULL(``` + @Same + ```,SPACE(0))+` + `lower(````)+`
IF LEN(@sHeaderString) > 0 SET @sHeaderString = @sHeaderString + @aHeader + ISNULL(@Same, SPACE(0)) + @zHeader
IF LEN(@sHeaderString) = 0 SET @sHeaderString = @aHeader + ISNULL(@Same, SPACE(0)) + @zHeader

IF @Kind IN (`char`,`varchar`,`nchar`,`nvarchar`)
BEGIN
IF @Rank = 1 SET @DBAU = `lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `),SPACE(0))+` + `lower(````)`
IF @Rank > 1 SET @DBAU = @DBAU + `+lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `),SPACE(0))+` + `lower(````)`
END

IF @Kind IN (`bit`,`tinyint`,`smallint`,`int`,`bigint`)
BEGIN
IF @Rank = 1 SET @DBAU = `lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `),SPACE(0))+` + `lower(````)`
IF @Rank > 1 SET @DBAU = @DBAU + `+lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `),SPACE(0))+` + `lower(````)`
END

IF @Kind IN (`numeric`,`decimal`,`money`,`smallmoney`,`float`,`real`)
BEGIN
IF @Rank = 1 SET @DBAU = `lower(````)` + `+ISNULL(CONVERT(varchar(80),` + @Same + `),SPACE(0))+` + `lower(````)`
IF @Rank > 1 SET @DBAU = @DBAU + `+lower(````)` + `+ISNULL(CONVERT(varchar(80),` + @Same + `),SPACE(0))+` + `lower(````)`
END

IF @Kind IN (`uniqueidentifier`)
BEGIN
IF @Rank = 1 SET @DBAU = `lower(````)` + `+ISNULL(CONVERT(varchar(80),` + @Same + `),SPACE(0))+` + `lower(````)`
IF @Rank > 1 SET @DBAU = @DBAU + `+lower(````)` + `+ISNULL(CONVERT(varchar(80),` + @Same + `),SPACE(0))+` + `lower(````)`
END

IF @Kind IN (`datetime`,`smalldatetime`)
BEGIN
IF @Rank = 1 SET @DBAU = `lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `,103),SPACE(0))+` + `lower(````)`
IF @Rank > 1 SET @DBAU = @DBAU + `+lower(````)` + `+ISNULL(CONVERT(varchar(40),` + @Same + `,103),SPACE(0))+` + `lower(````)`
END

FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
END

CLOSE Fields DEALLOCATE Fields

IF LEN(@DBAU) = 0 SET @DBAU = `*`

SET @DBAI = ` SELECT `
SET @DBAO = ` FROM ` + @Bank + ` AS T`
+ CASE WHEN @DBWhere IS NULL THEN `` ELSE ` WHERE (` + @Cash + `) AND 0 = 0` END
+ CASE WHEN @DBThere IS NULL THEN `` ELSE ` ORDER BY ` + @Risk + `,` + CHAR(39) + `DBA` + CHAR(39) END

IF LEN(ISNULL(@PCWrite,`*`)) > 7 AND @DBUltra = 0
BEGIN
SET @tString = ` select lower(``< html>< body>`` UNION ALL ` SET @fString = ` UNION ALL select ````` SET @hString = `` IF @Header = 1 BEGIN SET @hString = ` select ``` + @sHeaderString + ``` UNION ALL ` END SET @Wish = `set nocount on; USE ` + DB_NAME() + @tString + @hString + @DBAI + `````+` + @DBAU + `+````` + @DBAO + @fString -- SET @Work = `bcp "` + @Wish + `" queryout "` + @PCWrite + `" -c -T` -- Query length of BCP is limited to only 1023 chars. -- Create SQL script file. IF @Status = 0 EXECUTE @Status = sp_OACreate `Scripting.FileSystemObject`, @FuseO OUTPUT IF @Status = 0 EXECUTE @Status = sp_OAGetProperty @FuseO, `GetSpecialFolder(2)`, @TmpPathObj OUTPUT IF @Status = 0 EXECUTE @Status = sp_OAGetProperty @TmpPathObj, `Path`, @TmpPath OUTPUT IF @Status = 0 EXECUTE @Status = sp_OAGetProperty @FuseO, `GetTempName`, @TmpFile OUTPUT SET @TmpFilename = @TmpPath + `\` + @TmpFile IF @Status = 0 EXECUTE @Status = sp_OAMethod @FuseO, `CreateTextFile`, @FileO OUTPUT, @TmpFilename, -1 IF @Status <> 0 GOTO ABORT IF @Status = 0 EXECUTE @Status = sp_OAMethod @FileO, `Write`, NULL, @Wish IF @Status = 0 EXECUTE @Status = sp_OAMethod @FileO, `Close` SET @Work = `osql -i "` + @TmpFilename + `" -o "` + @PCWrite + `" -n -h-1 -w8000 -E` EXECUTE @Return = master.dbo.xp_cmdshell @Work, NO_OUTPUT SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return EXECUTE @Status = sp_OAMethod @FuseO, `DeleteFile`, NULL, @TmpFilename EXECUTE @Status = sp_OADestroy @FuseO GOTO ABORT END IF LEN(ISNULL(@PCWrite,`*`)) > 7 BEGIN IF @Status = 0 EXECUTE @Return = sp_OACreate `Scripting.FileSystemObject`, @Fuse OUTPUT SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF @Status = 0 EXECUTE @Return = sp_OAMethod @Fuse, `CreateTextFile`, @File OUTPUT, @PCWrite, -1 SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF @Status <> 0 GOTO ABORT END SET @DBAI = `DECLARE Records CURSOR GLOBAL FAST_FORWARD FOR` + @DBAI IF @Status = 0 EXECUTE (@DBAI+@DBAU+@DBAO) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return OPEN Records SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain FETCH NEXT FROM Records INTO @Next SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain SET @HeaderDone = 0 WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Save = `` IF ISNULL(@File,0) = 0 BEGIN -- Print header (TEXT). IF @HeaderDone = 0 BEGIN SET @OutputText = @OutputText + `` + CHAR(13) + CHAR(10) SET @HeaderDone = 1 END IF @Header = 1 BEGIN SET @OutputText = @OutputText + `` + @sHeaderString + `` + CHAR(13) + CHAR(10) SET @Header = 0 END SET @OutputText = @OutputText + `` + @Next + `` END ELSE BEGIN -- Print header (FILE). IF @HeaderDone = 0 BEGIN SET @Save = @Save + `` + CHAR(13) + CHAR(10) SET @HeaderDone = 1 END IF @Header = 1 BEGIN SET @Save = @Save + `` + @sHeaderString + `` + CHAR(13) + CHAR(10) SET @Header = 0 END -- Print the data. SET @Save = @Save + `` + @Next + `` + CHAR(13) + CHAR(10) IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, `Write`, NULL, @Save IF @Status = 0 SET @Status = @Return END FETCH NEXT FROM Records INTO @Next SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END CLOSE Records DEALLOCATE Records -- Print footer (TEXT). IF ISNULL(@File,0) = 0 BEGIN SET @OutputText = @OutputText + `` + CHAR(13) + CHAR(10) END ELSE BEGIN SET @Save = `` + CHAR(13) + CHAR(10) IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, `Write`, NULL, @Save END -- Close. IF ISNULL(@File,0) <> 0 BEGIN EXECUTE @Return = sp_OAMethod @File, `Close`, NULL IF @Status = 0 SET @Status = @Return EXECUTE @Return = sp_OADestroy @File IF @Status = 0 SET @Status = @Return EXECUTE @Return = sp_OADestroy @Fuse IF @Status = 0 SET @Status = @Return END ABORT: -- This label is referenced when OLE automation fails. IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR (`SaveTableAsHTML Windows error [%d]`,16,1,@Status) SET @Task = `IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ` + CHAR(39) + @DBAE + CHAR(39) + `) DROP TABLE ` + @DBAE EXECUTE (@Task) SET NOCOUNT OFF RETURN (@Status) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

Get dates from week number in T-SQL

In Microsoft SQL Server, I have a week number
for example;










SELECT DATEPART(wk, GETDATE())  -- today 05.11.2009
result : 45

Logo Bordro programında onay tarihini değiştirmek veya iptal etmek.

Logo Bordro programında geçmiş puantajları kilitlemek (onaylamak) için Ayarlar --> İşlemler --> Onaylama seçilir. Girişi yapılan tarihte bir yanlışlık olursa aşağıdaki SQL scriptleri ile düzenleme yapılabilir.






--LISTELEME

SELECT LREF, TYP, APPDATE,* FROM LH_001_APPDATE WITH(NOLOCK) WHERE (TYP = 1) ORDER BY
TYP DESC, APPDATE DESC
--UPDATE
update LH_001_APPDATE set APPDATE = '20090930' where lref= 1
--DELETE
delete from LH_001_APPDATE

Only Date Part to GetDate() function in SQL Server

Problem:
I want to getdate() function to compare datetime field.  Date part of both field (2009-10-15 ) but not equal. 

SELECT getdate(),* from GUNLUK_GELENLER WHERE Per_ZeitStatusDatum = getdate()

--No Result

SOLUTION:

SELECT getdate(),* from GUNLUK_GELENLER WHERE
CONVERT(VARCHAR(10),Per_ZeitStatusDatum,111) =
CONVERT(VARCHAR(10),GETDATE(),111)

Host 'host_name' is blocked because of many connection errors.

Error Message :
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

Resolution:

If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:
The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement. See Section 5.1.3, “Server System Variables”.
By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

Prevent cache in HTML

META tag inside HEAD. Like this :

< HEAD>
< META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE" >
< /HEAD>

Logo Unity: Veritabanında integer olarak tutalan zaman bilgisinin gösterilmesi

Logo programlarının veritabanına integer olarak kaydettiği zaman bilgisini aşağıdaki function ile zaman biriminden görebilirsiniz.

CREATE FUNCTION [dbo].[EXTRACTINTDATE](@PDATE AS INT)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @FHOUR INT
DECLARE @FMINUTE INT
DECLARE @FSECOND INT
DECLARE @HOURSTR VARCHAR(2)
DECLARE @MINSTR VARCHAR(2)
DECLARE @FSECSTR VARCHAR(2)


SET @FHOUR = CONVERT(INT,@PDATE/16777216,103)
SET @FMINUTE = (@PDATE-(@FHOUR*16777216))/65536
SET @FSECOND = (@PDATE-((@FMINUTE*65536)+(@FHOUR*16777216)))/256

SET @HOURSTR = CONVERT(VARCHAR(2), @FHOUR, 103)
SET @MINSTR = CONVERT(VARCHAR(2), @FMINUTE, 103)
SET @FSECSTR = CONVERT(VARCHAR(2), @FSECOND, 103)

RETURN (@HOURSTR + ':' + @MINSTR + ':' + @FSECSTR)
END


Örnek Kullanım:

select TIME_,[dbo].[EXTRACTINTDATE](TIME_) from LG_XT019_109


Sonuç:

TIME_ NEW_TIME
168634208 10:13:39
168636156 10:13:46
220538930 13:37:40
238367942 14:53:52
238762111 14:59:56

Case Problem in C Sharp: Control can't fall through from one case label to another

PROBLEM:
Control can't fall through from one case label to another 4 each case

String status = (String)Session["Status"];

switch (status)
{
case "Book Pending":

string count1 = wucHSRBookModify1.getCount("Offer Pending", "",
"", "", "");
int intcount1 = Convert.ToInt16(count1);
if (intcount1 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}

case "Modification Pending":

string count2 = wucHSRBookModify1.getCount("Offer Pending", "Book Pending",
"", "", "");
int intcount2 = Convert.ToInt16(count2);
if (intcount2 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}
case "Availability Pending":

string count3 = wucHSRBookModify1.getCount("Offer Pending", "Book Pending",
"Modification Pending", "", "");
int intcount3 = Convert.ToInt16(count3);
if (intcount3 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}
}


RESOLUTION:
You need to insert a break; at the end of each case before another case begins.

Prevent unchecking of a checkbox in Java Script

<\input type="checkbox" onclick="if(!this.checked)this.checked=true"\>

Prevent href in Java Script

Test

Disable the link above

Create a for Loop in JavaScript

JavaScript, like all programming languages, supports loops or repetition, in its case with the for statement. The for statement in JavaScript comes in two flavors: one loops over values, the other over entries in an object or array.

Loop Over Values in JavaScript
1. Code the for statement, following the syntax below:
for (variable=value; condition; increment) { statement(s) }

2. Loop over a range of values by setting a variable equal to the initial value, specifying for the condition the test that the variable not exceed the maximum value, and incrementing the variable each time through the loop. In the example below, the numbers between 1 and 9 will be displayed in a series of alert boxes. (i++ means to increment the value of the variable i by 1.)
for (i=1; i<10; i++) { alert (i); }

3. Terminate processing of the loop's statements and return to the top for the next iteration, if necessary, by using the continue statement.

4. Break out of the loop, continuing with the statement following it, by coding a break statement if necessary.



Loop Over Object or Array Elements

1. Code the for statement, following the syntax below. "in" is a special JavaScript keyword used for this purpose.
for (variable in object) { statement(s) }

2. Process each element in the object or array as appropriate. The variable given in the for statement will take on the value of each array index, or object field, in turn. Example:
for (i in my_array) { alert ("Value of element " + i + " is " + my_array[i]);

3. Move to the next iteration, or break out of the loop, using the same continue and break statements described in the preceding section.

Prevent multi-row updates with Trigger

The best way to do this would be with a trigger :

create trigger trig_single_updates_only on table_name
for update
as
begin
if (select count(*) from inserted) > 1
begin
raiserror ('You cannot update more than one row at time - please add an appropriete where clause',16,1)
rollback
end
end

Unity : Aynı setle farklı veritabanına bağlanmak

Unity'de bir set ile birden fazla veritabanına bağlanılabilir. Birden fazla veritabanına test amaçlı çalışmalarda bağlanılabilir. Bu bağlantıyı yapmak için:
1. Mevcut Logodb.cfg logodb_orj.cfg olarak kopyalanır.
2. Lgconfig.exe çalıştırılır. Test database'ini yolu bu alana girilir.



3. Logodb.cfg dosyasının ismi Spaintest.cfg olarak değiştirilir.
4. Logodb_orj.cfg dosyasının ismi Logodb.cfg olarak değiştirilir.
5. Unity Kısayolu SpainTest olarak kopyalanır ve özelliklerine /DB:Spaintest.cfg eklenir.



Sonuçta her iki kısayol farklı veritabanlarına bağlanacağı için tek bir set ile iki bağlantı yapılmış olur. (Unity2 versiyonu 1.71)

Unity : Türlerine göre yıllık kesilen faturaların sayısı

Fatura türlerine göre gruplanmış, bir yıl içinde kesilen faturaların sorgusu aşağıdadır. Kriterleri değiştirerek farklı amaçlarla bu sorguyu kullanabilirsiniz. Sorguda fatura türünü gösteren DBO.Ay_isl fonksiyonu için tıklayınız.

SORGU:

select TRCODE,COUNT(TRCODE) as fatura_sayisi,DBO.Ay_isl('FT',TRCODE) fatura_turu
from LG_108_01_INVOICE GROUP BY TRCODE ORDER BY TRCODE

UNITY : STLINE üzerinde REMAMOUNT ve REMLNUNITAMNT sıfırlanmama sorunu

Logo Unity'de STLINE üzerinde bulunan REMAMOUNT ve REMLNUNITAMNT alanlarında bir çıkış hareketi olduktan sonra normalde sıfır olması gerekirken 2,48689957516035E-14
gibi rakamlar oluşuyorsa bu kayıtların update yapılarak düzeltilmesi gerekir. (Bu sorun 1.71 versiyonunda var) Bu problemden dolayı aynı malzemeye ait çıkış hareketlerinde sorunlar meydana geliyor, hareket veya irsaliye fişleri, malzemeler yeterli miktarda gözükseler bile oluşturulamıyor. Aşağıdaki sorguları kullanarak sorunu giderebilirsiniz.



select REMAMOUNT,REMLNUNITAMNT,* from LG_109_01_SLTRANS WHERE REMLNUNITAMNT < 0.0001 AND REMLNUNITAMNT<>0
--UPDATE LG_109_01_SLTRANS WITH(READPAST) SET REMLNUNITAMNT=0 WHERE REMLNUNITAMNT < 0.0001 AND REMLNUNITAMNT<>0

select REMAMOUNT,* from LG_109_01_SLTRANS WHERE REMAMOUNT < 0.0001 AND REMAMOUNT<>0
--UPDATE LG_109_01_SLTRANS WITH(READPAST) SET REMAMOUNT=0 WHERE REMAMOUNT < 0.0001 AND REMAMOUNT<>0

Unity'de Farklı Dövizler üzerinden Borç Kapatma İşlemleri

Farklı dövizler üzerinden borç kapatma yapılacaksa, kur farkları işlem tarihi ya da ödeme tarihi esas alınarak hesaplatılır. Kur farkının hesaplama şekli ve ödemelerin ne şekilde izleneceği cari hesap kartında Ticari Bilgiler sayfasında belirlenir.

Cari hesap için ödeme İzleme öndeğeri
Borç kapatma işlemlerinin ne şekilde yapılacağı a cari hesap kartında Ticari bilgiler sayfasında Ödeme izleme alanında belirlenir. Ödeme izleme 2 şekilde yapılır.

1. Aynı dövizli işlemlerle: Cari hesaba ait borç takip ve ödemeler tek bir döviz türü üzerinden izlenir. Farklı işlem dövizi üzerinden kaydedilen faturalar ve ödeme hareketleri birbirini kapatamaz. Kapatma işlemi yapıldığında program işlem döviz türleri uyuşmuyor mesajı verir.
2. Değişik dövizli işlemlerle: Farklı işlem dövizi üzerinden yapılan işlemler ve ödemeler birbirini kapatabilir.

LOGO Unity, STLINE üzerindeki sıfır olan kur bilgisinin update edilmesi

Logo Unity'de, yerel para birimi ile dövizlerin birlikte gösterildiği bazı raporlarda "Divide by zero" hatası alıyorsanız LG_209_01_STLINE tablosunda döviz bilgisinin girilmemesinden kaynaklanan bir sorun olabilir. Aşağıdaki Query, STLINE tablosundaki döviz bilgisi sıfır olan satırların, döviz tablosundan ilgili tarihteki döviz bilgisi alınarak update yapılmasını sağlar. Sorguyu kendi tablolarınıza göre değiştirmelisiniz.


DECLARE @LOGREF INT
DECLARE @DATE DATETIME
DECLARE @RR FLOAT
DECLARE BIRIMKODU CURSOR FOR
SELECT LOGICALREF, DATE_
FROM LG_209_01_STLINE (NOLOCK) WHERE REPORTRATE=0 AND TRCODE IN (2,3,7,8,1,6)
OPEN BIRIMKODU
FETCH NEXT FROM BIRIMKODU
INTO @LOGREF, @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RR=RATES1 FROM L_DAILYEXCHANGES (nolock) WHERE CRTYPE=1 AND EDATE =@DATE
UPDATE LG_209_01_STLINE SET REPORTRATE =@RR
WHERE LOGICALREF=@LOGREF
FETCH NEXT FROM BIRIMKODU
INTO @LOGREF, @DATE
END
CLOSE BIRIMKODU
DEALLOCATE BIRIMKODU
GO

How to change postgresql Sequence number?

In general you can use the code below to set the value. (similar to
"ALTER TABLE XTable AUTO_INCREMENT = 311" in MsSQL database)

SELECT setval('schemaname.sequencename', 1467);

Expiring a cookie in CSharp

The first thing you need to understand about cookies is this: Cookies carry an expiry date. The second thing you need to understand is this: Expiry dates are the cause of most cookie-related bugs.

Every time you set the Value of a cookie, remember also to set the Expires date. If you fail to do this you will quickly find yourself losing Cookies owing to them having expired immediately when updating them on the client machine or when the browser closes.

When a cookie expires, the client no longer sends it to the server, so you need to make sure that the Expires property of the cookie is always in the future. If you just set a cookie's value then it will create a cookie with Expires set to DateTime.MinValue (01-Jan-0001 00:00).

You can set a cookie's Expires property using any DateTime value (a positive relief after ASP). For example, if you want a Cookie to expire after the user has not been to that part of your site for a week, you would set Expires = DateTime.Now.AddDays(7).

If you want the cookie to be permanent then the temptation is to use DateTime.MaxValue, as I did in the lat version of this article. However, there is a simple gotcha here.

DateTime.MaxValue is precisely 31-Dec-9999 25:59:59.9999999. But Netscape, even at version 7, doesn't cope with that value and expires the cookie immediately. Amazingly, and somewhat annoyingly, investigation showed that Netscape 7 will cope with 10-Nov-9999 21:47:44 but will not handle a second higher (I'll be honest, I didn't test it to any fraction of a second, I really wasn't interested).

Thus if, like me, you subscribe to the "it doesn't have to look pretty on Netscape, as long as it's functional on the latest version" school of thought, always use a date prior to that. A commonly accepted "permanent" cookie expiry date is DateTime.Now.AddYears(30), ie. 30 years into the future. If someone hasn't visited your site for that long, do you really care what the state was last time they were there?

SQL Query that search all column names based on criteria

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.


That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Serkan SONMEZ'
GO




CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Unity : Satış fiyat kartlarının bitiş tarihini değiştirmek

UPDATE LG_208_PRCLIST SET ENDDATE='20101231' WHERE VALUE='20081231'

Unity : Faturası olmayan irsaliyeden F işaretini kaldırmak

Faturası olmayan irsaliyeden F işaretini kaldırmak için aşağıdaki query'ler kullanabilirsiniz.

1) Seçmek için;
SELECT * FROM LG_208_01_STFICHE WHERE BILLED=1 AND INVOICEREF NOT IN (SELECT LOGICALREF FROM LG_208_01_INVOICE)

2) Düzeltmek için;
UPDATE LG_208_01_STFICHE SET BILLED=0 WHERE BILLED=1 AND INVOICEREF NOT IN (SELECT LOGICALREF FROM LG_0208_01_INVOICE)

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Problem:

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Resolution:

This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

SQL SERVER - Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission

I got following error when I was trying to delete user ‘test’ with ‘SA’ login. I was little surprised but then I tried to delete with the windows authenticated systemadmin account. Once again I got the same error.

Msg 15151, Level 16, State 1, Line 3Cannot drop the login ‘test’, because it does not exist or you do not have permission.
The reason I was surprised that I was systemadmin and I should be allowed to delete the login. I am including the script which I used to delete the account here.

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N’Test’)
DROP LOGIN [Test]

Fix/Workaround/Solution:
I finally found out that error was misleading. I had another session open in SQL Server Management Studio with username ‘test’ and it was preventing me to drop the user. Once I closed that open session I was successfully able to delete the user using above script.
In Normal Case:Following error usually show up when user is logged in, which I did not get it.

Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.

However, when I tried to re-create scenario I got above error. I want to share this experience with users and want to know if they have ever faced this scenario.

Msg 4621, Level 16, State 10, Line 1 Permissions at the server scope can only be granted when the current database is master

ERROR

Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master

Fix/Workaround/Solution:

If you look at the database in use is AdventureWorks and when any server level permission has to be granted the database in context should be of master database. Change the database used to master and it should fix the error.

Reference : Pinal Dave (http://blog.sqlauthority.com/)

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name
Parameter
Description
column_name
Required. The field to be formatted.
format
Required. Specifies the format.

SQL FORMAT() Example
We have the following "Products" table:
Prod_Id
ProductName
Unit
UnitPrice
1
Jarlsberg
1000 g
10.45
2
Mascarpone
1000 g
32.56
3
Gorgonzola
1000 g
15.67
Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").
We use the following SELECT statement:
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDateFROM Products
The result-set will look like this:
ProductName
UnitPrice
PerDate
Jarlsberg
10.45
2008-10-07
Mascarpone
32.56
2008-10-07
Gorgonzola
15.67
2008-10-07

JavaScript: Copy to a Date var

1.First you start with the Date object you want to copy; let's call that "d1".
2.Create a new Date object where you want to copy the value to, say "d2":
"var d2 = new Date()".
3.Call "setTime" on the new variable and pass in the "valueOf()" of your source date: "d2.setTime(d1.valueOf())".

How to create a For Loop In JavaScript?

1- A basic While Loop.
The Basic While Loop-First of all you just need to understand the while loop in it's most basic form, so that you'll understand how the for loop works. Lets take a look at a very basic form of the while loop:
i = 0;
while(i <= end)
{...i++;

)

First of all you'll notice the "i" variable, this will most likely be declared as an int by the way, it's basically what will keep count of the number of times the computer is supposed to go through the loop. You'll also notice the i++ inside the loop, this is what makes the "i" variable go up by one after every loop. It can then stop looping once it reaches whatever you've set the "end" variable to, which will more than likely be an int as well.

2-Variables
Now we'll take a look at the for loop piece by piece. The for loop itself is really just shorthand in Java for the above while loop. it's also going to have an "i" variable, so you could go ahead and declare that. It'll also have a starting value, which was zero in the while loop above, and an end value. Just make sure the starting value is smaller than the end value, and that it will eventually reach the end value. Don't want an infinite loop do you?
int i;
int begin;
int end;
3-Start, End, And Count-
Now all you have to do is to set up the for loop:
for(i = begin; i <= end; i++)

Just remember that you can set the beginning and ending values to whatever you need them to be.

How to create and write to a browser cookie in ASP.NET using C#

1- Go to the code-behind of the .aspx page you wish to create the browser cookie from. I will show 2 approaches on creating such a cookie.
2- Method 1:

// A cookie called "userInfo" is dynamically created and the key "userName" is assigned the value "patrick".
Response.Cookies["userInfo"]["userName"] = "patrick";
// A new key called "lastVisit" is added to the collection of values in the cookie and assigned the current datetime value.
Response.Cookies["userInfo"]["lastVisit"] = DateTime.Now.ToString();
// The expiration value of the cookie is set to 1 day in the future from the current date.
Response.Cookies["userInfo"].Expires = DateTime.Now.AddDays(1);
3- Method 2:

HttpCookie aCookie = new HttpCookie("userInfo");
// The key "userName" is added to the cookie and assigned the value "patrick" as the above example.
aCookie.Values["userName"] = "patrick";
// The key "lastVisit" is added to the cookie and assigned the current datetime value.
aCookie.Values["lastVisit"] = DateTime.Now.ToString();
// The expiration date of the cookie is set to 1 day in the future from the current date.
aCookie.Expires = DateTime.Now.AddDays(1);
// Add the cookie to the browser cookie collection.
Response.Cookies.Add(aCookie);

How to Block SQL Injection Hackers with ASP Validation

Protect your database-driven ASP website from hacker attacks with powerful input validation. This will slow down hackers who use methods like SQL injection attacks and XSS (cross-site scripting) via the URL querystring and form inputs. These methods are simple enough that you can do it yourself with only basic coding knowledge.
Step 1
The first goal of a hacker is to repeatedly try to break a website, causing it to display a variety of valuable errors that give away private database details. In this way, he can gain insight into the structure of the database and ultimately create a map or footprint of all its tables and columns. The second goal of the hacker is to actually manipulate the database by executing scripts in malicious ways. With control over the database, the hacker may possibly steal credit card numbers, erase data or infect it with viruses, among other nasty things. In essence, the URL querystring and textbox are the two backdoors into a database. Getting errors and manipulating the backdoors are the two methods used by hackers to ultimately destroy a database. For more details, see my companion article in the resources section or at http://www.ehow.com/how_4434719_protect-website-hacker-attacks.html .
Step 2
Block input containing malicious code.The number one way to block a hacker from manipulating the URL querstrying and textboxes is to block their input. But, how do you determine who they are, what they will input and whether or not it is safe? Unfortunately, you cannot know. So, you must assume that all user input could be potentially dangerous. A common saying in the programming world is that ALL INPUT IS EVIL. Thus, it must be treated with caution. Everything from everybody should be checked every time to ensure dangerous code does not slip in. This is accomplished by checking all input that is submitted via a querystring or form and then rejecting or removing unsafe characters before it ever reaches the database. If this sounds like a lot of trouble, you are right. But, it is the price we pay to protect our websites and databases from the wrath of hackers. It is your responsibility as the webmaster to ensure that only clean, safe input is allowed to enter your database.
Step 3
Input validation.To check if the input entered into the URL querystring or textbox is safe, we can use input validation rules. In other words, using ASP code on a web page can validate the input collected from the querystring or form to make sure it contains only safe characters. Once the input is deemed safe, it can be stored in a new variable, inserted into the SQL string and sent to the database.
Step 4
The wash and rinse cycle.Input validation should be a two-part process, like a wash and rinse cycle. We want to thoroughly clean all input by first checking for safe characters and second by checking for bad strings. See the resources at the end of this article for a more in depth discussion on this method.
Step 5
Only allow safe characters.Part one of the validation process is to reject all input unless it contains safe characters. This is the strictest and most effective form of input validation. It only allows input that is known to be good. Essentially, letters and numbers can be trusted. Special characters are the real culprits which give hackers their power and should be avoided. This extreme measure may not be feasible for all types of input, but try to restrict as many special characters as possible. See the resources at the end of this article for a more in depth discussion on this method.
Step6
Following is an ASP example that could be used for a login or search input field. It allows only a-z, A-Z, 0-9 and an apostrophe, hyphen and space. You can modify the function to include characters as you see fit. %'validation allows only good charactersfunction valGoodChars(input)good_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'- "valGoodChars = truefor i = 1 to len(input)c = mid(input, i, 1)if (InStr(good_chars, c) = 0) thenvalGoodChars = falseexit functionend ifnextend function'collect the form inputsearchInput = Request.QueryString("searchKeyword")‘if an unsafe input is entered the user is asked to try againif (not valGoodChars(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 7
Part two of the validation process is to reject all input if it contains bad strings. After you have collected good input with the method above, you should check it again for input that is known to be bad. Dangerous things could happen if the good character function allowed an apostrophe and hyphen, or other letter combinations like SCRIPT, SELECT, UPDATE, DELETE, etc. That is why the bad string function should be used in conjunction with the good character function. See the resources at the end of this article for a more in depth discussion on this method.
Step 8
Following is an ASP example that rejects bad characters and should be used after checking for good characters. Bad characters could include a pair of hyphens and the word script, among other things. You can modify the function to include the bad strings as you see fit. |%'validation disallows bad stringsfunction valBadStrings( input )bad_strings = Array( "--", "script" )for each i in bad_stringsif ( InStr( input, i ) <> 0 ) thenvalBadStrings = falseexit functionend ifnextvalBadStrings = trueend function‘if an unsafe input is entered the user is asked to try againif (not valBadStrings(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 9
Safely query the database.Now that the input has been laundered through the good and bad functions, it is ready to be inserted into the SQL query and executed by the database. Following is sample ASP code that inserts the searchInput. queries the database for a match, then displays the results.<%'check the database for a matchSet myRecordSet = Connect.Execute ("SELECT * FROM dbo.myTable WHERE myColumn LIKE '%" & searchInput & "%'")'display the resultsif myRecordSet.EOF then'display message no results foundelse'display resultsend if end if%>By validating the input before sending it to the database you have greatly reduced the risk of your database being compromised by hackers.
Step 10
Filter characters.Another method that can be used in conjunction with the above two functions, but is considered to be very weak when used alone, is to sanitize the input by filtering or escaping. A well-known threat is the single quote or apostrophe because it breaks the SQL statement. Following is an ASP example that renders the single quote harmless, by replacing it with two single quotes.'doubleup single quotesnewSafeString = replace(searchInput, "'", "''")Other variations for the replace function include stripping out the script tag and replacing it with a space. Or, filter out characters such as the dollar sign $ quotation mark “ semi-colon ; and apostrophe ‘ the left and right angle brackets <> the left and right parentheses ( ) the pound sign # and the ampersand &. Or convert these characters to their HTML entities.Remember to use a solution that best fits your website or consult a professional.
Step 11
If you would like to pursue more advanced security techniques, please see the resources at the end of this article. Topics discussed include, password policies, buffer overrun, creative table and column names, table name aliases, set and check data types, .bak files, stored procedures with parameters, and log files.

How to add a "Bookmark Us" Link to Your Website or Blog using Javascript

1.Open your blog or webpage code editor. There will typically be an option in your blog interface or web page design software. The default editing option is usually "Visual" or "WYSIWYG". Look for "edit HTML" or similar.

2.Within your code, locate the BODY tag. This will come after the HEAD tag. You will need to add the code below right after the BODY tag.

3.Save your page and upload to your server if you have your own website. If you have a blog, you will need to save and publish.

CSharp: How to fix following C# error in Visual Studio: Cannot apply indexing with [] to an expression of type 'System.Data.DataTable'

Error:
Cannot apply indexing with [] to an expression of type 'System.Data.DataTable'

Cause:
string variable = Table[0][1].ToString();

Solution:
DataTable Table;string variable = Table.Rows[index][column-index].ToString();

Example:

DataTable Table;
string variable = Table.Rows[0][1].ToString();

The above error happens when you try to index the datatable without first accessing the "Rows" property. You must first access the "Rows" property and then provide the index.

Find SQL Servers From Local Network - (By Command Line)

Go to command prompt and type in “osql -L” or “sqlcmd -L”.

Or
SQLCMD -L > c:\servers_filename.txt
Copy to servers_filename.txt

MS-SQL: How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure (OpenRowset)

If you are planning to use OpenRowset queries in order to connet to remote database servers or if you have already implemented OpenRowset queries as a solution to remote connections as an alternative tp linked servers in Microsoft SQL Server 2005, you should first configure the database instance to enable Ad Hoc Distributed Queries in the installed SQL Server database instance where the Ad Hoc query will run.



We should connect to the related SQL Server as an administrator and open a new query window.



To set 'show advanced options' run_value equal to 1 or to enable it, run


sp_configure 'show advanced options', 1

reconfigure


The return message from the above sql statements for a successful run is as;


Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


After Advanced Options is enabled, you can again run sp_configure t-sql command and in the returned list of configuration settings, go to row where name is 'Ad Hoc Distributed Queries' and control its run_value.

If 'Ad Hoc Distributed Queries' is turned off for considering server security run_value should be "0"But since we want to enable 'Ad Hoc Distributed Queries' component in order to run 'OpenRowset/OpenDatasource' sql statements, we should set the run_value to "1"
The below sql code is a sample how you can enable a SQL Server configuration parameter.


sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure


The returned message is :
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

Now you can see the run_value is set to 1 if you run the "sp_configure" command and control for the "Ad Hoc Distributed Queries" row.




Now you can run your OpenRowset queries successfully from your SQL Server 2005 or SQL Server 2008 (Katmai) databases.

Unity : Günlük Ciroları gösteren SQL sorgusu

Günlük Ciroları gösteren SQL sorgusu

SELECT SUM(LG_444_01_INVOICE.NETTOTAL) AS [TOPLAM Ciro], LG_444_01_INVOICE.DATE_
FROM LG_444_01_INVOICE INNER JOIN
LG_444_CLCARD ON LG_444_01_INVOICE.CLIENTREF = LG_444_CLCARD.LOGICALREF
WHERE (LG_444_01_INVOICE.CANCELLED = 0)
GROUP BY LG_444_01_INVOICE.TRCODE, LG_444_01_INVOICE.DATE_
HAVING (LG_444_01_INVOICE.TRCODE = 1 AND LG_444_01_INVOICE.DATE_ >='20090101'
AND LG_444_01_INVOICE.DATE_ <='20090131') ORDER BY LG_444_01_INVOICE.DATE_

Sonuç:

Unity : Firma ve dönemleri listeleyen sorgu

Unity'de kullanılan dönem ve firmaları listelemek için aşağıdaki sorguyu çalıştırabilirsiniz.

SELECT FIRM.NR AS FIRMA_NO,FIRM.NAME AS FIRMA_ADI,PERIOD.NR AS DONEM_NO FROM L_CAPIFIRM AS FIRM INNER JOIN L_CAPIPERIOD AS PERIOD ON FIRM.NR=PERIOD.FIRMNR ORDER BY FIRMA_NO,DONEM_NO

Unity : 2009 Aylık BS formu sorgusu

SELECT C.CODE AS CARI, C.DEFINITION_ AS ACIKLAMA, C.TAXNR AS VERGI_NO, C.TAXOFFICE AS VERGI_DAIRESI, C.TELNRS1,
SUM(INV.NETTOTAL - INV.TOTALVAT) AS TUTAR, COUNT(INV.LOGICALREF) AS BELGE_SAYISI,MONTH(DATE_) AS AY
FROM dbo.LG_444_01_INVOICE INV INNER JOIN
dbo.LG_444_CLCARD C ON INV.CLIENTREF = C.LOGICALREF
WHERE (INV.TRCODE IN (6, 7, 8, 9,14)) AND (INV.CANCELLED = 0) AND INV.DECPRDIFF=0
GROUP BY C.CODE, C.DEFINITION_, C.TAXNR, C.TAXOFFICE, C.TELNRS1,MONTH(DATE_)
HAVING (SUM(INV.NETTOTAL - INV.TOTALVAT) >= 8000)

Unity : 2009 Aylık BA formu sorgusu

SELECT C.CODE CARI
, C.DEFINITION_ ACIKLAMA
,C.TAXNR AS VERGI_NO
,C.TAXOFFICE AS VERGI_DAIRESI
,C.TELNRS1
, SUM(INV.nettotal-INV.totalvat) AS TUTAR
,COUNT(INV.LOGICALREF) AS BELGE_SAYISI
,MONTH(DATE_) AS AY

FROM LG_444_01_INVOICE INV, LG_444_CLCARD C
WHERE INV.CLIENTREF =C.LOGICALREF
and ((INV.TRCODE IN (1,2,3,4)) OR
(INV.TRCODE = 14 AND INV.DECPRDIFF=1))

AND INV.CANCELLED=0
group BY C.CODE,C.DEFINITION_,C.TAXNR,C.TAXOFFICE,C.TELNRS1,MONTH(DATE_)
HAVING SUM(INV.nettotal-INV.totalvat)>=8000

Error message "Reserved 7713" in MS-Access

This message in ODBC error. Invalid SQL returned by ODBC

Malzeme Kartı özelliklerine otomatik kayıt açmak. (LG_XXX_CHARASGN)

Unity'de malzeme kartında bulunan "Malzeme Özellikleri" tabına otomatik olarak kayıt eklemek için aşağıdaki cursor'u kullanabilirsiniz.





Kullanımı:



EXEC [dbo].[SP_CHARASGN_INSERT] '508' ,'822096031A0330640000208'

Açıklaması:
İlk parametre firma numarası, ikinci parametre ise malzeme kodudur.


SQL Kodu:
CREATE PROCEDURE [dbo].[SP_CHARASGN_INSERT]

@FIRMNR VARCHAR(5),
@NEWCODE VARCHAR(30)
AS
EXEC(
'DECLARE
@MAXREF INT,
@MAXREF2 INT,
@MAXREF3 INT,
@LOGICALREF INT,
@LINENR INT,
@CHARVALREF INT,
@CHARCODEREF INT,
@CODE VARCHAR(50),
@CHARVAL VARCHAR(50),
@SPECODE VARCHAR(50),
@CYPHCODE VARCHAR(50)

DECLARE CARI CURSOR FOR

SELECT LOGICALREF,CODE FROM LG_' + @FIRMNR + '_ITEMS (NOLOCK) WHERE LOGICALREF NOT IN (
SELECT ITEMREF FROM LG_' + @FIRMNR + '_CHARASGN) AND CARDTYPE NOT IN (20,22) AND ACTIVE =0 AND CODE =''' + @NEWCODE + '''
ORDER BY CODE

OPEN CARI
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LINENR=1
WHILE @LINENR<>5
BEGIN
SELECT @MAXREF=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARASGNSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARASGNSEQ SET LASTLREF=@MAXREF WHERE ID=1
SET @CHARCODEREF = @LINENR + 1
IF @LINENR=1 --BRAND
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_BRAND WHERE CODE=SUBSTRING(@CODE,4,3)
END
IF @LINENR=2 --TICKET
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_TICKETNO WHERE CODE=SUBSTRING(@CODE,7,3)
END
IF @LINENR=3 --METER
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_METRAJ WHERE CODE=SUBSTRING(@CODE,10,4)
END
IF @LINENR=4 --COLOR
BEGIN
SET @CHARVAL=SUBSTRING(@CODE,19,5)
END
SET @CHARVALREF = 0

SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
IF @CHARVALREF = 0
BEGIN
SELECT @MAXREF2=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARVALSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARVALSEQ SET LASTLREF=@MAXREF2 WHERE ID=1

SELECT @MAXREF3=ISNULL(MAX(VALNO),0) + 1 FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF
INSERT INTO LG_' + @FIRMNR + '_CHARVAL (LOGICALREF,CHARCODEREF,VALNO,CODE,NAME)
VALUES (@MAXREF2,@CHARCODEREF,@MAXREF3,@CHARVAL,@CHARVAL)
SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
END

INSERT INTO LG_' + @FIRMNR + '_CHARASGN (LOGICALREF,ITEMREF,CHARCODEREF,CHARVALREF,LINENR,MATRIXLOC,PRIORITY)
VALUES (@MAXREF,@LOGICALREF,@CHARCODEREF,@CHARVALREF,@LINENR,0,0)
SET @LINENR = @LINENR + 1
END
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
END
CLOSE CARI
DEALLOCATE CARI')

How Can I UpdateProgress in AjaxToolkit?

First, our page would add the following definitions.
1.ToolkitScriptManager
2.UpdatePanel
3.UpdateProgress
4.Button
5.Image





Then the properties of objects are organized as follows:

Clicking the button works ButtonClick procedure. While this process is the defining image picture looks on the update panel.

Unity'de hareket görmeyen Stok,Cari ve Muhasebe kartlarını iptal etmek

Hareket görmeyen cari kartlarını, muhasebe hesap kodlarını,malzeme kartlarını
kullanım dışı yapmak için siteden topladığım kodları tek başlık altında toplamak istedim. Kartları silmek isteyen arkadaşlara tavsiyem önce aşağıdaki kodlarlar kartları kullanım dışı yapmak ve sonra muhasebe programından kartları toplu çıkartmak olacaktır.


Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.
Sizlere tavsiyem kartları kullanım dışı yapmak yeterli olmalı. a ve b seçenekleri bu işlem için yeterli. C seçeneği ise kartları silmek için yapılan bir işlem. C seçeneğini gerçekleştirmemenizi tavsiye ediyorum.

1)Hareket Görmemiş Cari Kartları Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen cari kartların bir dökümünü alalım.
SELECT * FROM LG_108_CLCARD
WHERE LOGICALREF NOT IN (SELECT CLIENTREF FROM LG_108_01_CLFLINE) order by CODE ASC

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım
UPDATE LG_108_CLCARD
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT CLIENTREF FROM LG_108_01_CLFLINE)

c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz cari kartlar silinmiş olması gerekli.

Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.


2)Hareket Görmemiş Muhasebe Hesap Kodlarını Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen kartların bir dökümünü alalım.
SELECT * FROM LG_108_EMUHACC
WHERE LOGICALREF NOT IN (SELECT ACCOUNTREF FROM LG_108_01_EMFLINE) order by CODE ASC

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım. Ancak burada dikkat edilmesi gereken ana hesapları kullanımdışı yapmamalıyız.
UPDATE LG_108_EMUHACC
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT ACCOUNTREF FROM LG_108_01_EMFLINE)

c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz muhasebe hesap kodları silinmiş olması gerekli.

Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.

3) Hareket Görmemiş Stok Kartlarını Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen kartların bir dökümünü alalım.
SELECT * FROM LG_108_ITEMS
WHERE LOGICALREF NOT IN (SELECT STOCKREF FROM LG_108_01_STLINE)

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım.
UPDATE LG_108_ITEMS
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT STOCKREF FROM LG_108_01_STLINE)


c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz stok kartları silinmiş olması gerekli.

Unity için Navigatör kurulumu

1- *.lrf dosyaları (6 tane) navi.sys (1 tane) C:\Program Files\Microsoft Office\OFFICE11 dizinine kopyalanacak

2- LGNAVI.DLL dosyası C:\Program Files\Microsoft Office\OFFICE11\XLSTART dizinine kopyalanacak.

3- Kopyalama tamamlandığında “incorrect registry settings “ hatası veriyorsa versiyon hatası olabilir. Yaptığımız örnekte LGNAVI.dll 1.71.0.0 , LENGINE1 1.71.0.0 olduğunda sorun giderildi.

How can i install/remove a .net windows service using InstallUtil?

With Community Server 2007.1, it is now possible to install multiple instances of a Windows
Service without the need for custom MSI builds. This is done by manually
installing the Windows Service with the command line utility and specifying the
name of the service to be used.

Previously, the limitations on multiple installs were due to the product name installed with
the MSI, the hard coded service name in the service installer, and also some
registry keys that were needed by the installer. Doing multiple
installations required these to be manually changed in code and re-compiled in
a custom build.

With the new version, the hard coded limitations have been removed, allowing for one
instance of the add-ons to be installed with the MSI, and then additional
instances of the program to be activated through the command line.

How to Install

In order to install additional instances of the service, you will need to use the
InstallUtil.exe program that is included with the .NET Framework SDK (available
here). The InstallUtil.exe program is located in the .NET Framework's
installation directory, which will typically be in the
following directory:

C:\Windows\Microsoft.NET\Framework\v2.0.50727

First, you will need to create the first instance by installing using the MSI package. This extracts all of the files and allows you to quickly install your first instance. To create a second instance, you will need to make a copy of the installation directory, since the second instance will need its own set of files, configuration, and will run separately from the first instance.
The add-ons are normally installed to a folder in C:\Program Files\Telligent by default.

Once you have created a copy of the add-on's installation files, you need to register it with
Windows as a service. To activate it, you will install the service using InstallUtil.exe and add a /name parameter to specify what the service will be named. The names have to be unique, so if you already have " PostGreSQL Backup Service ", you might need to name it "PostGreSQL Backup Service". You then specify the filename of the Windows Service, which is usually like “postgrebackup.exe ". So for example, it would be:

installutil c:\postgrebackup\postgrebackup.exe /LogToConsole=true /LogFile=c:\backuplog.log

You would need to run this from a command prompt within the add-ons directory. The name
must be in quotes if it contains spaces, and it must come before the filename. After installation, if you bring up the Windows Services console (Start, Run, services.msc), you should find the new service installed and listed alphabetically.

You may need to add the path to the InstallUtil.exe program onto the command if the .NET
Framework folder isn't in your PATH setting.

How to Uninstall

Uninstalling the service is very similiar to installing. You will still use the InstallUtil.exe program and will need to specify the name of the service to uninstall, though this time you will need to add a /u switch to specify that you intend to uninstall it. For instance:

InstallUtil /u /name=" PostGreSQL Backup Service" postgrebackup.exe

The name must be specified since if you have multiple instances of the same executable
installed, it will need to know which one. If no name is specified, it will remove the one with the default name as opposed to a custom named one.

Again, the uninstall and name parameters must come before the filename so that it know they are for that file.

You may need to add the path to the InstallUtil.exe program onto the command if the .NET
Framework folder isn't in your PATH setting.