Hi,
I got one question about MSSQL2005.
I am using "Microsoft SQL Server Migration Assistant for Oracle" to migrate Oracle Database to Microsoft SQL server 20005.
[Oracle DB]
In Oracle DB, there are two records in table
In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.
In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.
SQL(1)
select xxx from CUSTOMER_ID='YT4996'
--> output 1 rows.
SQL(2)
select xxx from CUSTOMER_ID='YT4996 '
--> output 1 rows.
[MSSQL2005]
After migrate data to MSSQL, there are two identical records as Oracle DB.
In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.
In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.
SQL(1)
select xxx from CUSTOMER_ID='YT4996'
--> output 2 rows.
SQL(2)
select xxx from CUSTOMER_ID='YT4996 '
--> output 2 rows.
Question:
==> In Oracle DB, 'YT4996 ' <> 'YT4996'
==> In MSSQL2005, 'YT4996 ' = 'YT4996'
Do any setting, affect MSSQL2005, make 'YT4996 ' = 'YT4996' ?
I want MSSQL2005 behaviour as Oracle....
Anyone have hint?
You must have defined the datatype for the column as varchar. This will force sqlserver to strip the trailing blank spaces. If you change the datatype to char, the trailing spaces will be preserved.|||Thanks for info.
The column is defined as varchar(12).
but I cannot use char(12), since this column will contain variable width data, e.g. 'WT123', 'WT123456'.
If using char(12), all records in this column will become
'WT123 '
'WT123456 '
which will waste space when using char(12).
|||"Set Ansi_Padding ON" (non-default) before creating the column will force the trailing blanks not to be truncated. This setting needs to be set also during your execution if you decide to create varchar variables and preserve the trailing blanks.
Here is a good example (from bol):
Code Snippet
SET ANSI_PADDING ONGO
PRINT 'Testing with ANSI_PADDING ON'
GO
CREATE TABLE t1
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)
SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
GO
SET ANSI_PADDING OFF
GO
PRINT 'Testing with ANSI_PADDING OFF'
GO
CREATE TABLE t2
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)
SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
GO
DROP TABLE t1
DROP TABLE t2
GO
Thanks for your info.
I had test your script, it work.
<<<BUT>>>.......
I find this "SET ANSI_NULLS ON" is not work if the COULMN defined as NOT NULL.
Here is my original script for table [CUSTOMER_CREDIT]
===============================================
USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)
GO
If I had create the table like this:
CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NULL,
[CREDIT_ID] varchar(6) NOT NULL,
.....
If the COULMN [CREDITOR_ID] defined as NULL, "SET ANSI_PADDING ON" is work, such that I have two records:
[CREDITOR_ID] = 'ABCD ' , ie space after string "ABCD".
[CREDITOR_ID] = 'ABCD' , ie no space after string "ABCD".
But if the COULMN [CREDITOR_ID] defined as "NOT NULL", "SET ANSI_PADDING ON" is NOT work.
Any ideal?
|||Here is the script to create table and testing:
USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]') AND type in (N'U'))
BEGIN
DECLARE @.drop_statement varchar(500)
DECLARE drop_cursor CURSOR FOR
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
'.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name)
FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
WHERE fk.referenced_object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]')
OPEN drop_cursor
FETCH NEXT FROM drop_cursor
INTO @.drop_statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC (@.drop_statement)
FETCH NEXT FROM drop_cursor
INTO @.drop_statement
END
CLOSE drop_cursor
DEALLOCATE drop_cursor
DROP TABLE [MAIN].[CUSTOMER_CREDIT]
END
GO
USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE
[MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)
GO
===============================================================
SET ANSI_PADDING ON
GO
INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996', 'XX','2400','0','0','C','30',CURRENT_TIMESTAMP)
GO
SET ANSI_PADDING ON
GO
INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996 ','XX','0' ,'0','0','S','15',CURRENT_TIMESTAMP)
GO
SELECT 'VARCHAR'='>' + CREDITOR_ID + '<' FROM [MAIN].[CUSTOMER_CREDIT]
GO
