x

Vardecimal state without using sp_db_vardecimal_storage_format

The vardecimal state for any database (2005 SP2 onwards) can be either on or off. To find out this value you have to execute the sp_db_vardecimal_storage_format stored procedure with the relevant database name.

Eg EXECUTE sp_db_vardecimal_storage_format master

I can not find any relevant database property or column in the sys.databases view which relates to this value. It must be held somewhere. Does any one know where it? Or if it is not available by any othere means, why?,
more ▼

asked Jun 28, 2012 at 04:21 PM in Default

StuKay gravatar image

StuKay
170 9 11 13

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

This is internally stored by the version of the database. So it should be checked by the said procedure as recommended by Microsoft. But this is how you can get the information from databaseproperty

select name as 'Database Name', 
(case 
when databaseproperty(name, 'version') = 612 then 'ON'
when databaseproperty(name, 'version') is null then null
else 'OFF'
end) as 'Vardecimal State'
from master.dbo.sysdatabases
more ▼

answered Jun 28, 2012 at 04:40 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks for the reply but it does not seem to work. If I run sp_db_vardecimal_storage_format along side the code it shows the status to be ON for many databases whereas the code shows it to be OFF. The version number in these cases is generally 611
Jun 29, 2012 at 08:29 AM StuKay
@StuKay Are you running it in master database? I have not developed any thing fancy ;) This excerpt is taken from the procedure's definition itself, which exists in every database by default. You can see the procedure definition yourself, if you have the permissions. There you can sort out any difference with this query.
Jun 29, 2012 at 10:05 AM Usman Butt
Exactly as @Usman wrote, this is from the system stored proc itself. It is only on SQL 2005. On 2008+ it is not necessary and this SP on higher versions returns automatically ON for all user databases and OFF for system databases.
Jun 29, 2012 at 11:43 AM Pavel Pawlowski
All of your remarks have been beneficial. Thank you. I have also discovered you can not toggle the feature in SQL 2008. Master databases are always OFF and users databases are always ON. The recovery mode makes no difference. It makes you wonder why MS have bothered displaying it as an Option in the SSMS property page for a database other than the relevant SQL2005 versions. Note for SQL2008: This option always shows as TRUE in SSMS even for system databases! Do you think MS have just hard coded the word in the screen?
Jun 29, 2012 at 01:51 PM StuKay
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951

asked: Jun 28, 2012 at 04:21 PM

Seen: 1134 times

Last Updated: Jun 29, 2012 at 01:51 PM