?
Solved

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

Posted on 2010-01-11
22
Medium Priority
?
1,088 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:kmgingee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 3
22 Comments
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26283408
It may be benificial if you can post your T-SQL query however have you tried qulifiying your object names use brackets [ ].
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26283552
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
 

Author Comment

by:kmgingee
ID: 26284340
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Expert Comment

by:JohnSansom
ID: 26284485
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
 

Author Comment

by:kmgingee
ID: 26284567
I am using MSDASQL...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26285671
Then try refering it as "L"."D"."dbo"."ID_POS"
0
 

Author Comment

by:kmgingee
ID: 26286127
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
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26286247
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
 

Author Comment

by:kmgingee
ID: 26297212
this is a linked server:BTW we checked: we do have select permissions both in SQL as well as in Sybase
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300074
Kindly check it for the login that is linking SQL Server and Sybase servers have access on both the ends..
0
 

Author Comment

by:kmgingee
ID: 26303701
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
 

Author Comment

by:kmgingee
ID: 26326240
hi:

anything on this??
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26333332
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
 

Author Comment

by:kmgingee
ID: 26352997
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26377800
Yes.. It is not applicable to table column names..
And are you creating any SQL Statements dynamically where this can be a problem..
0
 

Author Comment

by:kmgingee
ID: 26404184
none whatsoever,the totally weird thing is i am able to access these tables via OPENQUERY but not directly with a remote account.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26420750
>> OPENQUERY but not directly with a remote account.

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

Author Comment

by:kmgingee
ID: 26424080
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26424976
>> 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
 

Author Comment

by:kmgingee
ID: 26440844
Already tried this:
local login - NAM\21249   Remote user ->data_access   password:the password of data_access
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26442943
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
 

Author Comment

by:kmgingee
ID: 26485596
checked: the data_access account does have select permissions on the sybase
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question