I have a linked server using SQL Se

[bjw]

I have a linked server using SQL Server 7.0 to an Oracle database.

I can write the select statement using the Query Analyzer in the Enterprise Manager to query the Oracle database using:

select * from openquery([oracleremote], 'select * from task')

Oracleremote is the alias for the linked server. Task is the name of the table in the Oracle database.

How would I write this statement using ASP (Active Server Page)? [sig][/sig]

 

[TomSark]

It shouldn't matter whether you are using ASP, VB, or whatever... if it's a valid sql string, you can execute it.

So... why can't you set up the select in a command object and execute it? Simply have the result come back to you in a recordset that you process in VB Script...

Tom

 

[bjw]

Thanks Tom for responding.

So are you saying that assuming I have instantiated cmdTemp and Rs and of course the object to connect to the database the following should work?

cmdTemp.CommandText = "select * from openquery([oracleremote]" & ", " & "'" & "select * from task" & "'" & &quot"
Rs.Open cmdTemp, , 0, 1

 

[TomSark]

That's what I believe... As long as the query is sent to SQL Server, not Oracle, first, then it should work and you should get a row back...

I don't beleive I ever tried sending an openquery via a vb app before, but it should work...

Let me know if it doesn't work...

Tom

 

[bjw]

I'll let you know how things turn out.

Thanks!

 

[Guest_imported]

This does not work. The browser takes the current DB login on the SQL server and tried to authenticate on the Oracle server with those credentials. I have the same set up and no control of the Oracle server or my credentials on the SQL Server. Therefore, I set up impersonation on the linked server which is great for Query Analyzer but causes Active Server Pages error 'ASP 0113' when you try to use ASP pages to query the same thing. There is a problem with the ActiveConnection parameter....any ideas?