468,539 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

Weird Left Join error when moving to SQL

1,255 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT pr.ProgramID, pr.AccessType, pr.AppID, ro.RoleID
  2. FROM dbo_sysPrograms_tbl AS pr, dbo_sysRoles_tbl AS ro
  3. WHERE (((pr.Menu_YN)=True));
  4.  
DOES NOT WORK
all 3 left joins
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. 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);
  3.  
DOES NOT WORK
2 Left Joins, RoleID, ProgramID, missing APPID left join
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.RoleID = per.RoleID) AND (apr.ProgramID = per.ProgramID);
  3.  
DOES NOT WORK
2 Left Joins, AppID and RoleID, missing ProgramID left join
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.RoleID = per.RoleID);
  3.  
DOES WORK
1 Left join, RoleID
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON apr.RoleID = per.RoleID;
  3.  
2 left joins, programID and AppID, missing RoleID
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID);
  3.  
3 INNER Joins
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. 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);
  3.  
  4.  
Interestingly enough, this code "works" in that it does not return an error, but nor does it return the correct recordset.
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID, Nz([per].[RoleID],-1) AS Expr1
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.ProgramID = per.ProgramID) AND (apr.AppID = per.AppID)
  3. WHERE (((Nz([per].[RoleID],-1))<>[apr].[RoleID]));
  4.  
Dec 18 '15 #1
5 1408
zmbd
5,400 Expert Mod 4TB
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??
Dec 18 '15 #2
jimatqsi
1,255 Expert 1GB
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!
Dec 18 '15 #3
jforbes
1,107 Expert 1GB
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?
Dec 19 '15 #4
jimatqsi
1,255 Expert 1GB
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;
Dec 22 '15 #5
Rabbit
12,513 Expert Mod 8TB
I use the "SQL Server Native Client xx.x" drivers that come when they install SQL Server Management Studio.
Dec 22 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by StealthBananaT | last post: by
2 posts views Thread by michael | last post: by
9 posts views Thread by Alan Lane | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.