SQL Compatability Level

SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it.

These compatibility levels reflect the version of SQL server.
* 60 = SQL Server 6.0
* 65 = SQL Server 6.5
* 70 = SQL Server 7.0
* 80 = SQL Server 2000
* 90 = SQL Server 2005


Here is a sample script that will show you the current compatibility level, set it to 90, then show you the current level after it has been set.

sp_dbcmptlevel dbname
go
sp_dbcmptlevel dbname, 90
go
sp_dbcmptlevel dbname
go


Once you change the compatibility level, you will want to be sure that your system still runs correctly.

How do you tell what version of SQL Server you are running.

The question came up as to which version of sql server is being run, and how do you determine the exact version number of the SQL server.

Here is what I have come up with. From TSQL if you are connected to the SQL Server, you can run the following command:


SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'


which should return something like the following:

SQL Server 9.00.4035.00 - SP3 (Standard Edition (64-bit))


Additionally if you are at the command prompt on the SQL server itself, you can run the following command:

osql -E -q"SELECT @@VERSION"

Dell Buying Experience / Customer Service FAIL!

I understand that outsourced customer support is a reality that isn’t going away any time soon but I never expected to receive that via on-line chat. Likely my naiveté. One could argue the issue here is with Dell’s pricing. It is one thing to offer a limited time special on high end machines that are very sector specific but when you sell the very same, almost commodity machine into different sectors, you better be pricing them the same or be prepared to deal with the fall out. I don’t think Dell dealt with this very well.

10:59:27 AM		Initial Question/Comment: Read about a $228 offer for small biz on a Mini 10v but can't find it. Had ordered a 10v just today. Can I get it?
11:04:07 AM	System	You are being transferred to another Agent. Please stand by...
11:04:12 AM	System	You are now being connected to an agent. Thank you for using Dell Chat
11:04:12 AM	System	Connected with QCSMB_Jeff_Chan
11:04:17 AM	QCSMB_Jeff_Chan	Thank you for contacting the Dell Small Business Sales chat line, my name is Jeff and I will be your sales agent today. If you have further questions once our chat session has ended, I can be reached by e-mail at jeffrey_l_chan@dell.com
11:04:29 AM	Andrew Robinson	ok
11:04:53 AM	Andrew Robinson	did you see my question?
11:05:11 AM	QCSMB_Jeff_Chan	These are only the promotion that we have.
11:05:12 AM	QCSMB_Jeff_Chan	http://www.dell.com/content/products/productdetails.aspx/laptop-inspiron-10?c=us&cs=04&l=en&s=bsd
11:05:46 AM	Andrew Robinson	I don't see what that promotion is. Just looks like list pricing.
11:06:12 AM	QCSMB_Jeff_Chan	What I can process is what is online right now?
11:06:17 AM	QCSMB_Jeff_Chan	Is there anything else that I can assist you with?
11:06:41 AM	Andrew Robinson	But I ordered 3 or 4 hours ago when there was supposedly a 228 special but I didn't get it.
11:08:10 AM	Andrew Robinson	How about you take a look into it and see if there is anything you can do. my email is arobinson@.....com my order number is 7602xxxxx and my dell customer number is 650xxxxx
11:08:31 AM	Andrew Robinson	I would really appreciate any help you can give me on this.
11:08:57 AM	QCSMB_Jeff_Chan	I can only give what is online right now.
11:09:29 AM	Andrew Robinson	I know you said that already. If there was a special earlier in the day during which time I placed my order why wasn't it made available to me.
11:09:30 AM	Andrew Robinson	?
11:09:48 AM	Andrew Robinson	Should I cancel my order. Can I do that with you?
11:10:08 AM	QCSMB_Jeff_Chan	And your customer # shows that you are under the consumer department. Let me connect you to consumer sales.
11:10:33 AM	Andrew Robinson	I AM a biz customer. have bought servers and workstations from you.
11:11:00 AM	Andrew Robinson	for my business.
11:11:02 AM	QCSMB_Jeff_Chan	If you want to cancel the order. Call consumer customer care department at 800-6249897
11:11:55 AM	Andrew Robinson	I don't want to cancel my order. I just want to be given the price that was advertised. Looks like I should be a biz customer and maybe that is why i wasn't offered the price.
11:12:00 AM	QCSMB_Jeff_Chan	I understand, but you placed the order under consumer.
11:12:11 AM	Andrew Robinson	and yes, I know that you can only offer what is on the web site now.
11:12:38 AM	Andrew Robinson	this all seems silly.
11:12:50 AM	QCSMB_Jeff_Chan	Is there anything else that I can assist you with?
11:13:15 AM	Andrew Robinson	yes, i have stated what I would like you to assist me with. can you transfer me to your supervisor. thx
11:13:37 AM	QCSMB_Jeff_Chan	I can only give what is online right now.
11:13:47 AM	Andrew Robinson	are you just copying and pasting?
11:14:02 AM	Andrew Robinson	are you just copying and pasting?
11:14:02 AM	QCSMB_Jeff_Chan	Nope, I'm typing
11:14:02 AM	Andrew Robinson	are you just copying and pasting?
11:14:21 AM	Andrew Robinson	ok. this seems silly. I feel like i AM talking to an automated machine.
11:14:37 AM	Andrew Robinson	seems that you (dell) kind of messed up on this. why should I suffer?
11:14:48 AM	QCSMB_Jeff_Chan	Nope, I'm a live person.
11:15:09 AM	Andrew Robinson	this is going to make a fascinating blog posting.
11:15:33 AM	QCSMB_Jeff_Chan	What is online is Base Package: Starting Price $299. I'm not showing any $228.
11:15:57 AM	Andrew Robinson	try googling it. there was a pricing at 228 this morning.
11:16:29 AM	Andrew Robinson	http://www.google.com/search?q=dell+mini+10v+%24228
11:19:34 AM	Andrew Robinson	Jeff, are you still there?
11:20:43 AM	QCSMB_Jeff_Chan	Yes, but that is not an official dell website, kindly refer to our website if you are looking for promotions.
11:20:48 AM	QCSMB_Jeff_Chan	http://www.dell.com/content/products/productdetails.aspx/laptop-inspiron-10?c=us&cs=04&l=en&s=bsd
11:21:28 AM	Andrew Robinson	ok, i will just now say goodbye and go and have a seizure. thanks for all the info!
11:21:43 AM	QCSMB_Jeff_Chan	Thank you for choosing Dell. Have a great day!
11:21:43 AM	System	QCSMB_Jeff_Chan has left this session!
11:21:43 AM	System	The session has ended!

/ScriptResource.axd : Invalid viewstate

After reviewing Microsoft's CLR code for errors (see last blog post) and finding none. I tried turning off the cross site scripting filter (XSS) in Microsoft Internet Explorer 8 by adding the

X-XSS-Protection: 0

response header in IIS custom headers tab. This didn't work. I made a guess that the browser might think that the /ScriptResource.axd inserted into HTML of the page was a XSS injection because of the querystring. However, this didn't work.

{6230289B-5BEE-409e-932A-2F01FA407A92}

/ScriptResource.axd : Invalid viewstate

Yet again I am back to this topic.

Internet Explorer 8 (IE 8) is causing a lot of intermintent "/ScriptResource.axd : Invalid viewstate" errors with our web site. Since our web site makes automatic error logs that send us for the pages with error I have the request information. One thing I noticed was that we are getting a lot of request that look like this:

/ScriptResource.axd?d=vSlJhKauG_vkeppFuk4O%2fseparator.gif

A correct reference to /ScriptResource.axd looks a lot like this:

/ScriptResource.axd?d=KPD5hEMt5pl2DUlO-HtW7uGyz9QToptIaomjT4Xh2Elw9iR4V4XA10Vyl8rymXiSQ2llJ9D-oDEkrTRdelC7CR5Q4yGTQBrdaeyHxDcCQ3w1&t=633251016437037680

Notice that there is a query string token that starts d= with a value that is a encrypted or hashed value. My hunch was that on the page request the web server was sending back HTML somewhat like this:

<script src="/ScriptResource.axd?d=KPD5hEMt5pl2DUlO-HtW7uGyz9QToptIaomjT4Xh2Elw9iR4V4XA10Vyl8rymXiSQ2llJ9D-oDEkrTRdelC7CR5Q4yGTQBrdaeyHxDcCQ3w1&amp;t=633251016437037680" type="text/javascript"></script>

However, the d value was encrypted in such a way that browser couldn't parse the token correctly. Maybe the token contained a raw > or a quote?

So I started up reflector and start looking in the CLR for the line of code that outputted this string. Note that this is CLR 2.0:

I found that line in: Assembly System.WebExtensions.dll, Namespace System.Web.Handlers, in a class called ScriptResourceHandler, which implemented the GetScriptResourceUrl of the System.Web.Handlers.IScriptResourceHandler interface.

