I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.
ERROR: Incorrect syntax near the keyword 'LEFT'
MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;
SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)
Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp
I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.
|||SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL
MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;
Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*
|||
Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:
http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx
You will get more help on the SSMA related issues there.
|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.
I need help with another query:
SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));
Error on lines : Line 11 ans 20
|||I tried aliasingqryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]
But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.
ACCESS QUERY:
UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));
STORED PROCEDURE:
CREATE PROCEDURE UpdChkNum
AS
BEGIN
SET NOCOUNT ON;
/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float
/*SELECT stmts for procedure*/
WHILE
BEGIN
SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum
UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto
and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))
BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END
fetch next
END
END
GO
