Mehmet Akif Yanbak: MSSQL

MSSQL etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

31 Ağustos 2021 Salı

3 Aralık 2020 Perşembe

DECLARE @body NVARCHAR(MAX)
DECLARE @mSubject VARCHAR(MAX);
DECLARE @date DATETIME;
SET @date = CAST(CONVERT(CHAR(10), GETDATE(), 101) AS DATETIME);

SET @body = '<html><head>
                   <title>Email Başlık</title>
                   </head><body>
                   <table border="1" cellpadding="0" cellspacing="0" width="100%">
                   <tr style="text-align:left";>
                   <th>Plaka</th>
                   <th>İl</th>
                   <th>İlçe</th>
                   <th>Adres</th>
               </tr>';

SET @body = @body + CAST(
                   (
                       SELECT

                       'td/@align' = 'left',
                       td = T.Plaka,
                       '',

                       'td/@align' = 'left',
                       td = T.IlAdi,
                       '',

                       'td/@align' = 'left',
                       td = T.IlceAdi,
                       '',

                       'td/@align' = 'left',
                       td = T.Adres,
                       ''
                       From (
                        SELECT * FROM Adres
                       ) T
                       FOR XML PATH('tr'), ELEMENTS
                   )
                   AS NVARCHAR(MAX))


SET @body = @body + '</table></body></html>';
SET @mSubject = 'Falcon - ' + CONVERT(CHAR(10), DATEADD(dd, -1, @date), 104) + ' Konumu Çekilemeyen Adresler';

EXEC msdb.dbo.sp_send_dbmail
@recipients = N'akifyanbak@mail.com',
@copy_recipients =N'cc@mail.com',
@body = @body,
@body_format = 'HTML',
@subject = @mSubject,
@profile_name = 'sql_email_profile';

21 Kasım 2020 Cumartesi

MSSQL C# Data Tip karşılıkları

31 Ağustos 2020 Pazartesi

Enlem boylam uzaklık hesaplama

Since you're using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)

Gives

----------------------
538404.100197555

(1 row(s) affected)

Telling us it is about 538 km from (near) London to (near) Edinburgh.

Naturally there will be an amount of learning to do first, but once you know it it's far far easier than implementing your own Haversine calculation; plus you get a LOT of functionality.

If you want to retain your existing data structure, you can still use STDistance, by constructing suitable geography instances using the Point method:

DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=53.381538 set @orig_lng=-1.463526

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);

SELECT *,
    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326)) 
       AS distance
--INTO #includeDistances
FROM #orig dest

14 Mayıs 2020 Perşembe

Veritabanı ortamları oluşturma

DECLARE @environments TABLE (Environment VARCHAR(255) not null);
INSERT @environments(Environment) VALUES ('Test'),('Dev'),('DevAkif'),('DevGuest');
DECLARE @environment VARCHAR(255);
DECLARE @prefix VARCHAR(255)='DB_Name_';
DECLARE @path VARCHAR(255) =N'C:\MSSQL\Backup\Yesterday\Backup.bak';
DECLARE @backup_dbname VARCHAR(255) = 'DB_Name';
DECLARE @backup_dbname_log VARCHAR(255) = 'DB_Name_log';
DECLARE @data_path VARCHAR(255) ='C:\MSSQL\DATA\';
DECLARE @mdf VARCHAR(4)= '.mdf';
DECLARE @log VARCHAR(4)='_log';
DECLARE @ldf VARCHAR(4)= '.ldf';

DECLARE CreateEnvironmentsCursor CURSOR FOR
SELECT Environment FROM @environments;

