Friday, February 22, 2013

2 Linked Server Trouble shooting

Recently, I spend quite some time to trouble shooting the linked server. I like to keep note about this.

 

alter any linked server

“alter any linked server” is the permission for modify the link server setting. The scenario is even when I grant the permission to the user, user still get the permission denied when try to modify the link server from SSMS. 

The error message is somehow strange : ad hoc update system catalog are not allow.

image

However, if we do use sp_serveroption to change the linked server property, it works great.

Link Server error 7399 and 7330

When user execute the query against the linked server, they receive the below error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ASEOLEDB" for linked server "XXX" reported an error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ASEOLEDB" for linked server "XXXX"

This is actually a difficult one to track down. I am end up with using the procmon to monitor the sql server process while executing the query. It turns out the sybase client is looking for charsets\unicode\cp1252.uct. This file is part of Sybase open client. For some reason the SQL server is looking at C:\windows\System32 instead of Sybase client directory. I have copy the file and query works fine.

 

Reference

http://www.progtown.com/topic394863-linked-server-to-sybase-15-ase.html

2 comments:

  1. We found a similar issue - when following the use of ProcMon we found the files were in the right place but getting an Access Denied message. We set everyone to Read only on the files (cp1252 and cp850) and the error went away.

    Our investigations actually proved the row failing contained a special character of some form, like an ampersand or asterix - so without being able to do something with the Unicode files it could not translate and failed...

    ReplyDelete
  2. We've long been inspired by urban art and have finally created a collection that pays homage to this secret addiction of ours!

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates