Tag: sql server

SQL SERVER SEARCH ALL DATABASE SPECIFIC TABLE NAME

On By semt20In SqlLeave a comment 
USE YENIDATA
GO

DECLARE @dbname VARCHAR(500)
DECLARE @tblname NVARCHAR(500)

SET @tblname = 'LG%'

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT * FROM [sys].[databases] d
                INNER JOIN [sys].[master_files] m ON d.database_id = m.database_id
                WHERE d.state_desc = 'ONLINE'
                AND m.state = 0 -- ONLINE
                AND d.name = @dbname
                AND EXISTS (SELECT 1 FROM [sys].[tables] t WHERE t.name LIKE @tblname))
    BEGIN
        PRINT 'Tables starting with [' + @tblname + '] exists in database [' + @dbname + ']'
        EXEC ('USE [' + @dbname + ']; SELECT TABLE_CATALOG+''.''+TABLE_SCHEMA+''.''+TABLE_NAME AS ''Table Name'' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''' + @tblname + ''' AND TABLE_TYPE=''BASE TABLE''')
    END
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

SQL SERVER BÜTÜN VERİTABANLARINDA VIEW ARA

On By semt20In SqlLeave a comment 

    DECLARE @views TABLE (ViewName NVARCHAR(4000)) 
    DECLARE @ViewName NVARCHAR(4000) = 'PDA'
    DECLARE @query NVARCHAR(4000) = 'SELECT ''?'' + ''.'' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''.'' + v.name from [?].sys.views v inner join sys.schemas s on v.schema_id=s.schema_id WHERE v.name LIKE ''%' + @ViewName + '%'''

    INSERT INTO @views (ViewName)
    EXEC sp_msforeachdb @query

    SELECT  * 
    FROM    @views

SQL SERVER SONUCU HTML OLARAK KAYDET

On By semt20In SqlLeave a comment 

--CREATE PROCEDURE  [dbo].[P_HTML]  
--as
declare @bodyOzet varchar(max)
declare @bodyDetay varchar(max)
/*
CREATE TABLE [dbo].[HTML](
	[HTMLCODE] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
 
--DOSYAYA KAYDETMESI ICIN AYAR ACILMALI
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
GO

--DOSYAYA KAYDETME KOMUTU
declare @sql varchar(8000)
select @sql = 'bcp "select * from [DATABASE].[dbo].HTML" queryout E:\SONUC.html -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

 --ISLEM SORGUSU
 exec dbo.P_HTML 
*/
set @bodyDetay = cast( (
select  td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + UrunKodu + '</td><td>' + UrunAdi + '</td><td>' + Barkod + '</td><td>' + cast( Miktar as varchar(30) ) + '</td><td>' + cast( SatirNetTutar as varchar(30) )
from (
      select top 100 percent 
	  Tarih=		(select SF.DATE FROM StockFiches SF WHERE SF.Id=SL.StockFicheId),
	  UrunKodu=		(select P.Code FROM Products P WHERE P.Id=SL.StockId),
	  UrunAdi=		(select P.ProductName FROM Products P WHERE P.Id=SL.StockId),
	  Barkod=		(select P.Barcode FROM Barcodes P WHERE P.ProductId=SL.StockId),
      Miktar=		SL.Amount,
      SatirNetTutar=SL.LineNet
      from StockLines SL 
      
      ) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )

set @bodyDetay = N'Malzeme Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
          + '<tr><th>Tarih</th><th>Ürün Kodu</th><th>Ürün Adı</th><th>Barkod</th><th>Miktar</th><th>Tutar</th></tr>'
          + replace( replace( @bodyDetay, '&lt;', '<' ), '&gt;', '>' )
          + '</table>' 

/*aşağısı özet*/

set @bodyOzet = cast( (
select  td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + OdemeTuru + '</td><td>' + Cast(Toplam as Varchar) + '</td><td>' + Cast(IndirimToplam as Varchar) + '</td><td>' + cast( NetToplam as varchar(30) ) 
from (
select Tarih,OdemeTuru,Sum(Toplam) Toplam, Sum(IndirimToplam) IndirimToplam,Sum(NetToplam) NetToplam from (
SELECT 
	CONVERT(DATE, F.CreatedDate) AS Tarih  
	, CASE F.IO WHEN 0 THEN L.Total ELSE 0 - L.Total END AS Toplam
	, CASE F.IO WHEN 0 THEN L.TotalDiscount ELSE 0 - L.TotalDiscount END AS IndirimToplam
	, CASE F.IO WHEN 0 THEN L.LineNet ELSE 0 - L.LineNet END AS NetToplam
	, L.VatInc KDVDH
	, L.Vat KDV
	, CASE  F.PayType  
		WHEN 0 THEN 'Nakit Kasa'
		WHEN 1 THEN 'Banka Pos' 
		WHEN 2 THEN 'Kredi Kartı' 
		WHEN 3 THEN 'İkram' 
		END OdemeTuru 
	FROM StockLines L
	Left Join StockFiches F ON F.Id = L.StockFicheId 
	WHERE  
        F.FicheType in (0,1)  
	) as t group by Tarih,OdemeTuru  
      
      ) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )

set @bodyOzet = N'Özet Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
          + '<tr><th>Tarih</th><th>Odeme Turu</th><th>Toplam</th><th>Indirim Toplam</th><th>NetToplam</th></tr>'
          + replace( replace( @bodyOzet, '&lt;', '<' ), '&gt;', '>' )
          + '</table><br>' + @bodyDetay

truncate table HTML
insert into HTML(HTMLCODE) VALUES(@bodyOzet)
 
GO

PERKOTEK MYSQL LINK TO SQL SERVER

On By semt20In pdks, Sql2 Comments

Perkotek klasorunde mysql binde bu versionda test edildi;

MYSQL Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)

My sql connector mysql-connector-odbc-3.51.30-winx64.msi indirildi

Control panelde system dns yaratıldı;

Sql serverde linked servere sağ tıklayıp new deyip aşağıdaki ayarlar yapıldı

Bunların true olmasına dikkat et;

Tablolar geldi;

Link server üzerinden örnek sorgu çalıştır;

Spye çevir;

LOGO SAYIMIN EKSIKMI FAZLAMI OLDUGU SORGUSU

On By semt20In LOGO ERP, SqlLeave a comment 

SELECT  LOGICALREF, LOGICALREFPDA,STFICHEREF, FIS FROM (
SELECT AMOUNT SAYILAN,ONHAND STOKTA,CASE WHEN ONHAND<AMOUNT THEN 'SAYIM EKSIGI' WHEN ONHAND>AMOUNT THEN 'SAYIM FAZLASI' ELSE '2' END FIS,* FROM(
SELECT  *,(SELECT  ISNULL(SUM(STITOTS.ONHAND),0)  FROM LV_119_01_STINVTOT STITOTS WITH(NOLOCK)  WHERE (STITOTS.INVENNO = STL.SOURCEINDEX) AND 
(STITOTS.STOCKREF =  STL.STOCKREF) AND (STITOTS.DATE_  <= STL.DATE_)) ONHAND FROM BM_PDA_STLINE STL WHERE TRCODE=55) AS T) AS T2 GROUP BY  LOGICALREF, LOGICALREFPDA,STFICHEREF, FIS