Solved

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

Posted on 2004-10-07
18
1,211 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:vikingg97
  • 10
  • 8
18 Comments
 
LVL 19

Expert Comment

by:peh803
ID: 12251377
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12251600
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
 
LVL 19

Expert Comment

by:peh803
ID: 12251628
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12252427
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
 
LVL 19

Expert Comment

by:peh803
ID: 12253153
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12253799
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12258463
I still am having this problem?  Why is it an invalid character?  Very Confused?  PLEASE HELP?
0
 
LVL 19

Expert Comment

by:peh803
ID: 12258500
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
 
LVL 19

Expert Comment

by:peh803
ID: 12258509
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:vikingg97
ID: 12258570
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
 
LVL 19

Accepted Solution

by:
peh803 earned 500 total points
ID: 12258627
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12260257
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
 
LVL 19

Expert Comment

by:peh803
ID: 12260413
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12261396
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
 
LVL 19

Expert Comment

by:peh803
ID: 12261437
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
 
LVL 19

Expert Comment

by:peh803
ID: 12310498
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
 
LVL 1

Author Comment

by:vikingg97
ID: 12477938
Put the query in a stored proc
0
 
LVL 19

Expert Comment

by:peh803
ID: 12477986
Thanks for letting us know, vikingg97, I do appreciate it.  

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

Regards,
peh803
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now