Some SQL Tips


1.  I am back after a very long break. So, I somehow forgot SQL Server ‘SA’ Password, and this is how I recovered that.

  • Stop SQL SERVER
  • Change the startup parameters by adding add -m;
  • Start SQL Server
  • Open command prompt and type SQLCMD

Method 1:  Create new user and add to systemadmin role

CREATE LOGIN recovery1 WITH PASSWORD = 'abc123%';
sp_addsrvrolemember 'recovery1', 'sysadmin';
GO;

Now login using created user and change the ‘Sa’ password

Method 2: Change Password

EXEC sp_password NULL, abc123!@#$', 'sa';
GO;

Method 3: Give system admin roles to the windows user

EXEC sp_addsrvrolemember 'Softcat\tharaka_r', 'sysadmin';
GO;

2.  I could not remember who wrote this query, but I am sure it was not me. :p  So, I just simplified that.

DECLARE @RunDate AS DATETIME = getdate()
SELECT CONCAT (DATEPART(yyyy,DATEADD(dd,30,@RunDate)),'-', DATEPART(mm,DATEADD(dd,30,@RunDate)),'-', DATEPART(dd,DATEADD(dd,30,@RunDate))) AS DateNow , 30 AS Value;

Simplified version:

DECLARE @RunDate AS DATETIME = getdate();
SELECT CONVERT(VARCHAR(10),DATEADD (dd,30, @RunDate),120) AS DateNow , 30 AS Value;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s