Weird Left Join error when moving to SQL

[jimatqsi]

I have some code that works fine in an all Access environment. I am moving the back-end to SQL Server 2012. After moving the back-end I am getting this error when I run a particular query.

ODBC--Call Failed
[Microsoft][SQLServer Natrive Client 10.0][SQL Server]The multi-part identifier "ro.RoleID" could not be bound. (#4104) [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "ro.RoleID" cound not be bound.(#4104)

The error repeats itself, just like that. The text is slightly different when I use the SQL Server driver instead of the Native Client but the reported error is the same.

The query I want to run is PermittedPrograms_qry. PermittedPrograms_qry should be a join of the dbo_sysPermissions_tbl table to AllProgramRoles_qry on AppID, RoleID and ProgramID. In this case, I am trying to find what programs are not permitted, so I need to do a Left Join. INNER JOIN works fine, no problem. Left Join works in some cases and not in others.

There is some weird problem joining to the dbo_sysRoles_tbl table. AllProgramRoles_qry works fine by itself but when the error happens it seems to point back to a problem in that query.

These joins work:
1 Left join, RoleID
2 left joins, programID and AppID, missing RoleID
3 INNER Joins

These joins do not work and result in the error I showed above:
all 3 left joins
2 Left Joins, RoleID, ProgramID, missing APPID left join
2 Left Joins, AppID and RoleID, missing ProgramID left join

Note that joining on RoleID by itself works okay.

I've tried this, using both the SQL Native Client driver and the SQL Server driver. I've also tried it in SQL 2008 mode (I have tried this with both COMPATIBILITY_LEVEL = 100) and 2012 mode(COMPATIBILITY_LEVEL = 110).

Below you will find the SQL code for the various joins I've described. Note that all ID fields are integers.

Thanks for any advice you may have.

The SQL for the AllProgramRoles_qry is here. Note the "ro." that appears in the error messages. This query works fine by itself, and in some combinations below.

SELECT pr.ProgramID, pr.AccessType, pr.AppID, ro.RoleID
FROM dbo_sysPrograms_tbl AS pr, dbo_sysRoles_tbl AS ro
WHERE (((pr.Menu_YN)=True));
 

DOES NOT WORK
all 3 left joins

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID) AND (apr.RoleID = per.RoleID);
 

DOES NOT WORK
2 Left Joins, RoleID, ProgramID, missing APPID left join

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.RoleID = per.RoleID) AND (apr.ProgramID = per.ProgramID);
 

DOES NOT WORK
2 Left Joins, AppID and RoleID, missing ProgramID left join

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.RoleID = per.RoleID);
 

DOES WORK
1 Left join, RoleID

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON apr.RoleID = per.RoleID;
 

2 left joins, programID and AppID, missing RoleID

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID);
 

3 INNER Joins

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
FROM AllProgramRoles_qry AS apr INNER JOIN dbo_sysPermissions_tbl AS per ON (apr.RoleID = per.RoleID) AND (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID);
 
 

Interestingly enough, this code "works" in that it does not return an error, but nor does it return the correct recordset.

SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID, Nz([per].[RoleID],-1) AS Expr1
FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.ProgramID = per.ProgramID) AND (apr.AppID = per.AppID)
WHERE (((Nz([per].[RoleID],-1))<>[apr].[RoleID]));
 

[zmbd]

I've not gone thru all of your SQL yet; however, most common issues are reserved words for table and/or field names, mis-spelled table/field name, extra or missing spaces in the SQL string, incorrect placement of the alias (if used).... and... I'm sure I'm missing one or two more causes.

NeoPa had something like this too>
home > topics > microsoft sql server > questions > microsoft sql server native client 11.0 error #4104
wonder if this could be related??

[jimatqsi]

Thanks for plowing through all that and pointing me to a similar report. In NeoPa's case, I would be suspicious of the VBA function calls, as I could imagine the query engine in SQL might try to find that in the SQL library.

I wonder if those calls could be prefixed with "vba." to eliminate the problem in that instance. But other than the use of a home-grown function within his query, his case looks a lot like my own, so I doubt it.

I'm going to look again for possible keyword conflicts. I already tried renaming RoleID and that did not help. I've struggled with avoiding the joined queries/subquery idea but I don't see how that's gonna happen without building a temp table.

Thank you again!

[jforbes]

The only thing that I can suggest it to take the parenthesis out of the Joins as you don't need them and their processing by Access or SQL or both might be causing your error.

What happens when you paste these queries into SQL Server Management Studio?

[jimatqsi]

jforbes, that was a good suggestion. I rewrote the query I wanted in SQL and it works as it should. So I guess it's a problem on the Access side. It works like that and is probably faster.

I don't NEED to fix it now, but it bothers me a lot to not know what is going on and when it might happen again. Maybe there's some other driver I could try? Or some change I could make to the table connection string? Here is a typical one:
ODBC;Description=IBEW SQL MS ODBC 11 driver;DRIVER=ODBC Driver 11 for SQL Server;SERVER=xxx.xx.xx.xxx;UID=AccessUser;PWD=*** ****;APP=Microsoft Office 2013;DATABASE=IBEWG;StatsLog_On=Yes;

[Rabbit]

I use the "SQL Server Native Client xx.x" drivers that come when they install SQL Server Management Studio.