{"id":25,"date":"2008-12-04T21:53:28","date_gmt":"2008-12-04T21:53:28","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=25"},"modified":"2013-01-24T02:01:22","modified_gmt":"2013-01-23T15:01:22","slug":"case-sensitive-t-sql-tsql-variables","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2008\/12\/04\/case-sensitive-t-sql-tsql-variables\/","title":{"rendered":"Case Sensitive T-SQL (TSQL) variables"},"content":{"rendered":"<div id=\"ntsbl-1592119637\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>I had an interesting problem the other day.<\/p>\n<p>Someone ran a sql script I had written on their SQL2005 database and it threw an error like so:<\/p>\n<pre>Must declare the scalar variable \"@profileId\".<\/pre>\n<p>Upon investigating the only thing I could see that was wrong was I had declared the variable like so:<\/p>\n<p>[csharp]Declare @profileID as int[\/csharp]<\/p>\n<p>The issue was the case of the variables was different (the &#8220;ID&#8221; part of my identifier).<\/p>\n<p>Digging around I found the following truth&#8230;<\/p>\n<p>A T-SQL variable will default to the Collation of the master database. So if you chose a case sensitive collation when you installed SQL Server&nbsp; then the master database will have this collation and you will run into theses issues.<\/p>\n<p>This problem occurs regardles of the Collation used on the database you may be executing the SQL statement against.<\/p>\n<p>To check the case sensitivity of your master database, run:<\/p>\n<p>[sql]Exec master.dbo.sp_server_info[\/sql]<\/p>\n<p>and look at attribute 16 &#8211; IDENTIFIER_CASE, if it says SENSITIVE you have a collation like Latin1_Bin or Latin1_CS_AS (CS stands for Case senstive), MIXED is a case-insenstive collation.<\/p>\n<p>You cannot alter the collation of the master database, so it is backup time, uninstall\/re-install SQL server with a case insensitive Collation, (default) and restore your databases, if you want to be rid of this issue.<\/p>\n<p>Cheers<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I had an interesting problem the other day. Someone ran a sql script I had written on their SQL2005 database and it threw an error [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-25","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/25","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=25"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/25\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=25"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=25"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=25"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}