May | 2013 | SQL with Manoj

Linked Server error: Named Pipes Provider: Could not open a connection to SQL Server [53] – MSDN TSQL forum

May 31, 2013 Leave a comment

–> Question:

I am trying to create a linked server of a remote server in my local system.

and getting following error:

Named Pipes Provider: Could not open a connection to SQL Server [53]
OLE DB provider “SQLNCLI10” for linked server “MY_LINKED_SERVER” returned message “Login timeout expired”…

–> My Answer:

What SQL Server version you are working on?

Do you have proper access rights and permission to connect to the Remote server?

Check, if TCP/IP and Named Pipes are enabled in Remote Server, in Config Manager?

And, are you executing Linked Server from the same machine, or any remote client?

The error number is 53, which means Network Path is not Found, try to ping the server, check Server Name in your Linked Server DDL script, Also check for Windows Firewall settings.

Ref Link.

Informatica | Thread: WRITER_1_*_1, Message Code: WRT_8229, Native Error: 3621, Violation of PRIMARY KEY constraint

May 30, 2013 2 comments

Today while working with an Informatica mapping, I faced a strange issue. The image below shows the design of the INFA mapping:

Here I’m Acquiring a new table with ~500k records and the table is also very fat with PK as a GUID column. Its just a plain data pull with a simple SELECT query, no JOINs, UNIONs, etc. But while running the Workflow it gave me PK Violation error, as shown below:

Severity: ERROR
Timestamp: 5/30/2013 7:22:54 PM
Node: INFA_NODE_SERVERNAME
Thread: WRITER_1_*_1
Process ID: 8216
Message Code: WRT_8229
Message: Database errors occurred: 
Microsoft OLE DB Provider for SQL Server: The statement has been terminated.
SQL State: 01000	Native Error: 3621
State: 1	Severity: 0
SQL Server Message: The statement has been terminated.

Microsoft OLE DB Provider for SQL Server:
Violation of PRIMARY KEY constraint 'PK_tblTableName'. 
Cannot insert duplicate key in object 'dbo.tblTableName'.
The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd).

SQL State: 23000	Native Error: 2627
State: 1	Severity: 14
SQL Server Message: Violation of PRIMARY KEY constraint 'PK_tblTableName'. 
Cannot insert duplicate key in object 'dbo.tblTableName'. 
The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd).

Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO tblTableName

This was strange because the error was for the GUID PK column, and there is no reason for duplicate values here. As I’ve already taken care of INSERTs & UPDATEs by ROUTER Transformation, which can be seen in the image above. I tried to find the root cause of the error, BINGed/GOOGLEd a lot, but no luck.

Then I checked with an experienced person in my team about this error. He immediately asked me if I’ve added any “WITH (NOLOCK)” option in my query with the Source Table. I said yes, so he asked me to remove it and try, as they had faced similar issues with NOLOCK option before. I removed it and the Workflow ran successfully 🙂 .

I knew that with NOLOCK option I’m doing Dirty Reads, and had added it just to avoid locking/blocking at the Source end. But I was not aware that these Dirty Reads means any kind of data, which can also be duplicate. Thus by adding NOLOCK option with the Table, which is also going through lot of changes, we may allow data to be read more than once. This may be due to Data Movement, Uncommitted Data or Page Splits on the Source table during our reads, where we may be reading the data before and after the Page-Splits.

So, be careful while using NOLOCK option while designing your queries.

For more information check following blog: http://sqlmag.com/database-development/quaere-verum-clustered-index-scans-part-iii

Categories: Informatica, SQL Errors Tags: Informatica, Message Code: WRT_8229, Native Error: 3621, NOLOCK, SQL State: 23000, Thread: WRITER_1_*_1, with nolock

SQL Error – Logical file ‘XYZ_Log2’ is not part of database ‘XYZ’. Use RESTORE FILELISTONLY to list the logical file names.

May 12, 2013 Leave a comment

I was restoring Databases as a routine monthly job. Suddenly in between I came across an error which didn’t let me restore a Database. I restored other Databases and left this one to do at the end. Finally I picked it up again and saw that some new kind of error I was facing. The error message says to use “RESTORE FILELISTONLY”, I check the syntax on MSDN and found that this Database had two log files (LDF) earlier, but now configured for only one. And as I was using the same old script to Restore it, I was getting this error.

Let’s try to reproduce it in a standalone box:

–> I took a backup of AdventureWorks2012 Database on my machine.

I created the RESTORE script and added another line of log file at line 8 below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012] 
FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

On executing I got an error as expected, shown below:

Msg 3234, Level 16, State 2, Line 1
Logical file ‘AdventureWorks2012_Log2’ is not part of database ‘AdventureWorks2012’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

As suggested in the Error Message, I used the “RESTORE FILELISTONLY” command to check what all files this Database is using:

RESTORE FILELISTONLY from disk=N'E:\Softwares\MS_bits\AdventureWorks2012.bak'

And I could see only two, one MDF & one LDF, as shown below:

LogicalName		PhysicalName					Type	FileGroupName
AdventureWorks2012_Data	E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf	D	PRIMARY
AdventureWorks2012_Log	E:\MSSQL11\Log\AdventureWorks2012_log.ldf	L	NULL

So, I removed the extra LDF log file option from the RESTORE script, as shown below:

RESTORE DATABASE [AdventureWorks2012x] 
FROM  DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
--MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

The above statement executed fine and the Database restored successfully.

So, I learnt a new thing here about the “RESTORE FILELISTONLY” command. This command tell us what all Database files (MDF, NDF, LDF) a Backup file contains without actually Restoring the Database.

Categories: DBA Stuff, SQL Errors Tags: RESTORE DATABASE, RESTORE FILELISTONLY

Off topic | ERROR: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

May 7, 2013 3 comments

Today while setting up the DEV Environment as part of Windows Server 2012 & SQL Server 2012 upgrade I faced this error.

I was installing an MSI from one system via a client (MSI deployment tool) to another remote system (on same domain) and was getting this error:

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

I checked on internet all possible options to resolve this error, which were:

1. “Remote Procedure Call (RPC)” service should be running on the remote computer.
2. “Windows Management Instrumentation” service should be running on the remote computer.
3. “TCP/IP NetBIOS Helper” service should be running on the remote computer.
4. “DCOM Server Process Launcher” service should be running.
5. File and printer sharing should be enabled, on LAN properties.

I checked all the above options and lot of other suggestions available on internet forums and they were all set correctly.

–> Finally my colleague suggested me to check the “Group Policy Object Editor”, and check the firewall properties “Remote Administration Exception” and “File and Printer Sharing Exception”. And yes he was right, they were not enabled.

So to enable the properties you have to go to “Group Policy Object Editor” by executing gpedit.msc command. A winodw will open, goto: Computer Configuration -> Administrative Templates -> Network -> Network Connections -> Windows Firewall -> Domain Profile:

Here, enable the following properties:
– Allow inbound remote administration exception
– Allow inbound file and printer sharing exception