November 7, 2019

Fix orphaned sql users

Creating-Updating DB

Then open query window and execute following stored procedure
- Transact-SQL
  
  EXEC sp_change_users_login 'AUTO_FIX','OrphanedUser'
  
  1
  
  EXEC sp_change_users_login 'AUTO_FIX','OrphanedUser'

SQL 2005 Full version

Transact-SQL

Exec sp_change_users_login 'Auto_Fix', 'OrphanedUser',null, 'orphaned'

1

Exec sp_change_users_login 'Auto_Fix', 'OrphanedUser',null, 'orphaned'

Useful script

CREATE LOGIN OrphanedUser

    WITH PASSWORD = 'orphaned',

    CHeck_Policy= OFF

CREATE LOGIN OrphanedUser

WITH PASSWORD = 'orphaned',

CHeck_Policy= OFF

Otherwise to associate user to login try the following:

CREATE USER OrphanedUser FOR LOGIN OrphanedUser

1	CREATE USER OrphanedUser FOR LOGIN OrphanedUser

October 29, 2019

Install SQL instance on domain controller

Installing SQL instance is not recommended and will usually fail (when starting the service section in the wizard). If this happens you can try the following:

This usually occurs when the master.mdf or the mastlog.ldf gets corrupt . In order to solve the issue go to the following path C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL , there you will find a folder ” Template Data ” , copy the master.mdf and mastlog.ldf and replace it in C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data folder . Thats it . Now start the MS SQL service and you are done

It will reset the sa password.

Default sa passwords.

SQL Server 2008/R2 Express
User: sa
Password: [blank password – leave field empty to connect]

SQL Server 201x Express
User: sa
Password: Password123

SQL Server 20xx Web or Standard
User: sa
Password: will be the same as your administrator or root user password at the time the VDS was provisioned.

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword' 
GO

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]

USE [master]

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

Otherwise you can you use the following link to do it properly.

How to Install Microsoft SQL on a Domain Controller

February 27, 2019

Operating system error 5(Access is denied.).

When creating backup need to set the permissions of the folder. Set the security permissions to allow ‘Authenticated Users’ to have read write permissions to the folder. Then run backup scripts again.

backup database <DATABASE NAME> to disk = <LOCATION>

1	backup database <DATABASE NAME> to disk = <LOCATION>

December 18, 2018

Get the Top 10 of each distinct column value

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate 
from
(
   SELECT *,
         --adds a row number
         ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn
   FROM src_PublicationEdition
   where EditionDate between 20140101 and 20180101
) a
Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID
--we can then limit by row number
where rn <= 10


--- OR

;with MAIN As
(
select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate, rn 
from
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn
   FROM src_PublicationEdition
   where EditionDate between 20140101 and 20180101
) a
Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID
)
select * from MAIN
where rn < 10
order by Code

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate

from

(

SELECT *,

--adds a row number

ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn

FROM src_PublicationEdition

where EditionDate between 20140101 and 20180101

) a

Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID

--we can then limit by row number

where rn <= 10

--- OR

;with MAIN As

(

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate, rn

from

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn

FROM src_PublicationEdition

where EditionDate between 20140101 and 20180101

) a

Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID

)

select * from MAIN

where rn < 10

order by Code

June 19, 2018

Clear sql buffer and cache

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database
DBCC FREESYSTEMCACHE ('tempdb')  -- clears cache for tempdb
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')
Go
DBCC FLUSHPROCINDB (8) -- 8 is the databaseid of Dec_ProdStats
GO
-- Clean all the caches with entries specific to the resource pool named "default".  
DBCC FREESYSTEMCACHE ('ALL', default);

--MARK_IN_USE_FOR_REMOVAL clause to release entries from all current caches once the entries become unused.
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;  

--Suppresses all informational messages.
DBCC FREEPROCCACHE with NO_INFOMSGS

