By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

converting queries from access 07 to sql server 05

P: 2
I'm in the process of converting numerous queries from access 07 to sql server 05 to improve runtime performance. Below is a sample of code and the error I'm getting.

INSERT INTO
[tblShipments] ( [AP SIC], [Load ID], [Shipment Id], [LMS Carrier Id], [Conf Mode], [SF Name], [SF Address], [SF City Name], [SF State/Province], [SF Postal/Zip Code], [SF Country], [ST Name], [ST Address], [ST City Name], [ST State/Province], [ST Postal/Zip Code], [ST Country], [Wgt Gross Planned], [Qty Planned], [Pickup Stop Seq], [Delivery Stop Seq], [Start Date], [Start Time], [Start TS], [End Date], [End Time], [End TS], [Pln Loading Date], [Pln Loading time], [Pln Unloading Date], [Pln Unloading Time], [SH Arv PU Loc TS], [SH Arv D Loc Date], [SH Arv D Loc TS], [Transport Means Grp], [Load Distance], [Out of Route Distance], [Total Miles], [SH Arv PU Loc Date], [Pln Unloading TS], [Depart PU EDI], [Arv D EDI], [LoadType], [Actual Ship Date], [SH Est AP LH Amt], [SH Est AR LH Amt], [SH Approved AP Amt], [SH Approved AR Amt], [FrctStndRte 1/0], [TCM Route Id], [SH Est Acs Amt], [SH Aprv Acs Amt] )
SELECT [T Shipments].[AP SIC], [T Shipments].[Load Id], [T Shipments].[Shipment Id], LTrim([T Shipments].[LMS Carrier Id]) AS Expr1, LTrim([T Shipments].[Conf Mode]) AS [Conf Mode], LTrim([T Shipments].[SF Name]) AS [SF Name], LTrim([T Shipments].[SF Address]) AS [SF Address], LTrim([T Shipments].[SF City]) AS [SF City], LTrim([T Shipments].[SF State]) AS [SF State], LTrim([T Shipments].[SF Zip]) AS [SF Zip], LTrim([T Shipments].[SF Country]) AS [SF Country], LTrim([T Shipments].[ST Name]) AS [ST Name], LTrim([T Shipments].[ST Address]) AS [ST Address], LTrim([T Shipments].[ST City]) AS [ST City], LTrim([T Shipments].[ST State]) AS [ST State], LTrim([T Shipments].[ST Zip]) AS [ST Zip], LTrim([T Shipments].[ST Country]) AS [ST Country], [T Shipments].[Wgt Gross Planned], [T Shipments].[Qty Planned], LTrim([T Shipments].[Pickup Stop Seq]) AS [Pickup Stop Seq], LTrim([T Shipments].[Delivery Stop Seq]) AS [Delivery Stop Seq], [T Shipments].[Start Date Ld], [T Shipments].[Start Time Ld], [T Shipments].[Start TS Ld], [T Shipments].[End Date Ld], [T Shipments].[End Time Ld], [T Shipments].[End TS Ld], [T Shipments].[Pln Loading Date], [T Shipments].[Pln Loading Time], [T Shipments].[Pln Unloading Date], [T Shipments].[Pln Unloading Time], [T Shipments].[SH Arv PU Loc TS], [T Shipments].[SH Arv D Loc Date], [T Shipments].[SH Arv D Loc TS], [T Loads].[TMG], [T Loads].[Load Distance], [T Loads].[Out of Route Distance], [T Loads].[Load Distance]+[T Loads].[Out of Route Distance] AS [Total Miles], [T Shipments].[SH Arv PU Loc Date], [T Shipments].[Pln Unloading TS], CASE WHEN([T Shipments].[SH PU Rcvd EDI 0/1],[T Shipments].[SH PU Rcvd EDI 0/1],CASE WHEN([T Shipments].[SH PU Rcvd Web 0/1],[T Shipments].[SH PU Rcvd Web 0/1],0)) AS [Depart PU EDI], CASE WHEN([T Shipments].[SH Arv D Loc EDI 0/1],[T Shipments].[SH Arv D Loc EDI 0/1],CASE WHEN([T Shipments].[SH Arv D Loc Web 0/1],[T Shipments].[SH Arv D Loc Web 0/1],0)) AS [Arv D EDI], LTrim([T Loads].[Load Type (VOR)]) AS [Load Type (VOR)], [T Loads].[Actual Ship Date], [T Shipments].[SH Est AP LH Amt], [T Shipments].[SH Est AR LH Amt], [T Shipments].[SH Approved AP Amt], [T Shipments].[SH Billed AR Amt], [T Loads].FrctStndRte, LTrim([T Loads].[TCM Route Id]) AS [TCM Route Id], [T Shipments].[SH Est Acs Amt], [T Shipments].[SH Aprv Acs Amt]
FROM
[T Shipments]
RIGHT INNER JOIN [T Loads]
ON
[T Shipments].[Load Id] = [T Loads].[Load Id]
GROUP BY [T Shipments].[AP SIC], [T Shipments].[Load Id], [T Shipments].[Shipment Id], LTrim([T Shipments].[LMS Carrier Id]), LTrim([T Shipments].[Conf Mode]), LTrim([T Shipments].[SF Name]), LTrim([T Shipments].[SF Address]), LTrim([T Shipments].[SF City]), LTrim([T Shipments].[SF State]), LTrim([T Shipments].[SF Zip]), LTrim([T Shipments].[SF Country]), LTrim([T Shipments].[ST Name]), LTrim([T Shipments].[ST Address]), LTrim([T Shipments].[ST City]), LTrim([T Shipments].[ST State]), LTrim([T Shipments].[ST Zip]), LTrim([T Shipments].[ST Country]), [T Shipments].[Wgt Gross Planned], [T Shipments].[Qty Planned], LTrim([T Shipments].[Pickup Stop Seq]), LTrim([T Shipments].[Delivery Stop Seq]), [T Shipments].[Start Date Ld], [T Shipments].[Start Time Ld], [T Shipments].[Start TS Ld], [T Shipments].[End Date Ld], [T Shipments].[End Time Ld], [T Shipments].[End TS Ld], [T Shipments].[Pln Loading Date], [T Shipments].[Pln Loading Time], [T Shipments].[Pln Unloading Date], [T Shipments].[Pln Unloading Time], [T Shipments].[SH Arv PU Loc TS], [T Shipments].[SH Arv D Loc Date], [T Shipments].[SH Arv D Loc TS], [T Loads].TMG, [T Loads].[Load Distance], [T Loads].[Out of Route Distance], [T Loads].[Load Distance]+[T Loads].[Out of Route Distance], [T Shipments].[SH Arv PU Loc Date], [T Shipments].[Pln Unloading TS], CASE WHEN([T Shipments].[SH PU Rcvd EDI 0/1],[T Shipments].[SH PU Rcvd EDI 0/1],CASE WHEN([T Shipments].[SH PU Rcvd Web 0/1],[T Shipments].[SH PU Rcvd Web 0/1],0)), CASE WHEN([T Shipments].[SH Arv D Loc EDI 0/1],[T Shipments].[SH Arv D Loc EDI 0/1],CASE WHEN([T Shipments].[SH Arv D Loc Web 0/1],[T Shipments].[SH Arv D Loc Web 0/1],0)), LTrim([T Loads].[Load Type (VOR)]), [T Loads].[Actual Ship Date], [T Shipments].[SH Est AP LH Amt], [T Shipments].[SH Est AR LH Amt], [T Shipments].[SH Approved AP Amt], [T Shipments].[SH Billed AR Amt], [T Loads].FrctStndRte, LTrim([T Loads].[TCM Route Id]), [T Shipments].[SH Est Acs Amt], [T Shipments].[SH Aprv Acs Amt];

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Aug 10 '09 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 145
can you repost your code in "[code]" tags and/or post lines 2,3,4 ?
Aug 12 '09 #2

Post your reply

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