Simple Stored Procedure

USE MASTER_DB; GO CREATE PROCEDURE SP_GetEmployees_Details @ENAME nvarchar(50), @JOB nvarchar(50) AS SET NOCOUNT ON; SELECT e.ENAME, e.JOB, d.DNAME,e.SAL,* FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO AND e.ENAME = @ENAME AND e.JOB = @JOB AND e.COMM IS NULL; GO EXECUTE SP_GetEmployees_Details N’BLAKE’, N’MANAGER’; — Or EXEC SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’; GO — Or EXECUTE SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’; GO Continue reading Simple Stored Procedure

Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

Msg 945, Level 14, State 2, Line 1 Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Recovery Pending. Continue reading Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. Alter account & unlock Execute the below SQL code in master database: ALTER LOGIN sa WITH PASSWORD=’password’ UNLOCK Continue reading Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

How to change the column data type to Identity?

How to change the data type int to identity (1,1)?  When we set identity to any column no need to insert the value. System will automatically inserts the sequential number. It is not possible to change the data type to identity for tables which have large amount of data. Here is the way: Run the below query to create Temp table with the same data … Continue reading How to change the column data type to Identity?

Mismatch between the PK and FK constraints in database tables

We can see the below error due to mismatch between the PK and FK constraints in database tables. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The statement has been terminated.”.An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The INSERT statement conflicted with the FOREIGN KEY constraint “<Dimension Table>_FK1”. The conflict occurred in … Continue reading Mismatch between the PK and FK constraints in database tables