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

I am using the Microsoft OLE DB Provider for ODBC Drivers  in MS SQL 2005, I am having an error when I  reference a table name that includes an underscore .
The error I get is:
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.

Is there any way I can fix that error wihtout using the OPENQUERY command?
Thank you
kmgingeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnSansomCommented:
It may be benificial if you can post your T-SQL query however have you tried qulifiying your object names use brackets [ ].
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
kmgingeeAuthor Commented:
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..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JohnSansomCommented:
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/
0
kmgingeeAuthor Commented:
I am using MSDASQL...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then try refering it as "L"."D"."dbo"."ID_POS"
0
kmgingeeAuthor Commented:
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.

....
0
JohnSansomCommented:
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

Open in new window

0
kmgingeeAuthor Commented:
this is a linked server:BTW we checked: we do have select permissions both in SQL as well as in Sybase
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Kindly check it for the login that is linking SQL Server and Sybase servers have access on both the ends..
0
kmgingeeAuthor Commented:
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
0
kmgingeeAuthor Commented:
hi:

anything on this??
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If I remember correctly, _ is a special character in Sybase..
Kindly confirm as I am not that strong in Sybase compared to SQL Server..
0
kmgingeeAuthor Commented:
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..

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes.. It is not applicable to table column names..
And are you creating any SQL Statements dynamically where this can be a problem..
0
kmgingeeAuthor Commented:
none whatsoever,the totally weird thing is i am able to access these tables via OPENQUERY but not directly with a remote account.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> OPENQUERY but not directly with a remote account.

Kindly confirm whether they are different accounts or a single account or not..
0
kmgingeeAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
kmgingeeAuthor Commented:
Already tried this:
local login - NAM\21249   Remote user ->data_access   password:the password of data_access
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
kindly login to sybase using data_access account and confirm whether you are able to perform select on those tables.
If it works, then this impersonation also should work.

Did you get any errors while setting up Linked server and if so then try recreating the linked server again to verify it out..

Also try applying SP3 for your SQL Server 2005 since some issues related to Linked server were there in SP1 and SP2..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kmgingeeAuthor Commented:
checked: the data_access account does have select permissions on the sybase
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.