OPEN CreateEnvironmentsCursor
FETCH NEXT FROM CreateEnvironmentsCursor INTO @environment ;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @environment+ ' yedek alınıyor...';
    DECLARE @environment_db_name VARCHAR(255) =@prefix+@environment;
    DECLARE @environment_logical_name VARCHAR(255) = @environment_db_name;
    DECLARE @environment_mdf_path VARCHAR(255) = @data_path +@environment_db_name+ @mdf;
    DECLARE @environment_logical_log_name VARCHAR(255) = @environment_db_name + @log;
    DECLARE @environment_ldf_path VARCHAR(255) = @data_path +@environment_db_name+@log+@ldf;
    IF EXISTS (SELECT name FROM sys.databases WHERE name = @environment_db_name)
        BEGIN
            EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = @environment_db_name
            USE [master]
            EXEC('DROP DATABASE '+@environment_db_name)
        END
    RESTORE FILELISTONLY FROM DISK=@path

    RESTORE DATABASE @environment_db_name
    FROM DISK = @path
    WITH
    MOVE @backup_dbname TO @environment_mdf_path,
    MOVE @backup_dbname_log TO @environment_ldf_path

    EXEC('ALTER DATABASE '+@environment_db_name+' MODIFY FILE (NAME=['+@backup_dbname+'], NEWNAME='+@environment_logical_name+')');
    EXEC('ALTER DATABASE '+@environment_db_name+' MODIFY FILE (NAME=['+@backup_dbname_log+'], NEWNAME='+@environment_logical_log_name+')');

    EXEC('USE '+@environment_db_name +'; '
    +' ALTER DATABASE '+@environment_logical_name+' SET RECOVERY SIMPLE WITH NO_WAIT;'
    +'DBCC SHRINKFILE('+@environment_logical_log_name+') ;'
    +'ALTER DATABASE '+@environment_logical_name+' SET RECOVERY FULL WITH NO_WAIT;');

    IF USER_ID(@environment_db_name) IS NULL
    BEGIN
        CREATE USER [TestUser] FOR LOGIN [TestUser] -- Önceden oluşturulan user
    END
        ALTER ROLE [db_owner] ADD MEMBER [TestUser]

    FETCH NEXT FROM CreateEnvironmentsCursor INTO @environment
END

CLOSE CreateEnvironmentsCursor
DEALLOCATE CreateEnvironmentsCursor

13 Mayıs 2020 Çarşamba

SINGLE USER modundan veritabanını MULTI USER olarak ayarlama

use master
GO

select 
    d.name, 
    d.dbid, 
    spid, 
    login_time, 
    nt_domain, 
    nt_username, 
    loginame
from sysprocesses p 
    inner join sysdatabases d 
        on p.dbid = d.dbid
where d.name = 'dbname'
GO

kill 56 --=> kill the number in spid field
GO

exec sp_dboption 'dbname', 'single user', 'FALSE'
GO

https://stackoverflow.com/questions/14652923/set-database-from-single-user-mode-to-multi-user

Is there any way to get a list of open/allocated cursors in SQL server?

USE MASTER
GO
select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name
, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id

Açık kalan Cursorları listeleme

Kaynak: https://stackoverflow.com/questions/265605/is-there-any-way-to-get-a-list-of-open-allocated-cursors-in-sql-server

16 Mayıs 2018 Çarşamba

SQL'de Tüm Tabloların Geçerli "Identity Seed" değerini bulma

SELECT
    TABLE_NAME AS [Table],
    IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Id,
    IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableHasIdentity') = 1
AND
    TABLE_TYPE = 'BASE TABLE'
    order by Seed

23 Kasım 2017 Perşembe

Sql türkçe karakterli kolonda arama yapma

SQL_Latin1_General_CP1_CI_AS olan db için türkçe kolonlarda arama yapmak istiyorsak o kolonun ayarını yapmamız gerekiyor SQL_Latin1_General_CP1_CI_AI
CaseSensitivity
CI specifies case-insensitive (Büyük / küçük harf duyarlılığı belirtmez), CS specifies case-sensitive (Büyük / küçük harf duyarlığını belirtir.)

AccentSensitivity
AI specifies accent-insensitive (aksan-duyarsızlaştırmayı belirtir), AS specifies accent-sensitive(aksanlı duyarlılığı belirtir).

10 Ekim 2017 Salı

MSSQL max index uzunluğu

Uyarı! Bir kümelenmemiş dizini için maksimum anahtar uzunluğu 1700 bayttır. 'IX_Value' dizininin maksimum uzunluğu 2048 bayttır. Bazı büyük değer birleşimlerinde ekleme/güncelleştirme işlemleri başarısız olur.

24 Nisan 2017 Pazartesi

