{"id":483,"date":"2012-06-19T11:03:00","date_gmt":"2012-06-19T11:03:00","guid":{"rendered":"\/blogs\/bobb\/post\/Does-everybody-get-that-(Type-system-compatibility-mode-in-drivers).aspx"},"modified":"2013-01-03T23:59:22","modified_gmt":"2013-01-04T07:59:22","slug":"does-everybody-get-that-type-system-compatibility-mode-in-drivers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/","title":{"rendered":"Does everybody get that? (Type system compatibility mode in drivers)"},"content":{"rendered":"

\nThis post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events<\/a> in August. See previous posting<\/a> for the reason behind the blog post title.\n<\/p>\n

\nThe SQL Server "MAX" data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) have been around for some time (SQL Server 2005) now. And the data types that they are meant to replace (TEXT, NTEXT, IMAGE) have been deprecated for just as long. The old data types somehow made their way in SQL Azure (sorry, Windows Azure SQL Database now), I was kind of shocked about that. It's a best practice to use the new data types.\n<\/p>\n

\nA student in one of my classes once reported, at the "best practice" point in the proceedings, that changing the database from IMAGE to VARBINARY(MAX) had broken<\/strong> an old application. They were (and wanted to continue to) use the new ODBC driver. Is there any way to use the new data types with old apps? He wasn't using the obvious culprits (READTEXT and friends) in the application.\n<\/p>\n

\nThe problem ended up being with the application's use of column metedata. The application was using that metadata to allocate a buffer based on the maximum size of the data type. IMAGE had reported this maximum size as 2GB, VARBINARY(MAX) was reporting -1. The reason for the -1 was because, in SQL Server 2008, VARBINARY(MAX) column with the FILESTREAM attribute can be larger than the usual 2GB limit. It actually reports -1 in drivers dating back to SQL Server 2005, guess they were planning for the future then.\n<\/p>\n

\nFortunately, there is a way to have the driver use the SQL Server 2000 type system and return 2GB as maximum size for the MAX data types. In ODBC, this setting is a connection string parameter "DataTypeCompatibility=80", in ADO.NET it's "Type System Version=SQL Server 2000". In answering a question on a mail list recently, I was surprised to find out that the latest JDBC driver does appear to lack such a setting.\n<\/p>\n

\nDoes everybody get that?\n<\/p>\n

\nLooking forward to seeing you in August…\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. The SQL Server "MAX" data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) have been around for some time (SQL Server 2005) now. And the […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-data-access"],"yoast_head":"\nDoes everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Does everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. The SQL Server "MAX" data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) have been around for some time (SQL Server 2005) now. And the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-19T11:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:22+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/\",\"name\":\"Does everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-06-19T11:03:00+00:00\",\"dateModified\":\"2013-01-04T07:59:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Does everybody get that? (Type system compatibility mode in drivers)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Does everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/","og_locale":"en_US","og_type":"article","og_title":"Does everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin","og_description":"This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. The SQL Server "MAX" data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) have been around for some time (SQL Server 2005) now. And the […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-06-19T11:03:00+00:00","article_modified_time":"2013-01-04T07:59:22+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/","name":"Does everybody get that? (Type system compatibility mode in drivers) - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-06-19T11:03:00+00:00","dateModified":"2013-01-04T07:59:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-type-system-compatibility-mode-in-drivers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"Does everybody get that? (Type system compatibility mode in drivers)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=483"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/483\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}