Serkan Sonmez's Diary

Logo Bordro Veritabanı tabloları listesi

2012-02-01T11:15:00.000+02:00

TABLO ADI AÇIKLAMA


L_PERSONEL	Çalışma Alanı Tanımları
L_SYSLOG	Kullanıcı Kaydı İzleme
L_LOGREP	Log Records
LH_ROLLPAR	Bordro Parametreleri
LH_PAYELEM	Genel Çalışma Parametreleri
LH_FIRMPAR	Kurum çalışma parametreleri
LH_WORKFLAG	Mesai bağlantıları
LH_LAWPAR	Kanun Parametreleri
LH_USERTAB	Kullanıcı/kanun parametre tab
LH_TYPEDEF	Tip tanımları
LH_DEFAULT	Öndeğer tanımları
LH_QCODE	Kullanıcı tanımlı kodlar
LH_PERSON	Sicil Kartı
LH_PERIDINF	Nüfus Cüzdanı bilgileri
LH_PERFIN	Sicil Mali bilgileri
LH_LAWCHG	Kanuni değişiklikler
LH_PERHIST	Sicil tarihçesi
LH_PEREXPR	Deneyim Bilgileri
LH_PWORKFLG	Personel Ödeme Kesinti Bağlant
LH_CONTACT	Erişim bilgileri
LH_HEALTH	Sağlık Hareketleri
LH_FAMILY	Sicil Aile Bilgileri
LH_RELATION	Sicil yakınlık İlişkileri
LH_TRANS	TTF Satırları
LH_PERJOBD	Personel iş tanımları
LH_ASSIGN	Atamalar
LH_PERQUALF	Sicil Nitelik Bilgileri
LH_PERFIXR	Sicil demirbaş kayıtları
LH_PERSPECD	Sicil Özel gün satırları
LH_PNTCARD	Puantaj Kartı
LH_PNTLINE	Puantaj Satırları
LH_PNTTEMP	Puantaj Hesap Şablonu
LH_PNTDEFS	Puantaj öndeğerleri
LH_PDEFLINE	Puantaj Öndeğer Satırları
LH_PAYPERD	Bordro Dönem Bilgileri
LH_PERDPAYM	Dönem Ödemeleri
LH_LOANDEF	Borç tanımları
LH_PERLOAN	Borç takip kayıtları
LH_JOBDESC	İş tanımları
LH_JOBDLINE	İş tanım satırları
LH_POSJOBDL	Pozisyon iş tanımları
LH_RANKDEF	Derece Basamak Tanımları
LH_STAFF	Kadro tanım kaydı
LH_CARPLANS	Kariyer Planı
LH_PERFDEF	Performans Tanımları
LH_PERFPERD	Dönemsel performans değerlend
LH_PERFLN	Performans Değerlendirme Satır
LH_EDUCDEF	Eğitim tanımları
LH_EDUCPLAN	Eğitim Plan Detay Satırları
LH_EDUCPRE	Eğitim Önkoşul tanımları
LH_EDUCREG	Eğitim Kayıtları
LH_PEREDUC	Personel eğitim kayıtları
LH_EDUCEVAL	Eğitim değerlendirmesi
LH_INSTDEF	Eğitmen tanımları
LH_EDUCPLC	Eğitim yeri tanımları
LH_EDUCREQ	Eğitim talepleri
LH_EDUCQLF	Eğitim Nitelik İlişkisi
LH_REMINDER	Hatırlatıcı
LH_QUALFDEF	Nitelik Tanımları
LH_FIXREG	Demirbaş Tanımları
LH_EXPENSE	Masraf kayıtları
LH_SPECDAY	Özel gün tanımları
LH_APPRJ	Başvuru projeleri
LH_APPRJLN	Başvuru proje adım/aşamaları
LH_APNOTICE	Başvuru/duyuru planları
LH_APPWORKS	Başvurulan İşler
LH_PERREQ	Açık kadro talepleri
LH_APPRJREQ	İhtiyaç belirleme
LH_APPNOM	Proje başvuru bilgileri
LH_APPLET	Kayıtlı mektuplar
LH_GENINFO	Genel Bilgiler
LH_ERROR	Bakım bilgileri
LH_EDUCREL	Eğitim ilişkileri
LH_PERDDEF	Performans Dönem Tanımları
LH_PERFGRD	Performans Değerlendirme
LH_FISERV	Fiili itibari hizmetler
LH_PERAWARD	Personel Ödül/Ceza Tanımları
LH_PERPAY	Ödeme Kesinti Satırları
LH_STAFFENT	Kadro/pozisyon kartları
LH_STAFHIST	Kadro/pozisyon tarihçesi
LH_PERFTRGT	Performans Hedef Grupları
LH_PNTLNDET	Puantaj farkı detay satırları
LH_DIFFCARD	İntibak kartı
LH_JOBFAMLY	İş ailesi
LH_JOBFAMLN	İş ailesi üyeleri
LH_CARMATRX	Kariyer matrisi
LH_CPTRGLN	Kariyer planı hedefleri
LH_CPINTSLN	Kar. Planı Yetk.Başl.Düzeyleri
LH_CPEDUCLN	Kar.Planı Gelişim Faaliyetleri
LH_CPQLFLN	Kar.Planı Yetkinlik Gelişimi
LH_CPINTLN	Kariyer planı görüşmeler
LH_CARSUGS	Kariyer önerileri
LH_PERFSUM	Performans Özet Bilgileri
LH_ACTIVITY	Aktivite tanımları
LH_VACGROUP	İzin grup tanımları
LH_VACGRPLN	İzin grup satırları
LH_CRITCGRP	Kritik grup tanımları
LH_CRIGRPLN	Kritik Grup Satırları
LH_TIMEPLAN	Zaman Planlama Kayıtları
LH_ACTPLNLN	Aktivite Plan Satırları
LH_VACTRANS	İzin devir bilgileri
LH_WORKDAYS	Çalışma Günleri
LH_EDUCFIRM	Eğitim Kurumları
LH_APPNTCLN	Görüşme/test satırları
LH_WORKCAL	Çalışma Takvimi
LH_WORKCALLN	Çalışma Takvimi Satırları
LH_SIMINFO	Sicil Emsal Bilgileri
LH_PRJREG	Proje Takibi Bilgileri
LH_PRJPLNLN	Proje Aktivite Satırları
LH_RIGHTGRP	Yetki Grubu Tanımları
LH_RIGHTGLN	Yetki Grubu Tanım Satırları

Devepress ASPxGrid üzerinde Delete/İnsert/Update butonunu gizlemek

2011-12-24T09:26:00.000+02:00

Devepress ASPxGridView üzerinde kullanıcı bazında Delete/İnsert/Update butonunu gizlemek gerekebilir. Bu işlemi yapmak için aşağıdaki kod kullanılabilir.

protected void ASPxGridView1_CommandButtonInitialize(object sender, ASPxGridViewCommandButtonEventArgs e)

{

if (e.ButtonType == DevExpress.Web.ASPxGridView.ColumnCommandButtonType.Delete)

e.Visible = false;

}

ERROR: The name "UserName" does not exits int the current context? in C sharp

2011-06-08T09:51:00.000+03:00

PROBLEM:

ERROR: The name "UserName" does not exits int the current context?

SOLUTION:
The name 'UserName' does not exist in the current context?

1. Left-Click in the "yourUserControl.cs" (solution explorer)

2. Select Properties

3. Change Build Action to "Embededd Resource"

Logo Tiger'da yeni dönem açma hatası: Tarihler Mali yıl başlangıcı ile uyumlu değil

2011-02-22T10:12:00.000+02:00

Logo Tiger'da Tiger2sys ile yeni bir dönem açılırken "Tarihler Mali yıl başlangıcı ile uyumlu değil" hatası geldiğinde;

Bu hata, önceki dönemin başlangıç zamanının yılbaşından başlamadığı durumlarda görülür.
Yeni yılda 1 Ocak'tan dönemi başlatabilmek için aşağıdaki sql cümlecikleri ile ;

SELECT * FROM L_CAPIFIRM WHERE NR=47

--ilk sorguyla firmanın LOGICALREF'i bulunur

--UPDATE L_CAPIFIRM SET BEGMON=1,BEGDAY=1 WHERE LOGICALREF=218

--ikinci sorguyla başlangıç gün ve ay bilgisi 1 olarak update edilir.

Bu işlemleri yaptıktan sonra yeni dönemi tiger2sys üzerinden açabilirsiniz.

C sharp Türkçe karakter kontrol metodu

2010-12-31T15:46:00.000+02:00

Eğer veritabanına Türkçe karakter yazmak istemiyorsanız, aşağıdaki metodu kullanabilirsiniz.

Kullanımı:
_Name += "MAIL_METIN,";
_Values += "'" + Tools.TurkceKarakterleriKaldir(HTMLCode) + "',";

public static string TurkceKarakterleriKaldir(string EskiMetin)
{
string YeniMetin = "";
string TurkceKarakterler="şŞçÇüÜİıöÖğĞ";
string YeniKarakterler ="sScCuUIioOgG";
for (int i = 0; i < EskiMetin.Length; i++)
{
bool Varmi = false;
for (int k = 0; k < TurkceKarakterler.Length; k++)
{
if (EskiMetin[i] == TurkceKarakterler[k])
{
YeniMetin += YeniKarakterler[k];
Varmi = true;
break;
}
}
if (!Varmi)
{
YeniMetin += EskiMetin[i];
}
}
return YeniMetin;
}

Tiger'da Cari Hesaba Muhasebe hesap kodunu otomatik olarak aktarmak

2010-09-03T17:24:00.002+03:00

Tiger'da Cari Hesaba Muhasebe hesap kodunu otomatik olarak aktarmak için aşağıdaki SQL scriptini çalıştırabilirsiniz.

DECLARE @carilref INT
DECLARE @carimuhlref INT
DECLARE @muhlref INT
DECLARE @carikodu varchar(50)
DECLARE @definiti varchar(50)
DECLARE @muhasebekodu varchar(50)

DECLARE @carimuhlref2 INT
DECLARE @muhlref2 INT
DECLARE @lastCarimuhSeq INT

DECLARE @eksikkayitsayisi INT