DBCC DROPCLEANBUFFERS --clears clean (unmodified) pages from the buffer pool
Precede that with a CHECKPOINT to flush any dirty pages to disk first
DBCC FLUSHPROCINDB --clears execution plans for that database

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database

DBCC FREESYSTEMCACHE ('tempdb') -- clears cache for tempdb

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

DBCC FLUSHPROCINDB (8) -- 8 is the databaseid of Dec_ProdStats

-- Clean all the caches with entries specific to the resource pool named "default".

DBCC FREESYSTEMCACHE ('ALL', default);

--MARK_IN_USE_FOR_REMOVAL clause to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

--Suppresses all informational messages.

DBCC FREEPROCCACHE with NO_INFOMSGS

DBCC DROPCLEANBUFFERS --clears clean (unmodified) pages from the buffer pool

Precede that with a CHECKPOINT to flush any dirty pages to disk first

DBCC FLUSHPROCINDB --clears execution plans for that database

February 26, 2018

Numbers Table

select * from master..spt_values 
where type ='P'
order by number


--Great for adding dates ie. Adding all Wednesdays between two dates
DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20190131'
SET @Date2 = '20200105'

SELECT 
--Change date to isodate
Convert(varchar, DATEADD(DAY,number+1,@Date1), 112) [Date]
FROM master..spt_values
WHERE type = 'P'
and Datename(dw, Cast(Cast(DATEADD(DAY,number+1,@Date1) as varchar) as datetime)) = 'Wednesday'
AND DATEADD(DAY,number+1,@Date1) < @Date2

select * from master..spt_values

where type ='P'

order by number

--Great for adding dates ie. Adding all Wednesdays between two dates

DECLARE @Date1 DATE, @Date2 DATE

SET @Date1 = '20190131'

SET @Date2 = '20200105'

SELECT

--Change date to isodate

Convert(varchar, DATEADD(DAY,number+1,@Date1), 112) [Date]

FROM master..spt_values

WHERE type = 'P'

and Datename(dw, Cast(Cast(DATEADD(DAY,number+1,@Date1) as varchar) as datetime)) = 'Wednesday'

AND DATEADD(DAY,number+1,@Date1) < @Date2

February 1, 2018

Rebuild indexes

GO 
DBCC DROPCLEANBUFFERS; 
GO
SET NOCOUNT ON
GO

--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80

DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()

if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end

DECLARE @NumTables VARCHAR(20)

SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON  s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON  t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1  -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status

SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END

CLOSE RebuildIndex
DEALLOCATE RebuildIndex

drop table #TablesToRebuildIndex

Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'

GO

DBCC DROPCLEANBUFFERS;

SET NOCOUNT ON

--Set the fillfactor

DECLARE @FillFactor TINYINT

SELECT @FillFactor=80

DECLARE @StartTime DATETIME

SELECT @StartTime=GETDATE()

if object_id('tempdb..#TablesToRebuildIndex') is not null

begin

drop table #TablesToRebuildIndex

end

DECLARE @NumTables VARCHAR(20)

SELECT

s.[Name] AS SchemaName,

t.[name] AS TableName,

SUM(p.rows) AS RowsInTable

INTO #TablesToRebuildIndex

FROM

sys.schemas s

LEFT JOIN sys.tables t

ON s.schema_id = t.schema_id

LEFT JOIN sys.partitions p

ON t.object_id = p.object_id

LEFT JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE

p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index

AND p.rows IS NOT NULL

AND a.type = 1 -- row-data only , not LOB

GROUP BY

s.[Name],

t.[name]

SELECT @NumTables=@@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR

SELECT

ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),

ttus.SchemaName,

ttus.TableName,

ttus.RowsInTable

FROM

#TablesToRebuildIndex AS ttus

ORDER BY

ttus.RowsInTable

OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)

DECLARE @SchemaName NVARCHAR(128)

DECLARE @tableName NVARCHAR(128)

DECLARE @RowsInTable VARCHAR(20)

DECLARE @Statement NVARCHAR(300)

