April 19, 2018 at 6:45 am
So, I made a mistake at my company yesterday. I was assigned to make some stored procedure fixes to one of our older applications, which I had never worked on before. I made the fix & sent it over to be deployed to production. What I didn't realize however, was the production server for this particular application, is still sql 2000 & there were 2 functions I used in my stored procedure that caused an error when they tried to deploy to production yesterday. Turns out, there's still a few apps using this old sql 2000 db that I was unaware of. They're looking into upgrading out of sql 2000 but it's a big task & still probably at least a year away.
So anyway, I was just wondering if anyone knows of a way to verify a stored proc is compatible with sql 2000? Is there perhaps a website or some software you can enter the code & it'll tell you if it's incompatible? Our problem here, is both our development & qa databases are sql 2012 HOWEVER, we don't have permissions to run anything on our sql 2000 production db. So I just have to sort of hope everything was compatible when deploying to prod.
Thanks
April 19, 2018 at 6:48 am
Goalie35 - Thursday, April 19, 2018 6:45 AMSo, I made a mistake at my company yesterday. I was assigned to make some stored procedure fixes to one of our older applications, which I had never worked on before. I made the fix & sent it over to be deployed to production. What I didn't realize however, was the production server for this particular application, is still sql 2000 & there were 2 functions I used in my stored procedure that caused an error when they tried to deploy to production yesterday. Turns out, there's still a few apps using this old sql 2000 db that I was unaware of. They're looking into upgrading out of sql 2000 but it's a big task & still probably at least a year away.So anyway, I was just wondering if anyone knows of a way to verify a stored proc is compatible with sql 2000? Is there perhaps a website or some software you can enter the code & it'll tell you if it's incompatible? Our problem here, is both our development & qa databases are sql 2012 HOWEVER, we don't have permissions to run anything on our sql 2000 production db. So I just have to sort of hope everything was compatible when deploying to prod.
Thanks
Yes:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017
Remember to change it back afterwards
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 19, 2018 at 7:53 am
ChrisM@Work - Thursday, April 19, 2018 6:48 AMYes:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017
Remember to change it back afterwards
That won't work for 2012+ versions, which is the question.
I believe the only option would be to be careful or test on a 2008 or previous version.
April 20, 2018 at 6:30 am
Just to add some specific content to this thread, here's some things SQL 2000 can NOT use:
1.) ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD, LAG, or any aggregate where you add an OVER clause, or any other windowed function.
2.) Common Table Expressions (aka CTEs)
3.) DATEFROMPARTS, DATETIMEFROMPARTS
4.) varchar(max) and nvarchar(max) data types - you would actually need to use text and ntext instead.
5.) Recursive queries, because of #2 above.
6.) DATETIME2, DATETIMEOFFSET, and associated data types
7.) APPLY operator - so no CROSS APPLY or OUTER APPLY
If that doesn't rather severely limit your options, I don't know what will, and it's probably not an exhaustive list.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 20, 2018 at 11:21 am
One way would be to create a VM running a version of windows that supports SQL Server 2000, install SQL Server 2000. Script the database and objects and run it on the VM. Fix each error and drop and try again.
April 20, 2018 at 11:37 am
sgmunson - Friday, April 20, 2018 6:30 AMJust to add some specific content to this thread, here's some things SQL 2000 can NOT use:1.) ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD, LAG, or any aggregate where you add an OVER clause, or any other windowed function.
2.) Common Table Expressions (aka CTEs)
3.) DATEFROMPARTS, DATETIMEFROMPARTS
4.) varchar(max) and nvarchar(max) data types - you would actually need to use text and ntext instead.
5.) Recursive queries, because of #2 above.
6.) DATETIME2, DATETIMEOFFSET, and associated data types
7.) APPLY operator - so no CROSS APPLY or OUTER APPLYIf that doesn't rather severely limit your options, I don't know what will, and it's probably not an exhaustive list.
8) Filtered indexes
9) System management Objects (views and functions)
10) PIVOT and UNPIVOT
11) Table valued constructors
12) Column encryption
13) XML, spatial and hierarchyid data types
14) GROUPING SETS, ROLLUP, and CUBE
15) MERGE
16) TRY...CATCH...(THROW)
17) INTERSECT & EXCEPT
And the list can go on.
http://www.sqlservercentral.com/articles/Development/2734/
April 23, 2018 at 10:49 am
Luis Cazares - Friday, April 20, 2018 11:37 AMsgmunson - Friday, April 20, 2018 6:30 AMJust to add some specific content to this thread, here's some things SQL 2000 can NOT use:1.) ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD, LAG, or any aggregate where you add an OVER clause, or any other windowed function.
2.) Common Table Expressions (aka CTEs)
3.) DATEFROMPARTS, DATETIMEFROMPARTS
4.) varchar(max) and nvarchar(max) data types - you would actually need to use text and ntext instead.
5.) Recursive queries, because of #2 above.
6.) DATETIME2, DATETIMEOFFSET, and associated data types
7.) APPLY operator - so no CROSS APPLY or OUTER APPLYIf that doesn't rather severely limit your options, I don't know what will, and it's probably not an exhaustive list.
8) Filtered indexes
9) System management Objects (views and functions)
10) PIVOT and UNPIVOT
11) Table valued constructors
12) Column encryption
13) XML, spatial and hierarchyid data types
14) GROUPING SETS, ROLLUP, and CUBE
15) MERGE
16) TRY...CATCH...(THROW)
17) INTERSECT & EXCEPTAnd the list can go on.
http://www.sqlservercentral.com/articles/Development/2734/
SQL 2000 did support GROUP BY WITH ROLLUP and GROUP BY WITH CUBE, but did NOT support GROUPING SETS. FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply