Kikus Kikus - 4 months ago 39
Java Question

JDBC errors when running a T-SQL query that uses OPENQUERY to access a linked server

I'm trying to execute a SQL Server query using prepared statements:



PreparedStatement pst = con.prepareStatement("select * from openquery(SERVERNAME," +
"'Select r.A , r.B, c.C from Y r" +
"INNER JOIN X c" +
"ON r.RNID = c.RNID ')" +
"where c.C in ?");

pst.setString(1, data);

ResultSet rs = pst.executeQuery();


I get this error message:


com.microsoft.sqlserver.jdbc.SQLServerException: Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SERVERNAME".


Update

After fixing the SQL statement to add the missing spaces

PreparedStatement pst = con.prepareStatement("select * from openquery(SERVERNAME, " +
"'Select r.A , r.B, c.C from Y r " +
"INNER JOIN X c " +
"ON r.RNID = c.RNID ') " +
"where c.C in ?");


I now get the error


com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "c.C" could not be bound

Answer

The table aliases used within the OPENQUERY function are simply not available to the query that calls it. So, this will fail with 'multi-part identifier "c.ID" could not be bound':

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c')
WHERE c.ID = 1

but this works

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c')
WHERE ID = 1

as does this

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c') AS x
WHERE x.ID = 1
Comments