Openquery works but 4 part name table don''t

Question

Post reply

Openquery works but 4 part name table don''t

Nelson Viggiani-332216

Ten Centuries

Points: 1264
More actions
December 28, 2006 at 11:56 am

#62397

select
* from openquery(LPUSA01,'select * from BZRCRP')     --- IT WORKS FINE
select * from LPUSA01.LPUSA01.EBSTSTNEW.BZRCRP          ---- IT DOESN'T work
     give me this error:
erver: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].
any ideas why?   since botth are using the same linked server and the first query ran whitout problems.
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Four part is Servername.DatabaseName.ObjectOwner.TableName

your database name is the same as the servername?

Something looks fishy, but not sure.

Sorry if I'm not much of a help.

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 2

select * from LPUSA01.LPUSA01.EBSTSTNEW.BZRCRP

this is a linked servr with AS400

first LPUSA01 is the linked server.

second LPUSA01 is the AS400 server name

third EBSTSTNEW is the catalog or Database

fourth BZRCRP is the table

it works fine in another server

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 3

Tom Goltl

SSCertifiable

Points: 5755

December 28, 2006 at 12:19 pm

#680344

Try:

select * from LPUSA01..EBSTSTNEW.BZRCRP

Tom

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 4

I tried that and nothing same error, maybe Ineed to update the MDAC ??

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 5

I'm assuming your using an ODBC datasource for your connection and the name of the datasource is LPUSA01. If not the the syntax should be.

linkedserver.datasourcename.library.table

Try that.

Tom

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 6

Yes ... the linked server is LPUSA01 and the server name is LPUSA01

That's my problem

I'm doing linkedserver.datasourcename.library.table

LPUSA01.LPUSA01.EBSTSTNEW.BZRCRP

work fine in anohter SQL server but does not work on the development server.

what Am I missing????

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 7

I'm just guessing now. Do you have the latest client access and patches? Have you tried deleting the datasource and recreating it. Do you have the same datasource name on your computer that points to something else. Are the login mappings correct.

Once again just guessing.

Tom

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 8

yes I do have all the patches up to date and client access also

select * from openquery(LPUSA01,'select * from bzrcrp') this work, same connection and work

select * from LPUSA01.LPUSA01.EBSTSTNEW.BZRCRP ...DO NOT WORK

doesn't make any sense to me...

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 9

I did a google search on the error and several of the articles mentioned it being a security mapping issue. Try mapping all users to a single 400 signon.

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 10

Well, this one works.

SELECT * FROM LPUSA01...BZRCRP WORKS !!!!

thanks

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 11

Hi Nelson,

The default behavior of the SQL Server is to parse the query locally. If you want the dynamic queries to be made and there parsing at the remote server, we use OPENQUERY() function. With this function, you are directing the SQL Server to parse and execute the query at the remote server (linked server in your case). With the first statement, you are actually sending the query to the linked server and the query is executed there, but with the second statement, SQL Server is trying to parse the query locally and hence it is throwing error.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

MohammedU SSCoach Points: 19644 More actions · Answer 12

What is the error you are getting when you are running 4 part name query?

MohammedU
Microsoft SQL Server MVP