Archive

Archive for the ‘Debug’ Category

Could not drop the primary key constraint ‘constraint name’ because the table has an XML or spatial index.

February 28, 2015 Leave a comment

Error Message

Msg 3734, Level 16, State 1, Line 187
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 187
Could not drop constraint. See previous errors.

Description:
This error message appears when you try to remove a PRIMARY KEY constraint from a table which has an XML index.

 

Troubleshooting

—Error Statement

ALTER TABLE [Person].[Person] DROP CONSTRAINT [PK_Person_BusinessEntityID]

–Troubleshooting
go
select kc.name ConstraintName,object_name(kc.parent_object_id) tablename,kc.type,si.name indexname
from sys.key_constraints kc inner join sys.indexes si on kc.parent_object_id=si.object_id where
kc.name=’PK_Person_BusinessEntityID’ and si.type_desc=’XML’

–//Drop the indexes first then the constraint

Msg 3734, Level 16, State 1, Line 5
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 5
Could not drop constraint. See previous errors.
ConstraintName                 tablename    type    indexname
PK_Person_BusinessEntityID    Person    PK    PXML_Person_AddContact
PK_Person_BusinessEntityID    Person    PK    PXML_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPATH_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPROPERTY_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLVALUE_Person_Demographics

Resolution

Drop the XML Index First and then drop the Primary Key Constraint

Categories: Debug

Transactional Replication : Log Reader Agent was unable to connect to the Publisher using the specified login

November 11, 2014 Leave a comment

SQL Server Agent is running with Windows Credential with no roles assigned. Behind the scene Log Reader Agent Connect to the Publisher Database to read database log, master  and resource database to obtain license and version information. As we have not granted any sort of permission to the Agent Account at the publisher server ,so it fails with below error.

Error :

Error : “2014-11-11 03:09:04.073 Status: 0, code: 22040, text: ‘The Log Reader Agent was unable to connect to the Publisher using the specified login. Make sure that the login used by the agent is a member of either the sysadmin fixed server role at the Publisher or the db_owner fixed database role in the publication database. ‘.

2014-11-11 03:09:04.073 The Log Reader Agent was unable to connect to the Publisher using the specified login. Make sure that the login used by the agent is a member of either the sysadmin fixed server role at the Publisher or the db_owner fixed database role in the publication database.”

 

Solution :

  1. Grant SysAdmin Server Role to the agent account
  2. Grant DB_Owner to the Agent Account on Publisher and master database.
Categories: Debug

Database mirroring connection error

June 16, 2010 Leave a comment

While setting up Database mirroring I came across an error which is too common on the web but too much of workaround and solutions.

Therefore I thought to write a blog post for the error. The problem we’re trying to solve is that you try to start database mirroring and you receive this error:

The server network address “‘TCP: //server FQDN Name:5022′” cannot be reached or does not exist

Database mirroring connection error 4 ‘an error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP: // server FQDN Name.com:5022’.

I assume that you have the following if not check each of the steps defined below.

  1. Full Database Backup of the Database which will be used for DB Mirroring.
  2. Restored the Backup on the Mirror Server in No Recovery mode.
  3. Telnet the ENDPOINT (Port No: 5022 or as per requirement): Ports are opened at both the end (Principle and Mirror).

     

  4. Check Endpoints have started on Principal and Mirror.
    SELECT * FROM sys.database_mirroring_endpoints
    SELECT name, port FROM sys.tcp_endpoints
  5. Try setting up the DB Mirroring.
  6. Start the mirroring.
  7. Run netstat to see that end points have established a successful connection.

Categories: Debug