RSS
 

Archive for the ‘SQL Server’ Category

Vergi Kimlik No Doğrulama SQL Fonksiyonu

01 Apr

Bu küçük SQL fonksiyonunu Vergi Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;

IF dbo.FN_VKNO_DOGRUMU(1111111111)=1
	PRINT 'DOGRU VERGI KIMLIK NO'
ELSE
	PRINT 'YANLIS VERGI KIMLIK NO'

Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.

CREATE FUNCTION [dbo].[FN_VKNO_DOGRUMU](@VKNO VARCHAR(10))
RETURNS BIT
BEGIN
DECLARE
@V_LAST_DIGIT SMALLINT,
@V_1 SMALLINT,
@V_2 SMALLINT,
@V_3 SMALLINT,
@V_4 SMALLINT,
@V_5 SMALLINT,
@V_6 SMALLINT,
@V_7 SMALLINT,
@V_8 SMALLINT,
@V_9 SMALLINT,
@V_11 SMALLINT,
@V_22 SMALLINT,
@V_33 SMALLINT,
@V_44 SMALLINT,
@V_55 SMALLINT,
@V_66 SMALLINT,
@V_77 SMALLINT,
@V_88 SMALLINT,
@V_99 SMALLINT,
@TOPLAM SMALLINT,
@R Bit
--
SET @V_1 = (CAST(SUBSTRING(@VKNO,1,1) AS SMALLINT) + 9) % 10
SET @V_2 = (CAST(SUBSTRING(@VKNO,2,1) AS SMALLINT) + 8) % 10
SET @V_3 = (CAST(SUBSTRING(@VKNO,3,1) AS SMALLINT) + 7) % 10
SET @V_4 = (CAST(SUBSTRING(@VKNO,4,1) AS SMALLINT) + 6) % 10
SET @V_5 = (CAST(SUBSTRING(@VKNO,5,1) AS SMALLINT) + 5) % 10
SET @V_6 = (CAST(SUBSTRING(@VKNO,6,1) AS SMALLINT) + 4) % 10
SET @V_7 = (CAST(SUBSTRING(@VKNO,7,1) AS SMALLINT) + 3) % 10
SET @V_8 = (CAST(SUBSTRING(@VKNO,8,1) AS SMALLINT) + 2) % 10
SET @V_9 = (CAST(SUBSTRING(@VKNO,9,1) AS SMALLINT) + 1) % 10
SET @V_LAST_DIGIT = CAST(SUBSTRING(@VKNO,10,1) AS SMALLINT)
--
SET @V_11 = (@V_1 * 512) % 9
SET @V_22 = (@V_2 * 256) % 9
SET @V_33 = (@V_3 * 128) % 9
SET @V_44 = (@V_4 * 64) % 9
SET @V_55 = (@V_5 * 32) % 9
SET @V_66 = (@V_6 * 16) % 9
SET @V_77 = (@V_7 * 8) % 9
SET @V_88 = (@V_8 * 4) % 9
SET @V_99 = (@V_9 * 2) % 9
--
IF @V_1 != 0 AND @V_11 = 0 SET @V_11 = 9
IF @V_2 != 0 AND @V_22 = 0 SET @V_22 = 9
IF @V_3 != 0 AND @V_33 = 0 SET @V_33 = 9
IF @V_4 != 0 AND @V_44 = 0 SET @V_44 = 9
IF @V_5 != 0 AND @V_55 = 0 SET @V_55 = 9
IF @V_6 != 0 AND @V_66 = 0 SET @V_66 = 9
IF @V_7 != 0 AND @V_77 = 0 SET @V_77 = 9
IF @V_8 != 0 AND @V_88 = 0 SET @V_88 = 9
IF @V_9 != 0 AND @V_99 = 0 SET @V_99 = 9
--
SET @TOPLAM = @V_11 + @V_22 + @V_33 + @V_44 +  @V_55 + @V_66 +  @V_77 + @V_88 + @V_99
IF @TOPLAM %10 =0
BEGIN
SET @TOPLAM=0
END
ELSE
BEGIN
SET @TOPLAM = (10 - (@TOPLAM %10))
END
--
IF @TOPLAM = @V_LAST_DIGIT
SET @R = 1 -- DOĞRU
ELSE
SET @R = 0 -- YANLIŞ
--
RETURN @R
--
END
 
No Comments

Posted in SQL Server, Türkçe

 

TC Kimlik No Doğrulama SQL Fonksiyonu

01 Apr

Bu küçük SQL fonksiyonunu TC Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;

IF dbo.FN_TCNO_DOGRUMU(11111111111)=1
	PRINT 'DOGRU TC KIMLIK NO'
ELSE
	PRINT 'YANLIS TC KIMLIK NO'

Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.

