Change temporary datetime regional to SQL Server

If my database set to COLLATE Thai_CI_AS. Then, I select datetime format 106, result below

SELECT CONVERT(VARCHAR,GETDATE(),106)
Result: 26 พ.ค. 2008


If I want to select in english format, I must use command this

SET LANGUAGE us_english
SELECT CONVERT(VARCHAR,GETDATE(),106)
Result: 26 May 2008

SET LANGUAGE us_english
SELECT CONVERT(VARCHAR,GETDATE(),106)

SET LANGUAGE thai
SELECT CONVERT(VARCHAR,GETDATE(),106)

************************************************************

Case Study:
My problem, I created web application with ASP.NET and the application use datetime in format "dd-MMM-yyyy" for example "16-May-2008"(I set CultureInfo to "en-US" in Global.asax shown below).

void Application_BeginRequest(object sender, EventArgs e)
{
// Code that runs on application startup
System.Globalization.CultureInfo vNewCulture = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = vNewCulture;
}

In test environment I connected the application to SQL Server database(in same IIS). The server set regional language to "en-US". When I query data from database, result is nomally.

Then I migrate database to real database server. In the real database server set regional to Thai "th-TH". When the application query data from database server, I founded error.

I fixed this problem by insert "SET LANGUAGE us_english" at the beginning of query data to all query.

************************************************************

http://mark-dot-net.blogspot.com

How to send username and password in ftp url path

If you want to access to ftp://localhost/ with username 'myUserName' and password 'myPassword'. There are 3 method to access this ftp by explorer windows.

Method 1
Step1: Start -> run
Step2: Input ftp://localhost/
Step3: Input username and password into dialog box.
Step4: Access completed.

Method 2 Auto insert username on dialog box
Step1. Start -> run
Step2. Input ftp://myUserName@localhost/
Step3. Input only password into dialog box.
Step4: Access completed.

Method 3 Auto insert username and password in diaglog box.
Step1. Start -> run
Step2. Input ftp://myUserName:myPassword@localhost/
Step3. Access completed.

SET ANSI_NULLS ON Example

-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO

-- Print message and perform SELECT statements.
PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- Drop table t1.
DROP TABLE t1

Reference
http://msdn.microsoft.com/en-us/library/ms188048.aspx

Bug in SQL Server 2000 when create store procedure contain linked server

When I created store procedure command contained linked server in its. I founded error message below.

Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent querysemantics. Enable these options and then reissue your query.

Just insert the following commands before creating/updating your store procedure or function.

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE FUNCTION dbo.functionName
...
END

References
http://support.microsoft.com/kb/296769
http://www.nophadon.com/2004/03/18/error-7405-heterogeneous-queries-sql-server-2/

Get current datetime

SQL Server:

SELECT GETDATE()

SELECT {fn NOW()}

SELECT CURRENT_TIMESTAMP

Oracle:

SELECT SYSDATE AS Now FROM dual;

SELECT CURRENT_DATE AS Now FROM dual;

Reference
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
http://www.psoug.org/reference/date_func.html

Command for describe table

SQL Server

Syntax:

SP_HELP <tablename>

SP_COLUMNS <tablename>

Oracle
Describe an Oracle Table, View, Synonym, package or Function.
Note: that because this is a SQL*Plus command you don't need to terminate it with a semicolon.

Syntax:

DESC table

DESC view

DESC synonym

DESC function

DESC package