Moving queries from Access 07 to SQL Server 05

Question

Moving queries from Access 07 to SQL Server 05

jrw39

Mr or Mrs. 500

Points: 565
More actions
August 7, 2009 at 12:42 pm

#135188

I'm trying to run this code and I get the error below the code.
UPDATE [tblShipments]
LEFT OUTER JOIN [T Bill To 2] ON ([tblShipments].[Shipment Id] = [T Bill To 2].[Shipment Id]) AND ([tblShipments].[Load Id] = [T Bill To 2].[Load Id])
SET [tblShipments].[Bill To] = [T Bill To 2]![Bill To]
WHERE ((([tblShipments].[Bill To]) Is Null));
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'tblShipments'.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Please don't double-post.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jrw39 Mr or Mrs. 500 Points: 565 More actions · Answer 2

I apologize,

I have one additional question on the joins

this query is similar to the last I used your example but I'm still getting an error

before

UPDATE [tblShipments] LEFT OUTER JOIN [T Bill To].[ Conversion] ON [tblShipments].[Bill To] = [T Bill To Conversion].[Bill To] SET [tblShipments].[Bill To] = [T Bill To Conversion].[Conversion]

WHERE ((([tblShipments].[Bill To]) Like ’Post’));

after

UPDATE

[tblShipments]

SET [tblShipments].[Bill To] = [T Bill To].[Conversion]

FROM

[tblShipments]

LEFT JOIN [T Bill To.Conversion]

ON [tblShipments].[Bill To] = [T Bill To].[Conversion]

WHERE ((([tblShipments].[Bill To]) Like 'Post'));

parses fine then I get the following error when I try to execute it

Msg 208, Level 16, State 1, Line 1

Invalid object name 'T Bill To.Conversion'.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

jrw39 (8/7/2009)
I apologize,
I have one additional question on the joins
this query is similar to the last I used your example but I'm still getting an error
before
UPDATE [tblShipments] LEFT OUTER JOIN [T Bill To].[ Conversion] ON [tblShipments].[Bill To] = [T Bill To Conversion].[Bill To] SET [tblShipments].[Bill To] = [T Bill To Conversion].[Conversion]
WHERE ((([tblShipments].[Bill To]) Like ’Post’));
after
UPDATE
[tblShipments]
SET [tblShipments].[Bill To] = [T Bill To].[Conversion]
FROM
[tblShipments]
LEFT JOIN [T Bill To.Conversion]
ON [tblShipments].[Bill To] = [T Bill To].[Conversion]
WHERE ((([tblShipments].[Bill To]) Like 'Post'));
parses fine then I get the following error when I try to execute it
Msg 208, Level 16, State 1, Line 1
Invalid object name 'T Bill To.Conversion'.

Heh... c'mon... what word didn't you understand about not double posting? 😉 Double posting just splits resources and makes it so that people have to go to more than one spot if they need to find the answer to a similar problem. Stop doing it. Put your additional question with the other post where you got your first question answered.

For those who want to know the answer to the first question, it may be found at the following thread...

http://www.sqlservercentral.com/Forums/Topic767209-1291-1.aspx#bm767222

Please don't post to this thread anymore. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)