Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'dbo.CNTXDataFile' does not match with a table name or alias name used in the query.State: 42000
I now get this error after i have fixed the outer/inner error join based on the info I found on BOB.
I am using a SQL Server .prm file: odbc\sqlsrv.prm. I changed the left_outer and right_outer to be = to nothing, and I modified the following:
ANSI_92
Y.
I also, changed the parameter in designer to ANSI92 = Yes.
SELECT
dbo.COSORN.Legal_Name,
dbo.CNTXDataFile.ProcPeriod,
sum(dbo.CNTXDataFile.Credit)
FROM
dbo.COSORN INNER JOIN dbo.COSOtoCompany ON (dbo.COSOtoCompany.COSORN=dbo.COSORN.COSORN
AND dbo.COSOtoCompany.State=dbo.COSORN.State
AND dbo.COSOtoCompany.TaxType=dbo.COSORN.TaxType
AND dbo.COSOtoCompany.ProcPeriod=dbo.COSORN.ProcPeriod)
WHERE
( dbo.CNTXDataFile.AtlanTaxType=dbo.COSOtoCompany.TaxType )
AND ( dbo.CNTXDataFile.ProcPeriod=dbo.COSOtoCompany.ProcPeriod )
AND ( dbo.CNTXDataFile.State=dbo.COSOtoCompany.State )
AND (
dbo.CNTXDataFile.ProcPeriod = '200505'
)
GROUP BY
dbo.COSORN.Legal_Name,
dbo.CNTXDataFile.ProcPeriod
It just happens when I combine objects from different tables with dbo.CNTXDataFile. If I only use the one table, then I get no error messages. i have attached the SQL that BO is generating. Thanks for your help!.
If you look at the query the table dbo.CNTXDataFile is not showing up in the from clause, can you check the joins with this table to other two tables in the universe.
As I think you have realised the query will not work if the table does not appear in the from clause.
There is another edit of the PRM file you can try, where you set the Joins to FULL_ODBC, memory fails me exactly what lines you have to edit, but a search of BOB should turn up the info…
THe line that takes “Full_ODBC” is the line that I had originally changed to ANSI_92.
I will try to change to Full_ODBC.
The changes that I am making to the .prm file are only affecting my machine. Does this mean that I have to touch every full_client user to mimic my changes?
This is what we do to make the .prm file to read sql as per ANSI standard
Remove
LEFT_OUTER=$*
RIGHT_OUTER=*$
And Change
OUTERJOINS_GENERATION=USUAL
to
OUTERJOINS_GENERATION=FULL_ODBC
ALso since the .prm file resides locally on ecah user’s machine, you will ahve to touch each individual’s machine to make this change.
From 6 version onwards, you can make this change in the universe and so no need to touch every machine.
Thanks Reema. I am on ver 6.5.1, and I had to make the changes to the .prm file. I didn’t understand what you meant by your comment. can you please explain. thanks,
There is a setting in the parameters tab within Designer where you can set the ANSI values to Yes.
You will have to try it out and see if it works for you - I tried amending this setting in the past and it still didn’t generate the right SQL…
A search on BOB led me to this topic which exactly is the problem I am dealing with. Does anybody know the solution for this. A particular column from ABC table when added does not appear in the From clause and I get the same error: Database error: [Microsoft OLE DB Provider for SQL Server] : The column prefix ‘dbo.ABC’ does not match with a table name or alias name used in the query…
I have set ANSI92 to Yes in Designer (Using XI). I tried defining another parameter OUTERJOINS_GENERATION, but that did not help.
Hi - iif my memeory doesn’t fail me…
i just modified the ANSI92 = Yes and deleted the other lines that i did change in the process out of the parm file. save your universe, exit and login again and try it. Let me know if that helps.
You also need to make changes to the sqlsrv.prm file:
C:\Program Files\Business Objects\BusinessObjects Enterprise 6\dataAccess\RDBMS\connectionServer\odbc
Open the sqlsrv.prm file and make these changes:
ANSI_92
Y
(BOB member since 2005-01-06)