MS SQL 2005 Linked Server Query Problem - underscore in table name

It may be benificial if you can post your T-SQL query however have you tried qulifiying your object names use brackets [ ].

If L is the linked server name, then reference it like L.D.dbo.[ID_POS] so that it follows standard four part naming convention..

I did write [L].[D].[dbo].[ID_POS], but I still got an error. I do not have this problem with tables whose name do not have an underscore..

What Linked Server Provider are you using. This may be an issue when using an ODBC provider but I'm not convinced.

See here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/aa09a27b-835c-4a53-a3dc-12fb1f9baf89/

I am using MSDASQL...

Then try refering it as "L"."D"."dbo"."ID_POS"

I still get the same error message:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "L" does not contain the table "D.dbo.ID_POS". The table either does not exist or the current user does not have permissions on that table.

....

I wonder if this could be a permissions issue.

Validate that the SQL Server Login you are using has SELECT rights on both of the tables by executing.

GRANT SELECT ON TableName to DatabaseUserName

Select allOpen in new window

this is a linked server:BTW we checked: we do have select permissions both in SQL as well as in Sybase

Kindly check it for the login that is linking SQL Server and Sybase servers have access on both the ends..

Sorry,but I am not sure what you mean,here is what we have tried:
linked server from MSSQL -> Sybase
in MSSQL -> we log in with our windows login
in Sybase -> we log in using a account called "data_access"
in the linked server security settings ->we log in after checkin g ""Be made using this security context" -> we enter the "data_access" credentials,as I mentioned earlier the "data_access" account has SELECT permission on the scheme that we want called "Decalog"

We have already tried this as well:
local login - NAM\21249   Remote user ->data_access   password:the password of data_access

still we face the problem where we are not able to do a select query on tables that have an _ in them ( ex: ID_POS),it works fine for other tables ( ex : CURRENCY,POSITIONS etc)


Again sorry for the long post:I thought maybe we did not clearly specify what steps we had already tried


Please advise

Arun

hi:

anything on this??

If I remember correctly, _ is a special character in Sybase..
Kindly confirm as I am not that strong in Sybase compared to SQL Server..

Well yes,as it says in the Sybase site


the underscore ( _ ) wildcard character to represent any single character. For example, to find all six-letter names that end with “heryl” (for example, Cheryl):

select au_fname
from authors
where au_fname like "_heryl"


dont think this applies to table column names though..

Yes.. It is not applicable to table column names..
And are you creating any SQL Statements dynamically where this can be a problem..

none whatsoever,the totally weird thing is i am able to access these tables via OPENQUERY but not directly with a remote account.

>> OPENQUERY but not directly with a remote account.

Kindly confirm whether they are different accounts or a single account or not..

with OPENQUERY in  SQL 2005 (which i log in using windows authentication) i am querying the sybase database without any security credentials whatsoever

on the linked server i am using a remote account called 'xxxx' which is an account on the sybase db which has select permission on it.

>> on the linked server i am using a remote account called 'xxxx' which is an account on the sybase db which has select permission on it.

Have you associated that remote account to any of your account in SQL Server using  sp_addlinkedsrvlogin

http://msdn.microsoft.com/en-us/library/ms189811.aspx

If not, then do it and check it out..

Already tried this:
local login - NAM\21249   Remote user ->data_access   password:the password of data_access

checked: the data_access account does have select permissions on the sybase