OPENQUERY Error on a linked server

Question

OPENQUERY Error on a linked server

Denis Wilkinson

Mr or Mrs. 500

Points: 569
More actions
December 19, 2005 at 8:07 am

#89080

Hi,
I am getting an error on a linked server when I run the following code:
SELECT *
FROM OPENQUERY(DIVS162, 'DIVS162.missingbrokenforeign.dbo.SelectSurveyDetailReport NULL, ''NEW'', ''2005-12-01'', ''2005-12-10'', 1, 0, NULL')
The error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'DIVS162.missingbrokenforeign.dbo.SelectSurveyDetailReport NULL, 'NEW', '2005-12-01', '2005-12-10', 1, 0, NULL'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=DIVS162.missingbrokenforeign.dbo.SelectSurveyDetailReport NULL, 'NEW', '2005-12-01', '2005-12-10', 1, 0, NULL'].
The error is normally associated with Insert, Update and Delete statements but this is a simple select statement. The code runs fine in QA without using the OPENQUERY format, but I need to include the recordset in a stored proc so need to use the OPENQUERY format.
Can anyone assist?
rgds
denis
December 22, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

Denis,

I think I've gotten this error when aremote procedure has columns or functionality that can not be determined until runtime (e.g., uses temp tables or dynamic SQL).

This is a shot in the dark. Try the following:

SELECT *

FROM OPENQUERY(DIVS162, 'SET FMTONLY OFF; exec DIVS162.missingbrokenforeign.dbo.SelectSurveyDetailReport NULL, ''NEW'', ''2005-12-01'', ''2005-12-10'', 1, 0, NULL')

I added a "set fmtonly off" and then called the stored proc with an "exec <procname>".

Scott Thornburg

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 2

Hi Scott,

Thanks.. that did it, you learn something new every day.

Have a great holiday weekend, I will now I can rest with that problem resolved.

Denis

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 3

Denis,

Glad it worked. You are correct -- there's no end to learning about SQL Server ... or life for that matter.

And I'm glad that I could contribute to a more relexed Christmas / Holiday weekend for you.

Scott

megankiew Valued Member Points: 57 More actions · Answer 4