DECLARE @Status NVARCHAR(300)

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'

RAISERROR (@Status, 0, 1) WITH NOWAIT --RAISERROR used to immediately output status

SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'

EXEC sp_executesql @Statement

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable

END

CLOSE RebuildIndex

DEALLOCATE RebuildIndex

drop table #TablesToRebuildIndex

Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'

July 18, 2016

Adding a foreign key

Adds foreign key in tblDisplay for lnkclientID which exists in tblClient as IDClient

--

-- Adds foreign in tbldisplay

ALTER TABLE [dbo].[tblDisplay] WITH CHECK ADD CONSTRAINT [FK_tblClient_tblDisplay]

FOREIGN KEY([lnkClientID])
REFERENCES [dbo].[tblClient] ([IDClient])

--

-- Adds foreign in tbldisplay

ALTER TABLE [dbo].[tblDisplay] WITH CHECK ADD CONSTRAINT [FK_tblClient_tblDisplay]

FOREIGN KEY([lnkClientID])

REFERENCES [dbo].[tblClient] ([IDClient])

July 11, 2016

Adding weak Link

Adds a weak link (lnkAddressID) between tbl_SUB_SubscriptionContract and tbl_SUB_Address. Conditions accepted:

– ID has to exist in tblAddress

– 0 values accepted

ALTER TABLE [dbo].[tbl_SUB_SubscriptionContract] WITH NOCHECK
ADD CONSTRAINT [FK_tbl_SUB_SubscriptionContract_tbl_SUB_Address] FOREIGN KEY([lnkAddressID])
REFERENCES [dbo].[tbl_SUB_Address] ([IDAddress])
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
ALTER TABLE dbo.[tbl_SUB_SubscriptionContract]
NOCHECK CONSTRAINT [FK_tbl_SUB_SubscriptionContract_tbl_SUB_Address]

&nbsp;

ALTER TABLE [dbo].[tbl_SUB_SubscriptionContract] WITH NOCHECK

ADD CONSTRAINT [FK_tbl_SUB_SubscriptionContract_tbl_SUB_Address] FOREIGN KEY([lnkAddressID])

REFERENCES [dbo].[tbl_SUB_Address] ([IDAddress])

ON UPDATE NO ACTION

ON DELETE NO ACTION

NOT FOR REPLICATION

ALTER TABLE dbo.[tbl_SUB_SubscriptionContract]

NOCHECK CONSTRAINT [FK_tbl_SUB_SubscriptionContract_tbl_SUB_Address]

February 3, 2015

DateAdd

The DateAdd() Functions adds/subtracts a specific interval to a specified date

Dateadd(datepart,interval,date)

--datepart:

--year

--quarter

--month

--dayofyear

--day

--week

--weekday

--hour

--minute

--second

--millisecond

--microsecond

--nanosecond

Dateadd(datepart,interval,date)

--datepart:

--year

--quarter

--month

--dayofyear

--day

--week

--weekday

--hour

--minute

--second

--millisecond

--microsecond

--nanosecond

Example:

Using the DateAdd function in a loop to add 1 month to each date

Declare @Counter int
Set @Counter = 0

&nbsp;

--Add for 24 months

While @Counter &lt; 24

Begin

--increment counter
set @Counter+= 1

--increment by month for given counter against todays date

select DateAdd(MONTH, @Counter, GetDate())

End

Declare @Counter int

Set @Counter = 0

--Add for 24 months

While @Counter < 24

Begin

--increment counter

set @Counter+= 1

--increment by month for given counter against todays date

select DateAdd(MONTH, @Counter, GetDate())

End

The Lazy Developer

So so lazy….

Category Archives: MS SQL

Fix orphaned sql users

Install SQL instance on domain controller

Operating system error 5(Access is denied.).

Get the Top 10 of each distinct column value

Clear sql buffer and cache

Numbers Table

Rebuild indexes

Adding a foreign key

Adding weak Link

DateAdd