CREATE FUNCTION [dbo].[FN_TCNO_DOGRUMU](@TcNo BIGINT)
RETURNS BIT
AS
BEGIN
DECLARE @ATCNO BIGINT
DECLARE @BTCNO BIGINT
DECLARE @C1 Tinyint
DECLARE @C2 Tinyint
DECLARE @C3 Tinyint
DECLARE @C4 Tinyint
DECLARE @C5 Tinyint
DECLARE @C6 Tinyint
DECLARE @C7 Tinyint
DECLARE @C8 Tinyint
DECLARE @C9 Tinyint
DECLARE @Q1 INT
DECLARE @Q2 INT
DECLARE @R Bit
 
SET @ATCNO = @TcNo / 100
SET @BTCNO = @TcNo / 100
 
IF LEN(CONVERT(VARCHAR(19),@TcNo)) = 11
BEGIN
SET @C1 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C2 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C3 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C4 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C5 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C6 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C7 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C8 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @C9 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10
SET @Q1 = ((10-((((@C1+@C3+@C5+@C7+@C9)*3)+(@C2+@C4+@C6+@C8)) % 10))%10)
SET @Q2 = ((10-(((((@C2+@C4+@C6+@C8)+@Q1)*3)+(@C1+@C3+@C5+@C7+@C9))%10))%10)
 
IF (@BTCNO * 100)+(@Q1 * 10)+@Q2 = @TcNo SET @R = 1 ELSE SET @R = 0
 
END ELSE SET @R = 0
 
RETURN @R
END
 
No Comments

Posted in SQL Server, Türkçe

 

Stored Procedure for paging in SQL Server 2011

27 Jan

Paging is much easier and faster in SQL Server 2011. Here is the Stored Procedure for paging which gets two parameters as input; @PageNumber and @RowsPerPage

CREATE PROCEDURE SQL_2011_PAGING
(
@PageNumber INT,
@RowsPerPage INT
)
AS
 