DECLARE processes CURSOR FOR
SELECT CARI.LOGICALREF,CARI.CODE AS CARI_KODU,CARIMUH.LOGICALREF AS CARIMUH_LREF,MUH.CODE AS MUHASEBE_KODU FROM LG_107_CLCARD CARI WITH (NOLOCK)
LEFT OUTER JOIN LG_107_CRDACREF CARIMUH WITH (NOLOCK) ON CARIMUH.CARDREF=CARI.LOGICALREF AND CARIMUH.TRCODE = 5
LEFT OUTER JOIN LG_107_EMUHACC MUH WITH (NOLOCK) ON CARIMUH.ACCOUNTREF=MUH.LOGICALREF
WHERE (MUH.CODE<>CARI.CODE OR MUH.CODE IS NULL) and SUBSTRING(CARI.CODE,5,2) = '47' AND CARI.CARDTYPE<>22 and CARI.CODE NOT LIKE '25%' ORDER BY CARI.CODE

OPEN processes
FETCH NEXT FROM processes
INTO @carilref,@carikodu,@carimuhlref,@muhasebekodu
WHILE @@FETCH_STATUS = 0
BEGIN
--ilk olarak LG_107_CRDACREF tablosunda kayıt var mı kontrol edilecek. eğer varsa ve muhasebe kodu yok veya hatalıysa muhasebe tablosundan
--kayıt aktarılacak.
set @muhlref2 = 0

SELECT @muhlref2=LOGICALREF FROM LG_107_EMUHACC where CODE = @carikodu
if @muhlref2 = 0
begin
insert into #EksikKayitTableGintem (Firma,CariKodu) values (107,@carikodu)
end
else
begin
if @carimuhlref IS NOT NULL
begin

UPDATE [LG_107_CRDACREF]
SET
[TRCODE] = 5
,[ACCOUNTREF] = @muhlref2
WHERE [LOGICALREF] = @carimuhlref
print 'update' + cast(@carimuhlref as varchar(20))
end
ELSE
BEGIN
print 'insert' + cast(@carimuhlref as varchar(20))
SELECT @lastCarimuhSeq = LASTLREF from LG_107_CRDACREFSEQ where ID=1
UPDATE LG_107_CRDACREFSEQ SET LASTLREF = @lastCarimuhSeq + 1 WHERE ID=1
INSERT INTO [LG_107_CRDACREF]
([LOGICALREF],[TRCODE],[CARDREF],[TYP],[ACCOUNTREF],[CENTERREF],[SITEID],[RECSTATUS],[ORGLOGICREF],[PROJECTREF])
VALUES (@lastCarimuhSeq ,5 ,@carilref,1 ,@muhlref2 ,1 ,0,0,0 ,0)
END
end

--PRINT cast(@muhlref2 as varchar(20)) + '-' + @carikodu

FETCH NEXT FROM processes
INTO @carilref,@carikodu,@carimuhlref,@muhasebekodu
END
CLOSE processes
DEALLOCATE processes

How to check if column exists in SQL Server table

2010-03-25T10:00:00.000+02:00

CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
GO

GRANT EXECUTE ON [ColumnExists] TO [whoever]
GO

USAGE:
IF ColumnExists('LG_107_SPECODES', 'MARKA') = 'F'
BEGIN
ALTER TABLE LG_107_SPECODES
ADD MARKA varChar(50) NOT NULL
END
GO

Tiger2 Bordro Asgari Geçim İndirimi Hatası

2010-03-11T14:53:00.000+02:00

Tiger2 Bordro programında Asgari Geçim İndirimde çıkan sorun için Aile bireyleri tablosunu kontrol ederek FAMILYHIST tablosuna kayıt açan SQL scripti.
Not: Bu scripti çalıştırmadan önce FAMILYHIST tablosunun yedeğini alınız.
Örnek yedek alma komutu SELECT * INTO LH_001_FAMILYHIST_20100310 FROM LH_001_FAMILYHIST

--Personel'e ait aile bireyleri girişini düzenler
DECLARE @FamilyLref INT
DECLARE @PersonLref INT
DECLARE @Support INT
DECLARE @LastSeq INT
DECLARE @IsAvail INT
DECLARE @Donem DATETIME
SET @Donem='20100201'
DECLARE processes CURSOR FOR
SELECT
dbo.LH_001_FAMILY.LREF AS FAMILY_LREF,
dbo.LH_001_PERSON.LREF AS PERSON_LREF,
dbo.LH_001_FAMILY.SUPPORTED AS SUPPORT
FROM dbo.LH_001_PERIDINF RIGHT OUTER JOIN
dbo.LH_001_FAMILY ON dbo.LH_001_PERIDINF.LREF = dbo.LH_001_FAMILY.IDREF RIGHT OUTER JOIN
dbo.LH_001_PERSON ON dbo.LH_001_FAMILY.PERREF = dbo.LH_001_PERSON.LREF
WHERE dbo.LH_001_PERSON.FIRMNR = 382
OPEN processes
FETCH NEXT FROM processes
INTO @FamilyLref,@PersonLref,@Support
WHILE @@FETCH_STATUS = 0
BEGIN
SET @IsAvail = 0
SELECT @IsAvail = LREF FROM [LH_001_FAMILYHIST] where [CARDREF] = @FamilyLref AND [BEGDATE] = @Donem
IF @IsAvail = 0
BEGIN
print @FamilyLref
SELECT @LastSeq = LASTLREF from LH_001_FAMILYHISTSEQ where ID=1
UPDATE dbo.LH_001_FAMILYHISTSEQ SET LASTLREF = @LastSeq + 1 WHERE ID=1
INSERT INTO [bordro_db].[dbo].[LH_001_FAMILYHIST] ([LREF],[BEGDATE],[ENDDATE],[CARDREF],[SUPPORTED],[INJUREDEGREE],[MINWGDISCSTAT],[ORGLOGICREF],[RECSTATUS],[SITEID])
VALUES(@LastSeq,@Donem,NULL,@FamilyLref,@Support,0,1,0,0,0)
END
FETCH NEXT FROM processes
INTO @FamilyLref,@PersonLref,@Support
END
CLOSE processes
DEALLOCATE processes

LVF dosyası nasıl açılır?

2010-02-17T10:02:00.001+02:00

LVF dosyası Logo tarafından üretilen programlarda oluşturulmuş rapor çıktısı formatıdır. Bu dosyayı açmak için Logo tarafından verilen kurulum CD'sinde Report Viewer programını bulabilirsiniz.

Elinizde CD yoksa aşağıdaki linkten gerekli Report Viewer programını indirip LVF dosyasını açabilirsiniz.

Programlar:
Report Viewer
LVF Linki:
ftp://download.logo.com.tr/Windows/TOOLS/tools/ReportViewer.rar

Örnek Program Görünümü:

Tiger/Unity'deki tarih bilgisinin çevrim işlemleri

2010-02-15T08:52:00.001+02:00

Tiger/Unity veritabanında integer olarak tutulan tarih bilgisininin datetime'a çeviren fonksiyon.

CREATE FUNCTION fn_LogoDatetoSystemDate (@DEGER INT)
RETURNS datetime
AS
BEGIN
DECLARE @GUN VARCHAR(2), @AY VARCHAR(2), @YIL VARCHAR(4)

DECLARE @SONUC datetime
SELECT
@GUN=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)) AS VARCHAR(3))
SELECT
@AY=CAST(((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(65536*(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536)))-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)))/256 AS VARCHAR(3))
SELECT
@YIL=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536) AS VARCHAR(6))
SET @GUN=CASE WHEN LEN(@GUN)<2 THEN '0'+@GUN ELSE @GUN END
SET @AY=CASE WHEN LEN(@AY)<2 THEN '0'+@AY ELSE @AY END
SONUC:
IF @DEGER<>0
BEGIN
SET @SONUC=CONVERT(DATETIME, @YIL + '-' + @AY + '-' + @GUN + ' 00:00:00', 102)
END
IF @DEGER=0
SET @SONUC= NULL
RETURN @SONUC
END

Tiger/Unity için tarih bilgisinini datetime'a integer olarak çeviren fonksiyon.

CREATE FUNCTION fn_LogoTimetoSystemTime (@GELENDEGER INT)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @SAAT VARCHAR(2), @DAKIKA VARCHAR(2), @SANIYE VARCHAR(2), @SONUC VARCHAR(8)
SELECT
@SAAT=RTRIM(CONVERT(char(20), ROUND(@GELENDEGER / 16777216, 2))),
@DAKIKA=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216)/ 65536, 2))),
@SANIYE=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216 - ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216) / 65536, 2) * 65536) / 256, 2)))
SET @SAAT=CASE WHEN LEN(@SAAT)=1 THEN '0'+@SAAT ELSE @SAAT END
SET @DAKIKA=CASE WHEN LEN(@DAKIKA)=1 THEN '0'+@DAKIKA ELSE @DAKIKA END
SET @SANIYE=CASE WHEN LEN(@SANIYE)=1 THEN '0'+@SANIYE ELSE @SANIYE END
SET @SONUC= @SAAT + ':' +@DAKIKA + ':' + @SANIYE
RETURN @SONUC
END

Logo Tiger'da Muhasebe Kodu olmayan cari kartların kontrolü

2010-02-10T10:54:00.001+02:00

SELECT LOGICALREF,CODE, DEFINITION_, CITY FROM LG_106_CLCARD WHERE
(LOGICALREF NOT IN (SELECT CARDREF FROM LG_106_CRDACREF WHERE (LG_106_CRDACREF.TRCODE = 5)))
ORDER BY DEFINITION_

Logo Tiger'da firma dönem bilgilerini listelemek

2010-02-10T10:48:00.002+02:00

SELECT FRM.NR AS FIRMA_NO,FRM.NAME AS FIRMA_ADI
DONEM.NR AS DONEM_NO FROM L_CAPIFIRM AS FRM
INNER JOIN L_CAPIPERIOD AS DONEM ON FRM.NR=DONEM.FIRMNR
order by FRM.NR,DONEM.NR

Left Join with Distinct Statement in T-SQL

2010-02-10T09:30:00.000+02:00

Problem:

SQL Left Join, it return multiple rows of the same items. I tried DISTINCT but that didnt work because its on Left Join. So my question how to add DISTINCT to Left Join?

