This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 78%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
Our DBA ran a SQL Server 2019 Upgrade analysis against our current SQL 2016 server, which contains our ASPState database. The results showed that Image data type is deprecated. Is there an updated version of InstallSQLState.sql that creates the ASPState db and does not use the image data type?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 51%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYS%3C/text%3E%3C/svg%3E)
Hi @Morgan, Tony (morganaj) ,
Deprecated means that the functionality hasn't been removed, yet.Microsoft clearly indicated that ntext, text, and image data types will be removed from future SQL versions.However,the image data type (last I checked) was still available in SQL Server 2019.Also,I still suggest you change your datatype.
Best regards,
Yijing Sun
I understand what deprecated means, and we want to update to use varbinary instead, but the script that creates the database object for the ASPState database specifies using image data type. That is why my question was "Is there an updated version of InstallSQLState.sql that creates the ASPState db and does not use the image data type?". At least we can still use that, but we would rather update now than wait until it is removed from the schema in a future version.
Regards,
Anthony
Hi @Morgan, Tony (morganaj) ,
I think there are not any updated version of InstallSQLState.sql that create the ASPState db and do not use the image data type.
Best regards,
Yijing Sun
Isn't ASP just as old as the image data type? The image data type has been deprecated since SQL 2005, but I guess they had stopped developing ASP by then.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 95%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EM4%3C/text%3E%3C/svg%3E)
The ASPState database has nothging to do with the old ASP language, it is the name of the MS SLQ Session database.
It is not ASP the language it is the ASPState database that is a database MS supports for storing cached data. So they provide the scripst to create the database and setup permissions. So he cannot simply change the field to not be image, since that could break a whole lot of stuff in the SPROCS, etc. If MS is still making it an image field I would keep it and expect that at some point MS will provide an updated version of InstallSQLState.sql script once it is finally unsupported. It would be better for MS to provided an update script as soon as it was deprecated, but if they don't and the database works on 2019 with the script they did provide, then just go with it and use it.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 46%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAK%3C/text%3E%3C/svg%3E)
A typical session database looks like this:
So, back then, yes, they did indeed use image type for the larger/longer session Item. However, image type can still be used, and still be created in sql server 2019. I not tested, but perhaps a varbinary(max) will still work anyway?
So, such scripts that create the session database should in fact still work, and even work for 2019. You could even consider doing a database restore of the older one - see if that works.
But, from what I can find, the image type is still supported in 2019. So, down the road? Might become an issue, but it's not a deal breaker at this point in time.
I not had time to test, but I wonder if a varbinary(max) would work anyway?
I not had time to test, but I wonder if a varbinary(max) would work anyway?
@Albert Kallal , changing the data type to varbinary(MAX) in the tables and procs might work but is entirely unsupported so do so at your own risk. Microsoft provides the session state database DDL and ASP.NET session state database integration components as part of the .NET framework install.
I'm currently using asp.net state database with webforms - (SQL 2017). As noted in the comments here, while 2019 recommends avoiding image type, by all accounts it's still supported.
As noted, time permitting, I will see/test/try if varbinary(max) can be used in place of the image column. So, hopefully it can be, but 2019 still does allow creating of image column types, so either way, this can/should still work.
In fact, I'm using sql express. And keep in mind that the default scripts point to tempdb as to where the session table(s) are created. Since SQL Express does not have the user-agent (to schedule removal of the sessions, then we run a task once a day to do the same thing.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 12%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJL%3C/text%3E%3C/svg%3E)
You can find this MS sample for updated version of the script. (But you may still need a little fix to remove the image type left over inside the code).
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
aspstate is part of the aspnet 4.* framework. This framework is mature and only receiving security fixes. the old scripts are in the C:\Windows\Microsoft.NET\Framework64\v4.0.30319 folder.
you can safely change image to varbinary(max) in the table definition. this is the only change required to the scripts.
if you want to change the schema it is better to do it outside the utility. you can use the above script, or as I have done, create a visual database project from a generated database. you can then edit, and have a deployment package.
you might also want to change to the new async state provider
https://www.nuget.org/packages/Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync/