Querying two linked servers OPENQUERY with a INNER JOIN pulling back recordset

This is what I have so far for a query.  It returns records on T-SQL

SQLQUERYSTRING =

SELECT COUNT(*) FROM
OpenQuery(AS400,
'SELECT SUBNO, PERNO, cgrpid       
FROM hmodta.hmembp
WHERE cgrpid = ''S0038A''') as a INNER JOIN
OpenQuery(WEBSRVR2,
'SELECT Count(*), External_System_Key, User_ID
FROM mcnet..user_header
GROUP BY External_System_Key, User_ID')  AS p ON a.SUBNO + a.PERNO = p.External_System_Key

I want to use this query in ASP like this.  Is there some way I can access these 2 linked servers with this inner join?

Set rs = objConnection.execute(SQLQueryString)
     Do While not rs.eof
         Counts = trim(rs(0))
     rs.movenext
     Loop
rs.close
set rs = nothing

Thanks in advance!
vikingg97
LVL 1
vikingg97Asked:
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.

peh803Commented:
So this syntax isn't working for you?  What's the error you're getting?

SELECT COUNT(*) AS Count FROM
  (OpenQuery(AS400, 'SELECT SUBNO, PERNO, cgrpid FROM hmodta.hmembp WHERE cgrpid = ''S0038A''') as A
INNER JOIN
  (OpenQuery(WEBSRVR2, 'SELECT Count(*),
     External_System_Key, User_ID FROM mcnet..user_header
     GROUP BY External_System_Key, User_ID')) AS P
ON A.SUBNO + A.PERNO = P.External_System_Key
0
vikingg97Author Commented:
No I guess its not the syntax?   Here is the error...

Error Type:
HiT Software OLE DB Component for DB2/400 (0x80040E14)
HiTOLEDB400 rowset object - ODBS Prepare Error: Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE.
0
peh803Commented:
Something must be happening on the AS400 or WEBSRVR2 side, as SQL Server will see these are just data sets for manipulation.

So, the problem doesn't appear to be occurring on SQL server...the error doesn't look like a SQL server error, either.  

Can you try executing the two statements directly on their respective servers?  

Thanks,
peh803
0
Why Diversity in Tech Matter

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference ithrough the Colors of STEM program.

vikingg97Author Commented:
Yes that works.  It also works fine on T-SQL.

I know the SQL statement is fine, I just need to know how to read it into ASP pages.
0
peh803Commented:
How are you preparing the SQL String in ASP?  If you're doing it like you've got it in your intial post, it won't work.  It should be something like this:

SQLQUERYSTRING = "SELECT COUNT(*) FROM " & _
                               "  OpenQuery(AS400,  " & _
                               "  'SELECT SUBNO, PERNO, cgrpid " & _
                               "  FROM hmodta.hmembp " & _
                               "  WHERE cgrpid = ''S0038A''') as a INNER JOIN " & _
                               "  OpenQuery(WEBSRVR2, " & _
                               "  'SELECT Count(*), External_System_Key, User_ID " & _
                               "  FROM mcnet..user_header " & _
                               "  GROUP BY External_System_Key, User_ID') " & _
                               "  AS p ON a.SUBNO + a.PERNO = p.External_System_Key "

Perhaps you already know this?  Sorry if you do, just trying to figure out anything else that it could be...
peh803
0
vikingg97Author Commented:
Did it like that and got error:

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/Website/page.asp, line 64, column 42
SQLQUERYSTRING = "SELECT COUNT(*) FROM "&_  


Please help?
0
vikingg97Author Commented:
I still am having this problem?  Why is it an invalid character?  Very Confused?  PLEASE HELP?
0
peh803Commented:
Try executing this small vbscript snippet on your page and tell me what you get:

<%
Dim sSQL
sSQL = " SELECT COUNT(*) FROM " & _
                               "  OpenQuery(AS400,  " & _
                               "  'SELECT SUBNO, PERNO, cgrpid " & _
                               "  FROM hmodta.hmembp " & _
                               "  WHERE cgrpid = ''S0038A''') as a INNER JOIN " & _
                               "  OpenQuery(WEBSRVR2, " & _
                               "  'SELECT Count(*), External_System_Key, User_ID " & _
                               "  FROM mcnet.user_header " & _
                               "  GROUP BY External_System_Key, User_ID') " & _
                               "  AS p ON a.SUBNO + a.PERNO = p.External_System_Key "
Response.write "My SQL Code: "&sSQL
response.end
%>
0
peh803Commented:
Also, when you do this:
SQLQUERYSTRING = "SELECT COUNT(*) FROM "&_  

You need to make sure there's a "space" between the ampersand and the underscore:
not this: &_
but this: & _

Hope this helps,
peh803
0
vikingg97Author Commented:
OK I added the space between & _  and got passed the syntax error, but now I get the error I was getting earlier.  Here it is.

Error Type:
HiT Software OLE DB Component for DB2/400 (0x80040E14)
HiTOLEDB400 rowset object - ODBS Prepare Error: Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE.


This is what I get when I do a Response.Write SQLQUERYSTRING

SELECT COUNT(*) FROM OpenQuery(AS400,  'SELECT SUBNO, PERNO, cgrpid FROM hmodta.hmembp WHERE cgrpid = 'S0038A') as a INNER JOIN OpenQuery(WEBSRVR2, 'SELECT Count(*), External_System_Key, User_ID FROM mcnet..user_header GROUP BY External_System_Key, User_ID') AS p ON a.SUBNO + a.PERNO = p.External_System_Key

Help please.
0
peh803Commented:
Okay, glad you got past the syntax error.  Next step to determine why the error is occurring: Take apart your inner join statement in your ASP and see if you can call each individual query from your ASP.  Something like this:
<%

Dim sSQL, sSQL2
Dim rs1
rs1=server.createobject("adodb.recordset")
sSQL = " SELECT COUNT(*) AS MyCount FROM " & _
                               "  OpenQuery(AS400,  " & _
                               "  'SELECT SUBNO, PERNO, cgrpid " & _
                               "  FROM hmodta.hmembp " & _
                               "  WHERE cgrpid = ''S0038A''') "
sSQL2 = SELECT COUNT(*) AS MyCount FROM " & _
                               "  OpenQuery(WEBSRVR2, " & _
                               "  'SELECT Count(*), External_System_Key, User_ID " & _
                               "  FROM mcnet.user_header " & _
                               "  GROUP BY External_System_Key, User_ID') " & _
                               "  AS p ON a.SUBNO + a.PERNO = p.External_System_Key "

rs1.open sSQL, objConnection, 1, 3, 1
response.write "Statement 1: " & sSQL & "<BR>"
response.write "Count returned: " & rs1.fields("MyCount") & "<BR><HR>"
rs1.close
rs1.open sSQL2, objConnection, 1, 3, 1
response.write "Statement 2: " & sSQL2 & "<BR>"
response.write "Count returned: " & rs1.fields("MyCount") & "<BR>"
rs1.close

%>

Let me know what you get here...
peh803
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
vikingg97Author Commented:
Should I do a Set rs1=server.createobject("adodb.recordset")  ?  

Because I get an error
--Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'Open'
Which takes me to line--
rs1.open sSQL, objConnection, 1, 3, 1

However if I do the set, I end up with the same error I recevied above.

HiT Software OLE DB Component for DB2/400 (0x80040E14)
HiTOLEDB400 rowset object - ODBS Prepare Error: Token   was not valid. Valid tokens: ( <IDENTIFIER>.
0
peh803Commented:
yes, sorry, I should have had that in there....

My code was just incorrect.  I had this:

rs1=server.createobject("adodb.recordset")

where as you needed this:
set rs1=server.createobject("adodb.recordset")

Okay, so when you do the set, you still get the error?

like this....

run this and see what you get:
    rs1.open sSQL, objConnection, 1, 3, 1
    response.write "Statement 1: " & sSQL & "<BR>"
    response.write "Count returned: " & rs1.fields("MyCount") & "<BR><HR>"
    rs1.close

THEN, run this and see what you get:
   rs1.open sSQL2, objConnection, 1, 3, 1
   response.write "Statement 2: " & sSQL2 & "<BR>"
   response.write "Count returned: " & rs1.fields("MyCount") & "<BR>"
   rs1.close

Do either of these *not* error out?
0
vikingg97Author Commented:
They both give me the same error.

HiT Software OLE DB Component for DB2/400 (0x80040E14)
HiTOLEDB400 rowset object - ODBS Prepare Error: Token   was not valid. Valid tokens: ( <IDENTIFIER>.

Is there any other way I can do OpenQuery statements or should I keep trying to get this working?
0
peh803Commented:
You're executing these all against a SQL Server, right?  In that case, as long as the servers you're referencing in your OpenQuery command are set up properly in SQL Server and the SQL statements are all okay, it should work.  

You've verified that you can run the statement directly on the database server, so we know it's not the syntax issue.  There just seems to be a problem when try to call these statements from ASP code.  

I'm not sure what else to suggest.
peh803
0
peh803Commented:
Were you able to figure out the solution to your problem?  Please post it, if so.  I'm sure this knowledge is something that other users would be able to benefit from...

Thanks!!
peh803
0
vikingg97Author Commented:
Put the query in a stored proc
0
peh803Commented:
Thanks for letting us know, vikingg97, I do appreciate it.  

Perhaps it will be useful for future viewers to know this.

Regards,
peh803
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
ASP

From novice to tech pro — start learning today.