February 15, 2017 at 9:16 am
I got a new SQL 2016 server and have started restoring databases from a 2008 SQL Server.
When I do a test run of a stored procedure, I get no results and just "completed successfully" in 3 seconds. In 2008, I should be getting print messages and the exec time is about 2.5 minutes.
This particular SP uses linked server to Excel. To keep this short, there was some issues there that I fixed with the linked server. But I remembered there's a SQL Assessment / Upgrade Tool which analyzes databases, sps etc.., On the report, I see there are some non qualitified joins on them
| An example of "Unqualified join" is select * from table1, table2 where table1.col1 = table2.col1 Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins: |
iew
Do errors in the Assesment mean they will not work period? Why doesn't sql at least report some error message?
February 15, 2017 at 11:47 am
Sailor - Wednesday, February 15, 2017 9:16 AMI got a new SQL 2016 server and have started restoring databases from a 2008 SQL Server.When I do a test run of a stored procedure, I get no results and just "completed successfully" in 3 seconds. In 2008, I should be getting print messages and the exec time is about 2.5 minutes.
This particular SP uses linked server to Excel. To keep this short, there was some issues there that I fixed with the linked server. But I remembered there's a SQL Assessment / Upgrade Tool which analyzes databases, sps etc.., On the report, I see there are some non qualitified joins on them
An example of "Unqualified join" is
select * from table1, table2
where table1.col1 = table2.col1Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
- INNER JOINiew
Do errors in the Assesment mean they will not work period? Why doesn't sql at least report some error message?
I don't know which ones will just not work, but one thing you can count on is that between SQL 2008 and SQL 2016, the optimizer's behavior has changed, and some queries that ran fine before will now run rather poorly, and vice versa. Given the nature of the way that queries are dealt with where those queries have "unqualified joins", I'm pretty sure I wouldn't want too many of those hanging around anyway, as they often don't perform well to begin with. It may be that MS figured it was a good idea to call these out so as to help customers avoid issues, rather than simply making things break. I'm pretty sure that the implicit join syntax will be rather hard to get rid of, but if you at least start thinking about it, you'll probably get better results.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 11:47 am
I started hacking up the sp and found at the beginning there are a couple of checks to verify there's valid data in the spreadsheet and it stops processing if it's wrong and exits with an exit code...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply