Testing 2k code validity in 2k5 environment

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  February 24, 2011 at 4:52 pm

  #232652

  Briefly, I have a dev environment in 2k5 but the prod environment is 2k. The Dev db in question is set to 80 compatibility in the 2k5 environment. Avoiding the discussion of the foolishness of this, in our 2k5 dev environment we can run illegal scripts against the 2k database.

  To prove this:

  USE [master]

  GO

  /****** Object: Database [DB2kTester] Script Date: 02/24/2011 16:47:44 ******/

  CREATE DATABASE [DB2kTester] ON PRIMARY

  ( NAME = N'DB2kTester', FILENAME = N'C:\ssc\DB2kTester.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = N'DB2kTester_log', FILENAME = N'C:\ssc\DB2kTester_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

  GO

  EXEC dbo.sp_dbcmptlevel @dbname=N'DB2kTester', @new_cmptlevel=80

  GO

  IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

  begin

  EXEC [DB2kTester].[dbo].[sp_fulltext_database] @action = 'disable'

  end

  GO

  Then create this table, notice the illegal datatype for SQL 2k:

  CREATE TABLE testTable ( ttID INT IDENTITY( 1,1) NOT NULL,

  ttVCMax VARCHAR(MAX) NULL

  )

  If I want my app developers who are developing SQL (then getting sent up for peer review) to make sure they get errors for 2k syntax and methods, is it necessary for me to make sure dev is only against a 2k server, or am I missing some kind of setting I can have them wrap all statements in?

  This issue is primarily driven by local PC sandbox development which then migrates into our server deployment methods.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 24, 2011 at 5:37 pm

  #1291335

  No, compatibility mode won't help you there.

  For instance, aside from being able to use non-2000 data types, you can use CTEs, CROSS APPLY etc

  And even behaviours that are supposed to work - don't

  Under SQL2000, SELECT ISNUMERIC('121122,12') should return 0, but in compat. level 80 in sql 2005 it returns 1.

  Steer clear.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]