April 9, 2003 at 11:36 pm
Hopefully this won't be too long or too confusing... I can provide more info if needed.
I'm looking for any hints as to where the problems might be...
1.0 Moved all databases to a new server. Everything works fine except for a few queries in two different MDBs using
2 difference SQL databases
1.a To test, I made copies of the MDBs and created new DSNs pointing to the new server
1.b Changed all MDBs to use the new DSNs
1.c Data connectivity is using DAO 3.6
1.d Front ends are MS Access 2000 MDBS
1.e Database is SQL Server 7.0 Standard
2.0 Notes:
2.a The queries are select queries
2.b Both queries are similar in that they linked to SQL views, selecting personnel data from a 3rd SQL database
2.c Neither of the original linked views are updateable
2.d sysdatabases dbid's are not the same
2.e UserIDs are the same, SUserIDs are not
2.f sp_configure shows db settings are the same
2.g The databases are identical to original ones
2.h Test: modified the query to work on SQL Server directly - it worked fine
2.i Test: rewrote the query in Access (closer to what I think it should have been) - it worked fine
2.j Test: deleted the linked view and when I relinked it into Access, I selected a key field. After doing this the original
queries worked. This was only a test - I don't want to do this, as the views should not be updateable.
2.k I think the SQL error number was 107
3.0 Questions
3.a Should I worry about and try to solve this?
3.b Or should I simply redo the queries and continue, knowing there were some problems with the move?
3.c As a side note - Eventually I will rewrite all the queries and move them to SQL Server.
But should this influence my decision as to whether the move was successful?
Thanks for any help/hints
Marc
Edited by - marchanje on 04/09/2003 11:39:37 PM
April 10, 2003 at 5:03 am
I would log into the server wih the user being connected with thru Access. Then I would run those queries to see what effect that has. Eliminate first SQL side issues such as user restrictions and data first. Then let us know what happened.
April 10, 2003 at 6:47 am
I hate to say this, but I'm not sure what I need to do. I have run a trace on both boxes to see if I can see differences, but because of my lack of experience, I'm not sure what I'm looking at. I know this is not a forum for SQL server trouble shooting 101, but even some general hints towards what / how to research the root cause would be appreciated.
Thanks... Marc
April 10, 2003 at 3:48 pm
To the best of my abilities there are no permission problems.
Here are a few more details:
The tables / queries used (WeaponsDB.mdb to WeaponsDB SQL database)
Select * From tbl_WeaponType
Select * From tbl_WButtNum
Select * From tbl_WeaponDetail
Select * From tbl_Qualify
Select * From tbl_WeaponLocation
Select * From tbl_Weapons
Select * From qryPersoNelInfo
qryPersoNelInfo is an Access query that selects from qryPersoNNelInfo which is a linked view
that selects data from WeaponsDB and NewPDB (personnel database)
I can select each of the above tables/queries and get no errors
If I use them in the original query it aborts with the following error message:
The column prefix 'dbo.tbl_WeaponDetail' does not match with a table name or alias name used in the query. (#107)
If I change the the Inner join on qryPersoNelInfo to a Right join, the query works!
Original
===========================
SELECT qryPersonelInfo.Ssn,
qryPersonelInfo.FullName,
tbl_Weapons.WSerial,
tbl_WButtNum.WButtNum,
tbl_WeaponType.WeaponType,
tbl_WeaponLocation.WeaponLoc,
tbl_Qualify.QualifyType,
tbl_WeaponDetail.DateQualified,
tbl_WeaponDetail.DateReQualify,
tbl_WeaponDetail.WeaponScore,
qryPersonelInfo.Service_Abbrev,
qryPersonelInfo.SQ_CO_DE
FROM tbl_WeaponType
INNER JOIN (tbl_WButtNum
INNER JOIN ((((qryPersonelInfo
INNER JOIN tbl_WeaponDetail ON qryPersonelInfo.Ssn = tbl_WeaponDetail.SSN)
INNER JOIN tbl_Qualify ON tbl_WeaponDetail.QualifyID = tbl_Qualify.QualifyID)
LEFT JOIN tbl_WeaponLocation ON tbl_WeaponDetail.WeaponLocationID = tbl_WeaponLocation.WeaponLocationID)
INNER JOIN tbl_Weapons ON tbl_WeaponDetail.WeaponID = tbl_Weapons.WeaponID)
ON tbl_WButtNum.WButtID = tbl_Weapons.WButtID)
ON tbl_WeaponType.WeaponTypeID = tbl_Weapons.WTypeID
WHERE (((tbl_WeaponType.WeaponType)="M9"));
========================================================================
changed 1 Inner Join to an Right join and eliminated any nulls
(qryPersonelInfo.Ssn IS Not Null) and it works
========================================================================
SELECT qryPersonelInfo.Ssn,
qryPersonelInfo.FullName,
tbl_Weapons.WSerial,
tbl_WButtNum.WButtNum,
tbl_WeaponType.WeaponType,
tbl_WeaponLocation.WeaponLoc,
tbl_Qualify.QualifyType,
tbl_WeaponDetail.DateQualified,
tbl_WeaponDetail.DateReQualify,
tbl_WeaponDetail.WeaponScore,
qryPersonelInfo.Service_Abbrev,
qryPersonelInfo.SQ_CO_DE
FROM qryPersonelInfo
RIGHT JOIN (tbl_WeaponType
INNER JOIN (tbl_WButtNum
INNER JOIN (((tbl_WeaponDetail
INNER JOIN tbl_Qualify ON tbl_WeaponDetail.QualifyID = tbl_Qualify.QualifyID)
LEFT JOIN tbl_WeaponLocation ON tbl_WeaponDetail.WeaponLocationID = tbl_WeaponLocation.WeaponLocationID)
INNER JOIN tbl_Weapons ON tbl_WeaponDetail.WeaponID = tbl_Weapons.WeaponID)
ON tbl_WButtNum.WButtID = tbl_Weapons.WButtID)
ON tbl_WeaponType.WeaponTypeID = tbl_Weapons.WTypeID)
ON qryPersonelInfo.Ssn = tbl_WeaponDetail.SSN
WHERE (((tbl_WeaponType.WeaponType)="M9"));
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply