مجید اطلس باف

Previously I had a problem with my ASP .NET program which it's returned date format was not the appropriate one. As I've mentioned in the "Date Format in ASP .NET Web Application Served by IIS 6" post you can adjust your application date format with "globalization" element in "web.config", but I had another problem, although I changed the date format in my application but I had a problem when I wanted to submit a query to sql server and I got the following error when my query had a date in it's code:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

After searching a bit I found that the cause of this problem is the sql server date format. Although I had changed my application culture to "en-US" but my sql server default language was different. To find out your sql server default language, you can run the following query:

use master;
select name, alias, dateformat from syslanguages
where langid = (select value from master..sysconfigures where comment = 'default language')

The above query shows your sql server default language. But your database user might have another language. If you select "default" for user language during user creation, then the language of user will be set to the database default language, but you can select another language for your database user. To find out what is the default language for your database user, you should login with that user to the database and run the following command:

DBCC USEROPTIONS

the above query shows you the database user default language and dataformat.

After you have found that the user date format is not appropriate for you, you can change it by the following query:

EXEC sp_defaultlanguage 'USERNAME', 'english'

The above query changes the language of specified user to "english".

If you want to get information about a language properties in sql server (such as it's date format, month names, ...) you can run the following stored procedure:

sp_helplanguage

or you can specify your desired language as follows:

sp_sp_helplanguage english

So after changing my application culture to "en-US" using the globalization element in web.config and changing my sql server user language to "us_english" using the above query, all my date time problem were resolved.  :)
But there is a note about date format, if you use standard ISO date format that is "YYYYMMDD HH:MM:SS.mmm", then you will be independent of the language setting the sql database is configured in and everything works without doubt!

To read more about this subject visit the following pages:

• Date Format in ASP .NET Web Application Served by IIS 6
• How to Set the Day/Month/Year Date Format in SQL Server
• DBCC USEROPTIONS
• sp_defaultlanguage
• sp_helplanguage
• Numeric representation of Dates and Time

Sometimes it's necessary to know the length of a data column in SQL Server especially when you are working with variable length types such as varchar and text. To do this you can use simply DATALENGTH function in SQL Server. This function is used as follows:

DATALENGTH (expression)

and the parameter is an expression of any data type. This function is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. Also note that the DATALENGTH of NULL is NULL.

To read more about this subject visit the following page:

DATALENGTH (Transact-SQL)

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General. Each SQL Server collation specifies three properties:

• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text).
• The code page used to store non-Unicode character data.

So each SQL Server collation name is broken down into these parts:

sql_::sortrules::CodePage::ComparisonStyle

Comparison style segment allows specifying a collation's case sensitivity and accent sensitivity. Case sensitivity is determined by "CI" which means "case insensitive" and "CS" which means "case sensitive". If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses different ASCII codes for them. Accent sensitivity id determined by "AS" and "AI". "AS" means "accent sensitive" that is 'á' and 'ä' are treated  differently, but "AI" means "accent insensitive" which means that all "a", "á" and "ä" are treated the same way in comparisons.

To read more about this subject visit the following pages:

• SQL Server Collation Fundamentals
• SQL Server Architecture: Collations
• SQL Server and Collation
• SQL Server 2000 Collation - Searching and Ordering on Latin Characters

nslookup has a debug mode which shows detailed information about the domain. Just type "set debug" in nslookup command prompt and you get a lot of information about your desired domain. In the "Got answer" section of the results you can find the TTL of your domain. For example:

c:\>nslookup
>server ns2.dnslake.com
>set debug
>radcom.ir
....
Got answer:
 ANSWERS:
...
-> radcom.ir
internet address = 216.139.228.132
ttl = 60 (1 min)

UNIX machines like FreeBSD have a small utility called "scp" which is a utility which allows files to be copied between machines securely. Scp is an updated version of an older utility named "rcp". It works the same, except that information (including the password used to log in) is encrypted too.

The general form of this command is:

# scp source-specification destination-specification

For example:

# scp myfile myUser@sourceMachine:myFile

To copy a directory, use the -r (recursive) option. For example:

# scp -r myDir myUser@sourceMachine:myDir

To read more about this subject visit the following pages:

• Scp -- Secure File Transfer between UNIX machines
• Transferring Files with SFTP or SCP