Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Each SQL Server instance listens on a different port. Your default instance is listening on port 1433 but the named instance must listen on a different port to avoid conflicts, which is dynamically assigned by default.
Make sure the firewall allows TCP connections for the named instance port as well. The dynamic port number can be identified using SQL Server ...
Do not enclose the literal in the REPLACE function in square brackets. The enclosures are not needed in the LIKE clause either since you are looking for a single character.
SET Loc1 = REPLACE(CAST(Loc1 as nvarchar(4000)), N'&', N'and')
WHERE Loc1 LIKE N'%&%';`
Also, I don't see a need for the CAST here:
You should be able to reference the individual characters you want to replace by character code instead, to ensure you're referencing the exact character. You can do so using the NCHAR() function.
Here's what your REPLACE() function turns into as an example: REPLACE(CAST(Loc1 as NVARCHAR(4000)), NCHAR(38), N'and')
The decimal code for an ampersand is 38, and ...