Showing posts with label USEFUL QUERIES. Show all posts
Showing posts with label USEFUL QUERIES. Show all posts

Tuesday, February 02, 2016

Database Allocation Error Solution("Page ID issue" "broken links")

Database Allocation Error Solution

This Query is to solve Allocation errors in Databases. Whenever you see error messages that contains word  "Page ID issue" "broken links". in application, or dbcc checkdb querry tells you that database has allocation errors, run following query. xxxxxxx  in the query is to be replaced by the name of your problematic database.

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name  IN ('xxxxxxx')
ORDER BY 1
OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''

   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor

   FETCH NEXT FROM TableCursor INTO @Table
   WHILE @@FETCH_STATUS = 0
   BEGIN

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)
       END

       FETCH NEXT FROM TableCursor INTO @Table
   END

   CLOSE TableCursor
   DEALLOCATE TableCursor

   FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Saturday, December 05, 2015

QUERY to change sql password


Use Master


Go


ALTER LOGIN [sa] WITH PASSWORD=N'sa1'


Go

 

 

Replace ‘sa1’ with your desired password
 


Friday, December 04, 2015

Query to clear log



Replace emo by the Database name you want to clear the log


    dump transaction eMO with truncate_only  go


    declare @filename varchar(50)


    select @filename = file_name(2)


    dbcc shrinkfile (@filename, 10)


    go


    dbcc shrinkdatabase (eMO, truncateonly)


    go

QUERY for user login



USER LOGIN QUERY

This query is used for creating logins after server format.

After creation go to enterprise manager –security- logins select user name eg(counterpa)and give server role as system administrator.This is very important.

DECLARE @pwd sysname

 -- Login: AccountsLogin
SET @pwd = CONVERT (varbinary(256), 0x010023115659F2BC070504EBF66A684320B8C772DFA7D0D6DCF67BD0DF9F608F6EFCBBD2A231C9CF276FA8D06309)
EXEC master..sp_addlogin 'AccountsLogin', @pwd, @sid = 0x947C3F9404D50D43BA8ACDC4B97D6BBA, @encryptopt = 'skip_encryption'

 -- Login: BNPLUser
SET @pwd = CONVERT (varbinary(256), 0x0100943B0512CAE344AF2BB1F7E5CFA2EA3551B8CDA765FBDD66AB2465FEF74CAD09FA8A96AB3A354D13365153FD)
EXEC master..sp_addlogin 'BNPLUser', @pwd, @sid = 0x3C470040942E5C44AE9D8E745EE7CFA3, @encryptopt = 'skip_encryption'

 -- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'

 -- Login: counterpa
SET @pwd = CONVERT (varbinary(256), 0x0100BE4BB3255B7B4CACB947957E09A305477C3363D99580EB75EC0000D3CD3CBD7A99FDC8FA62A3D256C36F3398)
EXEC master..sp_addlogin 'counterpa', @pwd, @sid = 0x07884C478A281E488D2D624AB0AF1709, @encryptopt = 'skip_encryption'

 -- Login: emspa
SET @pwd = CONVERT (varbinary(256), 0x0100C768C06D8014A2AA7517D78A8BA4F3687682DD15D838DD73B827AB478971A839A1D0932648993DF7AA587B1E)
EXEC master..sp_addlogin 'emspa', @pwd, @sid = 0x1CD05164EF605648AA75F34381C24647, @encryptopt = 'skip_encryption'

 -- Login: MOCompilation
SET @pwd = CONVERT (varbinary(256), 0x01003A32EC48905E5584246B8914C54B4950D1C9670B18F33B37835B64963DD40648CFBD5D37AAB29CCA3DA8EB86)
EXEC master..sp_addlogin 'MOCompilation', @pwd, @sid = 0x95DC0D7307FC6545B71C4113B5105CAB, @encryptopt = 'skip_encryption'

 -- Login: pinlogin1
SET @pwd = CONVERT (varbinary(256), 0x01000549AC0891B0E42705E408AAEF7748ACB88819FB7C63BF7CE07ED8F690179B109CCCF2B3EB4CFCEBC858AA6E)
EXEC master..sp_addlogin 'pinlogin1', @pwd, @sid = 0x830DF2EA2DA23046B15C5EF3C55FF26A, @encryptopt = 'skip_encryption'

 -- Login: Postman
SET @pwd = CONVERT (varbinary(256), 0x0100DC48DF14ADF8A1AE1B6124F3FF4A466F493835903296565219D1ADABA876A8D341968D21C3B400663D1D7213)
EXEC master..sp_addlogin 'Postman', @pwd, @sid = 0xC8D008469CA8D94F98AC703D59966222, @encryptopt = 'skip_encryption'

 -- Login: subtreasurypa
SET @pwd = CONVERT (varbinary(256), 0x0100266ED779A82140166CB84D454E646FDB7A38D803831ACFA95244B40DCD0081EF791D87AA162DD5FC3306AAF0)
EXEC master..sp_addlogin 'subtreasurypa', @pwd, @sid = 0xEDA99A16706D634383DDD8C4F21FF150, @encryptopt = 'skip_encryption'

Thursday, December 03, 2015

QUERY For incorrect sequence of ledger entries (SB)

If its for single account

DECLARE @id INT
SET              @id = 0
                          UPDATE    DCL.ledger                         
SET              @id = seqno = @id + 1
where acctno=321001

For all accounts at once,

 DECLARE @id INT
SET              @id = 0
                          UPDATE    DCL.ledger                          
SET              @id = seqno = @id + 1
where acctno IN  (select acctno  from (select distinct acctno from dcl.ledger group by acctno having count(seqno) <> count(distinct seqno) and acctno in (select acctno from dcl.indexreg where acstatus in ('o','ti','s','f','p')) union select distinct(acctno) from dcl.ledger l where seqno=(select MAX(seqno) from dcl.ledger where acctno=l.acctno group by acctno) and lotdate <> (select max(lotdate) from dcl.ledger where acctno=l.acctno group by acctno) and acctno in (select acctno from dcl.indexreg where acstatus in ('o','TI','s','f','p'))) y )