Sql try catch - uzun sorgu için

BEGIN TRY
       BEGIN TRANSACTION

       --Buraya sorgular yazılır

       --Bi sıkıntı yoksa commit
       COMMIT TRANSACTION


       --sıkıtnı olursa rolback
       --ROLLBACK TRANSACTION

END TRY
BEGIN CATCH
       --Catch düştüyse rollback yapılır
       ROLLBACK TRANSACTION
END CATCH

23 Nisan 2017 Pazar

Veritabanı adı değiştme

ALTER DATABASE [old_name]
 
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [old_name]
 
 MODIFY NAME = [new_name]
GO
ALTER DATABASE [new_name]
 SET MULTI_USER
GO

20 Aralık 2016 Salı

Mssql "null" kolonu "not null" yapma

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

18 Ekim 2016 Salı

MSSQL Tarih oluşturma

DATEFROMPARTS(ycolumn, mcolumn, dcolumn)

31 Ağustos 2016 Çarşamba

MSSQL Backup

RESTORE FILELISTONLY FROM DISK='C:\Db\Backup.bak'

RESTORE DATABASE Db_Name
FROM DISK = N'C:\Db\Backup.bak'
WITH
MOVE 'backup_dbname' TO 'C:\MSSQL\DATA\Db_Name.mdf',
MOVE 'backup_dbname_log' TO 'C:\MSSQL\DATA\Db_Name_log.ldf'
Go

30 Ağustos 2016 Salı

MSSQL Ortalama veri ekleme hızı

DECLARE @count int;
SET @count=(select count(0) from Table_Name)

DECLARE @start datetime;
DECLARE @end datetime;
DECLARE @datediff int;
Set @end=(select top 1 CreatedDate from Table_Name order by CreatedDate desc)
SET @start=(select top 1 CreatedDate from Table_Name order by CreatedDate )
SET @datediff=( select DATEDIFF(second,@start,@end) AS DIFFDATE)

print ltrim(str(@datediff))+' saniyede toplam '+ltrim(str(@count))+' veri eklendi.';
print 'Ortalama '+ltrim(str(@count/@datediff))+' veri/saniye';

20 Ağustos 2016 Cumartesi

Tarihe göe günlük kayıt sayısı

SELECT Convert(char(8), LogDate, 112), count(distinct RefundId) FROM RefundProcessing GROUP BY Convert(char(8), LogDate, 112)

10 Ağustos 2016 Çarşamba

Mssql Function'dan dönen tablonun kolon sayısını alma

SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS WHERE TABLE_NAME = 'Function_Name'

7 Nisan 2016 Perşembe

sqlcmd komutlar

Sqlcmd
  [-U login id] [-P password] [-S server] [-H hostname] 
  [-E trusted connection] [-d use database name] [-l login timeout]
  [-N encrypt connection] [-C trust the server certificate]
  [-t query timeout] [-h headers] [-s colseparator] [-w screen width]
  [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
  [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"]
  [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel]
  [-W remove trailing spaces] [-u unicode output]
  [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile]
  [-f <codepage> | i:<codepage>[,o:<codepage>]]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting] [-b On error batch abort]
  [-v var = "value"...]
  [-X[1] disable commands[and exit with warning]]
  [-? show syntax summary]

https://technet.microsoft.com/tr-tr/library/ms165702(v=sql.105).aspx

6 Mart 2016 Pazar

MS SQL - While Döngüsü

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

---------

DECLARE @cnt INT = 11;

WHILE @cnt < 10000
BEGIN
INSERT INTO [dbo].[Chat]
           ([Name]
           ,[Content]
           ,[Lock]
           ,[CreatedDate]
           ,[ModifiedDate]
           ,[State]
           ,[Archive]
           ,[CreatedBy]
           ,[ModifiedBy]
           ,[Summary])
     VALUES
           ('Sohbet '+STR(@cnt)
           ,'İçerik'+STR(@cnt)
           ,0
           ,Getdate()
           ,Getdate()
           ,1
           ,0
           ,'admin'
           ,'admin'
           ,'Özet' +STR(@cnt))
   SET @cnt = @cnt + 1;
END;
GO