August 22, 2006 at 12:47 am
Hi,
I was wondering if anyone might be able to help me here. I got the following error when trying to create a view for the following query. This query comes from Access. I have Access database and would like to migrate it to SQL Server 2000 SP4.
The query goes like this
SELECT PSQuery.System_Name, PSQuery.FO_Name, PSQuery.FO_Order, PSQuery.FS_Name, PSQuery.Purpose, ProjectQuery.Project_Name, ProjectQuery.[Project Code], ProjectQuery.[2005/6], ProjectQuery.[2006/7], ProjectQuery.[2007/8], ProjectQuery.Reason_Code, ProjectQuery.Completion_Date, [Framework Outline].Objective, [Framework Outline].References AS Expr1, PSQuery.PN_ID
FROM (ProjectQuery INNER JOIN PSQuery ON ProjectQuery.PS_ID = PSQuery.PS_ID) INNER JOIN [Framework Outline] ON PSQuery.FO_ID = [Framework Outline].FO_ID
WHERE (((ProjectQuery.[Project Code])="C" Or (ProjectQuery.[Project Code])="c" Or (ProjectQuery.[Project Code])="O" Or (ProjectQuery.[Project Code])="o") AND ((ProjectQuery.Reason_Code)="E"))
ORDER BY PSQuery.System_Name, PSQuery.FO_Order;
When i copied and pasted this query into the view, the error message said
ODBC error: [microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'C'
ODBC error: [microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'c'
ODBC error: [microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'O'
ODBC error: [microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'o'
ODBC error: [microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'E'
How do i get around this problem?
Thank you in advance
August 22, 2006 at 2:32 am
You need to enclose the project codes in single quotes, as it stands the query is looking for columns called C, c etc. because the double-quotes are column identifiers, should be;
WHERE (((ProjectQuery.[Project Code])='C' Or (ProjectQuery.[Project Code])='c'... etc...
hope this helps...
August 22, 2006 at 3:22 am
SELECTPSQuery.System_Name,
PSQuery.FO_Name,
PSQuery.FO_Order,
PSQuery.FS_Name,
PSQuery.Purpose,
ProjectQuery.Project_Name,
ProjectQuery.[Project Code],
ProjectQuery.[2005/6],
ProjectQuery.[2006/7],
ProjectQuery.[2007/8],
ProjectQuery.Reason_Code,
ProjectQuery.Completion_Date,
[Framework Outline].Objective,
[Framework Outline].Reference,
PSQuery.PN_ID
FROMProjectQuery
INNER JOINPSQuery ON PSQuery.PS_ID = ProjectQuery.PS_ID
INNER JOIN[Framework Outline] ON [Framework Outline].FO_ID = PSQuery.FO_ID
WHERE ProjectQuery.[Project Code] IN ('C', 'c', 'O', 'o')
AND ProjectQuery.Reason_Code = 'E'
ORDER BYPSQuery.System_Name,
PSQuery.FO_Order
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply