Extracting Currency Symbol and Measuring units from SQL Server 2000

Question

Extracting Currency Symbol and Measuring units from SQL Server 2000

SSC Journeyman

Points: 82

November 9, 2005 at 9:57 pm

Hi guys,Does anyone know how to extract the currency symbol and measurement unitsusing an ISQL query?For instance, in .NET I can very easily do the following -Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbolandSystem.Globalization.RegionInfo.CurrentRegion.IsMetricIs such a thing possible under SQL Server?CheersMB

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Regarding such things as the currency symbol, measurement units or number formats,

these are all used for formatting or conversions and, for the most part,

do not exists within the DBMS portion of SQL Server.

Interface tools such as Query Analyzer or Enterprise Manager do have options

to set the default behavior such as whether to use regional settings

to display currency, numbers, dates, and time but these are not part of the DBMS.

That said, language, input default date format, and first day of week

for a specific connection can be obtained by running DBCC USEROPTIONS

Language is the language to be used when messages are returned from the DBMS.

Dateformat is to to convert strings to the datetime datatype.

Datefirst indicates the specified first day of each week: 1 for Monday

, 3 for Wednesday, and so on through 7 for Sunday.

SQL = Scarcely Qualifies as a Language

mukesh r bhakta SSC Journeyman Points: 82 More actions · Answer 2

Hi Carl,

I don't mean to be rude but the information you have supplied is out of context to my question....

Can someone please point me in the right direction?

Cheers

MB

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Values of what datatype you are talking about?

_____________
Code for TallyGenerator

mukesh r bhakta SSC Journeyman Points: 82 More actions · Answer 4

I want the query to return me the currency symbol of the server's Regional Settings. I also want the Measurement system from the server.

All these values can be accessed in the GUI via Control Panel.

I want to know if these can be retrieved using a TSQL query or a sp in SQL Server 2000.

Sergiy SSC Guru Points: 110209 More actions · Answer 5

There were couple of articles about "Reading Windows registry from SQL server".

Run search with these keywords, you'll find out some of them.

This one, for example:

http://www.sqlservercentral.com/columnists/dasanka/workingwiththeregistry.asp

_____________
Code for TallyGenerator

mukesh r bhakta SSC Journeyman Points: 82 More actions · Answer 6

Thanks for that information.

I am pretty sure I do not have to go deep into the registry for this. Someone told me there should be a sp to get this information without much ado.

Sergiy SSC Guru Points: 110209 More actions · Answer 7

If you want to get value "from Control panel" you must read registry.

Anything else will give you settings of SQL Server, whatever else, they could be the same, could be different from Windows settings, but "Control panel" settings are stored in registry and nowhere else.

_____________
Code for TallyGenerator

Carl Federl One Orange Chip Points: 25384 More actions · Answer 8

Your original question:

And your follow up:

Let me be perfectly clear:

THIS CANNOT BE DONE AS THIS INFORMATION IS NOT STORED INSIDE OF SQL SERVER.

This information is only stored in the Windows Registry. You can read the registry of the server on which SQL Server is running by using the undocummented and unsupported extended stored procedure xp_regread.

It is also best practices NOT to use such information within the database management system. Formating and data conversions should be done in the application tier.

Take a look at the thread named "Collation Settings for a Database"

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=233221#bm233483

The problem arose because:

There were two application servers.

One server was using US settings and the other server was using British settings.

Conversion of strings to dates was being performed in the DBMS.

This entire problem could have been avoided by performing date conversions in the application tier.

SQL = Scarcely Qualifies as a Language