Solution:
SELECT *
FROM dbo.LG_106_10_STLINE (NOLOCK) INNER JOIN
dbo.LG_106_ITEMS (NOLOCK) ON dbo.LG_106_10_STLINE.STOCKREF = dbo.LG_106_ITEMS.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_STFICHE (NOLOCK) ON dbo.LG_106_10_STLINE.STFICHEREF = dbo.LG_106_10_STFICHE.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_CLCARD (NOLOCK) ON dbo.LG_106_10_STFICHE.CLIENTREF = dbo.LG_106_CLCARD.LOGICALREF LEFT OUTER JOIN
dbo.L_CAPIDIV (NOLOCK) ON dbo.LG_106_10_STFICHE.BRANCH = dbo.L_CAPIDIV.NR LEFT OUTER JOIN
~~dbo.LG_106_SPECODES (nolock) LG_106_SPECODES_1~~
--This is I Changed
(select DISTINCT dbo.LG_106_SPECODES.SPECODE,dbo.LG_106_SPECODES.DEFINITION_ FROM dbo.LG_106_SPECODES (NOLOCK)) LG_106_SPECODES_1 ON dbo.LG_106_ITEMS.STGRPCODE = LG_106_SPECODES_1.SPECODE LEFT OUTER JOIN
dbo.L_TRADGRP (NOLOCK) ON dbo.LG_106_10_STFICHE.TRADINGGRP = dbo.L_TRADGRP.GCODE LEFT OUTER JOIN
dbo.LG_106_SPECODES LG_106_SPECODES_2 (NOLOCK) ON dbo.LG_106_10_STLINE.DELVRYCODE = LG_106_SPECODES_2.SPECODE LEFT OUTER JOIN
LG_106_PROJECT (NOLOCK) ON LG_106_10_STLINE.PROJECTREF = LG_106_PROJECT.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_INVOICE (NOLOCK) ON dbo.LG_106_10_STFICHE.INVOICEREF = dbo.LG_106_10_INVOICE.LOGICALREF
WHERE (dbo.LG_106_10_STFICHE.TRCODE IN (12)) AND
(LG_106_SPECODES_2.SPECODETYPE IS NULL OR LG_106_SPECODES_2.SPECODETYPE = 0)
AND (dbo.L_CAPIDIV.FIRMNR = 106)

I want to get ASCII value of string in C#, what can I do?

2010-01-20T08:26:00.000+02:00

Sample Code:

string value = "9quali52ty3";

// Convert the string into a byte[].
byte[] asciiBytes = Encoding.ASCII.GetBytes(value);

Result:

57 113 117 97 108 105 53 50 116 121 51

Logo Unity/Tiger veritabanı tabloları listesi

2010-01-18T16:44:00.001+02:00

LG_XXX_PRCLIST Alış/Satış fiyatları

LG_XXX_ASCOND Alış/Satış koşulları

LG_XXX_TOOLREQ Araç ihtiyacları

LG_XXX_XX_SRVNUMS Aylık hizmet toplamları

LG_XXX_XX_SRVTOT Aylık hizmet alış/satış toplamları

LG_XXX_XX_BNTOTFIL Banka aylık toplamları

LG_XXX_XX_BNFICHE Banka fişleri

LG_XXX_XX_BNFLINE Banka hareketleri

LG_XXX_BANKACC Banka hesapları

LG_XXX_BNCARD Bankalar

LG_XXX_LNGEXCSETS Bazı kayıtların diğer dillerdeki açıklamaları

LG_XXX_UNITSETF Birim setleri

LG_XXX_UNITSETC Birim setleri arası çevrim katsayıları

LG_XXX_UNITSETL Birimler

LG_XXX_XX_CLTOTFIL Cari hesap aylık toplamları

LG_XXX_XX_CLFICHE Cari hesap fişeri

LG_XXX_XX_CLFLINE Cari hesap hareketleri

LG_XXX_CLINTEL Cari hesap istihbarat bilgileri

LG_XXX_CLCARD Cari hesap kartları

LG_XXX_XX_CLRNUMS Cari hesap risk tabloları

LG_XXX_EMPGROUP Çalışan grubu

LG_XXX_LABORREQ Çalışan ihtiyaçları

LG_XXX_EMGRPASS Çalışan-Grup ataması

LG_XXX_EMPLOYEE Çalışanlar

LG_XXX_XX_CSROLL Çek/Senet bordroları

LG_XXX_XX_CSTRANS Çek/Senet hareketleri

LG_XXX_XX_CSCARD Çek/Senet kartları

LG_XXX_DISTTEMP Dağıtım şablonları

LG_XXX_DISTLINE Dağıtım şablonu satırları

LG_XXX_XX_PERDOC Döküman bilgileri (örnek malzeme resmi)

LG_XXX_XX_FOLDER Döküman katalog bilgileri (watermark varsa)

LG_XXX_FIRMDOC Döküman katalog girişi(watermark)

L_LDOCNUM Döküman numaralama şablonları

LG_XXX_ACCCODES Entegrasyon bağlantı kodları

LG_XXX_XX_INVOICE Faturalar

LG_XXX_XX_TRANSAC Firma dönem bilgileri

L_CDBTMP Form boyutları

L_DAILYEXCHANGES Günlük döviz kurları

LG_XXX_XX_STINVTOT Günlük malzeme ambar toplamları

LG_XXX_SRVCARD Hizmet kartları

LG_XXX_SRVUNITA Hizmet kaydı-Birim ataması

LG_XXX_DECARDS İndirim/Masraf kartları

LG_XXX_DISPLINE İş emirleri

LG_XXX_WSATTASG İş ist.-Özellik ataması

LG_XXX_WSATTVAS İş ist.-Özellik değeri ataması

LG_XXX_WORKSTAT İş istasyonları

LG_XXX_WSGRPF İş istasyonu grupları

LG_XXX_WSCHVAL İş istasyonu özellik değerleri

LG_XXX_WSCHCODE İş istasyonu özellikleri

LG_XXX_WSGRPASS İş istasyonu-grup ataması

LG_XXX_PEGGING İşlem bağlantıları (üretim emri, sipariş)

LG_XXX_QCLVAL Kalite kontrol değerleri

LG_XXX_QASGN Kalite kontrol hareketi- Kalite kontrol ataması

LG_XXX_XX_SLQCASGN Kalite kontrol hareketleri

LG_XXX_QCSLINE Kalite kontrol satırları

LG_XXX_QCSET Kalite kontrol setleri

LG_XXX_STCOMPLN Karma koli satırları

LG_XXX_CRDACREF Kart-Muhasebe kodları

LG_XXX_XX_CSHTOTS Kasa aylık toplamları

LG_XXX_XX_KSLINES Kasa işlemleri

LG_XXX_KSCARD Kasalar

L_RPFILTSXXX Kaydedilen rapor filtreleri

L_RPLAYS_XXX Kaydedilen rapor tasarımları

LG_XXX_OCCUPATN Kaynak kullanımları (üretim)

L_GOUSERS Kullanıcılar

L_CAPIDEF Kuruluş bilgileri (ambar, işyer, fabrika vb.)

LG_XXX_LOGREP LOG (izleme) kaydı

LG_XXX_XX_PRDCOST Maliyet dönem kapama kayıtları

LG_XXX_XX_STINVENS Malzeme alış/satış aylık toplamları

LG_XXX_ITEMSUBS Malzeme alternatifleri

LG_XXX_XX_STLINE Malzeme hareketleri

LG_XXX_CHARASGN Malzeme özellik ataması

LG_XXX_XX_SERILOTN Malzeme seri lot no. Bilgileri

LG_XXX_INVDEF Malzeme-Ambar bilgileri

LG_XXX_ITMUNITA Malzeme-Birim ataması

LG_XXX_ITMFACTP Malzeme-Fabrika bilgileri

LG_XXX_ITMWSDEF Malzeme-İş ist. bilgileri

LG_XXX_ITMWSTOT Malzeme-İş ist. Toplamları (günlük)

LG_XXX_ITEMS Malzemeler

LG_XXX_ITMCLSAS Malzeme-Malzeme sınıfı ataması

LG_XXX_SELCHVAL Malzeme-Özellik değerleri

LG_XXX_SUPPASGN Malzeme-Tedarikçi ataması

LG_XXX_ITMBOMAS Malzeme-Ürecetesi ataması

LG_XXX_EMCENTER Masraf malzemeleri

LG_XXX_XX_EMUHTOT Muhasebe aylık toplamları

LG_XXX_XX_EMFICHE Muhasebe fişleri

LG_XXX_XX_EMFLINE Muhasebe hareketleri

LG_XXX_EMUHACC Muhasebe hesapları

LG_XXX_ENGCLINE Mühendislik değişikliği işlemi

LG_XXX_XX_PRODUCER Müstahsil faturası

L_NET Network kontrolü (kimlerin hangi firma ve dönemle çalıştığı)

LG_XXX_OPRTREQ Operasyon ihtiyacları

LG_XXX_OPERTION Operasyonlar

LG_XXX_LNOPASGN Operasyon-Malzeme ilişkisi

LG_XXX_OPATTASG Operasyon-Özellik ataması

LG_XXX_PRVOPASG Öceki operasyon ilişkileri

LG_XXX_PAYLINES Ödeme plan satırları

LG_XXX_PAYPLANS Ödeme planları

LG_XXX_XX_PAYTRANS Ödeme/Tahsilat hareketleri

LG_XXX_SPECODES Özel kodlar

LG_XXX_CHARVAL Özellik değerleri

LG_XXX_CHARCODE Özellik kodları

L_POSTCODE Posta kodları

LG_XXX_PRCARDS Promosyon kartları

LG_XXX_COPRDBOM Reçete-ek ürün ataması

LG_XXX_FAREGIST Sabit kıymet kayıtları

LG_XXX_FAYEAR Sabit kıymet yıllık kaydı

LG_XXX_TARGETS Satış elemanı hareketleri

LG_XXX_SLSCLREL Satış elemanı-Cari hesap ilişkisi

LG_XXX_SLSMAN Satış elemanları

LG_XXX_ROUTETRS Satış rota satırları

LG_XXX_ROUTE Satış yönetim raporları

LG_XXX_XX_SLTRANS Seri/Lot hareketleri

L_SHPAGENT Sevkiyat firmaları