The code in c# looks like this:

      if (assembly.GlobalAssemblyCache)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(first.Name);
            builder.Append(',');
            builder.Append(first.Version);
            builder.Append(',');
            if (first.CultureInfo != null)
            {
                builder.Append(first.CultureInfo);
            }
            builder.Append(',');
            builder.Append(HexParser.ToString(first.GetPublicKeyToken()));
            name = builder.ToString();
        }
        else
        {
            name = first.Name;
        }
        if (_absoluteScriptResourceUrl == null)
        {
            _absoluteScriptResourceUrl = VirtualPathUtility.ToAbsolute("~/ScriptResource.axd");
        }
        str = string.Concat(new object[] { _absoluteScriptResourceUrl, "?d=", ScriptResourceHandler.EncryptString((zip ? (notifyScriptLoaded ? "Z" : "z") : (notifyScriptLoaded ? "U" : "u")) + name + "|" + resourceName + "|" + culture.ToString()), "&t=", second.Ticks });

Notice that the first letter of the d value is a flag for requesting zipped and flagging the notification of the script loading, the next part is the assemble name that the resource is in (delimited by a |), then the culture string. This is all sent to Page.EncryptString to encrypt.

internal static string EncryptString(string s)
{
    byte[] bytes = Encoding.UTF8.GetBytes(s);
    return HttpServerUtility.UrlTokenEncode(MachineKeySection.EncryptOrDecryptData(true, bytes, null, 0, bytes.Length));
}

Page.EncryptString does these things:

1) UTF8 encodes the string to a byte array
2) Encrypts the byte array using the Machine Key
3) Calls the UrlTokenEncode method of HttpServerUtility

MSDN defines UrlTokenEncode as: Encodes a byte array into its equivalent string representation using base 64 digits, which is usable for transmission on the URL.

So I took a look at UrlTokenEncode method, which takes a Byte array and calls ToBase64String(). The output of ToBase64String() then is parsed for characters that would cause problems in a value of a URL querystring parameters. These characters are subsituted for a charcters that are safe for the querystring value. Looking over the character set of Base64 there is upper and lower case alpha, all the numbers, and +, / =. Note: http://www.ietf.org/rfc/rfc1421.txt. In UrlTokenEncode + and / are hanlded correctly. However, Base64 uses the = (equal) as padding. Which means it should only be on the end of the string, and the UrlTokenEncode method handles the = at the end. However, there is a very odd case in the UrlTokenEncode method. If there is an = in the middle of the string Base64 string then an = is outputted. When does Base64 return a = in the middle of the string? And if this is the case would this cause IE not to work correctly?

{6230289B-5BEE-409e-932A-2F01FA407A92}

Completly Removing ViewState WebFroms

Here is the code to completely remove view state from the page:

#region ViewState

protected override void SavePageStateToPersistenceMedium(object viewState)
{
}

protected override object LoadPageStateFromPersistenceMedium()
{
return null;
}

#endregion

Make sure that none of the controls in the page rely on ViewState to function properly.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Last Update For a Set Of Rows In SQL Server

I am displaying a gridview (table) of rows from the SQL Server and want to refresh the page when anything in any of the rows changes. One thing I always do when I create a new table is add a non-null timestamp column to the table called LastUpdate. I can use this column to figure out if any of the rows has changed with this Transact-SQL:

SELECT MAX(LastUpdate)
FROM T1

Since the timestamp column automatically gets larger (across the whole database) when there is an UPDATE or INSERT the MAX(LastUpdate) will always be bigger if there is a row added or updated on T1. But what about Deletes? Imagine we have this table:

Id                                     LastUpdate
------------------------------------   ------------------
FEB5F896-4F2E-47C9-AA63-0001C63CD3DC   0x00000000018F4590
6692BBC4-E3C7-4B9A-B8DC-00035CAD4D00   0x0000000001913542
018483F7-7EA1-4F0D-8CD2-0008CF4851F9   0x000000000193D0E0
45CC45AD-A6EB-4E54-B6B5-000AE2DF2E9E   0x00000000018F9161
B770834E-331E-416F-BBA0-000AE722C6D2   0x00000000018FE96D

SELECT MAX(LastUpdate)
FROM T1

Would Give Us: 0x000000000193D0E0

If We executed DELETE T1 WHERE Id = '45CC45AD-A6EB-4E54-B6B5-000AE2DF2E9E'

SELECT MAX(LastUpdate)
FROM T1

Would still give us: 0x000000000193D0E0, even though the table has changed.

The solution is to add the COUNT(1) of the query to the SELECT. Like This:

SELECT CONVERT(varchar(max),CONVERT(bigint,MAX(LastUpdate))) + '-' + CONVERT(varchar(max),COUNT(1))
FROM T1

Notice that I have to convert LastUpdate to a bigint, even though LastUpdate displays nicely in the SQL Server Management Studio, it really is a Byte array and concatenating to a Byte array is rather hard. Then I convert to a varchar(max) so that I can add the count on the end.

I stored the string in javascript on my web page and make an Ajax call back to the web server every so often to see if the string has changed. It it does I know that my rows have changed and can refresh the page.

{6230289B-5BEE-409e-932A-2F01FA407A92}