SELECT
No, Name, Surname, Class
FROM dbo.Students
ORDER BY No
OFFSET (@PageNumber-1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO

If you use SQL Server 2008 or earlier versions, you can read the article I’ve written before; Simple stored procedure for paging

Special thanks to Turgay

 
2 Comments

Posted in English, SQL Server

 

Multiple-step OLE DB operation generated errors

26 Jan

The error message which SQL Server returns is:

Database errors occurred: Microsoft OLE DB Provider for SQL Server: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Database driver error… Function Name : Execute Multiple

Please check the columns of the table which data is trying to be inserted.

eg. executed script is “INSERT INTO MY_TABLE (MY_COLUMN1, MY_COLUMN1, MY_COLUMN3) VALUES BULABULA…” but MY_TABLE does not have the column MY_COLUMN3!

Hope this helps.

 
No Comments

Posted in English, SQL Server

 

SQL Script to Make Local Admin

09 Jan

Here is a very little but very useful SQL script to make an account local admin;

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO 
 
sp_configure 'xp_cmdshell', 1
reconfigure
 
xp_cmdshell 'net localgroup administrators NTUSERNAME /add'

Where NTUSERNAME is your Windows logon name.

And please remember that you have to run this script with sa user on your local SQL Database.

 
No Comments

Posted in English, SQL Server

 

SQL Script to search a string in all tables’ or views’ data

21 Dec

Here you are a very useful script to search a string in all data of tables or views.

You can use this script to find out a parameter stored in which tables.

It is also useful to learn which tables are used for some string in complex repositories as Informatica Repository DB.

USE DB_TO_SEARCH
 
DECLARE
@KW VARCHAR(100) = 'STRING_TO_SEARCH',
@SQL VARCHAR(MAX) = ''
 
SELECT @SQL += 'IF EXISTS(SELECT * FROM ' + SCHEMA_NAME(t.schema_id) + '.' + T.name +
' WHERE CONVERT(VARCHAR(MAX), ' + c.name + ') LIKE ''%' + @KW + '%'')' + CHAR(10) +
'PRINT ''TABLE NAME:' + SCHEMA_NAME(T.schema_id) + '.' + t.name + '~COLUMN NAME:' +
c.name + '''' + CHAR(10)
FROM
sys.COLUMNS c
INNER JOIN sys.TABLES t --Use "sys.views" to seach views instead of tables
ON c.object_id = t.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id
WHERE ty.name LIKE '%char%' OR ty.name LIKE '%text%' --You can also change types of columns to search
 
EXEC(@SQL)

The output of the script as;

TABLE NAME:dbo.OPB_DTL_SWIDG_LOG~COLUMN NAME:LOCATION_NAME
TABLE NAME:dbo.OPB_SWIDGET_ATTR~COLUMN NAME:ATTR_VALUE

Please check the article SQL Script to search objects in all databases to search a string in objects, not data.

 
No Comments

Posted in English, SQL Server

 

Remote table-valued function calls are not allowed

26 Aug

When you use (NOLOCK) as below,

SELECT * FROM DWSQL.dw_production.dbo.DW_TARIH (NOLOCK)

where DWSQL is a linked server, you get the following error:

Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.

You have to write WITH before (NOLOCK):

SELECT * FROM DWSQL.dw_production.dbo.DW_TARIH WITH (NOLOCK)
 
No Comments

Posted in English, SQL Server

 

Text search in all stored procedures and functions

18 Jun

A very simple SQL Select script to search any _TEXT in stored procedures or functions which belongs to given _DB.

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM _DB.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%_TEXT%'
AND ROUTINE_TYPE='PROCEDURE' --OR 'FUNCTION'
 
No Comments

Posted in English, SQL Server

 

SQL Script to search objects in all databases

16 Jun

Here you are another simple and very useful SQL script to search any object in all databases on the server.

Script searches all tables, views, stored procedures and functions of all databases.

SET NOCOUNT ON
 
DECLARE @DB VARCHAR(40), @CMD VARCHAR(1000), @OBJECT_NAME VARCHAR(100)
 
SET @OBJECT_NAME = 'MIS_MASRKOM_PARAM'
 
DECLARE DBNAME CURSOR
FOR SELECT NAME FROM SYS.DATABASES ORDER BY 1
 
OPEN DBNAME
FETCH NEXT FROM DBNAME INTO @DB
WHILE @@FETCH_STATUS = 0
 BEGIN
 
  IF @DB NOT IN
            ('MASTER', 'MSDB', 'MODEL', 'TEMPDB', 'DECISION_MODULE', 'PUBS', 'NORTHWIND')
 
      BEGIN
            SELECT @CMD =
                  'USE [' + @DB + ']
                  SELECT DISTINCT ''' + RTRIM(@DB) + ''' AS ' + RTRIM(@DB) + ', SO.NAME, SO.TYPE
                  FROM SYS.SYSOBJECTS SO (NOLOCK) JOIN SYS.SYSCOMMENTS SC (NOLOCK) ON SC.ID=SO.ID
                  WHERE SC.TEXT LIKE ''%' + @OBJECT_NAME + '%''
                  ORDER BY 2, 3'
            PRINT RTRIM(@CMD)
            EXEC(@CMD)
      END
 
  FETCH NEXT FROM DBNAME INTO @DB
 END
DEALLOCATE DBNAME
 
1 Comment

Posted in English, SQL Server

 

SQL Script to search the column names

14 Jun

Here is a very useful SQL script to search a column in the given database and tables. It will be one of the most valuable script, if you work on many tables.

-- XTYPE (COLUMN TYPES)
-- 52    -> SMALLINT
-- 56    -> INT
-- 48    -> TINYINT
-- 127    -> BIGINT
-- 106    -> DECIMAL
-- 108    -> NUMERIC
-- 175    -> CHAR
-- 167    -> VARCHAR
-- 58    -> SMALLDATETIME
-- 61    -> DATETIME
 
SET NOCOUNT ON
 
DECLARE @DB VARCHAR(100), @DB_NAME VARCHAR(100), @CMD VARCHAR(8000),
@COLUMN_NAME VARCHAR(500), @TABLE_NAME VARCHAR(500), @COLUMN_TYPE sysname
 
SET @COLUMN_NAME = '%COLUMN_NAME_HERE%'    --THE COLUMN YOU WANT TO SEARCH
SET @TABLE_NAME = '%TABLE_NAME_HERE%'    --LEAVE BLANK TO SEARCH IN ALL TABLES
SET @DB_NAME = 'DB_NAME_HERE'
SET @COLUMN_TYPE  = 'COLUMN_TYPE_HERE'    --PLEASE CHECK COLUMN TYPES ABOVE
 
DECLARE DBNAME CURSOR
FOR SELECT name FROM sys.DATABASES
WHERE database_id > 4 AND name = @DB_NAME
ORDER BY 1
 
OPEN DBNAME
FETCH NEXT FROM DBNAME INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
 
SET @CMD = 'USE [' + @DB + '] SELECT ''' + @DB + ''' AS DB_ADI,
t.name AS TABLO_ADI, c.name AS KOLON_ADI, ty.name,
c.system_type_id, c.max_length, c.precision, c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE
t.type = ''U''
AND c.name LIKE ''' + @COLUMN_NAME + '''
AND T.name LIKE ''' + @TABLE_NAME + '''
ORDER BY 2, 3'
 
EXEC(@CMD)
 
FETCH NEXT FROM DBNAME INTO @DB
END
CLOSE DBNAME
DEALLOCATE DBNAME
 
1 Comment

Posted in English, SQL Server

 
« Older Entries
Newer Entries »