Convert *= to Left Outer Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert *= to Left Outer Join

Author

Topic

fubak
Starting Member

3 Posts

Posted - 2011-11-14 : 00:57:16

I'm upgrading from SQL 2000 to SQL 2005 and I have some queries that are giving me a headache. When I run the new one, I see the error below...

OLD:
SELECT *
FROM @tmpDetails td,
tblInvoices i (NOLOCK),
tblEmployees e (NOLOCK)
WHERE i.InvoiceID = td.InvoiceID
AND td.EmployeeID *= e.EmployeeID

NEW:
SELECT *
FROM tblInvoices i (NOLOCK),
tblEmployees e (NOLOCK)
LEFT OUTER JOIN @tmpDetails td (NOLOCK)
ON td.EmployeeID = e.EmployeeID
WHERE i.InvoiceID = td.InvoiceID

ERROR:
The multi-part identifier "e.EmployeeID" could not be bound

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 01:10:41

it should be


SELECT *
FROM @tmpDetails td (NOLOCK)
INNER JOIN tblInvoices i (NOLOCK)
ON i.InvoiceID = td.InvoiceID
LEFT OUTER JOIN tblEmployees e (NOLOCK)
 ON td.EmployeeID = e.EmployeeID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-11-14 : 09:14:56

I looked at your post because we are stuck in this same boat - having to convert *=.

Do you have this query? It will show you all the Stored Procedures and Functions you need to change (the good thing is that this will even check your Dynamic SQL but it can not distinguish between comments):

SELECT
  OBJECT_NAME(object_id), 
  (SELECT type_desc FROM sys.objects O WHERE O.Object_ID = M.OBJECT_ID), 
  *
FROM
  sys.sql_modules M
WHERE
  (Definition LIKE '%*=%' OR Definition LIKE '%=*%')
ORDER BY
  2, 1

For your original inquiry, I can't see why you would get an error for the New and not the Old. Try running the old on its own, perhaps something else is going on.

Still, I agree with visakh you want to Left Outer Join to 'e' rather than 'td'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 09:16:47

quote:
Originally posted by denis_the_thief

I looked at your post because we are stuck in this same boat - having to convert *=.

Do you have this query? It will show you all the Stored Procedures and Functions you need to change (the good thing is that this will even check your Dynamic SQL but it can not distinguish between comments):

SELECT
OBJECT_NAME(object_id),
(SELECT type_desc FROM sys.objects O WHERE O.Object_ID = M.OBJECT_ID),
*
FROM
sys.sql_modules M
WHERE
(Definition LIKE '%*=%' OR Definition LIKE '%=*%')
ORDER BY
2, 1

For your original inquiry, I can't see why you would get an error for the New and not the Old. Try running the old on its own, perhaps something else is going on.

Still, I agree with visakh you want to Left Outer Join to 'e' rather than 'td'.

thanks for posting this dennis. Really lot of people will benefit from this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fubak
Starting Member

3 Posts

Posted - 2011-11-14 : 09:18:55

I took another route. I went into SSMS, selected all of the SPs, then generated a 'Drop and Create' script. I changed the compatibility of the DB to SQL 2005, and ran it. I then went through and worked the errors one by one.

I did this in development of course :)

Kristen
Test

22859 Posts

Posted - 2011-11-14 : 11:55:18

Do you need


WITH (NOLOCK)

or is that SQL 2008?

Strongly (no, VERY strongly) recommend that you don't use NOLOCK.

If you have Reads blocking Writes then look at setting the database to READ_COMMITTED_SNAPSHOT (introduced in SQL 2005) and remove all the NOLOCKs.

If you don't know the potential dangers of using NOLOCK with dirty reads, missing rows, duplicate rows, and so on then suggest you read up on it

. There wasn't much in the way of workarounds in SQL 2000 ... best to get rid of them if you are moving to SQL 2005.

fubak
Starting Member

3 Posts

Posted - 2011-11-14 : 12:54:51

Thx Kristen!

Kristen
Test

22859 Posts

Posted - 2011-11-14 : 13:09:04

You might want to read this also (ignore the fact that its for SQL 2008, nearly all of it applies to upgrading to SQL 2005 too)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 13:15:35

quote:
Originally posted by fubak

Thx Kristen!

see

http://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-14 : 14:02:06

Where's the "LIKE" Options on the posts...

Do you UNDERSTAND WHY Kristens says this?

Somewhere..it became a "solution" for slow queries..not sure what part of the world THIS Swine flu came out of, but it's a hack by not addressing the real problems

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-14 : 14:30:17

quote:
Originally posted by X002548

Where's the "LIKE" Options on the posts...

It will be in the next upgrade </THUD!>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources