by NCO

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 )

November 28, 2012 in Error, Mirroring, SQL Server by NCO

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 ).

If this error occurs when you’re trying to create a mirror, check this points:

  • Restore backup at secondary server has to be with NORESTORE option.
  • All log backup must be restored (Probably some job of SQL SERVER AGENT makes Transaction Log Backup, ensure that last log backup is restored in secondary too).
  • GRANT CONNECT ON ENDPOINT::Mirroring TO Public (I think with domain user that runs service account it’s enough, but it doesn’t work for me, so, PUBLIC, works).
  • Maybe Firewall block the Port Number, try to connect, with TELNET for example.

I hope it helps.

by NCO

Cannot create, add, or edit steps for an SQL Server Agent job in SQL Server Management Studio 2008 R2

November 28, 2012 in Error, SQL Server by NCO

If you get some error description like this, “Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011.” 


Probably this link to Microsoft’s Fix can help you: http://support.microsoft.com/kb/2315727 

by NCO

Errors inserting identity on table

November 21, 2012 in SQL Server, T-SQL by NCO

Here we’ve different errors that can occurs trying to insert some rows into table that has Identity field.

I want to insert all data from Table Banca_Movimientos_TEMP to table Banca_Movimientos3

Sentence 1:


insert into dbo.Banca_Movimientos3 
select * from dbo.Banca_Movimientos_TEMP


On executing this, I get the error:


Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘dbo.Banca_Movimientos3′ can only be specified when a column list is used and IDENTITY_INSERT is ON.


This is because it’s necessary to specify all fields in insert into statement.

Let’s do it.


Sentence 2:

insert into dbo.Banca_Movimientos3([IdMovimiento] ,[Concepto] ,[Fecha_Operacion] ,[Fecha_Valor] ,[Descripcion] ,[Importe] ,[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

Well, now another error occurs.

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘Banca_Movimientos3′ when IDENTITY_INSERT is set to OFF.

It’s needed to set IDENTITY_INSERT ON because field IdMovimiento is IDENTITY.
Sentence 3:
SET IDENTITY_INSERT Banca_Movimientos3 ON;
GO

insert into dbo.Banca_Movimientos3 ([IdMovimiento],[Concepto],[Fecha_Operacion],[Fecha_Valor],[Descripcion],[Importe],[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

SET IDENTITY_INSERT Banca_Movimientos3 OFF;
GO
(1385 row(s) affected)

Now, the insert statement is OK.

by NCO

Error Restoring Database

November 9, 2012 in SQL Server, T-SQL by NCO

Today,
Trying to restore a database from other server Backup, 
RESTORE DATABASE DB
  FROM DISK = ‘X:BackupDB_1.dmp’
  WITH 
    MOVE ‘DB’         TO ‘Y:DATADB.mdf’,
    MOVE ‘DB_log’   TO ‘Z:LOGDB_log.ldf‘,
    REPLACE,
    Recovery,  
    STATS = 10
go
I get this message:
Msg 3634, Level 16, State 1, Line 1

The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘Z:LOGDB_log.ldf’.

Msg 3156, Level 16, State 8, Line 1

File ‘DB_log’ cannot be restored to ‘Z:LOGDB_log.ldf’. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

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

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Previously I set the DB Database in single_user mode, and read_only mode, and it stills the same error.
I restarted SQL Server but the error continues.
Probably there are better forms to solve this issue, but this is what I did:
Restore the database with other log filename. SQL Server deletes old one.
If you want to have the original filename, try to restore again changing the log filename.
RESTORE DATABASE DB
  FROM DISK = ‘X:BackupDB_1.dmp’
  WITH 
    MOVE ‘DB’         TO ‘Y:DATADB.mdf’,
    MOVE ‘DB_log’   TO ‘Z:LOGDB_log1.ldf‘,
    REPLACE,
    Recovery,  
    STATS = 10
go
RESTORE DATABASE DB
  FROM DISK = ‘X:BackupDB_1.dmp’
  WITH 
    MOVE ‘DB’         TO ‘Y:DATADB.mdf’,
    MOVE ‘DB_log’   TO ‘Z:LOGDB_log.ldf‘,
    REPLACE,
    Recovery,  
    STATS = 10
go