L_SHPTYPES Sevkiyat türleri

LG_XXX_XX_ORFICHE Sipariş fişleri

LG_XXX_XX_ORFLINE Sipariş hareketleri

LG_XXX_XX_STFICHE Stok fişleri

LG_XXX_LOCATION Stok yerleri

L_CITY Şehirler

L_TRADGRP Ticari işlem grupları

LG_XXX_TRGPAR Trigger parametreleri

L_COUNTRY Ülkeler

LG_XXX_PRODORD Üretim emirleri

LG_XXX_RTNGLINE Üretim rota stırları

LG_XXX_ROUTING Üretim rotaları

LG_XXX_BOMREVSN Ürün reçete revizyonları

LG_XXX_BOMLINE Ürün reçete satırları

LG_XXX_BOMASTER Ürün reçeteleri

How can I get the Selected Item for LookupEdit in C#?

2010-01-18T15:21:00.001+02:00

try this:
this.bankaSubeleriIdLookUpEdit.Properties.GetDataSourceValue("IBANNo", this.bankaSubeleriIdLookUpEdit.ItemIndex).ToString();

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

2009-12-04T10:23:00.004+02:00

-- 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

2009-12-02T17:09:00.001+02:00

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.

2009-12-02T15:57:00.000+02:00

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

2009-11-19T15:10:00.004+02:00

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

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)) < 64BEGIN 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 = @RetainENDELSEBEGIN 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 = @RetainENDOPEN FieldsSET @Retain = @@ERROR IF @Status = 0 SET @Status = @RetainFETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @MoreSET @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 ABORTENDIF LEN(ISNULL(@PCWrite,`*`)) > 7BEGIN 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 ABORTENDSET @DBAI = `DECLARE Records CURSOR GLOBAL FAST_FORWARD FOR` + @DBAIIF @Status = 0 EXECUTE (@DBAI+@DBAU+@DBAO) SET @Return = @@ERRORIF @Status = 0 SET @Status = @ReturnOPEN RecordsSET @Retain = @@ERROR IF @Status = 0 SET @Status = @RetainFETCH NEXT FROM Records INTO @NextSET @Retain = @@ERROR IF @Status = 0 SET @Status = @RetainSET @HeaderDone = 0WHILE @@FETCH_STATUS = 0 AND @Status = 0BEGIN 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 = @RetainENDCLOSE Records DEALLOCATE Records-- Print footer (TEXT).IF ISNULL(@File,0) = 0BEGIN SET @OutputText = @OutputText + `

` + CHAR(13) + CHAR(10)ENDELSEBEGIN SET @Save = `` + CHAR(13) + CHAR(10) IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, `Write`, NULL, @SaveEND-- Close.IF ISNULL(@File,0) <> 0BEGIN 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 = @ReturnENDABORT: -- 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 ` + @DBAEEXECUTE (@Task)SET NOCOUNT OFFRETURN (@Status)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Get dates from week number in T-SQL

2009-11-05T15:24:00.000+02:00

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.

2009-11-02T17:50:00.000+02:00

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

2009-10-15T16:36:00.000+03:00

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)

Insert Values of Stored Procedure in Table or Table Valued Function

2009-09-25T08:28:00.000+03:00

I think different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO

Traditional Method:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP
GO
/* SELECT from TempTable */
SELECT * FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO

Alternate Method: Table Valued Function

/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(MyDate SMALLDATETIME,IntValue INT)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue INT
INSERT INTO @retTestFn
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO

It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.

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

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

2009-08-04T14:40:00.001+03:00

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.

ORA-06508: PL/SQL: could not find program unit being called

2009-05-25T10:35:00.000+03:00

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action: Check that all referenced programs, including their package bodies, exist and are compatible.

Prevent cache in HTML

2009-05-25T10:34:00.001+03:00

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

2009-05-14T13:35:00.004+03:00

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

2009-05-12T14:02:00.002+03:00

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

2009-05-11T09:30:00.004+03:00

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

ORA-01722: invalid number

2009-05-11T09:30:00.001+03:00

Cause:
The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action:
Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Prevent href in Java Script

2009-05-11T09:19:00.001+03:00

Test

Disable the link above

ORA-00932: inconsistent datatypes: expected string got string

2009-05-11T09:15:00.000+03:00

Cause: One of the following:

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.

An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.

Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
an object not intended for normal use, then do not access the restricted object.

Create a for Loop in JavaScript

2009-05-11T09:07:00.002+03:00

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.

ORA-00257: archiver error. Connect internal only, until freed.

2009-05-05T13:46:00.000+03:00

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

Prevent multi-row updates with Trigger

2009-05-05T13:45:00.000+03:00

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

ORA-12535 : ORACLE instance terminated. Disconnection forced

2009-05-05T13:40:00.003+03:00

Cause: The requested operation could not be completed within the time out period.

Action: Look at the documentation on the secondary errors for possible remedy. See SQLNET.LOG to find secondary error if not provided explicitly. Turn on tracing to gather more information.

How to prevent duplication record?

2009-05-05T13:40:00.001+03:00

You can use either of the following Approach:

1. Basically Creating a Unique Constraint suffice.
2. Creating Unique index (Unique Constraint + Index) would help you in case of frequent querying based upon that column.
3. Creating an Identity column to avoid duplication.

Transaction log in sql server

2009-05-05T13:28:00.000+03:00

could be a few things affecting... are the transaction logs under a maintenance plan of some description where full backups and frequent ttransaction logs backups for a FULL recovery mode database, or, maybe you are using SIMPLE recovery mode.

do you run a shrink or some other "clean up" process ? has the database files been analysed and optimised for a size and growth ? Is there sufficient disk space ? has the lof been allowed to grow (obviously not), what is the current size ?

all these types of things might come into play as well... not necessarily just a "rogue/unruley/hungry" process sending it into a spin...

one thing you can do if you notice and increase in size:

DBCC OPENTRAN

it will show you the open transactions and the SPID's associated. You can then track back to the SPID's and see the SQL that they are using at the time.

you could automate the whole lot in a stored procedure and schedule it to run every so often... have a look at a couple of code snippets below...

--capture logspace into a table (and a general approach for collecting dbcc outputs)
create table tmp_sqlperf(databaseName varchar(100),LogSize decimal(18,5),LogSpace decimal(18,5),Status int)

insert tmp_sqlperf
exec ( 'dbcc sqlperf (logspace)')
select * from tmp_sqlperf

--check current log activity
select * from fn_dblog(NULL,NULL)
--or
dbcc log('MY_DB',-1)

--check current SPID's with opentrans
dbcc opentran

--capture the SQL /processes belonging to a specific spid
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 57
SELECT * FROM ::fn_get_sql(@Handle)
--or
select * from sys.dm_exec_sql_text(@handle)
Open in New Window Select All Not what you're looking for? Ask an Expert.

Shrink a tablespace in Oracle 10G OEM?

2009-05-01T09:25:00.001+03:00

Here are the steps that you can follow to resize a tablespace using Oracle 10G OEM.

1. To log in to Oracle Enterprise Manager Database Control:

Open your Web browser and enter the following URL

http://hostname:port/em

In a default installation, the port number is 1158. If you are unsure of the correct port number to use, look for the following line in the ORACLE_BASE\ORACLE_HOME\install\portlist.ini file:

Enterprise Manager Console HTTP Port (db_name) = port

For example, if you installed the database on a host computer named mgmt42, and the port number listed in the portlist.ini file is 5500, then enter the following URL

http://mgmt42:5500/em
Enterprise Manager displays the Database Control Login Page.

Log in to the database using the SYSMAN database user account. Enterprise Manager displays the Oracle Database home page.

Use the password you specified for the SYSMAN account during the Oracle Database installation.

2. Select/click on Administration link > then on storage link on the next page and select tablespace

3. Click on datafile name then "Edit".

Enter new datafile size in MB or KB and click on apply when finished.

Retreiving data from backup of crashed Oracle

2009-05-01T09:09:00.000+03:00

If you you have a hot backup set of all data file, control file, archived log file, do the following:

1. Create the new directories (same or different hiarachy)
2. Copy all datafiles, archived log and init files from backup set to those dirs.
3. Modify the file was created from "backup control file to trace" command. This script will be used to recreate the control file
4. Startup nomount then run the script to recreat the new control file
5. Issue:
SQL>recover database
Oracle will ask you for the archived log, apply until cancel or turn on AUTO to let Oracle find the archived log file by itself
6. Alter database open

ORA-01400: cannot insert NULL into (string)

2009-04-24T09:56:00.000+03:00

Cause: An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

For example, if you enter:

connect scott/tiger create table a (a1 number not null); insert into a values (null);

Oracle returns:

ORA-01400 cannot insert NULL into ("SCOTT"."A"."A1") : which means you cannot insert NULL into "SCOTT"."A"."A1".

Action: Retry the operation with a value other than NULL.

ORA-12154: TNS:could not resolve the connect identifier specified

2009-04-24T09:54:00.000+03:00

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action: - If you are using local naming (TNSNAMES.ORA file):

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If you are using directory naming:

- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Verify that the LDAP directory server is up and that it is accessible.

- Verify that the net service name or database name used as the connect identifier is configured in the directory.

- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

- If you are using easy connect naming:

- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Make sure the host, port and service name specified are correct.

- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

8152 - String or binary data would be truncated.

2009-04-22T17:31:00.002+03:00

Server: Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

This error happen when I tried to insert a value too long in a varchar or nvarchar
column:

CREATE TABLE TableName_(id VARCHAR(2))
go

INSERT TableName_ VALUES ('Serkan')

Resolution:
1- Most programmers are lazy they prefer not to change code (and introduce bugs)
INSERT bla VALUES (LEFT('123',2))
2 - Here is one way to do it without changing code but by setting ANSI Warnings to off

SET ANSI_WARNINGS OFF
INSERT TableName_ VALUES ('Serkan')
SET ANSI_WARNINGS ON --set it back on so code following this won't be messed up

ORA-00604: error occurred at recursive SQL level string

2009-04-22T17:24:00.001+03:00

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-01017: invalid username/password; logon denied

2009-04-22T17:23:00.000+03:00

Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password.

Action: Enter a valid username and password combination in the correct format.

ORA-12638: Credential retrieval failed

2009-04-22T17:21:00.000+03:00

Cause: The authentication service failed to retrieve the credentials of a user.

Action: Enable tracing to determine the exact error.

MS SQL Server: Disconnect Users From Database with KILL SID (Kill User Session)

2009-04-21T08:44:00.003+03:00

You can delete all the active session with the following query.

DECLARE @spid INT
DECLARE @query NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID('Your_Database_Name')
AND spid != @@SPID
OPEN processes
FETCH NEXT FROM processes
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'KILLING '+RTRIM(@spid)
SET @query = 'KILL '+RTRIM(@spid)
EXEC(@query)
FETCH NEXT FROM processes
INTO @spid
END
CLOSE processes
DEALLOCATE processes

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

2009-04-20T08:22:00.006+03:00

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)

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0

2009-04-17T15:51:00.003+03:00

ERROR MESSAGE:
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

RESOLUTION:
We have to enable the Database Mail feature on the server. In the SQL Server Management Studio to use Transact SQL to enable Database Mail, execute the following statement.

use master
go
sp_configure ’show advanced options’,1
go
reconfigure with override
go
sp_configure ‘Database Mail XPs’,1
–go
–sp_configure ‘SQL Mail XPs’,0
go
reconfigure
go

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

HTTP Error 500 - Internal server error in IE

2009-04-17T14:36:00.002+03:00

Error Code: 500 Internal Server Error. The request was rejected by the HTTP filter. Contact your ISA Server administrator. (12217)

RESOLUTION:
The 500 Internal Server Error is a "server-side" error, meaning the problem is not with your PC or Internet connection but instead is a problem with the web site's server. Even though the issue is not yours to troubleshoot or resolve, there are a few things you can do:
Retry the web page by clicking the refresh/reload button or trying the URL from the address bar again. Even though the 500 Internal Server Error is reporting a general error on the web site's servers and not your computer, the server error may only be temporary. Trying the page again will often be successful.

Note: If the 500 Internal Server Error message appears during the checkout process at an online merchant, be aware that duplicate attempts to checkout may end up creating multiple orders - and multiple charges! Most merchants have automatic protections from these kinds of actions but it's still something to keep in mind.

Come back later. The 500 Internal Server Error message is one of the most common error message seen when checking out during an online purchase so sales are often disrupted. This is usually a great incentive to resolve the issue very quickly.

If you can't wait any longer for the problem to be resolved or if you'd like to help out, you may want to attempt to contact the webmaster or another website contact and advise them of their server error. The webmaster of most Internet sites can be reached via email at webmaster@website.com, replacing website.com with the actual website name.

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

2009-04-17T14:08:00.003+03:00

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

2009-04-17T12:12:00.002+03:00

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

2009-04-17T11:55:00.000+03:00

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.

How can be used to remove all database pages and clear cache from the memory in SQL Server?

2009-04-17T11:42:00.000+03:00

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

How do I reduce the size of my MS SQL database?

2009-04-17T11:26:00.000+03:00

Shrink the size of the database data and log files.

Syntax
DBCC SHRINKDATABASE ('database' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE ('database_id' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE (0 option [,option]) [WITH NO_INFOMSGS]

Options:
target_percent
NOTRUNCATE
TRUNCATEONLY

Key:
0 - Shrink the current database

target_percent - Percentage of free space to remain in the database file

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Only one of the truncate options can be specified - they do not apply to log files.

Example

DBCC SHRINKDATABASE (MyDatabase);

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

2009-04-17T11:17:00.002+03:00

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

ORA-12838: cannot read/modify an object after modifying it in parallel

2009-04-17T11:14:00.000+03:00

ORA-12838: cannot read/modify an object after modifying it in parallelIs it a must that i should commit immediately After that delete inorder to avoid that above error.

Resolution:

It is a limitation of parallel DML .

Once a table has been modified by a parallel DML statement, that table cannot be accessed in a subsequent SQL statement (serial or parallel, DML or query) in the same transaction before the transaction is committed.

Please check the following link for other limitations.
http://www.oracle.com/technology/oramag/oracle/05-may/o35dba.html

So you either have to remove the parallel execution, or commit after the delete.

BTW, if you are deleting every row, it's better to truncate the table. Be aware though that truncate is DDL therefore implies a commit in it.

Error Code 10061: Connection refused in Internet Explorer

2009-04-17T11:10:00.001+03:00

Error Code 10061: Connection refused
Background: When the gateway or proxy server contacted the upstream (Web) server, the connection was refused. This usually results from trying to connect to a service that is inactive on the upstream server.

Resolotion:

Problem was resolved by publishing another Web Chaining Rule, to redirect external requests to our ISP's upstream proxy.

Last Default Rule is set to retrieve requests directly

Reading Information from the LDAP Server with C#

2009-04-16T08:47:00.003+03:00

Bring information down from the server and format it into an XmlDocument object. So let's add some variables to our method. I have initialized them to string.Empty and for the instance of AuthenticationTypes, I have initialized it to Anonymous.

string domainAndUsername = string.Empty;
string userName = string.Empty;
string passWord = string.Empty;
AuthenticationTypes at = AuthenticationTypes.Anonymous;

Next we populate the connectivity information with real values:

domainAndUsername = @"LDAP://123.12.12.123/dc=youcanlearnseries,dc=abc.us,dc=com";
userName= "kenno" ;
passWord= "password";
at = AuthenticationTypes.Secure;

Yes, we could have populated this information when we initialized the objects, but if you want to pass in multiple Directory servers, Active Directory, Sun One, etc., then this allows you to create the variable and then populate it later after user input.

Then we create an instance of a DirectoryEntry object. This object encapsulates a node in the Directory Services hierarchy. We pass into the object the connectivity information we created previously.

DirectoryEntry entry = new DirectoryEntry(domainAndUsername,userName, passWord,at);

To actually query the Directory server, we will create an instance of a DirectorySearcher object and we will pass into the DirectorySearcher object the DirectoryEntry object we created in the previous step.

DirectorySearcher mySearcher = new DirectorySearcher(entry);

The results of the query are stored in a collection object, SearchResultCollection, called results.

SearchResultCollection results;

We still want to filter the final results so we add the filter query to the mySearcher object.

mySearcher.Filter = filter;

And finally, we query the LDAP repository, storing the results in the results collection.

results = mySearcher.FindAll();

The rest of the formula is simple yet slightly confusing in its implementation. We have all the information returned for our query of the Directory Server stored in a SearchResultCollection object as a collection of properties and values. We are going to build our own XML object to return to our calling web service. Using a foreach loop, we iterate through the result collection, pulling out properties first and then within each property we find the actual value stored in the hierarchy. The code below has been shortened to show how the loop through the collection works.

foreach(SearchResult resEnt in results)
{
ResultPropertyCollection propcoll=resEnt.Properties;
foreach(string key in propcoll.PropertyNames)
{
foreach(object values in propcoll[key])
{ switch (key)
case "sn":
sb.Append(key.ToString() + ""
+ values.ToString() + "");
break;
case "cn":
sb.Append(key.ToString() + ""
+ values.ToString() + "");
break;
case "name":
sb.Append(key.ToString() + ""
+ values.ToString() + "");
break;
}
}
}
}

The "key" that we need is determined by the LDAP directory you are using. Sun One uses different keys than Microsoft's Active Directory. So the system administrator might be able to inform you of the keys to use for your specific application, or you filter at a higher level and see what keys are returned and break it down yourself.

Each user that we select from our query we are going to wrap in a tag of our XML object. Notice that we create them as a StringBuilder object and then before we send them to the calling object, we load them into an XmlDocument object. We could have created an XML schema and then loaded them into the XmlDocument as we go, but for this example, I felt that was more information than needed.

How can I list Locked and inactive sessions in Oracle?

2009-04-14T13:17:00.002+03:00

SELECT c.owner, c.object_name, c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time, vs.module, vs.machine,
vs.osuser, vlocked.oracle_username, vs.SID, vp.pid,
vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid,
vs.process
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || UPPER ('&tab_name_leaveblank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED'; --<-change it

Query execution time in Oracle?

2009-04-14T13:07:00.001+03:00

Oracle 10g has R1 and R2 (r stands for Release).
I hope you have installed R3 (10.2.0.x)

To sped up the processing you have to do 2 important things:

1. To investigate and possibly to increase the size of SGA components - db_buffer_cache, shared_pool_cache

2. To run the statistics package (as SYS):

CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;
/

Now either run:

execute compute_statistics;

or schedule once weekly:

set serveroutput on
set linesize 200000
variable x number;
begin
DBMS_OUTPUT.enable(200000);
dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
commit;
dbms_output.put_line(TO_char(:x));
end;

How to find last executed queries in Oracle?

2009-04-14T13:06:00.000+03:00

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text,b.status
from v$SQLTEXT_WITH_NEWLINES a
, v$session b, v$process c
where a.address = b.sql_address
and a.username='&username'
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece

Which query is currently executing to debug the database performance?

2009-04-14T13:03:00.000+03:00

Open an interactive sql session to the server you are interested in using Sybase Central.

do and sp_who and see who is running queries at the moment.

dbcc traceon(7201)
go

dbcc sqltext(spid) --- where spid is the id of the client running the query you are interested in
go

This will give you the last executed statement. If your data server isn't a busy one, then this may help you in debugging

How to find last executed queries in SQL server 2005?

2009-04-14T12:53:00.001+03:00

SELECT deqs.last_execution_time AS [DateTime], 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 DESC

JavaScript Special Characters in C#

2009-04-13T13:51:00.004+03:00

In JavaScript you can add special characters to a text string by using the backslash sign.

Insert Special Characters
The backslash (\) is used to insert apostrophes, new lines, quotes, and other special characters into a text string.

Look at the following JavaScript code:

var txt="We are the so-called "Vikings" from the north.";
document.write(txt);

In JavaScript, a string is started and stopped with either single or double quotes. This means that the string above will be chopped to: We are the so-called

To solve this problem, you must place a backslash (\) before each double quote in "Viking". This turns each double quote into a string literal:

var txt="We are the so-called \"Vikings\" from the north.";
document.write(txt);

Code Outputs
\' --> single quote
\" --> double quote
\& --> ampersand
\\ --> backslash
\n --> new line
\r --> carriage return
\t --> tab
\b --> backspace
\f --> form feed

Example 2

_List.ListRow.AddColumn("" + GetStatus(_COSTLINE.GetSTATUS()) + " ", "Left", false, "7%");

How to change postgresql Sequence number?

2009-04-13T08:19:00.002+03:00

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);

The Tabular Data Stream (TDS) version 0x730a0003 of the client library used to open the connection is unsupported or unknown.

2009-04-09T08:32:00.004+03:00

Some customers have reported connection failure because of wrong TDS version.
The client application gets the following error message: (or similar depends on the protocol used)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

When you check the errorlog of your SQL Server, you see the following error message:

The Tabular Data Stream (TDS) version 0x730a0003 of the client library used to open the connection is unsupported or unknown. The connection has been closed. [CLIENT: x.x.x.x]

Error: 17802, Severity: 20, State: 1.

Depends on your client version, 0x730a0003 could be 0x73080003 or 0x73090003 as well.

The failure only happens when connecting to SQL Server 2008 CTP releases. Usually the client involved uses SQLClient which is part of .Net Framework. The reason here is that we don't supported connection between intermediate TDS version. SNAC usually ships with SQL Server and they are managed to be in the same TDS level. If you download SNAC CTP separately or make connection across machines with different level of SNAC, you may also see similar error. As a background, for 0x730a0003 TDS version, 73 represents TDS major version 7.3, 0x03 is the minor version for TDS7.3, 0xa is the intermediate TDS version number.

We have shipped the following intermediate TDS version for SQL Server 2008.

· 08 first 7.3 TDS version in Katmai (Shipped with Katmai CTP3 --Jun 2007--, Orcas Beta1)

· 09 TVP (Table Value Parameter) and NewDateTime (Shipped with Katmai CTP4 --build #1049, July 2007--, Orcas Beta2)

· 0A Large UDT (Shipped with Katmai CTP5 --build #1185, Nov 2007--, Orcas RTM, Vista SP1)

· 0B SparseColumn (Shipped with Katmai CTP6 --build #1321 Feb 2008-- )

All .Net Framework 3.5 released after Orcas RTM is on 0x0A TDS level. All SQL Server 2008 release after CTP6 (including RC0 and RTM) is on 0x0B TDS level. Connection can only be made between same TDS intermediate version, with one exception:

0B level of SQL Server can also accept 0A level of client. Thus, Orcas RTM can connect to SQL Server 2008 RTM without problem.

With the mechanism in place, it's fairly easy to tell which client can connect to which server. e.g. connection from Orcas RTM to Katmai CTP4 server would fail.

Orcas RTM contains .Net Framework 3.5. Some words worth to be quoted here: "As with .NET Framework 3.0, version 3.5 uses the CLR of version 2.0. In addition, it installs .NET Framework 2.0 SP1 and .Net Framework 3.0 SP1."

As a final note, you should not see the failure when there is no CTP server or beta version of client involved. Connection between RTM of client and server is always guaranteed.

Expiring a cookie in CSharp

2009-04-09T08:12:00.001+03:00

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?

Export and import to Oracle

2009-04-09T08:03:00.003+03:00

Prerequisites/Requirements :

exp and imp are utilities present in the $ORACLE_HOME/bin directory and are installed when Oracle is installed. Their prime purpose is to move logical objects out of and into the database respectively - for example dumping all of the tables owned by a user to a single file is achieved using the exp utility. It is important to distinguish between dumping data in this manner and backing up the database which is normally achieved using the rman utility.
Starting with Oracle 10g these two utilities are deprecated and the Oracle recommended alternatives are the data pump versions of these utilities which provide a number of new features including the ability to disconnect and reconnect to an interactive session (so a direct logon to the server isn't required) and improved performance. There are still a number of things that cannot be achieved with the data pump utilities - in particular exporting/importing across a named pipe which is a technique used by database administrators when the export file needs to be compressed on the fly or when one wishes to export directly to an import session.

Before executing these commands, the environment should be set correctly for the user in particular if the examples used on this page are followed the $ORACLE_HOME, $ORACLE_SID and $PATH environment variables need to be set appropriately in a unix environment and the %ORACLE_SID% environment variable needs to be set in a windows environment. If you login as the owner of the oracle software (usually the oracle user on unix) these are likely to have been set in the users profile. The Oracle Database instance needs to be up in order to export/import data. Type 'imp help=y' or 'exp help=y' for a detailed explanation of the available options for these utilities. Some brief examples follow to illustrate the usage of these utilities.

Using exp:

To export the entire database to a single file dba.dmp in the current directory.

- Login to server

exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or

exp SYSTEM/password PARFILE=params.dat

where params.dat contains the following information

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y
LOG=dba.log

To dump a single schema to disk (we use the scott example schema here)

- Login to server which has an Oracle client

exp / FIlE=scott.dmp OWNER=scott

To export specific tables to disk:

- Login to server which has an Oracle client

exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
-the above command uses two users : scott and hr

exp / FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user

Using imp:

To import the full database exported in the example above.

imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema

imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner

imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

To import just the scott schema exported in the example above

imp / FIlE=scott.dmp

If you do not supply any parameters then you enter an interactive session as illustrated below.

$>imp
Import: Release 9.2.0.6.0 - Production on Thu Mar 29 15:07:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: SYSTEM
Password: password
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Import file: expdat.dmp > /mention/path/of/dumpFile/includingFileName.dmp
Enter insert buffer size (minimum is 8192) 30720> (press enter to accept default)
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no > press enter
Ignore create error due to object existence (yes/no): no > press enter
Import grants (yes/no): yes > press enter
Import table data (yes/no): yes > press enter
Import entire export file (yes/no): no > press enter or type no
Username: give the userName for which you want the data to be imported
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: press enter
. importing TST_001_V2's objects into TST_001_V2

Good practices

Always take care about CHARSETS when you do export and import. Using the wrong ones can convert your data in a lossy manner. The best situation is when your source and destination database have the same character sets, so you can avoid completely any character conversion. You control this behaviour by setting NLS_LANG environment variable appropriately. When not set properly you may see 'Exporting questionable statistics' messages.
After doing an export, it is better to check your dump by doing an import with the parameter SHOW=Y. This checks the validity of your dump file.

Other considerations

You may need to patch your Oracle client (where you are running exp/imp) to the same level as the Oracle server to prevent errors
When importing large amounts of data consider dropping indexes prior to the import to speed up the process and re-creating them once the import is completed
The amount of archivelogs that may be created on a large import may fill up your disk
On INSERT triggers will fire, consider whether these need to be disabled
Increasing the RECORDLENGTH (max 65535) parameter can improve the length of time to perform an import/export as well as DIRECT=y for export

Referance: http://wiki.oracle.com/page/Oracle+export+and+import+?t=anon

SQL Query that search all column names based on criteria

2009-04-08T17:17:00.005+03:00

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

[Fatal Error] Internal Error: LA30

2009-04-08T15:37:00.000+03:00

Try deleting all .dcu files and the debugger info (.tds?) then forcing a full build (not just compile).

How to Deal With Errors in C++

2009-04-08T15:29:00.001+03:00

As a programmer, it's your job to produce robust systems that run smoothly at all times. You don't have to write functions that check flags and return cryptic error codes. Program recovery from a bad situation can be smooth because of a mechanism called C++ Exception Handling. Objects called Exceptions can be "thrown" from the error site and "caught" by an Exception Handler, giving you a chance to set things right.

1.Create an exception class for each base class in your design using a C++ development environment. All can share a common structure. For a File base class create a FileException exception class, for a GameEntity base class create a GameEntityException exception class. Get the pattern?

2.Place the exception class inside the public area of the base class. The exception class has to be visible as it will be called by the handler.

3.Give each exception class a constructor that accepts an input string. The input string will contain the error message that will be displayed when the exception object gets caught by the handler.

4.Create a handler function for each exception class you have designed. Each handler function is an overloaded "catch" function differing in the type of input argument. The first line of the two handlers would look like, "catch(File::FileException* e) {…" and "catch(GameEntity::GameEntityException* e) {…". Place these exception handler functions right underneath 'main.'

5.Inundate your programming space with "throw" statements. Put them in two major areas. Insert "throw" statements where you see potential for trouble, "if (/*divide by zero*/) {throw Math::MathException("division by zero");}. Declare a 'throw' statement on the right of a function or class that calls exceptions, "void Divide(/*parameters*/) throw (Math::MathException) {…".

6.Insert your execution code inside "main," within "try" braces. The C++ exception handling mechanism has a try, throw and catch structure. Not only does this make programs extremely robust but it is pleasant to read

How to Merge ASP With HTML

2009-04-08T15:21:00.002+03:00

Merging ASP and HTML enables you to create a dynamic website with a wealth of information.
Below is an illustration of how to create an ASP page for classic ASP and ASP.NET 2.0that show the login credentials of a user.

1.Write the programming language you will use for the ASP code. Your options are VBScript, JavaScript and VB or C# for ASP.NET.

Classic ASP
< %@Language="VBSCript,"

ASP.NET
< %@Page Language="VB," %>

2.Name the HTML Header and part of BODY section
< HTML>
< HEAD>
< TITLE >Login Page< /TITLE >
< /HEAD >

3. Begin the BODY section

< BODY BGCOLOR="White" TOPMARGIN="10" LEFTMARGIN="10" >

< FONT SIZE="4" FACE="ARIAL, HELVETICA" >
< B >Login Page< /B>< /FONT >< BR >

< HR SIZE="1" COLOR="#000000" >

4. Put ASP code within the HTML text

You logged in as user: <%= Request. ServerVariables ("LOGON_USER") %>

< P>You were authenticated using:< B> < %= Request.ServerVariables("AUTH_TYPE")% >< /B> authentication.

5.Insert the closing Tags for BODY and HTML
< /BODY>
< /HTML>

6. Save the file. Use .asp for classic ASP file and .aspx for ASP.NET file.
e.g UserCredential.asp or UserCredential.aspx

PHP vs ASP vs JSP vs ColdFusion vs PERL

2009-04-08T14:43:00.001+03:00

CF - I was an expert at it at one time - before java intragration. Costs $$$ - so why use it when there are so many good free ones out. Didn't scale as well as the other languages (when i used it in the past)

PERL - like the pervious guy said - perl is great - but why, when php is faster to develope, learn, and use. (runs faster too)

PHP - its great for small and medium sites. for large - complex sites with complex business logic, jsp/asp.net win hands down. Better tools to reuse logic with those. Runs very fast. the php/MySQL combo is hard to beat. Very stable.

asp - ok, easy to learn, not as easy as CF. Runs faster then CF, slower then PHP. If you don't already know it - move on to .net

asp.net - good web language. The fastest out there and very easy to scale. But again - you have to pay for the OS. Very stable.

JSP - only JSP and asp.net are really fully object oriented and you can reuse your code in websites, serverside apps and client apps. JSP is a little harded to learn (java vs VB) JSP can be fast - but its harder to tune then asp.net. Out of the box - asp.net is faster.

SQL Server vs MySQL vs Oracle vs PostgreSQL

2009-04-08T14:41:00.000+03:00

As far as features, performance and power goes, Oracle is leader of the pack clearly. There are more features and tools available for Oracle than for any other DB. However, you will be paying, and paying dearly. You will be able to rest assured that your DB system is the best that money can buy.

Now in most cases, money *is* an object, and as such tradeoffs must be made. In my opinion, PostgreSQL is just below Oracle for powerand features. In fact, there are some things now that PGSQL even do better than Oracle especially in terms of transaction concurrency, PGSQL's MVCC (Multi Version Concurrency Control) model is widely accepted as beign the best conscurrency handling system there is. And its catching up. PGSQL version 8 is due out any day now, there are huge performance increases and features being added in the new version such as native Windows support (it will now install and run on any Win32 system including NT, 2000, XP and 2003), replication in the Slony-I project which brings PGSQL's scaleability to Oracle's calibre.

MySQL is a non-fitter in my opinion. It would be a great product, but since PGSQL's performance has taken great steps in the last few years there is little room for this between PGSQL and SQLite for MySQL to fit in. (SQLite is an ultralightweight, ultrafast SQL database that is embeddable and can be used for small simple projects, and is blisteringly fast in such uses.) MySQL has a *huge* following, and I'm risking a lynching bad mouthing it here, but I personally feel that it is a has-been product with benefits that were once leaders of the pack but have since been overtaken by superior products, namely PostgreSQL and SQLite.

MS SQL is a good product to work with if you're a die hard Windows and Microsoft user and are familiar enough with the other software that goes with it, and are comfortable paying large amounts of money for licences. Personally, with far superior open source options, I feel that MS SQL is a silly choice unless you have your hands tied by other considerations such as licencing constraints or compatibility with other MS products.

Finally, my overall view is that I use SQLite for small projects such as web polls and guestbooks where only the standard SQL commands are sufficient and there is no need for transactions, row/column/table locking, stored procedures and the like. I use PostgreSQL for large projects requiring transactions, stored procedures and with replication I cannot see myself involved in anything too big for PG. In fact I can't think of anything outside genetics and other highly specialised fields where PG would not suffice.

Finally, PG is distributed under the BSD licence, which means there is no chance of licencing compliance issues in your application, and you are guaranteed that there will never be any costs associated with the use of the DB itself.

List of all Tables in Sybase

2009-04-08T09:10:00.002+03:00

CREATE TABLE #TT (IDENT NUMERIC(12) IDENTITY,
...... all the rest of your columns in your result set...)

SELECT ob.name as ParentProc, bo.name as ChildProc, bo.type as ChildType
INTO #procdepends
FROM sysdepends dp
JOIN sysobjects ob ON ob.id = dp.id
JOIN sysobjects bo ON bo.id = dp.depid
WHERE ob.type = 'P' AND bo.type = 'P'

INSERT INTO #TT
select pd1.ParentProc as Root, isnull(pd1.ChildProc,'') as Level1,
isnull(pd2.ChildProc,'') as Level2,
isnull(pd3.ChildProc,'') as Level3, isnull(pd4.ChildProc,'') as Level4,
isnull(pd5.ChildProc,'') as Level5
from #procdepends pd1
left outer join #procdepends pd2
on pd2.ParentProc = pd1.ChildProc
left outer join #procdepends pd3
on pd3.ParentProc = pd2.ChildProc
left outer join #procdepends pd4
on pd4.ParentProc = pd3.ChildProc
left outer join #procdepends pd5
on pd5.ParentProc = pd4.ChildProc
left outer join #procdepends pd6
on pd6.ParentProc = pd5.ChildProc
where pd1.ParentProc not in (select ChildProc from #procdepends)
order by pd1.ParentProc, pd1.ChildProc,
pd2.ChildProc, pd3.ChildProc,
pd4.ChildProc, pd5.ChildProc,
pd6.ChildProc

SELECT CASE WHEN A.ROOT = B.ROOT THEN '' ELSE A.ROOT END AS ROOT,
CASE WHEN A.LEVEL1 = B.LEVEL1 THEN '' ELSE A.LEVEL1 END AS LEVEL1,
............ the rest of your levels the same way....
FROM #TT A -- Current record
LEFT OUTER JOIN #TT B -- Prior record
ON A.IDENT = B.IDENT + 1

Table Counts on SQL Server

2009-04-08T08:54:00.001+03:00

DECLARE @SQL nvarchar(2000),
@TableName sysname,
@TableCount integer

Declare @TableCounts table(TableName sysname, TableCount integer)

SET NOCOUNT ON

DECLARE TableCursor CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'U' ORDER BY 1

OPEN TableCursor

FETCH NEXT FROM TableCursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL = N'SELECT @TableCountOut = COUNT(*) FROM [' + @TableName + ']'
exec sp_executesql @SQL, N'@TableCountOut integer output', @TableCount output
Insert @TableCounts (TableName, TableCount) Values (@TableName, @TableCount)
FETCH NEXT FROM TableCursor
INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Select * From @TableCounts

Can I find past queries from the sysobjects table?

2009-04-08T08:52:00.000+03:00

Questions:
Is there any way to look in logs, or to set up logs, that will track historical queries into the sysobjects table? The intruders seem to always be going after this table first to find our schema - is there any way to tell when a query was directed against that table?

Resolutions:
Profiler would work for future statements only...but it would work great for them. But... you're not going to be able to get at the past select statements

Do not run stored procedure if file does exist using sysobjects

2009-04-08T08:49:00.001+03:00

if not exists (SELECT * FROM Warehouse..sysobjects WHERE name = 'sales' and convert(varchar, crdate, 102) = CONVERT(varchar, GETDATE(), 102)) goto ExitHere

Sysobjects equivalent in PostgreSQL?

2009-04-08T08:36:00.000+03:00

Foreign keys can be queried like this:

select t.constraint_name, t.table_name, t.constraint_type,
c.table_name, c.column_name
from information_schema.table_constraints t,
information_schema.constraint_column_usage c
where t.constraint_name = c.constraint_name
and t.constraint_type = 'FOREIGN KEY'
and c.table_name = 'mytable';

DB Status in SQL Server : Indicates the number of records of all databases objects. (Stored procedure, User Table, Indexes)

2009-04-08T08:31:00.002+03:00

DECLARE @db_name AS varchar(100)
DECLARE @sql AS nvarchar(1000)
DROP TABLE #DB_Stats
CREATE TABLE #DB_Stats (
dbname varchar(100),
Description varchar(100),
Total_count int)

DECLARE cur_DB_Names CURSOR FOR
SELECT name FROM master..sysdatabases

open cur_DB_Names

Fetch next from cur_DB_Names into @DB_name

WHILE @@fetch_status =0
begin

SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''User_table'', count(*) from [' + @DB_name + ']..sysobjects where xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''indexes'', count(i.name) from [' + @DB_name + ']..sysobjects n join [' + @DB_name + ']..sysindexes i on (n.id = i.id) where n.xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''V'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''V'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''P'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''P'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''DTS'',COUNT(distinct cast(id as varchar(128))) from [msdb]..sysdtspackages'
exec sp_executesql @sql
Fetch next from cur_DB_Names into @DB_name
end

CLOSE cur_DB_Names
deallocate cur_DB_Names

SELECT * FROM #db_stats

String Format for Double [C#]

2009-04-07T17:00:00.002+03:00

String Format for Double [C#]

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.
Digits after decimal point
This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

// just two decimal places
String.Format("{0:0.00}", 123.4567); // "123.46"
String.Format("{0:0.00}", 123.4); // "123.40"
String.Format("{0:0.00}", 123.0); // "123.00"

Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.

// max. two decimal places
String.Format("{0:0.##}", 123.4567); // "123.46"
String.Format("{0:0.##}", 123.4); // "123.4"
String.Format("{0:0.##}", 123.0); // "123"

Digits before decimal point
If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567); // "123.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"

Thousands separator
To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.

String.Format("{0:0,0.0}", 12345.67); // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"

Zero
Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).
Following code shows how can be formatted a zero (of double type).

String.Format("{0:0.0}", 0.0); // "0.0"
String.Format("{0:0.#}", 0.0); // "0"
String.Format("{0:#.0}", 0.0); // ".0"
String.Format("{0:#.#}", 0.0); // ""

Align numbers with spaces
To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.

String.Format("{0,10:0.0}", 123.4567); // " 123.5"
String.Format("{0,-10:0.0}", 123.4567); // "123.5 "
String.Format("{0,10:0.0}", -123.4567); // " -123.5"
String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "

Custom formatting for negative numbers and zero
If you need to use custom format for negative float numbers or zero, use semicolon separator „;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.

String.Format("{0:0.00;minus 0.00;zero}", 123.4567); // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"

Some funny examples
As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.

String.Format("{0:my number is 0.0}", 12.3); // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"

ORA-06502: PL/SQL: numeric or value error string

2009-04-07T13:12:00.000+03:00

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

ORA-04932: increment or adjust of sequence number failed

2009-04-07T13:11:00.000+03:00

Cause: A call to the SSN failed to increment the sequence number.

Action: Verify that the MC hardware is functioning properly. If it is not, contact Digital"s customer support organization. If it is, contact Oracle support.

ORA-04931: unable to set initial sequence number value

2009-04-07T13:10:00.000+03:00

Cause: A call to the SSN failed to set the sequence number to its initial value, possibly caused by an MC hardware problem.

Action: Verify that the MC hardware is functioning properly. If it is not, contact Digital"s customer support organization. If it is, contact Oracle support.

ORA-04930: open sequence number failed or initial state is valid

2009-04-07T13:09:00.000+03:00

Cause: Either Shared Sequence Number OS component was not installed properly, or an MC hardware failure may have occurred or a previous instance was not shut down properly.

Action: Verify that there are no background or foreground Oracle processes from a previous instance on this node using the OS command ps -ef|grep . Verify that there are no shared memory segments belonging to the user which owns the Oracle installation by isuing the ipcs -b OS command. If there are shared memory segments or processes still on the system, use svrmgrl to shutdown the instance with the abort option. If the instance is not up, verify that the cluster software and/or the hardware is installed and working. Log in as superuser and issue the cnxshow command. Are all of the nodes in the cluster listed? Are they members of the cluster? Is the communications between nodes okay? If the answer to any of these questions is false, contact Digital"s customer support organization.

Disable all jobs on a SQL Server

2009-04-07T08:08:00.000+03:00

Have you ever needed to quickly disable all jobs on a SQL Server? If you have a lot of jobs to disable, then this stored procedure will help you out. We move our systems to our disaster recovery site twice per year for disaster recovery testing. As part of this process, we need to disable all jobs on our SQL Servers. It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you want to save all of the seconds that you can.

CREATE PROC isp_Disable Jobs
AS
SET NOCOUNT ON
CREATE TABLE #Job_Names
(
Job_Name SYSNAME NOT NULL
)
INSERT INTO #Job_Names

SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name
DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM #Job_Names
SET @job_id = NULL
OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name
END
CLOSE disable_jobs
DEALLOCATE disable_jobs
DROP TABLE #Job_Names
RETURN

If you want to quickly enable all jobs, just change @enabled = 0 to @enabled = 1. Change the stored proc name as well so that it makes sense.

How can I list a stored procedures in SQL Server?

2009-04-06T17:22:00.002+03:00

use [AustriaUnity]
GO
select [Object_id],[name],[type_desc] from sys.objects where type='P' and [name] LIKE '%%'

How to get ID of element that triggered an event with class name

2009-04-06T17:10:00.001+03:00

function overfade(evnt)
{
var obj = Event.element(evnt);
alert(obj.id);
}

Return characters only on key press event in JavaScript

2009-04-06T17:00:00.002+03:00

Question:
How can I return only character to be entered A-Z a-z on key press event . Restrict numbers and other key using javascript

Resolution:
< script>
function char(e) {
e = (e)?e:window.event;
key = String.fromCharCode(e.keyCode);
if (key.match(/[a-zA-Z]/)) return true;
e.returnValue=null
return false

}
< /script>
< input onkeydown="return char(event)">

How to Convert Unix Epoch to Datetime in MS SQL

2009-04-06T15:16:00.001+03:00

This article will demonstrate an easy way to convert an Integer Field that stores an UNIX Epoch number into a Human readable DATETIME value. This is very important when dealing with data imported from UNIX systems. This article will use a temp table to effectly demonstrate the use of the query funtion.

1. Example using MS SQL Server Management Studio Open a SQL Query window Using MS SQL Server Management Studio or your preferred SQL Query Tool to a database which you have access.

2. Execute the following to setup the table and data required for the deminstration;

* Create a TEMP Table in MS SQL by running the following;
* CREATE TABLE #tmp_epoch_test (unix_epoch_time INT);
* Insert a known value to test retreive later;
* INSERT INTO #tmp_epoch_test VALUES (1232648493);

3. Execute the following to return the DATETIME, human readable format;

* SELECT Dateadd(ss, unix_epoch_time,'19700101')
* FROM #tmp_epoch_test;

Returned Results should be: "2009-01-22 18:21:33.000"

4. Execute the following to remove the TEMP TABLE;

DROP TABLE #tmp_epoch_test

How to enable and disable advance options and Ole Automation Procedures in SQL Server 2005

2009-04-06T15:14:00.001+03:00

The system stored procedures in SQL Server are very useful. You can find a system stored procedure to do almost any task. However, in order to use these stored procedures, you need to enable Ole Automation Procedures.

In SQL Server 2005, the option is disabled by default.

You might recieve an error similar to the following

SQL Server blocked access to procedure 'sys.sp_OACreate of component 'Ole Automation Procedures

if you are trying to use the Ole Automation Procedures and the option is turned off.

You will need to enable advance options before enabling Ole Automation Procedures.

1. Use the following code to show the current status of advance option and Ole Automation procedure status.

-- Show Advance Options, 0 = disabled, 1 = enabled
EXEC sp_configure 'show advanced options';
GO

-- Show Ole Automation Procedures, 0 = disabled, 1 = enabled
EXEC sp_configure 'Ole Automation Procedures';
GO

2. Use the following code to enable the advance option and Ole Automation Procedures.

-- Enable the Advance options if they are not already turned on
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

3. Use the following code to disable the advance option and Ole Automation Procedures.

-- Disable the Advance options
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

-- Disable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO

How to find the disk size of a table in Microsoft SQL Server

2009-04-06T15:08:00.002+03:00

1.Connect to the SQL server instance. Depending on what I'm doing, I usually use SQL Management Studio, or my own custom script / application.

2.Execute "sp_spaceused". As you can imagine there are a few commands that will allow you to figure out your table size. I prefer "sp_spaceused".

usage: "sp_spaceused 'Name of your table'"
example: "sp_spaceused 'user_accounts'"

Yes, the table name is treated like a varchar / string for this command. Something else to keep in mind is that this particular procedure looks at the [sysindexes] system table. There are instances where this table can become a little out of sync; like right after an index is dropped. To compensate for this, there is an optional parameter that you can add to force the recalculation before execution. Just simply add a "true" to the end.

example: "sp_spaceused 'user_accounts', true"

For reference, the true simply forces the system to recalculate by executing "dbcc updateusage (0)" for all the tables, or "dbcc updateusage ('Name of your table')" for a specific table.

3. Analyze the results. The data returned includes the "name", "rows", "reserved", "data", "index_size", and "unused".

- "name", the name of the table.
- "rows", the number of records in the table.
- "reserved", the amount of space currently allocated for the table.
- "data", the current space used by the table's data.
- "index_size", the current size of the table's indexes.
- "unused", the amount of unused space reserved for the table.

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

2009-04-06T09:43:00.001+03:00

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

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

2009-04-06T09:41:00.001+03:00

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)

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

2009-04-06T08:48:00.000+03:00

Check these ;

a) Typo in instance name or wrong instance name. The instance name is not the one you are targeting.
b) Target SQL Server is not running
c) Named Pipe is not enabled on the server. In this case, the SQL server is not listenning on the specific pipe name.

ORA - 12514 :TNS the listener could not solve the SERVICE_NAME appearing in the descriptor of connection

2009-04-06T08:44:00.000+03:00

If the SERVICE_NAME parameter is used, the methods below will assist you in
successfully implementing it in the TNSNAMES.ORA file:

1. Use the GLOBAL_DBNAME parameter in the LISTENER.ORA for each SID that you
wish to identify as a separate service. Use the value of this parameter as
the value of the SERVICE_NAME parameter. Of course, any changes made to the
LISTENER.ORA to accomplish this will need to be made active by stopping and
then restarting the listener process.

2. Use the values of the parameters existing in the INIT.ORA, namely
SERVICE_NAMES and DB_DOMAIN to ascertain the value of the SERVICE_NAME that
should be used in the TNSNAMES.ORA. The valid construction of this value is
. with the dot "." separating the two INIT.ORA
values. If your SERVICE_NAMES=BIKES and your DB_DOMAIN=COM, then your
SERVICE_NAME=SONMEZ.COM

3. If you do not have a DB_DOMAIN parameter set in your INIT.ORA, or a
GLOBAL_DBNAME in the LISTENER.ORA, then you can simply use the SERVICE_NAMES
from the INIT.ORA in your TNSNAMES.ORA for parameter SERVICE_NAME.
EXAMPLE:
INIT.ORA contains:
service_names = "UNITY"
db_domain not set

Then Tnsnames.ora entry is:
(CONNECT_DATA =(SERVICE_NAME = "UNITY"))

4. If you have multiple values specified in the SERVICE_NAMES parameter
in the init.ora then use one of them
If SERVICE_NAMES is not set then db_name.db_domain parameters from
INIT.ORA file can be used.

5. If SERVICE_NAMES and DB_DOMAIN is not set in the init.ora or a
GLOBAL_DBNAME in the LISTENER.ORA,then your SERVICE_NAME
in Tnsnames.ora file will be DB_NAME

ORA 27102 - out of memory

2009-04-06T08:41:00.001+03:00

Problem:
ORA 27102 - out of memory

Resolution:
It seems you are in Unix/Linux environment. In this case the SWAP partition of your computer should be 3 times the RAM or if the RAM is 1GB the swap can be 2GB.
If you are in Windows you have to provide also TEMP environment variable and ensure enough disk space for swapping.

Check your physical memory:

In Windows:
go through TASK manager->performance

in UNIX:

$ sysdef grep mem
$sysdef grep swap

in Linux:

$ cat /proc/meminfo

ORA:00571 insufficient privileges

2009-04-06T08:38:00.000+03:00

Start SQL*Plus and then connect with SYSDBA privileges to the database containing the recovery catalog:
% sqlplus "sys/change_on_install as sysdba"
Create a user and schema for the recovery catalog:
SQL> CREATE USER rman IDENTIFIED BY rman
2 DEFAULT TABLESPACE tools
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON tools;

User created.
Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with privileges to maintain and query the recovery catalog:
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Grant succeeded.
Grant other desired privileges to teh RMAN user:
SQL> GRANT CONNECT, RESOURCE TO rman;

Grant succeeded.
After creating the catalog owner you should now create the catalog itself by using the CREATE CATALOG command within the RMAN interface. This command will create the catalog in the default tablespace of the catalog owner. you will need to connect to the database that will contain the catalog as teh catalog owner as follows:
% rman catalog rman/rman@catdb

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to recovery catalog database
recovery catalog is not installed
Now, run the CREATE CATALOG command to create the catalog. Note that this process can take several minutes to complete.
RMAN> create catalog;

recovery catalog created