I have a stored procedure 'getdatversions' on the linked server that has temp table and also update, insert operation towards the temp table. I used select * from openquery(DAEPO01, 'SET FMTONLY OFF; exec

DAEPO01.ePO_DAEPO01.dbo.getdatversions').

But i still got

Could not process object 'SET FMTONLY OFF; exec

DAEPO01.ePO_DAEPO01.dbo.getdatversions'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

Any help is much appreciated!

Megan

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 5

Megan,

If possible, could you post the code for the stored procedure? It appears that something specific within the procedure itself is the cause of your error message. The general approach of using SET FMTONLY OFF has worked for me many times.

Thanks,

Scott Thornburg

Bev P Valued Member Points: 69 More actions · Answer 6

I'm having the same problem. I'm trying to delete one row from an Oracle table by executing the following in query analyzer:

select * from openquery( MEPSD, 'SET FMTONLY OFF; delete FROM OEM_UPDATE_STAT WHERE MFR_ID = 163 ')

DELETE openquery( MEPSD, 'set fmtonly off; select mfr_id FROM OEM_UPDATE_STAT WHERE MFR_ID = 163 ')

I get the same error with each one. The Oracle dba created the table with a unique index.

Kbrown SSCommitted Points: 1794 More actions · Answer 7

Bev,

You post is many months back, but figure I'd still reply so others can see the solution. You're issue is different than the original poster.

Proper syntax is:

delete openquery (LINKEDSERVER,'select cols from table where 1=1)

This article explains the error message and proper syntax:

http://support.microsoft.com/kb/270119

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

WORKAROUND

You can work around this problem in the following ways:

1.	Use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations.
2.	As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider:

Steve Martin-365117 SSC Rookie Points: 41 More actions · Answer 8

Scott - just a quick note to throw in my thanks for the "SET FMTONLY OFF;" workaround it worked for me too - many thanks.

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 9

Steve,

You're most welcome. It's a good feeling to know that a post from almost a year ago is still useful to others.

For the record, my understanding is that when running OPENQUERY, SQL Server may (or even always -- not sure) obtain the metadata for the stored proc by implicitly using the SET FMTONLY ON command. This does not truly execute the proc, but simply returns its output schema. But here's the rub -- if the proc uses a temp table or dynamic SQL, the metadata may not be obtainable without truly executing the code. Thus the error comes back that the object (stored proc) has no columns.

To resolve this, the SET FMTONLY OFF prevents SQL Server from doing the partial metadata step, and instead fully executes the query and uses the actual returned schema.

This may be missing some aspects, but I think it covers the general idea.

Scott Thornburg

Mohan Ravi Valued Member Points: 61 More actions · Answer 10

Mohan Ravi

Valued Member

Points: 61

February 15, 2008 at 9:50 am

#778907

Thanks!, The "SET FMTONLY OFF;" made my life easy..

paksyeet SSCommitted Points: 1596 More actions · Answer 11

hi,

my problem is this script work fine;

select *

from openquery(MY_LINKEDSVR,'set fmtonly off;exec master.dbo.xp_fixeddrives')

go

but this script return the no column error;

select *

from openquery(MY_LINKEDSVR,'set fmtonly off;exec master.dbo.sp_helpdb')

go

error msg:

Server: Msg 7357, Level 16, State 1, Line 1

Could not process object 'set fmtonly off;exec master.dbo.sp_helpdb'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=set fmtonly off;exec master.dbo.sp_helpdb'].

the only difference is the stored procedure. same server.

please help..

jasonrose SSC Journeyman Points: 79 More actions · Answer 12

OMG THANK YOU!

Previously I had a query like...

SELECT

*

FROM

OPENQUERY(LINKED_DB, '

EXEC CAT.dbo.StoredProc blahblahblah

') a;

...and it worked JUST FINE for a while, then randomly went tits-up one day. Adding in the SET FMTONLY OFF got it to work again.

paksyeet, does sp_helpdb actually return anything? My understanding of SQL is limited (knowing just enough to get myself into trouble but not enough to get myself out ^^; ), but as I've just now found out, if the Stored Procedure performs a select that returns zero results, adding SET FMTONLY OFF will make it so it does not error, but if the Stored Procedure finishes without performing a select, returning back nothing, then you will still receive that error.

Hope this helps.

davidandrews13 SSCertifiable Points: 6820 More actions · Answer 13

hi.

i'm getting this error and i've added the SET FMTONLY OFF section as well.

here's the code that i'm running in SQL Query Analyzer:

select * from openquery ( [SEVERNAME] , ' SET FMTONLY OFF; exec SP_Name "email.email.com" , "SPPassword"')

this is the error:

------

Could not process object ' SET FMTONLY OFF; exec SP_Name "email.email.com","SPPassword"'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query= SET FMTONLY OFF; exec SP_Name "email.email.com","SPPassword"'].

-------

i've also tried the following code

[SERVERNAME]...SP_Name 'email.email.com','SPPassword'

but get the following error:

-----

Could not execute procedure 'create_user' on remote server 'ops-jtnet'.

[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

------

the stored procedure thats being called is on the mySQL Server "SERVERNAME" and is merely an insert statement to add user names to a mySQL table. i originally had the SP in microsoft SQL server but was getting the following error:

------

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' IRowsetChange::InsertRow returned 0x80004005: The provider did not give any information about the error.].

------

i have a linked server to this mySQL Server in SQL Sever

right clicking on this linked server and select 'properties' and the server options tab i have all ticks except for 'collation compatible'.

collation name is empty

connection timeout is 0

query timeout is 0

as a quick side note, i can call and run this stored procedure within 'mySQL Query Browser' as:

CALL SP_Name('email.email.com','myPassword')

thanks for reading this long post and hopefully someone will have the answer i'm looking for 🙂