IntelliSense is not working even though it is enabled.

I am using SSMS 2016,  My IntelliSense was working fine till few days back and suddenly one day it stopped working.

 I ignored it as I thought it will get fixed automatically.

But it was not fixed automatically, I have tried following options to get it fixed.

First tried with cache refresh.

     1.  Hit CTRL + Shift + R

     2.  Edit - > IntelliSence - > Refresh Local Cache

     and tried below options as well.

        

     1.  Go to Tools -> Options ->Text Editor -> General ->  Selected both the options  Auto list             members and Parameters information

      2. Go to Tools -> Options ->Text Editor -> IntelliSence -> Select option Enable IntelliSense. (Make sure it is enabled)

     3. Please make sure SQL CMD mode should be disabled for IntelliSense.

Tools -> Options -> Query Execution -> SQL Server - > General

Option ‘By default, Open new query in SQL CMD mode’ is disabled.

     Mostly the above trials will solve the problem, but unfortunately in my case, it didn’t worked so I have tried the below way.

     Go to Tools -> Options ->Text Editor -> IntelliSense -> Maximum script size setting Changed to unlimited.

    If this is also not working for you, it seems like an issue with installation. I recommend to re-install the tools.

    Thank you for visiting the blog, hope you got the required information.

How to restore database from backup file.

In this article, we will be discussing how to restore the database from the backup file.


If you are restoring the backup file for the first time you can restore using the below command.

RESTORE DATABASE AdventureWorks2012 FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE

Now, Let's see other scenario where the same backup file to be restored into another database ( New database is created in the same location of the previous database)

Let's try whether the above restore command works or not.

Here I am trying to restore the same backup file to another database which is AdventureWorks2012_NEW DB.

Command :

RESTORE DATABASE AdventureWorks2012_NEW FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE

Error:

Msg 1834, Level 16, State 1, Line 8

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf' cannot be overwritten.  It is being used by database 'AdventureWorks2012'.

Msg 3156, Level 16, State 4, Line 8

File 'AdventureWorks2012' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 1834, Level 16, State 1, Line 8

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf' cannot be overwritten.  It is being used by database 'AdventureWorks2012'.

Msg 3156, Level 16, State 4, Line 8

File 'AdventureWorks2012_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf'. Use WITH MOVE to identify a valid location for

the file.

Msg 3119, Level 16, State 1, Line 8

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 8

RESTORE DATABASE is terminating abnormally.

As the physical files already exist in that location, the backup command is unable to restore it. for this, we have to use the backup command with the move clause.

Picking logical names from backup using the below command.

RESTORE FILELISTONLY FROM DISK = 'C:\Ganesh\Backup\AdventureWorks.bak'

Restore command :

RESTORE DATABASE [AdventureWorks2012_NEW] FROM  DISK = N'C:\Ganesh\Backup\AdventureWorks.bak' WITH 

FILE = 1,  MOVE N'AdventureWorks2012' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW.mdf',

 MOVE N'AdventureWorks2012_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW_log.ldf',

NOUNLOAD,  REPLACE,  STATS = 5

You can see in the above image. Restore is complete.


Thank you for visiting the blog. hope you got the required information.

If you like this article. Please share your feedback in the comment section.

How to update column value with column value of other table using Join condition.

In this article, we will be discussing how to update column value with the column value of other table.

This is a very common requirement. Most often we will come across this situation.

Let's Create a table and insert sample data:

Here we are creating two tables.

CREATE TABLE UPDATETEST1 ( ID INT, VALUE NVARCHAR(250) ) 

INSERT INTO UPDATETEST1 VALUES (1,'ABC') 

INSERT INTO UPDATETEST1 VALUES (2,'BCD') 

INSERT INTO UPDATETEST1 VALUES (3,'CDE') 

INSERT INTO UPDATETEST1 VALUES (4,'DEF') 

CREATE TABLE UPDATETEST2 (ID INT, VALUE NVARCHAR(250)) 

INSERT INTO UPDATETEST2 VALUES (1,'ABC') 

INSERT INTO UPDATETEST2 VALUES (2,'XYZ') 

INSERT INTO UPDATETEST2 VALUES (3,'CDE') 

INSERT INTO UPDATETEST2 VALUES (4,'DEF') 

Select table1:

SELECT * FROM  UPDATETEST1 

Select table2:

SELECT * FROM UPDATETEST2

In the above two tables  ID  2 having two different values in the tables.

Now, let's see how to update the table UPDATETEST2 VALUE column with table  UPDATETEST1 VALUE  column based on the ID column condition. 

Query 1: Using NON-ANSI Join 

UPDATE A 
SET    A.VALUE = B.VALUE 
FROM   UPDATETEST2 A, 
       UPDATETEST1 B 
WHERE  A.ID = B.ID 
       AND A.VALUE <> B.VALUE 

Query 2: Using ANSI Join ( American National Standards Institute) 

UPDATE A 
SET    A.VALUE = B.VALUE 
FROM   UPDATETEST2 A 
       INNER JOIN UPDATETEST1 B 
               ON A.ID = B.ID 
WHERE  A.VALUE <> B.VALUE 

Output post update:

Thank you for visiting the blog.  hope you got the required information.