OPENQUERY Problem

Question

Post reply

OPENQUERY Problem

sstecher

SSC Eights!

Points: 997
More actions
August 17, 2004 at 2:23 pm

#108971

Hey Guys,
I'm trying to pull information off an AS400 with the following command. Does anyone see a problem with this? I keep getting errors.
/******************************************************************************************************************/
DECLARE @strJDELinkedServer VARCHAR(100)
DECLARE @strSQL   VARCHAR(7000)
DECLARE @strEXECSQL   VARCHAR(8000)
/******************************************************************************************************************/
SET @strJDELinkedServer = 'AS400 ODBC'
/******************************************************************************************************************/
SET @strSQL = 'CALL S10142CA.GENPRDCUST.SP9994(' + '''         050''' + ', ' + '''20040815''' + ')'
/******************************************************************************************************************/
SET @strEXECSQL = 'SELECT * FROM OPENQUERY([' + @strJDELinkedServer + '], ' + '''' + @strSQL + '''' + ')'
PRINT @strEXECSQL
/******************************************************************************************************************/
EXEC(@strEXECSQL)

Viewing 8 posts - 1 through 7 (of 7 total)

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

dbuchan SSCrazy Points: 2278 More actions · Answer 1

Try defining a linked server without a space in the name.

The systems fine with no users loggged in. Can we keep it that way ?br>

sstecher SSC Eights! Points: 997 More actions · Answer 2

That was one of my first thoughts. Still didn't work

sstecher SSC Eights! Points: 997 More actions · Answer 3

Sorry for being so verbose.

This is the following error I keep receiving

Could not process object 'CALL S10142CA.GENPRDCUST.SP9994(' 050', '20040815')'. 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=CALL S10142CA.GENPRDCUST.SP9994(' 050', '20040815')'].

Bruce Gilpin Mr or Mrs. 500 Points: 558 More actions · Answer 4

Bruce Gilpin

Mr or Mrs. 500

Points: 558

August 18, 2004 at 8:32 am

#519595

What driver are you using for the ODBC connection?

mbaker SSC Eights! Points: 901 More actions · Answer 5

"OpenQuery needs metadata about the columns at compile time"

http://support.microsoft.com/default.aspx?scid=kb;en-us;297368

If you perform a Google search of Microsoft.com using the search criteria:

"object has no columns" "linked server" site:microsoft.com

You will find several conditions where this error is returned due to the lack of information regarding the columns. The workaround from the above KB article is "To execute stored procedures, you can use four-part name distributed queries".

Hope this helps!

Mike

Carlos E. Mosquera SSC Enthusiast Points: 101 More actions · Answer 6

I got same problem trying to recive all fields from a DB2 database in AS/400 using a linked server on my openrowset queries.

For same table i recive diferent quantity of fields back, for example "SELECT FIELD1 FROM DB2TABLE" i'll recive 8500 in DB2 db, but using openrowset on my query analyzer i'll recive 8304, and if insert other field will recive 8289, or if i insert a where sentense will recive other number of fields.

The only usefull thing with openrowset is compare aggregate functions result.

For example: SELECT A.A AQTY, B.A BQTY

FROM OPENQUERY(AS400S, 'SELECT COUNT(FIELD1) A FROM SOMLIB.DB2TABLE WHERE SOMETHING') A,

(SELECT COUNT(FIELD1) A FROM SQLTABLE WHERE SOMETHING) B

Someone know why???

Carlos E. Mosquera

Lashams Ten Centuries Points: 1026 More actions · Answer 7

Hi, you could check out the Redbooks at http://www.redbooks.ibm.com/cgi-bin/searchsite.cgi?query=SQL

You can view all the redbooks online or download them at no cost, or purchase from IBM on CD-ROM.

There is a host of information in these books so you may find what you are after.